Source and destination database permissions for Amazon Redshift

User permissions on the source database

The following is an example of how to create an Amazon Redshift user with the permissions needed to connect to Tonic Structural.

We recommend that you use a backup as your source database instead of connecting directly to your production environment.

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

--create user
CREATE USER tonic_user WITH PASSWORD 'tonic_password';

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

--add SELECT GRANTs on all tables in each schema in the DB 
GRANT SELECT ON ALL TABLES IN SCHEMA public TO tonic_user;

--add SELECT GRANT on pg_catalog.svv_table_info
GRANT SELECT ON pg_catalog.svv_table_info TO tonic_user; 

User permissions on destination database

The destination database must exist before Structural can connect to it.

The required permissions for the user that Structural uses to connect to the destination database depend on whether the workspace preserves the source database ownership in the destination database.

By default, workspaces do not preserve the source ownership in the destination database. The destination user then requires the following permissions:

-- Allows viewing table metadata.
GRANT SELECT ON pg_catalog.svv_table_info TO tonic_destination_user;

-- Allows creation of temporary tables on the destination database during a session.
GRANT TEMPORARY ON DATABASE tonic_destination_database TO tonic_destination_user;

-- Allows creation of schemas and permanent objects within the destination database.
GRANT CREATE ON DATABASE tonic_destination_database TO tonic_destination_user;

-- If any schemas from the source already exist in the destination database and the destination user is not the owner, run the followingfor each relevant schema.
GRANT DROP ON SCHEMA schema_name TO tonic_destination_user;

However, if the workspace does preserve the source database ownership, then the destination database user must be a superuser who holds ownership and privileges of all schemas and tables.

--create a superuser
CREATE USER tonic_user createuser PASSWORD 'tonic_password';