DDL and role manipulation statements v5

Users are global objects in a PostgreSQL instance, which means they span multiple databases while PGD operates on an individual database level. Because of this behavior, 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.