Set up a Supabase target and replicate PostgreSQL to it

In the SQL editor of the Supabase dashboard, or with psql as postgres, create the role:

CREATE ROLE gluesync_writer LOGIN PASSWORD '<a strong password>' NOINHERIT;

Let it create the tables that Gluesync creates for you (step 5) in the schema public:

GRANT USAGE, CREATE ON SCHEMA public TO gluesync_writer;

Let it create the helper schema for bulk load (step 8). Gluesync keeps its staging tables in a schema named GLUESYNC, in capital letters, and creates it the first time an entity uses bulk load:

GRANT CREATE ON DATABASE postgres TO gluesync_writer;

CREATE SCHEMA "GLUESYNC";
GRANT USAGE, CREATE ON SCHEMA "GLUESYNC" TO gluesync_writer;

If the tables already exist and you do not want Gluesync to create them, grant the privileges on each one instead of CREATE on the schema. TRUNCATE is needed only for the option Empty the table first in step 6:

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON public.customers TO gluesync_writer;

In the Control Plane, select Create your first pipeline, or the + button of the pipeline list. The Agents setup wizard opens;

In 1. Setup source, search for postgres and select the PostgreSQL CDC agent tile. The connection form opens. Enter the Host name / Address, the Port, the Database name, the Username of a user with the CDC grants and the Password. Select Test and continue, then Next;

In 2. Setup target(s), search for supabase and select the Supabase agent tile. The connection form opens with Port 5432, Database name postgres and Username postgres already filled in;

Enter the Host name / Address. Change Username to gluesync_writer (1), or to the pooler form of step 3, and enter the Password;

Under Security, keep Enable TLS and Trust server certificate on (2). Both are on by default. Select Test and continue.

Select Next, enter a Pipeline name and select Finish.

In the pipeline, select Entities (the blue button with the plus sign). The Add entities page opens with the Object browser;

Expand the source schema, for example shop, and select the check box of customers. The Field mapping dialog opens;

In Target Schema select public. Select the Target Table field and type customers. Select Create table "customers". The banner New table customers will be created on confirm. appears and every source column gets a proposed type;

Look at the Length column. The cells of notes, ref and payload (text, uuid and jsonb) show 0 with a red mark, the banner says Missing required field and Confirm is disabled.

Select each of the three cells and type -1, then press Tab. The type stays text, uuid and jsonb, and the error clears. The Error tab reads Error (0);

Select Confirm. A second dialog, Create table customers, shows the exact statement (1). Read it, then select Create table customers (2). Select Copy snippet (3) instead if your database administrator must run the statement. Both dialogs close and customers appears under Selected entities.

Select the check box of orders and repeat. Its columns have lengths, so the dialog has no errors. Then select Confirm and continue.

In the entity list, select the play button of shop.customers. The Sync changes (CDC) dialog opens;

Keep Full copy and Keep and merge. Full copy copies all rows and then follows the live changes. Keep and merge overwrites rows that have the same key and leaves the others. Choose Empty the table first only if the target table must contain the source rows and nothing else: it runs a TRUNCATE, needs that privilege and asks you to confirm with I understand rows in the target will be deleted;

Select Start entity. Do the same for shop.orders.

On the source, change the data:

INSERT INTO shop.customers (id, name, notes, payload, tags, credit, seen_at, active)
VALUES (3, 'Gamma', 'n3', '{"a":1}', '{x,y,z}', 99.99, now(), true);
UPDATE shop.customers SET name = 'Acme SpA', credit = 2000 WHERE id = 1;
DELETE FROM shop.customers WHERE id = 2;

After a few seconds, read the target:

SELECT id, name, credit, tags FROM public.customers ORDER BY id;

In the entity list, select the arrow at the end of the row of shop.orders and then Count both. The page counts the rows of the source and of the target.

Open the entity menu (the three dots on its row) and select Edit settings, then Fine tuning;

Under Bulk load settings, select Bulk for CDC and Bulk for Snapshot. Close the dialog and select Confirm and continue to save the entity;

In Supabase, list the staging tables:

SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'GLUESYNC';

On the source, update three rows of orders and delete one, then select Count both on the entity page. The entity keeps running: you do not restart it.

In Supabase, as postgres, make postgres the owner of the table orders. Gluesync then writes a table that it does not own and has no privilege on:

GRANT gluesync_writer TO postgres;
ALTER TABLE public.orders OWNER TO postgres;
REVOKE gluesync_writer FROM postgres;

On the source, update a row of orders;

Open the entity shop.orders. The status is Error, the banner reads Target write failed and the message names the SQLSTATE and the table.

Grant the privileges to the role of the connection:

GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE ON public.orders TO gluesync_writer;