Role management v5

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.

If a PGD node is joined to a PGD group manually, without using bdr_init_physical, existing roles aren't copied to the newly joined node. This is intentional behavior to ensure that access isn't accidentally granted.

Roles, by default, have access to all databases. Automatically copying users to a newly created node would therefore open the possibility of unintentionally exposing a database that exists on the node. If the PGD cluster had a user Alice, and Alice was automatically created on a newly joined node, by directly connecting to that node, Alice would have access to all the databases on that node. PGD therefore doesn't copy over existing roles. In this situation, we recommend that you copy over roles manually.

PostgreSQL allows you to dump all roles with the command:

pg_dumpall --roles-only > roles.sql

You can then edit the file roles.sql to remove unwanted users before running the file's commands on the newly created node.

When joining a new node, the “No unreplicated roles” rule also applies. If an object in the database to be replicated relies on an unreplicated role in an (unreplicated) local database, then the join fails when it attempts to replicate that object.

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.