PostgreSQL

Overview

Signals reads conversion and customer data straight from a PostgreSQL database on a schedule and sends the columns you nominate to your ad platforms.

Reading it directly means there is no file job to maintain and nothing to go stale between runs. Signals reads only; nothing is written back.

You can point it at a table, or at a query. The query path is what you want when the shape Signals needs does not match anything you already have.

An unqualified table name is resolved through the connecting role’s search_path, and different roles can carry different paths. orders therefore means analytics.orders to the analyst who wrote the query and public.orders to the role Datahash connects as: two tables, one piece of query text, and no error to say which one was read. Write table names as schema.table on both paths. Diagnosing a feed that has been reading the wrong table since setup costs far more than qualifying the name did.

Supported destinations

Destinations supported by PostgreSQL

Category	Supported
Offline Conversions	Google Offline Conversions API Google Store Sales LinkedIn Offline Conversions API Meta Offline Conversions API OpenAI Offline Conversions API Snapchat Offline Conversions API TikTok Offline Events API
Lead Conversions	Google Enhanced Conversions for Leads LinkedIn Leads Conversions API Meta Conversions API for CRM Snapchat Leads Conversions API TikTok CRM Integrations
Custom Audience	Google Customer Match Meta Custom Audience Snapchat Custom Audience TikTok Custom Audience

Authentication

In Studio, open Sources, find the Database category, and click the PostgreSQL tile.

Field	What it is	Where to find it
Authentication Name	Your own label for this set of credentials.	You choose it. Name it for the database it belongs to.
Username	The database user Signals connects as.	Created in the database by your administrator.
Password	The password for that user.	Set when the user was created.

Caution

Create a dedicated read-only user for Datahash rather than reusing an application or administrator account. It keeps the connection working when someone rotates a password, and it stops the connection seeing more than it needs. Enter the password on the Datahash connection screen only.

Building that role takes four statements rather than one, and each covers a level the previous one does not:

CREATE ROLE datahash LOGIN PASSWORD '...';
GRANT CONNECT ON DATABASE appdb TO datahash;
GRANT USAGE ON SCHEMA public TO datahash;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO datahash;

The fourth line is not sufficient by itself, and the default public schema hides that. PostgreSQL grants USAGE on public to PUBLIC, and CONNECT on a database likewise, so on an untouched database a role given nothing but select on all tables does reach them. PostgreSQL 15 revoked CREATE from PUBLIC on that schema, not USAGE, so this still holds on current releases. Point the connection at a schema your own team created, or at a database where those grants to PUBLIC have been revoked as hardening, and the same role holds privileges on tables it cannot reach: the error names the schema, which reads like a table privilege problem and sends whoever is debugging it back to re-run the line that was already correct. Running all four costs nothing and behaves the same either way.

Add a fifth statement whenever the set of tables might grow, once for each role that creates tables in the schema:

ALTER DEFAULT PRIVILEGES FOR ROLE app_migrations IN SCHEMA public GRANT SELECT ON TABLES TO datahash;

Granting select on all tables is evaluated once, against the tables in existence at that instant. Default privileges cover tables created afterwards, but they are recorded per creating role, and the form without FOR ROLE records them for the role running the statement and no other. A DBA who runs it in their own session, while an application or migration role is what actually owns new tables, has changed nothing about the next table added and gets no indication of that until a run fails on it. Establish which roles create tables in the schema, and repeat the statement with FOR ROLE naming each one.

Configuration

Instance Setup

Select the Integration Type, which is the kind of data your records hold. Offline Events is the common choice. Give the instance a name, then choose the table path or the query path. On either path you enter the Database Name, Host Name and Port, and then either the Table Name or a SQL query.

The integration type decides the columns Signals expects and which destinations the connection can feed, so it is worth settling before anyone models the data. It also decides which file format you get on the next screen.

Port and host based authentication

5432 is the default listening port. Where a connection pooler is in play, the number changes: PgBouncer conventionally listens on 6432 and passes sessions through to the server, so establish which of the two your operations team expects this connection to use.

pg_hba.conf is consulted before any password comparison happens. PostgreSQL walks it row by row, matching each row’s connection type (local, host, hostssl or hostnossl), database, user and source address against the incoming connection, and the first row that matches decides which authentication method applies. A source address no row covers is turned away with an error stating there is no entry for that host. The wording says nothing about the password, so a reader chasing a credentials problem loses time before opening the file. Whichever addresses Signals connects from have to be covered by a row that permits this role, and the server configuration has to be reloaded before the change takes effect. Ask your Datahash representative which addresses to add, rather than reading them off a failed connection attempt.

Table path

Enter the connection details including the table name, then complete the setup. The name is typed rather than picked from a list, so it has to match the database exactly.

Query path

Enter the connection details without the table name, then write the query. Preview the results before finishing, and read the column names in the output rather than just the row count: a query that runs but returns the wrong names will fail silently once the connection is live.

Manage instance

One instance reads one table or query for one integration type. To read another, add another instance from the Manage existing instance table. To change an existing one, open it from there, click the edit option in the menu to the top right, update the fields and click Finish.

File format and schema

Download the file format from the setup screen and shape the table to it. The column names are what Signals matches on, so a column that has been renamed or cased differently is not recognized and the row it belongs to is dropped rather than reported.

On the query path the same rule applies to the column aliases your query returns. Aliasing to the expected names is the usual way to reconcile data you cannot or should not restructure.

Data & identifiers

Personal identifiers are normalized and hashed before they are sent to a destination.

Best practices

• Use a dedicated read-only user scoped to the data Datahash reads, rather than an application account.
• Prefer the query path with explicit column aliases over pointing at a raw table. It lets your team change the underlying model without breaking the connection.
• Point the read at a hot standby where there is one, and settle hot_standby_feedback before you depend on it. A standby cancels a query that stands in the way of WAL replay, with ERROR: canceling statement due to conflict with recovery, and a scheduled scan is long enough and quiet enough to be the query that meets it. Turning hot_standby_feedback on tells the primary to keep the row versions the standby’s readers still need, which costs bloat there; raising max_standby_streaming_delay from its 30 second default buys the read time on the standby instead.
• Clean the data in the view or query: lowercase email, phone in E.164, no stray whitespace. Hashing a badly formatted value produces a hash that will never match.
• Rotate the password periodically and update the connection afterwards.

Troubleshooting & FAQ

The connection times out rather than reporting bad credentials. That points at reachability rather than authentication. Confirm the host is exposed, the port is open, and any firewall or allow list permits the connection.

Credentials are rejected. Check the user can read the data from a client of its own first. Most failures here are permissions rather than typos.

The table name is rejected. It is typed rather than selected, so it has to match exactly. PostgreSQL lowercases unquoted identifiers, so a table created with a capitalized name may not be reachable by that name.

The query previews correctly but nothing is delivered. The column names it returns do not match the expected schema. Alias them to the names in the downloaded file format.

It was working and stopped. The password may have been changed or the user disabled. A dedicated service user avoids this.

A connection that ran for months fails on a table added last week. Granting select on all tables in a schema covered the tables present when it ran, and covers nothing created since, so the reading role is denied permission on the new relation while every older table still works. Grant select on that table, then check that default privileges exist for the role that created it: default privileges recorded against one role say nothing about tables another role creates, which is why this recurs on databases where someone believed it had been handled. The same gap applies to a table dropped and recreated, since the recreated table is a new object as far as the privilege system is concerned.