Before you create a PostgreSQL workspace

The source and destination databases must exist before you connect to them. They should use the same version of PostgreSQL and must have the same extensions installed.

Use the following instructions to create database accounts that have the minimum required permissions that are needed for Tonic to function.

Creating a source database account

The following is an example of how to create a new user, called tonic, and grant the necessary permissions on the public schema.

For the source database, we recommend that you use a backup instead of connecting directly to your production environment.

If your database contains additional schemas that are included, then you must run the same commands for those schemas.

--create a user. It's better to use a user with limited permissions as opposed to an rds_superuser
CREATE USER tonic WITH PASSWORD 'tonic_password';

--ensure that the tonic user gets the correct access to new tables as they are added
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO tonic;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO tonic;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO tonic;

--add USAGE GRANTs on all schemas in the DB 
GRANT USAGE ON SCHEMA public TO tonic; 

--add SELECT and GRANTs ON ALL TABLES in each schema in the DB. 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tonic; 

--add USAGE GRANTs on all sequences in the DB 
GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO tonic; 

--add SELECT GRANTs on all sequences in each schema 
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO tonic; 

Creating a destination database account

The following is an example of how to create a new user, called tonic, grant the user ownership of the public schema, and make the user a superuser.

If your database contains additional schemas that are included, then you must run the same commands for those schemas.

--create a new user. 
CREATE USER tonic WITH PASSWORD 'tonic_password'; 

--make this user the schema owner of whichever schemas are in the database. 
ALTER SCHEMA public OWNER TO tonic; 

--allow user to create new schemas. 
GRANT CREATE ON DATABASE test_data_output TO tonic; 

--This user must be given the SUPERUSER role (or azure_pg_admin in Azure Postgres).
--The exact command depends on if you use Amazon RDS.

--On Azure Postgres
GRANT tonic TO azure_pg_admin;

--On Postgres RDS 
GRANT RDS_SUPERUSER TO tonic;

--On Google Cloud SQL Postgres
GRANT cloudsqlsuperuser to tonic;

--On Postgres (non-RDS) 
ALTER USER tonic WITH SUPERUSER;