Supabase glyph Supabase agent

Target Supabase

Supabase speaks the PostgreSQL wire protocol, so this agent is the PostgreSQL target with Supabase’s connection defaults. Type mapping, snapshot and CDC writes and the bulk COPY path are the PostgreSQL target’s.

Prerequisites

To have Gluesync writing into your Supabase project you will need:

  • The database connection details of the project, under Connect in the Supabase dashboard: a direct connection, or the session pooler;

  • The password of the database user (postgres by default). On Supabase this role is not a superuser, and that is enough: the agent creates its GLUESYNC helper schema and its staging tables as that role, and writes the target tables it owns;

  • Network access from Core Hub to the database. The direct connection (db.<project-ref>.supabase.co) resolves to an IPv6 address unless the project has the IPv4 add-on. A Core Hub without IPv6 connectivity can use the session pooler instead, which has IPv4 addresses.

Setup via Web UI

  • Hostname / IP Address: the direct connection host, for example db.<project-ref>.supabase.co, or the session pooler host, for example aws-0-<region>.pooler.supabase.com;

  • Port: Optional, defaults to 5432. Use 5432 for the direct connection and for the session pooler. Port 6543 (transaction pooler) is not supported, see Connection types;

  • Database name: postgres;

  • Username: postgres on the direct connection. The session pooler needs the project reference in the user name: postgres.<project-ref>;

  • Password: Password belonging to the given username;

  • Enable TLS: on by default;

  • Trust server certificate: on by default. See TLS.

Specific configuration

The agent has the PostgreSQL target’s connection settings:

  • Use connection pool (useConnectionPool): default true;

  • Gluesync schema name (necessary for bulk operations) (gluesyncSchemaName): default GLUESYNC, the schema for the staging tables;

  • Disable automatic schema creation (disableAutomaticSchemaCreation): default false. Turn it on when the connection user must not create schemas, and create the Gluesync schema yourself.

Connection types

Connection Supported Notes

Direct connection, db.<project-ref>.supabase.co:5432

✅

Recommended. IPv6 only unless the project has the IPv4 add-on. User postgres.

Session pooler, aws-0-<region>.pooler.supabase.com:5432

✅

IPv4 and IPv6. User postgres.<project-ref>.

Transaction pooler, port 6543

❌

The write path uses prepared statements, which the transaction pooler does not support. Point the agent at the direct connection or the session pooler.

TLS

TLS is on by default, and Supabase accepts only encrypted connections when Enforce SSL is on.

  • With Trust server certificate on (default), the JDBC sslmode is require: the connection is encrypted and the certificate is not checked against a CA;

  • With Trust server certificate off, the JDBC sslmode is verify-ca: the server certificate must be signed by a CA found in the trust store you provide in the connection settings (trust store path and password). Import in it the CA certificate of the project, which the Supabase dashboard offers for download in the database settings.

Tables and schemas

Target tables live in public or in the schemas you created. The schemas Supabase manages for itself (auth, storage, realtime, vault, extensions, graphql and others) are system schemas for the full-catalog walk of Core Hub: it does not walk or sample them.

Gluesync can create the target tables, as it does for any SQL target (see Target table creation), and writes any table in the project that the connection user owns or can write to. A table you create yourself, for example in a Supabase migration, is written as it is.

Row level security

Tables with row level security enabled are written. The postgres role bypasses row level security on Supabase, so the table needs no policy for Gluesync, also with FORCE ROW LEVEL SECURITY. The integration test suite covers a table with row level security enabled and forced.

A connection user without the BYPASSRLS attribute follows the policies of the table: it needs policies that let it INSERT, UPDATE, DELETE and SELECT the rows, and a forced policy applies to the table owner too.

Extension types

Columns of types that an extension installs in the extensions schema are written, on snapshot and on CDC: vector (pgvector), geometry, geography and raster (PostGIS) and hstore.

Data types

The mapping is the one of the PostgreSQL target. The integration test suite writes these types, as column values and also as primary keys, through the snapshot and the CDC paths, with the binary and the text COPY and with the batch statements, and compares the values as text with the source:

Group PostgreSQL types

Integers and booleans

smallint, integer, bigint, oid, boolean, smallserial, serial, bigserial

Numbers

numeric and decimal with and without precision and scale, money, real, double precision (NaN, Infinity and -Infinity included)

Dates and times

date, time, timetz, timestamp, timestamptz, interval. Dates and timestamps before 1582 keep their value.

Text

varchar(n), varchar, char(n), text, json, jsonb, xml, uuid, enum types, tsvector, tsquery

Binary and bit strings

bytea, bit(n), bit varying(n), bit varying

Network addresses

inet, cidr, macaddr, macaddr8

Geometric

point, lseg, line, circle, box, path, polygon

Ranges

int4range, int8range, numrange, tsrange, tstzrange, daterange and the six multirange types

Replication and snapshot types

pg_lsn, txid_snapshot, pg_snapshot

Arrays

Arrays of the types above, for example integer[], text[], timestamptz[], uuid[], jsonb[]

Extension types

vector, geometry, geography, raster, hstore

A numeric value of NaN cannot be read or written: the Gluesync decimal type has no such value.

Updates with bulk operations during CDC

When Use bulk operations during CDC (useBulkOperationsDuringCDC) is on for an entity, the agent does not apply an update as an UPDATE statement. It loads the change into a staging table, deletes the existing row from the target table and inserts the new version of it. With the option off, updates are applied as UPDATE statements.

For the target table this has the following effects:

  • UPDATE triggers on the target table do not fire, while DELETE and INSERT triggers do. A trigger such as moddatetime on updated_at does not run, so the column keeps the value of the source;

  • Foreign keys that reference the table with ON DELETE CASCADE delete their child rows on every update, and ON DELETE SET NULL clears the child columns. A foreign key with RESTRICT or NO ACTION makes the write fail while child rows exist;

  • Supabase Realtime subscribers, logical replication and anything else that reads the change log of the project database see a DELETE and an INSERT instead of an UPDATE;

  • The new row gets a new physical location (ctid), and columns that Gluesync does not write, such as a column with a default value, are set again as for a new row.

Entities whose target table has an identity (auto-increment) column ignore the option: their updates are always applied as UPDATE statements.

Setup via Rest APIs

Here following an example of calling the Core Hub’s Rest API via curl to setup the connection for this Agent.

Connect the agent

curl --location --request PUT 'http://core-hub-ip-address/pipelines/{pipelineId}/agents/{agentId}/config/credentials' \
--header 'Content-Type: application/json' \
--header 'Authorization: ••••••' \
--data '{
        "hostCredentials": {
        "connectionName": "myAgentNickName",
        "host": "db.<project-ref>.supabase.co",
        "port": 5432,
        "databaseName": "postgres",
        "username": "postgres",
        "password": "",
        "maxConnectionsCount": 100,
        "enableTls": true,
        "trustServerCertificate": true
      }
}'

Not covered in this release

  • Supabase as a source and change data capture from Supabase;

  • The transaction pooler (port 6543);

  • numeric values of NaN.