Redshift

Create a Group and a User

As a super user, execute the following SQL commands to create a group, a user assigned to that group, and permissions to access a system table.

CREATE USER dot_user PASSWORD '<something secret>' SYSLOG ACCESS UNRESTRICTED;
ALTER USER dot_user SYSLOG ACCESS UNRESTRICTED;

CREATE GROUP dot_group;

ALTER GROUP dot_group ADD USER dot_user;

-- Grant select to system table for meta data
GRANT SELECT ON svv_table_info TO GROUP dot_group;

Grants Read Access to Data

Then for each schema schema, execute the following three commands to grant read-only access.

-- Grant usage on schema and select on current and future child tables
GRANT USAGE ON SCHEMA "schema" TO GROUP dot_group;
GRANT SELECT ON ALL TABLES IN SCHEMA "schema" TO GROUP dot_group;
ALTER DEFAULT PRIVILEGES IN SCHEMA "schema" GRANT SELECT ON TABLES TO GROUP dot_group;

Note: to programmatically generate these three queries for all schemas, you can use the following command. These commands still need to be executed.

SELECT 
    'GRANT USAGE ON SCHEMA "' || schema_name || '" TO GROUP dot_group;' || '\n' ||
    'GRANT SELECT ON ALL TABLES IN SCHEMA "' || schema_name || '" TO GROUP dot_group;' || '\n' ||
    'ALTER DEFAULT PRIVILEGES IN SCHEMA "' || schema_name || '" GRANT SELECT ON TABLES TO GROUP dot_group;' AS single_schema_statement
FROM svv_all_schemas
WHERE schema_name not in ('information_schema', 'pg_catalog', 'pg_internal');

Allow Dot IPs

If your organization uses a network policy to manage Redshift access, Dot will only access your Redshift through the following IPs:

• 3.229.110.216

• 3.122.135.165

1. In the Redshift dashboard, click on the desired cluster name.

1. When viewing information for your Redshift cluster, click the Properties tab.

1. Scroll down to the Network and security settings section.

1. If Public Accessibility is not enabled, click Edit publicly accessible button then enable.

1. Click VPC security group link.

1. Click Edit inbound rules.

1. Add the following IPs of Type Redshift:

• 3.229.110.216

• 3.122.135.165