Connect Single Origin to Redshift

Single Origin reads metadata only from your Amazon Redshift data warehouse: database, schema, table, view and column definitions, plus system catalog and usage information. It never reads the contents of your tables.

You connect Single Origin by creating a dedicated read-only user and role in your cluster and granting them metadata access. The setup is a short SQL script, and nothing leaves your environment.

What you're granting

You grant a dedicated role the following. None of these grant access to the data inside your tables.

REFERENCES is what keeps this metadata-only: it exposes the shape of each table (columns and types) but not the rows. Together with read access to the system catalog, Single Origin can profile the structure and usage of your warehouse without reading your data.

Before you start

You'll need:

• A superuser (for example, the cluster's admin user) to run the script.
• A strong password for the new service user (shown as <STRONG_PASSWORD> below), or IAM-based authentication if you prefer not to manage a password.
• The list of databases and schemas you want Single Origin to see.
• Your cluster's connection details: endpoint (host), port and database name.

Option 1: Base setup script

Run this once as a superuser (Redshift Query Editor v2 works well). Replace <STRONG_PASSWORD> with your value:

-- 1. Create a dedicated role and user for Single Origin
CREATE ROLE single_origin_role;

CREATE USER single_origin_user PASSWORD '<STRONG_PASSWORD>';
GRANT ROLE single_origin_role TO single_origin_user;

-- 2. Allow read access to system tables and views (object + usage metadata)
GRANT ROLE "sys:monitor" TO ROLE single_origin_role;

Option 2: Grant metadata access per schema

Run this block for each schema you want Single Origin to see. Replace <schema_name>:

GRANT USAGE ON SCHEMA "<schema_name>" TO ROLE single_origin_role;

GRANT REFERENCES ON ALL TABLES IN SCHEMA "<schema_name>" TO ROLE single_origin_role;

-- Include tables created in the future, so you don't have to re-run this later
ALTER DEFAULT PRIVILEGES IN SCHEMA "<schema_name>"
  GRANT REFERENCES ON TABLES TO ROLE single_origin_role;

Repeat the block for each schema in each database. To work on another database, reconnect the Query Editor to that database first. Grants apply to the database you're connected to.

Helper: generate the per-schema grants

Instead of writing the block by hand for every schema, run this query while connected to the target database (replace <database_name>). It generates the statements for all user schemas. Copy the output and run it:

SELECT
  'GRANT USAGE ON SCHEMA "' || schema_name || '" TO ROLE single_origin_role;' || chr(10) ||
  'GRANT REFERENCES ON ALL TABLES IN SCHEMA "' || schema_name || '" TO ROLE single_origin_role;' || chr(10) ||
  'ALTER DEFAULT PRIVILEGES IN SCHEMA "' || schema_name ||
    '" GRANT REFERENCES ON TABLES TO ROLE single_origin_role;'
FROM svv_all_schemas
WHERE database_name = '<database_name>'
  AND schema_name NOT IN ('information_schema', 'pg_catalog', 'pg_internal');

Export view definitions

1. Open the Redshift Query Editor.
2. Run the following query to list your view definitions.
3. When it finishes, click Export and save the result as CSV.
4. Send the file to your Single Origin contact.

SELECT
     schemaname as schema_name,
     viewname as view_name,
     definition as view_definition,
     viewowner as view_owner
FROM pg_views
WHERE view_owner != 'rdsdb'

Finish up

Once the script has run, send your cluster's endpoint, port, database name and the single_origin_user credentials to your Single Origin contact (or enter them in the onboarding flow). You may also need to allow network access to the cluster, usually through an interface VPC endpoint (PrivateLink) or by allowlisting Single Origin's IP range. Your contact can give you the details. Single Origin then connects and starts reading metadata. No further action is needed on your side.

Revoking access

You can remove Single Origin's access at any time. Run as a superuser:

DROP USER single_origin_user;
DROP ROLE single_origin_role;

Dropping the role removes every grant associated with it. Dropping the user ends Single Origin's ability to connect at once. If Redshift reports that the role or user owns objects or has dependent privileges, revoke those grants first, then drop.