Postgres

Step 1 of 10: Name it

What to call this connection, and the deployment it belongs to.

Step 2 of 10: Name the database

Host, database, schema and the reading role.

Add a Postgres integration and enter the host, port, and database.

Choose every schema, or add the ones you want read: the script is written for whichever you choose. schema_pattern denies information_schema by default, so you don't need to worry about excluding it here.

Convalesce is a hosted service: it connects to the database over the internet from 34.66.85.47. Give the database a public address, and allow ours wherever inbound connections are limited: the instance's security group and its public access setting on RDS, an authorised network on Cloud SQL, a firewall rule on Azure, or pg_hba.conf on your own server.

Step 3 of 10: Choose how to sign in

Password or AWS IAM.

Two ways to authenticate the read connection, set via auth_mode.

Step 4 of 10: Set a password

The password the reading role signs in with.

The password below goes into the create role line of the SQL in the next step, and is what we store. Generate one, or type your own.

Step 5 of 10: Provision the read role

Create the reading role and grant it read access.

Postgres has no privilege that separates "see the structure" from "read the data," the way Snowflake's references does: select is the only grant that opens a table up, and it's also what profiling needs, so there's one grant to make per schema, not two.

Stored procedures (pg_proc) and view definitions (pg_depend, pg_rewrite) are system catalogs Postgres exposes to any connected role once it has usage on the schema; no extra grant is needed for either.

Because select is the one privilege that opens both schema introspection and row data, granting reader read access on a table means it can read that table's data, not just its shape.

This grant lets Convalesce read rows as well as the shape of your tables. While it investigates a failure the agent may run small read-only queries to confirm a cause: capped, with personal columns masked, never stored, and sent to the AI model. You can switch that off for this connection on its Settings tab once it is connected, without changing the grant.

create role reader with login password '<password>';

grant connect on database shop to reader;

grant usage on schema public to reader;
grant select on all tables in schema public to reader;
grant select on all sequences in schema public to reader;
-- So tables and sequences created after this point are covered too:
alter default privileges in schema public grant select on tables to reader;
alter default privileges in schema public grant select on sequences to reader;

grant usage on schema sales to reader;
grant select on all tables in schema sales to reader;
grant select on all sequences in schema sales to reader;
-- So tables and sequences created after this point are covered too:
alter default privileges in schema sales grant select on tables to reader;
alter default privileges in schema sales grant select on sequences to reader;

Step 6 of 10: Query-based lineage, if you want it (optional)

Optional: lineage from pg_stat_statements.

Optional, and off by default. This is the one part of the setup that needs work beyond the grants above, because it reads from pg_stat_statements rather than the system catalogs.

Postgres 13 or later. pg_stat_statements renamed its timing columns in 13, and Convalesce reads the newer names, so 12 and earlier can't use this feature at all.

Enable and load the extension, which needs a restart because it hooks in at shared_preload_libraries — add shared_preload_libraries = 'pg_stat_statements' to postgresql.conf and restart before running the SQL below.

pg_read_all_stats is the scoped option (Postgres 10+); a superuser account works too but isn't something you'd want to hand a scheduled read connection.

pg_stat_statements only remembers queries since the last restart or pg_stat_statements_reset(), so lineage reflects recent activity, not everything that's ever run against the database.

-- After restarting, once per database you want to track:
create extension if not exists pg_stat_statements;

grant pg_read_all_stats to reader;

Step 7 of 10: Choose what is read (optional)

Optional: narrow it to some tables.

Everything the credential can see is read unless you narrow it here. List the tables you want, the ones to leave out, or both.

Step 8 of 10: Test the connection

Check Convalesce can reach it with what you entered.

The test runs on the same worker a real run would, with the recipe exactly as it will be saved, so it fails the way a run would.

Step 9 of 10: Choose how often

How often Convalesce reads it.

Step 10 of 10: Review and connect

Check everything, then save the connection.