PostGres
It's time to start the technical onboarding with Catalog. It's pretty simple; you need to grant access to your Postgres metadata to Catalog and to perform a couple of tests.
What is required to complete the onboarding ?
You need certain qualifications to complete the onboarding. You should be
🧑💻 A super user with the ability to impersonate all schema owners
🕙 Your Postgres version should be ≥ 11
When new schemas are created, part of the process will need to be repeated
What will Catalog do with this access ?
🔒 Catalog will only be granted USAGE on all schemas and REFERENCE on all tables. It is the minimum role for Catalog to read your metadata without accessing your data.
Need some more info ?
1. Whitelist Catalog's IP on Postgres
To allow Catalog to connect to Postgres, you will first need to whitelist Catalog's IP address:
Client whitelists Catalog's IP: 35.246.176.138
Catalog connects directly to your Postgres databases. As such, it needs to be public. It is frequent that our clients provides Catalog an access to an exact replica of their database.
2. Create Catalog User on Postgres
You are asked to enter credentials in Catalog's app Interface. Here is a step-by-step guide to create them. You can run the SQL code below.
[Find the whole procedure here]
2.1 Create procedure to grant rights on current content
The procedure grants USAGE, CREATE, REFERENCES rights to a user on existing content
CREATE rights are required to allow REFERENCES to work
REFERENCES are required for Catalog to see column names
❗️Catalog will not be creating schemas or referencing foreign keys with these rights
CREATE OR REPLACE PROCEDURE grant_rights_schema_today(user_name VARCHAR)
AS $$
DECLARE
row record;
sch text;
BEGIN
FOR row IN (
SELECT nspname
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
) LOOP
sch := row.nspname;
EXECUTE 'GRANT USAGE ON SCHEMA ' || sch || ' TO "' || user_name || '"';
EXECUTE 'GRANT CREATE ON SCHEMA ' || sch || ' TO "' || user_name || '"';
EXECUTE 'GRANT REFERENCES ON ALL TABLES IN SCHEMA ' || sch || ' TO "' || user_name || '"';
END LOOP;
END;
$$ LANGUAGE plpgsql;
2.2 Create procedure to grant rights on future content
The procedure extends the rights given in the procedure in 2.1 to future content
For each schema you'll impersonate the schema owner as only he can execute such procedure
CREATE OR REPLACE PROCEDURE grant_rights_schema_future(user_name VARCHAR)
AS $$
DECLARE
row record;
usr text;
sch text;
BEGIN
FOR row IN (
SELECT distinct tableowner,schemaname
FROM pg_tables
WHERE schemaname not LIKE 'pg_%'
AND schemaname != 'information_schema'
AND tableowner != 'rdsadmin'
) LOOP
sch := row.schemaname;
usr := row.tableowner;
EXECUTE 'ALTER DEFAULT PRIVILEGES FOR USER "' || usr || '" IN SCHEMA ' || sch
|| ' GRANT REFERENCES ON TABLES TO "' || user_name || '"';
END LOOP;
END;
$$ LANGUAGE plpgsql;
Here is an example of a statement of the loop, for schema tesla
own by user elon
: ALTER DEFAULT PRIVILEGES FOR USER elon IN SCHEMA tesla GRANT REFERENCES ON TABLES TO catalog
2.3 Create Catalog User
Use a safe password, from a password manager for example.
Password should not include @
or :
.
CREATE USER "catalog"
PASSWORD '<password>';
2.4 Grant rights to Catalog user
You can finally grant rights to Catalog user with procedures defined in 2.1 and 2.2
❗️ You will have to run these commands every time a new schema is created
CALL grant_rights_schema_today('catalog');
CALL grant_rights_schema_future('catalog');
3. Test the Catalog User
The Catalog User should now be correctly set up. Before you give Catalog the credentials, let's first run a test to make sure everything works well.
Connect to your Postgres cluster using the
catalog
credentials you've just createdRun the following checks:
3.1 Check schemas the Catalog user can see
SELECT
db.oid AS database_id,
db.datname AS database_name,
n.oid::TEXT AS schema_id,
n.nspname AS schema_name,
u.usename AS schema_owner,
u.usesysid AS schema_owner_id,
sd.description AS comment
FROM pg_catalog.pg_namespace AS n
CROSS JOIN pg_catalog.pg_database AS db
JOIN pg_catalog.pg_user u ON u.usesysid = n.nspowner
LEFT JOIN pg_catalog.pg_description sd ON sd.classoid = n.oid
WHERE TRUE
AND db.datname = CURRENT_DATABASE()
AND n.nspname NOT LIKE 'pg_%%'
AND n.nspname NOT IN ('catalog_history', 'information_schema')
You should get all of your Postgres schemas.
4. Add new credentials in Catalog App
You can now enter the newly created credentials in the Catalog App here.
Go to "Settings > Integrations"
Click on "Postgres Add"
Add the credentials

