Role management =============== 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. .. Note:: Remember that a user in Postgres terms is simply a role with login privileges. 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: .. code:: sql 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.