PostgreSQL
On-premises, AWS RDS/Aurora, GCP Cloud SQL, Azure Database for PostgreSQL, Supabase, Neon.
Access mode: Read-only or read-write
Trino connector. Queryable via Flume’s Lakehouse. This system can be accessed both through its native protocol (for metadata introspection) and via Trino federation (for data profiling and cross-system analytical queries).
Required information
| Field | Details |
|---|---|
| Host / IP | FQDN or IP. RDS: <instance>.rds.amazonaws.com. Cloud SQL: public or private IP, or use Cloud SQL Auth Proxy. |
| Port | Default 5432. |
| Database name(s) | Each Postgres connection is database-specific. List all databases. |
| Schema(s) | Default is public. Most enterprises use custom schemas, so list all of them. |
| Service account credentials | Username + password. IAM-based: RDS IAM role ARN or GCP service account email. |
| SSL mode | prefer, require, verify-ca, or verify-full. If verify-ca/full, provide CA certificate. |
| Access level | Read-only: GRANT USAGE ON SCHEMA + GRANT SELECT ON ALL TABLES. Stored procs: GRANT EXECUTE ON ALL FUNCTIONS. |
Network considerations
RDS/Aurora: Security group inbound on 5432. Private subnet requires VPC peering or PrivateLink.
Cloud SQL: Private IP requires VPC peering with Flume’s GCP project. Public IP requires authorized networks entry. Cloud SQL Auth Proxy preferred: secure connectivity without IP allowlisting.
On-premises: VPN, SSH tunnel, or bastion host.
pg_hba.conf: Ensure Flume’s source IP range has a matching host entry with correct auth method (md5, scram-sha-256, or cert).
PgBouncer: If present, confirm it’s in session mode (not transaction mode) if we need prepared statements or advisory locks.
Credential and auth management
Preferred: IAM Auth (RDS). Flume generates short-lived tokens via IAM role. Requires rds_iam role granted to DB user. Tokens expire every 15 min; client handles rotation.
Preferred: IAM Auth (Cloud SQL). GCP service account with cloudsql.client role. Auto-rotated.
Preferred: Certificate auth. Provide client cert + key. Flume configures sslcert and sslkey.
Acceptable: Password auth. SCRAM-SHA-256 preferred (default in PG 14+). MD5 acceptable.
Connection pooling: If PgBouncer is in the path, prepared statements may fail in transaction mode. Session mode required for full compatibility.
Stored procedure and logic access
PostgreSQL has both stored procedures (CALL) and functions (SELECT). Flume needs EXECUTE privilege on the target schema’s routines. For introspection: access to pg_proc, pg_catalog, or information_schema.routines. If procedures use SECURITY DEFINER, confirm the definer role has necessary underlying permissions. For PL/pgSQL, PL/Python, or other language extensions, confirm the extension is installed.
Validation checks
| Check | Method | Expected result |
|---|---|---|
| Network reachability | pg_isready -h <host> -p <port> | accepting connections |
| Authentication | SELECT 1; | Returns 1 |
| Identity confirmation | SELECT current_user, current_database(); | Expected service account and database |
| Schema visibility | SELECT * FROM information_schema.tables WHERE table_schema = '<schema>' | Lists expected tables |
| Function/proc listing | SELECT routine_name, routine_type FROM information_schema.routines WHERE routine_schema = '<schema>' | Lists stored procs and functions |
| Execute test | SELECT <schema>.<function>(test_params); or CALL <schema>.<proc>(test_params); | Returns expected result |
| Permission check | SELECT has_schema_privilege('<user>', '<schema>', 'USAGE'); | Returns true |
| Default privileges | SELECT * FROM information_schema.role_table_grants WHERE grantee = '<user>'; | Confirms grants on future tables too |
Every connection starts from the pre-engagement checklist and goes through the universal validation protocol before production sign-off.