Stitch Documentation
has moved!

Please update your bookmarks to https://www.stitchdata.com/docs

If you're not automatically redirected after 5 seconds, click here.

Creating a Stitch User for an EXISTING Redshift Connection

Important!
The info in this doc is only applicable to EXISTING Redshift connections. This means you've already replicated data from Stitch into your data warehouse.

If you HAVEN'T replicated data yet, click here.

The credentials used to launch a Redshift cluster belong to the master user, or admin, for the cluster. If you want to control the permission settings, you'll need to create an additional database user for Stitch.

Creating a Redshift user requires superuser permissions. If you’re not a Redshift superuser or you’re not sure what this means, please contact someone on your technical team before proceeding.

Required Redshift Permissions

To successfully pipe your data to your Redshift data warehouse, the Stitch user must:

  • Be able to CREATE schemas, tables, and views
  • Be able to SELECT from systems tables
  • Be the owner of all integration schemas and tables. We occasionally need to run COMMENT and ALTER TABLE commands to properly insert your data.

Restricting the Stitch user to a single schema or revoking public access to tables in the information schema will prevent Stitch from functioning properly. If you'd like guidance or more info on why we need these permissions, please reach out to us.

For reference, you can find the full list of Redshift permissions here.

Creating the Stitch User

You must be a superuser to create a Redshift user. Please contact your technical team if you're not sure what this means or how to proceed.

Note that the instructions below were tested using a Redshift instance with the default configuration.

First, connect to your Redshift instance using your SQL client. Note that anything inside square brackets - [like this] - is something you need to define when running the commands yourself.

After connecting, run this command to create the user:

create user [stitch_username] with password '[password]';

Next, you'll assign the CREATE permissions to the user. For [database name], enter the name of the Redshift database you connected to Stitch:

grant create on database [database_name] to [stitch_username];

If you restricted access to the information schema on your cluster, you'll also need to run this command to grant the Stitch user SELECT permissions:

grant select on all tables in schema information_schema to [stitch_username];

Transferring Ownership of Integration Schemas & Tables

When an integration is initially connected to Stitch, a schema specific to that connection is created in your data warehouse. Ownership to those schemas and all the tables contained within them must be granted to the Stitch user for data replication to be successful.

To see a list of all tables and their owners, you can query the catalogue tables using this statement:

select * from [stitch_username] pg_tables;

Take note of any integration tables that aren’t owned by the Stitch user - those are the tables you’ll need to update. Next, run this command this command for every schema created by a Stitch integration:

alter schema [schema_name] owner to [stitch_username];

In addition to granting schema ownership to the Stitch user, you’ll also need to transfer ownership of every table in the schema to the Stitch user. The following command will do this on an individual basis, but you can also use the script following it to simplify things.

alter table [table_name] owner to [stitch_username];

To make this step a little easier, you can run the following script from the Linux command line to detect every table in a single integration schema and assign ownership to the Stitch user:

for table in `psql -qAt --host [yourcluster.redshift.amazonaws.com] --port [port] --user [admin_user] -c "select tablename from pg_tables where schemaname = '[schema_name]';" [database_name]` ; 
do psql -qAt --host [yourcluster.redshift.amazonaws.com] --port [port] --user [admin_user] -c "alter table \"[schema_name]\".\"$table\" owner to [stitch_username];" [database_name] ; done

Repeat the steps in this section for every Stitch integration schema in your data warehouse.

Was this article helpful?
0 out of 0 found this helpful

Comments

Questions or suggestions? If something in our documentation is unclear, let us know in the comments!