Note: Metaplane supports PostgreSQL hosted on-premise and/or in your cloud network.
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
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 metaplane_group; GRANT SELECT ON "schema"."table" TO GROUP metaplane_group;
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 supports both SSH and Reverse SSH tunnel setups. Use the checkbox in the modal shown below to use either option.
Metaplane will always connect to your database from these static IP addresses:
Updated 3 months ago