Postgres

Postgres is an object-relational database management system with an emphasis on extensibility and standards-compliance.

Create user

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:


Whitelisted IPs

Metaplane will always connect to your data warehouse from these static IP addresses:

  • 52.22.202.169/32
  • 34.199.237.80/32
  • 54.227.153.130/32
  • 34.230.219.122/32
  • 44.197.96.121/32
  • 34.206.79.174/32
  • 107.22.42.246/32

What’s Next
Did this page help you?