PostgreSQL

Grant access to your PostgreSQL metadata to Catalog and run a few tests to verify the setup.

Before You Begin

You need to be a PostgreSQL superuser with the ability to impersonate all schema owners. Your Postgres version should be 11 or later.

When new schemas are created, part of the process will need to be repeated.

Catalog will only be granted USAGE on all schemas and REFERENCES on all tables. That is the minimum role for Catalog to read your metadata without accessing your data.

More Information

See the PostgreSQL documentation:

• Privileges and access documentation
• User creation and access to system tables

1. Whitelist Catalog's IP on Postgres

To allow Catalog to connect to Postgres, you will first need to whitelist Catalog's IP address.

Here are our fixed IPs:

• For instances on app.us.castordoc.com: 34.42.92.72
• For instances on app.castordoc.com: 35.246.176.138

Catalog connects directly to your Postgres databases. As such, it needs to be public. It is frequent that our clients provide Catalog an access to an exact replica of their database.

2. Create Catalog User on Postgres

You are asked to enter credentials in the Catalog App. Here is a step-by-step guide to create them. You can run the SQL code below.

View the full SQL procedure

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 owned 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.

1. Connect to your Postgres cluster using the catalog credentials you've just created
2. Run 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

Catalog Admin Required

You must be a Catalog admin to do this.

You can now enter the newly created credentials in the Catalog App.

• Go to Settings > Integrations
• Click Postgres Add
• Add the credentials

Troubleshooting

This section covers common issues and how to resolve them.

Asset Counts Dropped Sharply After a Run or Maintenance Window

Catalog reconciles what exists in Postgres against what it already indexed. Operations that remove schemas, drop tables, or recreate databases under the same name can look like large deletes until the next successful ingestion.

1. Confirm whether maintenance scripts dropped or renamed schemas that Catalog had indexed.
2. Re-run grants for future content if new schemas replace old ones so the Catalog user keeps REFERENCES visibility.
3. If counts still look wrong while Postgres still holds the data, contact Coalesce Support with timestamps and one example schema name.

Multiple Postgres Integration Entries Overlap the Same Data

Teams sometimes create second connections during migrations or tests. Overlapping sources can confuse ownership of assets until ingestion settles.

1. Prefer one canonical Postgres source per environment and retire trial credentials once production credentials work.
2. Align hostname, database name, and user so Catalog does not duplicate the same logical instance.

Column Names Are Missing on Table Pages

REFERENCES on tables is required for Catalog to read column-level metadata.

1. Re-run the grant procedures in Create Catalog User on Postgres for schemas that were added after onboarding.
2. Confirm REFERENCES grants succeeded on tables in information_schema visibility tests.

Connection Errors After Password Rotation or Host Change

Catalog stores the credentials you entered during onboarding.

1. Update Settings > Integrations with the current password, host, or port.
2. Repeat Test the Catalog User queries from this page before relying on new ingestion.

Postgres Version Is Below 11

Catalog documents a minimum version for supported deployments.

1. Upgrade the database to a supported Postgres release or connect Catalog to a replica that meets the minimum version.
2. If you cannot upgrade yet, discuss options with Coalesce Support before expanding scope.

---

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 for columns, schemas, tables, and 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:

• Privileges and access documentation
• User creation and access to system tables

---