Set Up Postgres Data...

This guide explains how to prepare your PostgreSQL database as a Data Source or Event Store for use with ReJot.

Enabling Logical Replication

TIP

The only step absolutely required for ReJot to work is to enable logical replication. Creating custom roles and permissions is recommended for production use cases, but not necessary for development. ReJot will automatically create a replication slot, publication, schemas, and tables when the sync process is started.

Logical replication is not enabled by default in PostgreSQL. The configuration that manages logical replication is called wal_level, which must be set to logical. Note that changing this config requires a database restart.

There are two methods to enable this config: either through the postgresql.conf file,


        wal_level = 'logical'

or by setting it through SQL directly:


        SHOW wal_level;

-- Inspect current setting
ALTER SYSTEM
SET
  wal_level = 'logical';

NOTE

Hosted PostgreSQL providers like Supabase, Neon, or Google Cloud SQL manage this setting via their own dashboards. When given the option to choose from a pooled connection or a dedicated connection, choose the dedicated connection.

Postgres as a Source Data Store

Using a Postgres data store as a Source Data Store means that ReJot will listen to the logical replication stream of that store and apply Public Schema transformations to its messages. ReJot leverages PostgreSQL’s logical replication stream using the pgoutput native plugin to capture database changes.

Replication Slots

A PostgreSQL replication slot is a mechanism that keeps track of changes replicated from a publication for each subscriber, ensuring no changes are missed even if a subscriber disconnects temporarily. It maintains a record of which changes have been consumed by subscribers and prevents the deletion of Write-Ahead Log (WAL) segments until all subscribers have processed them.

TIP

In most cases it is not necessary to create a replication slot manually. ReJot will create a slot if it doesn’t exist yet based on the data store config in your manifest.

Creating a PostgreSQL Publication

A PostgreSQL Publication is defined on a primary Postgres node and specifies which set of tables in a database can be replicated to other systems.

On first starting a Sync service, a publication is automatically created if it doesn’t exist yet. This publication includes all tables and is equivalent to:


        CREATE PUBLICATION rejot_publication FOR ALL TABLES;

If you prefer to control which tables are included in the publication, you can create the publication manually:


        CREATE PUBLICATION rejot_publication FOR TABLE some_table, another_table;

See the PostgreSQL docs for more details.

Postgres as an Event Store

To use PostgreSQL as an event store, the user used to connect to the database must be able to create a schema called rejot_events. The rejot-cli will create these schemas for you on launch. If you don’t want to give the rejot_role access to create schemas, you’ll have to manually create these schemas and assign the following roles to the rejot_role.

Hardening the ReJot Database Role

We recommend creating a separate role for ReJot and granting access to that user for production use cases. If you want to get started quickly, you can use an admin user instead. ReJot will create the necessary schemas, databases, and tables automatically.


        CREATE ROLE rejot_role
WITH
  REPLICATION LOGIN PASSWORD '<some secure password>';

Source Data Store Permissions

To be able to read data from a source data store, ReJot must be able to create a replication slot, publication, and must be able to issue SELECT statements on the tables being replicated.

To apply Public Schema transformations, the ReJot Postgres user must have the SELECT permission on the relevant schemas (public by default).


        GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO rejot_role;

Destination Data Store Permissions

For ReJot’s replication to function, we must be able to keep track of the last seen transaction ID and the state of Public Schema transformations. This is done in the rejot_data_store schema. When starting the sync service, ReJot will automatically run migrations to create the necessary schema and tables. To do this, write permissions on the rejot_data_store schema are required.

Event Store Permissions

Similar to the destination data store, ReJot will automatically run migrations to create the necessary schema and tables needed for the event store. This is the rejot_events schema.

Recommendations

For maximum security we recommend creating a separate role for ReJot and granting specific permissions to that role. The replication slot, publication and schemas should be created manually by a system administrator. Then the necessary permissions for the use case should be granted to the ReJot role.

Reference


        -- Create the schemas using a privileged user
CREATE SCHEMA IF NOT EXISTS rejot_events;

CREATE SCHEMA IF NOT EXISTS rejot_data_store;

-- Grant access to these new schemas to your unprivileged user
GRANT CREATE, USAGE ON SCHEMA rejot_events TO rejot_role;
GRANT CREATE, USAGE ON SCHEMA rejot_data_store TO rejot_role;


GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA rejot_data_store TO rejot_role;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA rejot_data_store TO rejot_role;

GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA rejot_events TO rejot_role;

Set Up Postgres Data Store