Connect Postgres

Depending on where your PostgreSQL server is hosted, you may need to take some additional steps in addition to the ones listed below. For example, on Amazon RDS, you may need to enable the appropriate security groups.

1. Create a PostgreSQL read-only user

Bigeye recommends creating a read-only user for connecting to your database. You can do this by following these steps using psql:

  1. Connect to your PostgreSQL server instance using the following command:
sudo -u postgres psql
  1. Select the database you would like to connect to Bigeye:
\c databasename;
  1. Create a new role for your Bigeye read-only user:
CREATE ROLE bigeye_read_only_user LOGIN PASSWORD '<yourpassword>';
  1. Grant the necessary privileges for the new user to connect to your
    database:
GRANT CONNECT ON DATABASE exampledb TO bigeye_read_only_user;
GRANT USAGE ON SCHEMA public TO bigeye_read_only_user;
  1. Run the following command to generate the queries that will grant select
    access to your new user:
SELECT 'GRANT SELECT ON '||schemaname||'."'||tablename||'" TO bigeye_read_only_user;' FROM pg_tables WHERE schemaname IN ('public') ORDER BY schemaname, tablename;
  1. Copy the GRANT statements generated by the query and then paste them into your terminal window. To restrict access to a subset of tables, only run the GRANT statements for the corresponding tables. You can also limit access to specific columns using schema.

Your new read-only user now has the necessary permissions for Bigeye to connect to your database.

📘

Default Privileges

To ensure that the Bigeye service account has access to schemas and databases that are created in the future, you will want to use ALTER DEFAULT PRIVILEGES to modify the default permissions.

Example SQL script to create the user and grant the appropriate permissions:

-- Create the role
CREATE ROLE bigeye_read_only_user LOGIN PASSWORD '<yourpassword>';

-- Grant the role the ability to connect to the database
GRANT CONNECT ON DATABASE "your-database" TO bigeye_read_only_user;

-- Grant the role the ability to "use" the schema
GRANT USAGE ON SCHEMA "your-schema-1" TO bigeye_read_only_user;
GRANT USAGE ON SCHEMA "your-schema-2" TO bigeye_read_only_user;

-- Change the defaults for new (future) tables in schemas
ALTER DEFAULT PRIVILEGES IN SCHEMA "your-schema-1" GRANT SELECT ON TABLES TO bigeye_read_only_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA "your-schema-2" GRANT SELECT ON TABLES TO bigeye_read_only_user;

-- Change the defaults for new (future) schemas in database
ALTER DEFAULT PRIVILEGES GRANT USAGE ON SCHEMAS TO bigeye_read_only_user;

-- Grant select on EACH existing table (using step 6 above to generate this)

GRANT SELECT ON TABLE "your-schema-1"."your-table-1" TO bigeye_read_only_user;
GRANT SELECT ON TABLE "your-schema-1"."your-table-2" TO bigeye_read_only_user;
GRANT SELECT ON TABLE "your-schema-2"."your-table-1" TO bigeye_read_only_user;
GRANT SELECT ON TABLE "your-schema-2"."your-table-2" TO bigeye_read_only_user;

2. Allow Bigeye's IP address

By default, PostgreSQL restricts connections to hosts and networks included in the pg_hba.conf file. You may need to add Bigeye’s IP address to this file to allow connectivity to your database; you’d add the line to pg_hba.config in the following format:

host databaseName username 35.163.65.120 md5

Here’s an example, where the username is bigeye_read_only and the database is mydatabase:

host mydatabase bigeye_read_only 35.163.65.120 md5

You may need to restart your PostgreSQL server for the changes to take effect.

For more details on modifying the pg_hba.conf file, consult the PostgreSQL documentation.

3. Add PostgreSQL as data source in Bigeye

From the Catalog tab in the sidebar, select Add Source. This will take you to a page where you can configure the connection to your database.

1266


To connect a database, you will need to input the following information:

  • Alias - identifying name for this data source
  • Vendor - Postgres
  • Hostname - name of the server that hosts your database
  • Port - 5432
  • Database name - your database name
  • Username - username of the Postgres read-only user you created previously
  • Password - password of the Postgres read-only user you created
  • Timeout

Once filled out, the form should look something like this

663

4. Next Steps

Bigeye will now start loading and profiling your tables. It can take up to 24 hours for the profiling to complete and your autometrics/autothresholds to populate. You can now proceed to deploying autometrics in the Getting Started guide.