Configuring PostgreSQL
Configuring your existing Postgres database for Springtail.
Creating the Springtail user
To read the logical replication stream, Springtail requires a user account in your existing primary PostgreSQL instance. We recommend creating a new user specifically for Springtail.
Option 1: Superuser access
Creating a user with SUPERUSER
privileges allows Springtail to fully manage replication slots, publications, and triggers for the logical replication stream.
In Amazon RDS, the user must be granted the rds_superuser
role.
To create this user, use the following command, replacing <secret_password>
with your desired password:
Make sure you use a strong password, and keep it handy for instance setup.
Option 2: Read-only user
Alternatively, you can create a read-only user with restricted access. This user needs:
-
CONNECT access to the databases to be replicated.
-
SELECT access to tables within those databases for replication purposes.
-
REPLICATION role to start replication and query the replication slot (or
rds_replication
role in AWS). -
Permission to execute a function that exposes users that are allowed to log in to the Springtail Proxy. See details in the user authentication section below.
In this case, you will need to set up and manage replication slots, publications, and triggers manually. Springtail will provide scripts to perform these tasks, but you’ll be responsible for executing them correctly.
To create this user, use the following command, replacing <secret_password>
with your desired password:
After creating the user, grant CONNECT
access to each database:
Finally, connect to each database and grant SELECT
access:
Make sure you use a strong password, and keep it handy for instance setup.
Replication slots, publications, triggers and functions
To set up logical replication in each database, Springtail creates one publication, one replication slot, and two triggers that utilize three custom functions.
-
A publication in PostgreSQL captures a set of changes (inserts, updates, and deletes) made to a specific table or set of tables in a database that can be sent to subscribers for replication. This is what defines the set of tables being replicated and captures their changes.
-
A replication slot in PostgreSQL ensures that the changes made in the primary database are retained until they are consumed by a subscriber. It acts as a buffer that stores the changes for a subscriber until they are applied. This ensures that Springtail does not miss any changes to the data, even in the face of network disconnect, database restart, or other such issues.
-
The triggers are created to capture schema changes within the database by emitting custom messages into the replication stream that Springtail can use to keep it’s copy of the schemas up-to-date. It also ensures that tables created without a primary key are marked with
REPLICA IDENTITY FULL
which will ensure that the entire row is sent whenever updates or deletes are performed against the table.
Authenticating existing users in Springtail
Springtail authenticates users when they log in to the Springtail Proxy. Springtail’s Proxy performs user authentication based on the users that exist in the Primary database and only allows those users that have CONNECT access to query the replicated database. The Proxy caches the set of users and the databases to which they have access, and refreshes this data every few seconds. Currently, no other access checks are performed on users accessing replicated data (e.g., row level permissions or table level access checks).
When setting up a database instance, a function named springtail_get_user_access()
is installed on the primary database instance, allowing the Springtail user to query the details of existing Postgres users. The information exposed includes their username, their hashed password (MD5 or SCRAM-SHA-256), and the set of databases to which they are allowed to connect, and it is used by the Springtail proxy to authenticate users and limit their access to only the databases they should see.
Limiting access (optional)
By default the springtail_get_user_access()
function will expose all users on the Postgres instance with login permission and are using either a MD5 or SCRAM-SHA-256 password. It is possible to limit the set of users returned by this function by adding the springtail_user
role to a user (the role does not have to convey any permissions). If the springtail_user
role exists, then only those users with this role will be returned by the function.
To create the springtail_user
role:
To assign the role to an existing user called john
:
To revoke the role from a user called john
:
Enabling the function for a read-only Springtail user
If, when setting up Postgres, a read-only user is created as the Springtail user (Option 2 above), then that user must be given permission to execute the function for retrieving user credentials.
Assuming a user called springtail
was created as the read-only user: