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.
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.
--create a user. It's better to use a user with limited permissions as opposed to an rds_superuserCREATE USER tonic WITH PASSWORD 'tonic_password';--add USAGE GRANTs on all schemas in the DBGRANT 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 DBGRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO tonic;--add SELECT GRANTs on all sequences in each schemaGRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO tonic;
--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 PostgresGRANT azure_pg_admin TO tonic;--On Postgres RDSGRANT RDS_SUPERUSER TO tonic;--On Postgres (non-RDS)ALTER USER tonic WITH SUPERUSER;