Skip to main content
Tungsten Clustering

Roles and Privileges

Role statements are replicated:

  • CREATE ROLE
  • ALTER ROLE
  • DROP ROLE, and the
  • USER and GROUP forms.

Send them through the Connector and every node gets them.

The limitation​

A role belongs to the whole PostgreSQL server. Replication covers one database only. So a replica can already have a role that arrives over the replication channel.

When that happens, the statement fails on the replica. The replicator stops with an error and goes OFFLINE:ERROR. Replication does not continue until someone fixes it by hand.

Two situations cause this:

  1. Someone creates or drops a role directly on a node, instead of through the Connector.
  2. Two replicated databases in the same cluster carry the same role statement.

DROP ROLE has the same problem in reverse. If the role is already gone on the replica, the statement fails and replication stops.

How to avoid it​

Send every role change through the Connector. Never create or drop a role directly on a node.

If a role must exist on one node only, give it a name that no replicated statement will ever use.

Check that your existing nodes hold the same roles before you start replicating. A role that exists on a replica but not on the primary will break the first statement that tries to create it.

How to recover​

If replication has already stopped:

  1. Read the error to find the role and the node.
  2. On that node, make the role match what the statement expects. Drop it if the statement was CREATE ROLE. Create it if the statement was DROP ROLE.
  3. Put the replicator back online:
shell> trepctl online

If you would rather skip the statement than fix the role, use the sequence number from the error message:

trepctl online -skip-seqno <seqno>

Skipping leaves the two nodes different. Only do this when you know the role on the replica is already correct.

GRANT and REVOKE​

Privileges on tables and other objects inside the database replicate normally.

GRANT and REVOKE on a database, on a tablespace, or on role membership change server-wide state.

Treat them like role statements: send them through the Connector, and apply them by hand on each node if you manage them outside the cluster.