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. 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 :ref:`bdr.create_node ` and the ``join_target_dsn`` of :ref:`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. Once all nodes are joined, to continue to allow DML and DDL replication, you can reduce the permissions further to the following: - The user has the ``REPLICATION`` attribute. - It inherits the bdr_superuser role.