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 with row count statistics.
Make sure to generate a secure password and store it securely — you'll save it into Metaplane later.
-- Create Metaplane user and group CREATE USER metaplane_user PASSWORD 'ChangeMe123' SYSLOG ACCESS UNRESTRICTED; CREATE GROUP metaplane_group; ALTER GROUP metaplane_group ADD USER metaplane_user; -- Grant select to system table for row counts GRANT SELECT ON svv_table_info TO GROUP metaplane_group;
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 metaplane_group; GRANT SELECT ON ALL TABLES IN SCHEMA "schema" TO GROUP metaplane_group; ALTER DEFAULT PRIVILEGES IN SCHEMA "schema" GRANT SELECT ON TABLES TO GROUP metaplane_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 metaplane_group;' || '\n' || 'GRANT SELECT ON ALL TABLES IN SCHEMA "' || schema_name || '" TO GROUP metaplane_group;' || '\n' || 'ALTER DEFAULT PRIVILEGES IN SCHEMA "' || schema_name || '" GRANT SELECT ON TABLES TO GROUP metaplane_group;' AS single_schema_statement FROM information_schema.schemata WHERE schema_name not in ('information_schema', 'pg_catalog', 'pg_internal');
To grant table-level permissions, execute commands such as the following:
GRANT USAGE ON SCHEMA "schema" TO GROUP data_viewers; GRANT SELECT ON "schema"."table" TO GROUP data_viewers;
To add a source, navigate to the Connections page and click the Redshift button. A modal will prompt you for the credentials you just created, then allow you to save the connection for testing:
If you restrict Redshift access based on IP, you can follow these steps to whitelist the Metaplane IPs.
- In the Redshift dashboard, click on the desired cluster name.
- When viewing information for your Redshift cluster, click the Properties tab.
- Scroll down to the Network and security settings section.
- If Public Accessibility is not enabled, click Edit publicly accessible button then enable.
- Click VPC security group link.
- Click Edit inbound rules.
- Add the following IPs of Type
Updated 2 months ago