Tonic
Search…
PostgreSQL
In Postgres, the Source and Destination database can be housed on the same Postgres instance or on different instances. Below you will find details on how to input your connection details and how to create a Tonic user on both the source and destination databases with the minimum level of permissions needed for Tonic to function.
Both the source and output database must already exist.

Connecting To Your Databases

The connectivity details for a source and output are defined as part of the workspace. For Postgres, the source and output require similar details.
    Required
      Server: The host name or IP address of the PostgreSQL server instance. Tonic also supports connecting via Unix domain socket, in which case the Server value is the directory.
      Database: The name of the Postgres database.
      Port: The port number to connect to the server host. The PostgreSQL default is 5432.
      Username: The database account with the appropriate permissions in the source and destination.
      Password
    Optional (See Workspace Setup for more information on these settings)
      Enable SSL (enabled by default)
      Trust Server Certificate
      Use SSH Tunnel
      Show Client Certificate Settings
Create Workspace

Whitelisting IP Addresses for Hosted Tonic Application

If you are using Tonic's hosted application instance and your database only allows connections from whitelisted IPs, you'll need to add Tonic's static IP addresses.

Source Database Account Setup

The following is an example of how to create a new user, called tonic, and grant necessary permissions on the public schema. For the source database we recommend using a backup instead of connecting directly to your production environment.
If your database contains additional schemas that are being included then you must run the same commands for those schemas as well.
1
--create a user. It's better to use a user with limited permissions as opposed to an rds_superuser
2
CREATE USER tonic WITH PASSWORD 'tonic_password';
3
4
--add USAGE GRANTs on all schemas in the DB
5
GRANT USAGE ON SCHEMA public TO tonic;
6
7
--add SELECT and GRANTs ON ALL TABLES in each schema in the DB.
8
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tonic;
9
10
--add USAGE GRANTs on all sequences in the DB
11
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO tonic;
12
13
--add SELECT GRANTs on all sequences in each schema
14
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO tonic;
Copied!

Destination Database Account Setup

1
--create a new user.
2
CREATE USER tonic WITH PASSWORD 'tonic_password';
3
4
--make this user the schema owner of whichever schemas are in the database.
5
ALTER SCHEMA public OWNER TO tonic;
6
7
--allow user to create new schemas.
8
GRANT CREATE ON DATABASE test_data_output TO tonic;
9
10
--This user must be given the SUPERUSER role (or azure_pg_admin in Azure Postgres).
11
--The exact command depends on if you are using Amazon RDS.
12
13
--On Azure Postgres
14
GRANT tonic TO azure_pg_admin;
15
16
--On Postgres RDS
17
GRANT RDS_SUPERUSER TO tonic;
18
19
--On Google Cloud SQL Postgres
20
GRANT cloudsqlsuperuser to tonic;
21
22
--On Postgres (non-RDS)
23
ALTER USER tonic WITH SUPERUSER;
Copied!
Last modified 1mo ago