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; ALTER USER metaplane_user 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 svv_all_schemas 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.
Metaplane supports both SSH and Reverse SSH tunnel setups. Use the checkbox in the modal shown below to use either option.
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 3 months ago