Role management v6.0.1

Users are global objects in a PostgreSQL instance. A CREATE ROLE command or its alias CREATE USER is replicated automatically if it's executed in a PGD replicated database. If a role or user is created in a non-PGD, unreplicated database, the role exists only for that PostgreSQL instance. GRANT ROLE and DROP ROLE work the same way, replicating only if applied to a PGD-replicated database.

Role rule - No un-replicated roles

If you do create a role or user in a non-PGD, unreplicated database, it's especially important that you do not make an object in the PGD-replicated database rely on that role. It will break the replication process, as PGD cannot replicate a role that is not in the PGD-replicated database.

You can disable this automatic replication behavior by turning off the bdr.role_replication setting, but we don't recommend that.

Roles for new nodes

New PGD nodes that are added using bdr_init_physical will automatically replicate the roles from other nodes of the PGD cluster.

Starting with PGD 6.0.1, when a PGD node is manually joined to a PGD group without using bdr_init_physical, existing roles are automatically copied to the newly joined node. This means that you no longer need to create roles manually on the new node before joining it to the group.

When roles are copied to a new node, if there are existing roles (or tablespaces) with the same name, the new node's existing roles (or tablespaces) will be updated to share the same settings (including passwords) as the roles (or tablespaces) on the source node in the join operation.

Connections and roles

When allocating a new PGD node, the user supplied in the DSN for the local_dsn argument of bdr.create_node and the join_target_dsn of bdr.join_node_group are used frequently to refer to, create, and manage database objects.

PGD is carefully written to prevent privilege escalation attacks even when using a role with SUPERUSER rights in these DSNs.

To further reduce the attack surface, you can specify a more restricted user in these DSNs. At a minimum, such a user must be granted permissions on all nodes, such that following stipulations are satisfied:

• The user has the REPLICATION attribute.
• It's granted the CREATE permission on the database.
• It inherits the bdr_superuser role.
• It owns all database objects to replicate, either directly or from permissions from the owner roles.

Also, if any non-default extensions (excluding the BDR extension) are present on the source node, and any of these can be installed only by a superuser, a superuser must create these extensions manually on the join target node. Otherwise the join process will fail.

In PostgreSQL 13 and later, you can identify the extensions requiring superuser permission and that must be manually installed. On the source node, execute:

    SELECT name, (trusted IS FALSE AND superuser) AS superuser_only
      FROM pg_available_extension_versions
     WHERE installed AND name != 'bdr';

Once all nodes are joined, to continue to allow DML and DDL replication, you can further reduce the permissions to the following:

• The user has the REPLICATION attribute.
• It inherits the bdr_superuser role.