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:
- Connect to your PostgreSQL server instance using the following command:
sudo -u postgres psql
- Select the database you would like to connect to Bigeye:
- Create a new role for your Bigeye read-only user:
CREATE ROLE bigeye_read_only_user LOGIN PASSWORD '<yourpassword>';
- Grant the necessary privileges for the new user to connect to your
GRANT CONNECT ON DATABASE exampledb TO bigeye_read_only_user; GRANT USAGE ON SCHEMA public TO bigeye_read_only_user;
- 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;
- 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.
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 184.108.40.206 md5
Here’s an example, where the username is
bigeye_read_only and the database is
host mydatabase bigeye_read_only 220.127.116.11 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.
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
Once filled out, the form should look something like this
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.
Updated 4 months ago