DDL and role manipulation statements v5

Suggest edits

Users are global objects in a PostgreSQL instance, which means they span multiple databases while PGD operates on an individual database level. This means that role manipulation statement handling needs extra thought.

PGD requires that any roles that are referenced by any replicated DDL must exist on all nodes. The roles don't have to have the same grants, password, and so on, but they must exist.

PGD replicates role manipulation statements if bdr.role_replication is enabled (default) and role manipulation statements are run in a PGD-enabled database.

The role manipulation statements include the following:

  • CREATE ROLE
  • ALTER ROLE
  • DROP ROLE
  • GRANT ROLE
  • CREATE USER
  • ALTER USER
  • DROP USER
  • CREATE GROUP
  • ALTER GROUP
  • DROP GROUP

In general, either:

  • Configure the system with bdr.role_replication = off and deploy all role changes (user and group) by external orchestration tools like Ansible, Puppet, and Chef or explicitly replicated by bdr.replicate_ddl_command().

  • Configure the system so that exactly one PGD-enabled database on the PostgreSQL instance has bdr.role_replication = on and run all role management DDL on that database.

We recommended that you run all role management commands in one database.

If role replication is turned off, then the administrator must ensure that any roles used by DDL on one node also exist on the other nodes. Otherwise PGD apply stalls with an error until the role is created on the other nodes.

PGD with non-PGD-enabled databases

PGD doesn't capture and replicate role management statements when they run on a non-PGD-enabled database in a PGD-enabled PostgreSQL instance. For example, if you have DBs 'bdrdb' (bdr group member) and 'postgres' (bare db), and bdr.role_replication = on, then a CREATE USER run in bdrdb is replicated, but a CREATE USER run in postgres isn't.

← Prev

DDL Command handling matrix

Next →

Workarounds for DDL restrictions