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 create a tonic user on both the source and destination databases with the minimum level of permissions needed for Tonic to function.

Source Database

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 or fast follower database 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.

--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';
--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;

Destination Database

--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 are using Amazon RDS.
--On Azure Postgres
GRANT azure_pg_admin TO tonic;
--On Postgres RDS
GRANT RDS_SUPERUSER TO tonic;
--On Postgres (non-RDS)
ALTER USER tonic WITH SUPERUSER;