PostgreSQL

📘

Whitelist IPs

If your database's network-access policy requires you to allowlist Polytomic's IP addresses, see Whitelist IPs.

1. Create a user on your PostgreSQL database. You supply this account's credentials to Polytomic. See the next section for required permissions and queries to execute.

2. In Polytomic, go to Connections → Add connection → PostgreSQL.

3. Enter host and credential details:

4. Choose your SSL settings. See SSL below.

5. Click Save.

SSL

Use SSL is on by default. When it's on, the SSL mode setting controls how Polytomic verifies your server's certificate:

For verify-ca and verify-full, Polytomic trusts the standard public certificate authorities and the Amazon RDS certificate authorities, so Amazon RDS and Aurora work without any extra setup. If your server uses a self-signed certificate or one issued by a private CA, upload the root certificate (PEM format) in the CA certificate field.

verify-full requires the Hostname you enter to match a name in the server's certificate. If you connect using an IP address, a load balancer, or a PrivateLink endpoint name, the hostname won't match, so use verify-ca instead.

🚧

Connections created before SSL modes were introduced

Connections saved before the SSL mode setting existed continue to use require until you choose a different mode.

Client certificates

If your server requires clients to present a certificate (mutual TLS), turn on Use client certificates and upload the Client certificate and Client key in PEM format.

Required PostgreSQL user permissions

Create a PostgreSQL user for Polytomic

These queries create a user for Polytomic with access to your PostgreSQL database:

CREATE USER polytomic_user WITH PASSWORD 'my_password';
GRANT CONNECT ON DATABASE my_database TO polytomic_user;
GRANT USAGE ON SCHEMA my_schema TO polytomic_user;

Read permissions

To read from PostgreSQL, the Polytomic user (denoted with the polytomic_user placeholder below) needs SELECT on the tables you want to sync. Run these queries for each schema you want to sync:

GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO polytomic_user;

-- Also grant SELECT on tables created in the future, so that Polytomic can
-- read them when you use "Automatically add new tables" in a Bulk Sync
ALTER DEFAULT PRIVILEGES IN SCHEMA my_schema GRANT SELECT ON TABLES TO polytomic_user;

ALTER DEFAULT PRIVILEGES only applies to tables created by the role that runs it. If other roles create tables in the schema, run it once for each of them with ALTER DEFAULT PRIVILEGES FOR ROLE table_creator IN SCHEMA my_schema ....

Write permissions

To let Polytomic also write to PostgreSQL, grant these additional permissions:

-- Allow Polytomic to create new schemas for Bulk Sync destinations
GRANT CREATE ON DATABASE my_database TO polytomic_user;

-- Allow Polytomic to create tables in an existing schema
GRANT USAGE, CREATE ON SCHEMA my_schema TO polytomic_user;

-- Allow Polytomic to write to existing tables (for example, Model Sync targets)
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON ALL TABLES IN SCHEMA my_schema TO polytomic_user;

📘

PostgreSQL 15 and later

Starting with PostgreSQL 15, users no longer have CREATE on the public schema by default. If Polytomic writes to public, include it in the GRANT USAGE, CREATE ON SCHEMA query above.

We recommend letting Polytomic create its destination tables. Bulk Syncs load data into a staging table, then drop the destination table and rename the staging table in its place. They also add, rename, and drop columns as your source schema changes. PostgreSQL only allows a table's owner to do these things. Tables that Polytomic creates are owned by the Polytomic user, so they need no further grants.

If a Bulk Sync must write to a table that already exists and was created by another role, transfer ownership to the Polytomic user:

ALTER TABLE my_schema.my_table OWNER TO polytomic_user;

Connecting via SSH

Polytomic supports connecting to PostgreSQL via SSH. See Connecting via an SSH tunnel for more information.