PostgreSQL

Sifflet monitors the health and quality of the data in your PostgreSQL databases, from table updates down to individual field values and business metrics. Sifflet catalogs the tables and views of the schemas you select, so you can monitor them and see how an incident on your production data affects the downstream assets of your other integrations.

To learn what Sifflet imports from PostgreSQL, see PostgreSQL Collected Data.

Permissions Required

Sifflet uses a dedicated PostgreSQL user (a role with the LOGIN attribute) that authenticates with a password. Sifflet only needs read access: it reads metadata about your schemas and tables, and runs read-only SELECT queries to compute monitors.

Sifflet's monitor queries run on your database and consume its resources. Each query starts with a SQL comment containing "app": "Sifflet" and the ID of the monitor, so you can identify Sifflet's queries in your database logs.

Requirements

Required Roles for Setup

• In PostgreSQL: a role that can create roles (superuser or CREATEROLE) and grant SELECT on the tables of the monitored schemas (owner of the tables or superuser).
• In Sifflet: the Admin system role to create the credential, and the Admin or System Editor system role to create the source. See Access Control.

Supported Versions and Editions

• Sifflet connects to PostgreSQL with username and password authentication. Other authentication methods, such as client certificates or cloud IAM database authentication, are not supported.
• Managed PostgreSQL services that expose a standard PostgreSQL endpoint use the same setup. This includes Google Cloud SQL for PostgreSQL and AlloyDB for PostgreSQL, with built-in database users: see Cloud SQL for PostgreSQL and AlloyDB for PostgreSQL for their network setup.

Network Access

• Default: Sifflet connects to your database over the internet, so its host and port must be reachable from the public internet.
• IP allowlisting: you can restrict inbound traffic to your database (firewall rules, cloud security groups, pg_hba.conf) to the IP addresses of your Sifflet instance, listed in Settings > Integration Preferences.
• Private networks: the Sifflet Agent supports PostgreSQL: deploy it in your private network so that your database doesn't need to be exposed to the internet.

Cloud SQL for PostgreSQL

Sifflet connects directly to the IP address of your Cloud SQL instance: it doesn't use the Cloud SQL Auth Proxy or the Cloud SQL connectors, so the instance must not require connectors. See Connect to Cloud SQL for PostgreSQL.

• Public IP: turn on public IP for the instance, and add the IP addresses of your Sifflet instance (Settings > Integration Preferences) to its authorized networks. Use the public IP address of the instance as the Host of the source.
• Private IP only: deploy the Sifflet Agent in a network that can reach the private IP address of the instance, and use this address as the Host of the source.
• Sifflet doesn't support client certificates: don't require trusted client certificates on the instance.

AlloyDB for PostgreSQL

Sifflet connects directly to the IP address of your AlloyDB instance: it doesn't use the AlloyDB Auth Proxy or the AlloyDB connectors, so the instance must not require connectors. See AlloyDB connection overview.

• Public IP: turn on public IP for the instance, and add the IP addresses of your Sifflet instance (Settings > Integration Preferences) to its authorized external networks. Use the public IP address of the instance as the Host of the source.
• Private IP only (default): deploy the Sifflet Agent in a network that can reach the private IP address of the instance, and use this address as the Host of the source.

Connect Sifflet to PostgreSQL

Step 1: Create a Database User in PostgreSQL

1. Connect to the database that Sifflet monitors, as a role that has the rights listed in Required Roles for Setup.

2. Create the Sifflet user and allow it to connect to the database. Replace <PASSWORD> with a strong password and <DATABASE> with the name of your database. You can choose another user name than sifflet_user: use it consistently in all the following scripts.

   SQL

   CREATE ROLE sifflet_user WITH LOGIN PASSWORD '<PASSWORD>';
   GRANT CONNECT ON DATABASE "<DATABASE>" TO sifflet_user;

3. Grant read access to each schema that Sifflet monitors. Run this block once per schema, replacing <SCHEMA> with the schema name. Quoted names are case-sensitive.

   SQL

   -- Repeat for each schema that Sifflet monitors
   GRANT USAGE ON SCHEMA "<SCHEMA>" TO sifflet_user;
   GRANT SELECT ON ALL TABLES IN SCHEMA "<SCHEMA>" TO sifflet_user;

4. Optional (new tables): GRANT SELECT ON ALL TABLES only applies to the tables and views that exist when you run it. To let Sifflet monitor tables created later, set default privileges. Default privileges only apply to objects created by the role named in FOR ROLE: replace <TABLE_OWNER_ROLE> with the role that creates the tables in this schema (for example, the role used by your application or your pipelines), and repeat the statement for each such role.

   SQL

   -- Repeat for each schema that Sifflet monitors and each role that creates tables in it
   ALTER DEFAULT PRIVILEGES FOR ROLE <TABLE_OWNER_ROLE> IN SCHEMA "<SCHEMA>" GRANT SELECT ON TABLES TO sifflet_user;

Cloud SQL and AlloyDB: Create the User with SQL

Cloud SQL and AlloyDB don't provide a PostgreSQL superuser. Create the user as the postgres user (or another user with the cloudsqlsuperuser or alloydbsuperuser role), and run the GRANT SELECT and ALTER DEFAULT PRIVILEGES statements as the owner of the tables (or a member of the owner role). Create the Sifflet user with SQL rather than in the Google Cloud console, so that it only gets the permissions above: in Cloud SQL, users created in the console get the cloudsqlsuperuser role, which gives them much more than read access.

Keep the user name and password: you need them in step 2.

Step 2: Add the PostgreSQL Source in Sifflet

1. Create the credential: in Integrations > Credentials, click New credentials, enter a Credential name, and paste the following JSON in the Credential field, with the user name and password from step 1:

   JSON

   {
     "user": "sifflet_user",
     "password": "<PASSWORD>"
   }

2. Create the source: in Integrations > Sources, click New source, select PostgreSQL, and fill in the form:

   Field	Description	Example
   Source name	The name of the source in Sifflet	PostgreSQL production
   Host	The host name or IP address of your PostgreSQL server, without protocol or port	db.example.com
   Port	The port of your PostgreSQL server (default 5432)	5432
   Database	The name of the database that Sifflet monitors	app_db
   Use Sifflet Agent	Only if you use the Sifflet Agent: check it so that the Agent connects to the database. This option is visible when the Agent is turned on for your Sifflet instance.	-
   Credentials	The credential created above	postgres-production

   Use the same host as the other tools that connect to this database (BI tools, dbt, pipelines): Sifflet matches the host, port, and database to link their assets in lineage.

3. Select the scope: click Load assets and select the schemas that Sifflet catalogs and monitors. Turn on Automatically include new schemas so that Sifflet also catalogs schemas created later. Create one source per database. For more details on scope selection, refresh frequency, and scheduling, see Integrations Management.

4. Test and save: click Test to check that Sifflet can connect to the database, then click Save & Run. Sifflet starts the first metadata refresh: you can follow its status and logs in the source details (see Inspecting Source Details and Resolving Failures). Your tables and views appear in the Data Catalog once this first run completes.