Appendix
Full code
CREATE OR REPLACE PROCEDURE grant_rights_schema_today(user_name VARCHAR)
AS $$
DECLARE
row record;
sch text;
BEGIN
FOR row IN (
SELECT nspname
FROM pg_namespace
WHERE nspname NOT LIKE 'pg_%'
AND nspname != 'information_schema'
) LOOP
sch := row.nspname;
EXECUTE 'GRANT USAGE ON SCHEMA ' || sch || ' TO "' || user_name || '"';
-- Create is needed references to work
EXECUTE 'GRANT CREATE ON SCHEMA ' || sch || ' TO "' || user_name || '"';
-- References allow the catalog user to see columns names
-- It also allows it to reference foreign keys, but we will never do that
EXECUTE 'GRANT REFERENCES ON ALL TABLES IN SCHEMA ' || sch || ' TO "' || user_name || '"';
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- This second procedures ensure that the catalog
-- user can get meta data on FUTURE content.
-- Such grant must be done by the future CREATOR
CREATE OR REPLACE PROCEDURE grant_rights_schema_future(user_name VARCHAR)
AS $$
DECLARE
row record;
usr text;
sch text;
BEGIN
FOR row IN (
SELECT distinct tableowner,schemaname
FROM pg_tables
WHERE schemaname not LIKE 'pg_%'
AND schemaname != 'information_schema'
AND tableowner != 'rdsadmin'
) LOOP
sch := row.schemaname;
usr := row.tableowner;
EXECUTE 'ALTER DEFAULT PRIVILEGES FOR USER "' || usr || '" IN SCHEMA ' || sch
|| ' GRANT REFERENCES ON TABLES TO "' || user_name || '"';
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- Create user with syslog access
CREATE USER "catalog"
PASSWORD '<password>'
-- Grant usage on all schemas
-- You will need to run this everytime a new a new schema is added
CALL grant_rights_schema_today('catalog');
CALL grant_rights_schema_future('catalog');
Queries run by Catalog
Information below concerns the queries Catalog will run on Postgres with the rights granted above. We only extract metadata (columns, schemas, tables, roles).
Databases
SELECT db.oid AS database_id, db.datname AS database_name, de.description AS "comment" FROM pg_catalog.pg_database AS db LEFT JOIN pg_catalog.pg_description AS de ON de.classoid = db.oid WHERE db.datname = CURRENT_DATABASE()
Schemas
SELECT db.oid AS database_id, db.datname AS database_name, ns.oid::TEXT AS schema_id, ns.nspname AS schema_name, u.usename AS schema_owner, u.usesysid AS schema_owner_id, de.description AS "comment" FROM pg_catalog.pg_namespace AS ns CROSS JOIN pg_catalog.pg_database AS db JOIN pg_catalog.pg_user AS u ON u.usesysid = ns.nspowner LEFT JOIN pg_catalog.pg_description AS de ON de.classoid = ns.oid WHERE TRUE AND db.datname = CURRENT_DATABASE() AND ns.nspname NOT LIKE 'pg_%%' AND ns.nspname NOT IN ('catalog_history', 'information_schema')
Tables
WITH ids AS ( SELECT t.oid AS table_id, t.relname AS table_name, n.nspname AS schema_name, n.oid AS schema_id, u.usename AS table_owner, t.relowner AS table_owner_id, td.description AS "comment", t.reltuples::BIGINT AS tuples FROM pg_class AS t JOIN pg_catalog.pg_namespace AS n ON n.oid = t.relnamespace LEFT JOIN pg_catalog.pg_description AS td ON td.objoid = t.oid AND td.objsubid = 0 LEFT JOIN pg_catalog.pg_user AS u ON u.usesysid = t.relowner WHERE TRUE AND n.nspname NOT LIKE 'pg_%%' AND n.nspname NOT IN ('catalog_history', 'information_schema') AND t.relam IN (0, 2) ), meta AS ( SELECT db.datname AS database_name, db.oid AS database_id, t.table_schema AS schema_name, t.table_name AS table_name, t.table_type FROM information_schema.tables AS t CROSS JOIN pg_catalog.pg_database AS db WHERE TRUE AND db.datname = CURRENT_DATABASE() AND t.table_schema NOT LIKE 'pg_%%' AND t.table_schema NOT IN ('catalog_history', 'information_schema') ) SELECT m.database_name, m.database_id, m.schema_name, i.schema_id, m.table_name, i.table_id, m.table_type, i.table_owner, i.table_owner_id, i.tuples, i.comment FROM meta AS m JOIN ids AS i ON (i.table_name = m.table_name AND i.schema_name = m.schema_name)
Columns
WITH ids AS ( SELECT d.datname AS database_name, d.oid AS database_id, t.oid AS table_id, t.relname AS table_name, n.nspname AS schema_name, n.oid AS schema_id FROM pg_class AS t JOIN pg_catalog.pg_namespace AS n ON n.oid = t.relnamespace CROSS JOIN pg_catalog.pg_database AS d WHERE TRUE AND d.datname = CURRENT_DATABASE() AND n.nspname NOT LIKE 'pg_%%' AND n.nspname NOT IN ('catalog_history', 'information_schema') AND t.relam IN (0, 2) ), columns AS ( SELECT ids.database_name, ids.database_id, c.table_schema AS schema_name, ids.schema_id, c.table_name AS table_name, ids.table_id, c.column_name, ids.table_id || '.' || c.column_name AS column_id, c.data_type, c.ordinal_position, c.column_default, c.is_nullable, c.character_maximum_length, c.character_octet_length, c.numeric_precision, c.numeric_precision_radix, c.numeric_scale, c.datetime_precision, c.interval_precision, c.interval_type, c.interval_precision, d.description AS "comment" FROM information_schema.columns AS c JOIN ids ON c.table_schema = ids.schema_name AND c.table_name = ids.table_name LEFT JOIN pg_catalog.pg_description AS d ON d.objoid = ids.table_id AND d.objsubid = c.ordinal_position ) SELECT * FROM columns
Users
SELECT usename AS user_name, usesysid AS user_id, usecreatedb AS has_create_db, usesuper AS is_super, valuntil AS valid_until FROM pg_catalog.pg_user
Groups
SELECT groname AS group_name, grosysid AS group_id, grosysid AS group_list FROM pg_group
References
Here's a list of all the PostGres documentation referenced above
Last updated
Was this helpful?