Connecting to Postgres

Privacy Dynamics can connect to your Postgres database. This guide helps you authenticate and authorize Privacy Dynamics to access your data in Postgres.

Requirements

To complete this guide, you will need the following:

• A Postgres database.
• A Postgres user with CREATEROLE privileges.
• A Privacy Dynamics account.

Instructions

Before you can connect to Postgres in Privacy Dynamics, you will want to create a new service account user and role for Privacy Dynamics to use.

Configure Your Postgres Database

The following script completes the setup for our service account. You will need to customize this with the names of your databases, schemas, and service account user, and then execute it in your database with a privileged user.

-- create the service account user and role
create user svc_pvcy password 'MyLongAndSecretPassword987654321';
create role anonymizer;

revoke all from role anonymizer;

-- CONFIGURE ORIGIN SCHEMA(s)

-- any existing schemas that you want us to read from need their own grants
-- we require usage on the schema and select on any tables/views in that schema
grant usage on schema raw_sensitive to anonymizer;
grant select on all tables in schema raw_sensitive to anonymizer;

-- optionally grant access to any future tables or views in source schema
alter default privileges 
    for role <user or role that creates tables in this schema>
    in schema raw_sensitive
    grant select on tables to anonymizer;

-- CONFIGURE DESTINATION SCHEMA(s)

-- Option 1: Allow us to create new schemas in the destination database
grant create on database dev to anonymizer;

-- Option 2: Allow us to write to an existing schema owned by another user
grant usage on schema privacy_safe to anonymizer;
grant create on schema privacy_safe to anonymizer;

-- GRANT ROLE TO USER
grant anonymizer to svc_pvcy;

Add the Postgres Connection in Privacy Dynamics

1. Sign in to your Privacy Dynamics account.
2. Go to the Connections page.
3. Select Add Connection.
4. Choose Postgres and select Next.
5. Enter the connection details:
   • Name - a name for you to identify the connection.
   • Host - the endpoint, without the port or database name.
   • Port - the port we use to connect to your database. The default is 5432.
   • Username - the username of the service account we created earlier (svc_pvcy).
   • Password - the password for the service account user.
   • Database - the name of the database you would like to use.
6. Select TEST CONNECTION to verify the credentials.
7. Select ADD CONNECTION and your connection saves if there are no errors.

Using SSH Forwarding

Privacy Dynamics supports the use of an SSH "jump box" or bastion host for Postgres connections. When creating the connection, select Use SSH, and then supply the SSH Host, SSH Port, SSH Username, and SSH Private Key fields. For more details and a tutorial on using SSH with Privacy Dynamics, please see this walkthrough video.

Other Configuration

If you have network access controls in place that limit connections to Postgres or your SSH bastion host, you will need to add Privacy Dynamics' IP addresses to your Allowlist. You can find those IP addresses in this public JSON file.