Generic Postgres

Prerequisites

• If your Postgres database is protected by security groups or other firewall settings, you will need to have the data-syncing service's static IP available to complete Step 2.

Step 1: Create read replica (optional)

You may choose to create a read replica to read from. Depending on your database and data characteristics, this may ensure that the Prequel service does not put any unnecessary load on your primary database during data transfer.

Step 2: Allow access

Create a rule in a security group or firewall settings to whitelist:

• incoming connections to your host and port (usually 5432) from the static IP.
• outgoing connections from ports 1024 to 65535 to the static IP.

Step 3: Create a read-only user

1. Open a connection to your Amazon RDS PostgreSQL database.
2. Create a user for Prequel by executing the following SQL command.

CREATE USER <username> PASSWORD '<some-password>' NOSUPERUSER NOCREATEDB NOCREATEROLE;

Grant user read-only access to all relevant schemas. Though public is the default schema, replace with the schema name if needed.

GRANT USAGE ON SCHEMA "public" TO <username>;
GRANT SELECT ON ALL TABLES IN SCHEMA "public" TO <username>;

4. Repeat the step above for all schemas with data that needs to be synced. (Replace "public" with the correct schema name)
   a. Note: if you prefer, you may instead grant usage only the desired tables.

GRANT USAGE ON SCHEMA "public" TO <username>;
GRANT SELECT ON <table_name_a> TO <username>;
GRANT SELECT ON <table_name_b> TO <username>;
GRANT SELECT ON <table_name_c> TO <username>;

Step 4: Add source to Prequel

Use the Admin UI or a cURL request to add the configured source to Prequel.

1. The name is a descriptive name of the source for your purposes (i.e., a description)
2. The host [for example, 6.7.8.9 or your-db.sd8jekhrlkhla.us-east-1.rds.amazonaws.com]
3. The port [most likely 5432]
4. The vendor: postgres
5. The database, from Step 2.postgres by default, or whatever database you prefer to use
6. The username from Step 3
7. The password from Step 3