Postgres is an object-relational database management system with an emphasis on extensibility and standards-compliance.
As a super user, execute the following SQL commands to create a group, a user assigned to that group, and permissions to access and monitor system tables.
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_1 WITH PASSWORD 'ChangeMe123'; CREATE GROUP metaplane_group; ALTER GROUP metaplane_group ADD USER metaplane_user_1; -- Grant Postgres' monitor role to the Metaplane group GRANT pg_monitor TO metaplane_group
Granting permissions to tables in a schema
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');
Granting permissions to one specific table
To grant table-level permissions, execute commands such as the following:
GRANT USAGE ON SCHEMA "schema" TO GROUP metaplane_group; GRANT SELECT ON "schema"."table" TO GROUP metaplane_group;
Adding Postgres source
To add a source, navigate to the Connections page and click the Postgres button. A modal will prompt you for the credentials you just created, then allow you to save the connection for testing:
Metaplane will always connect to your data warehouse from these static IP addresses:
Updated over 1 year ago