Security and roles v5
Only superusers can create the BDR extension. However, if you want, you can set up the
pgextwlist extension and configure it to allow a non-superuser to create a BDR extension.
Configuring and managing PGD doesn't require superuser access, nor do we recommend that. The privileges required by PGD are split across the following default and predefined roles, named similarly to the PostgreSQL default and predefined roles:
- bdr_superuser — The highest-privileged role, having access to all PGD tables and functions.
- bdr_read_all_stats — The role having read-only access to the tables, views, and functions, sufficient to understand the state of PGD.
- bdr_monitor — At the moment, the same as bdr_read_all_stats. To be extended later.
- bdr_application — The minimal privileges required by applications running PGD.
- bdr_read_all_conflicts — Can view all conflicts in
These PGD roles are created when the BDR extension is installed. See PGD default roles for more details.
Managing PGD doesn't require that administrators have access to user data.
Arrangements for securing conflicts are discussed in Logging conflicts to a table.
You can monitor conflicts using the
System catalog and information schema tables are always excluded from replication by PGD.
In addition, tables owned by extensions are excluded from replication.
All PGD functions are exposed in the
bdr schema. Any calls to these
functions must be schema qualified, rather than putting
bdr in the
All PGD operators are available by way of the
pg_catalog schema to allow users
to exclude the
public schema from the search_path without problems.
Administrators must not grant explicit privileges on catalog objects such as tables, views, and functions. Manage access to those objects by granting one of the roles described in PGD default roles.
This requirement is a consequence of the flexibility that allows joining a node group even if the nodes on either side of the join don't have the exact same version of PGD and therefore of the PGD catalog.
More precisely, if privileges on individual catalog objects were
explicitly granted, then the
bdr.join_node_group() procedure might
fail because the corresponding GRANT statements extracted from the
node being joined might not apply to the node that's joining.
Users are global objects in a PostgreSQL instance.
CREATE USER and
CREATE ROLE commands are replicated automatically if they're
executed in the database where PGD is running and the
bdr.role_replication is turned on. However, if these commands are executed
in other databases in the same PostgreSQL instance, then they aren't replicated,
even if those users have rights on the PGD database.
When a new PGD node joins the PGD group, existing users aren't automatically
copied unless the node is added using
bdr_init_physical. This behavior is intentional
and is an important security feature. PostgreSQL allows users to access multiple
databases, with the default being to access any database. PGD doesn't know
the access each user has to databases and so can't safely determine
the users to copy across to the new node.
PostgreSQL allows you to dump all users with the command:
You can then edit the file
roles.sql to remove unwanted users before
reexecuting that on the newly created node.
Other mechanisms are possible, depending on your identity and access
management solution (IAM) but aren't automated at this time.
DDL changes executed by a user are applied as that same user on each node.
DML changes to tables are replicated as the table-owning user on the target node. We recommend but don't enforce that a table be owned by the same user on each node.
Suppose table A is owned by user X on node1 and owned by user Y on node2. If user Y has higher privileges than user X, this might be viewed as a privilege escalation. Since some nodes have different use cases, we allow this but warn against it to allow the security administrator to plan and audit this situation.
On tables with row-level security policies enabled, changes
are replicated without reenforcing policies on apply.
This behavior is equivalent to the changes being applied as
NO FORCE ROW LEVEL SECURITY, even if
FORCE ROW LEVEL SECURITY is specified.
If this isn't what you want, specify a row_filter that avoids
replicating all rows. We recommend but don't enforce
that the row security policies on all nodes be identical or
at least compatible.
The user bdr_superuser controls replication for PGD and can
add or remove any table from any replication set. bdr_superuser
doesn't need any privileges
over individual tables, nor do we recommend it. If you need to restrict access
to replication set functions, you can implement restricted versions of these
SECURITY DEFINER functions
and grant them to the appropriate users.
When allocating a new PGD node, the user supplied in the DSN for the
local_dsn argument of
bdr.create_node and the
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 the above DSNs. At a minimum, such a user must be granted permissions on all nodes, such that following stipulations are satisfied:
- The user has the
- It's granted the
CREATEpermission 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, the permissions can be further reduced to just the following to still allow DML and DDL replication:
- The user has the
- It inherits the bdr_superuser role.
PGD enforces additional restrictions, effectively preventing the use of DDL that relies solely on TRIGGER or REFERENCES privileges.
GRANT ALL still grants both TRIGGER and REFERENCES privileges,
so we recommend that you state privileges explicitly. For example, use
GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE instead of
ALTER TABLE ... ADD FOREIGN KEY is supported only if the user has
SELECT privilege on the referenced table or if the referenced table
has RLS restrictions enabled that the current user can't bypass.
Thus, the REFERENCES privilege isn't sufficient to allow creating a foreign key with PGD. Relying solely on the REFERENCES privilege isn't typically useful since it makes the validation check execute using triggers rather than a table scan. It's typically too expensive to use successfully.
In PostgreSQL, both the owner of a table and anyone who was granted the TRIGGER privilege can create triggers. Triggers granted by the non-table owner execute as the table owner in PGD, which might cause a security issue. The TRIGGER privilege is seldom used, and PostgreSQL Core Team has said, "The separate TRIGGER permission is something we consider obsolescent."
PGD mitigates this problem by using stricter rules on who can create a trigger on a table:
- Owner of the table can create triggers according to same rules as in PostgreSQL (must have EXECUTE privilege on the function used by the trigger).
- Users who have TRIGGER privilege on the table can create a trigger only if they create the trigger using a function that's owned by the same owner as the table and they satisfy standard PostgreSQL rules. (They must have EXECUTE privilege on the function.) Suppose both table and function have the same owner, and the owner decided to give a user both TRIGGER privilege on the table and EXECUTE privilege on the function. In that case, it's assumed that it's okay for that user to create a trigger on that table using this function.
- Users who have TRIGGER privilege on the table can create triggers using functions that are defined with the SECURITY DEFINER clause if they have EXECUTE privilege on them. This clause makes the function always execute in the context of the owner of the function both in standard PostgreSQL and PGD.
This logic is built on the fact that, in PostgreSQL, the owner of the trigger isn't the user who created it but the owner of the function used by that trigger.
The same rules apply to existing tables, and if the existing table has triggers that aren't owned by the owner of the table and don't use SECURITY DEFINER functions, you can't add it to a replication set.
These checks were added with PGD 3.6.19. An application that
relies on the behavior of previous versions can set
bdr.backwards_compatibility to 30618 or lower to behave like
PGD replication apply uses the system-level default search_path only.
Replica triggers, stream triggers,
and index expression functions might assume other search_path settings that then fail when they
execute on apply. To ensure this doesn't occur, resolve object references clearly using either the default
search_path only, or set the search
path for a function using
ALTER FUNCTION ... SET search_path = ... for the functions affected. When using the default search_path, always use fully qualified references to objects, for example, schema.objectname.
PGD predefined roles are created when the BDR extension is installed. After BDR extension is dropped from a database, the roles continue to exist. You need to drop them manually if dropping is required. This practice allows PGD to be used in multiple databases on the same PostgreSQL instance without problem.
GRANT ROLE DDL statement doesn't participate in PGD replication.
Thus, execute this on each node of a cluster.
- ALL PRIVILEGES ON ALL TABLES IN SCHEMA BDR
- ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA BDR
SELECT privilege on:
EXECUTE privilege on:
All privileges from
Also, EXECUTE privilege on:
EXECUTE privilege on:
- All functions for column_timestamps datatypes
- All functions for CRDT datatypes
Many of these functions require additional privileges
before you can use them. For example, you must be
the table owner to successfully execute
These additional rules are described with each specific function.
PGD logs conflicts into the
bdr.conflict_history table. Conflicts are
visible only to table owners, so no extra privileges are required
to read the conflict history. If it's useful to have a user that can
see conflicts for all tables, you can optionally grant the role
bdr_read_all_conflicts to that user.
PGD was verified using the following tools and approaches.
Coverity Scan was used to verify the PGD stack providing coverage against vulnerabilities using the following rules and coding standards:
- MISRA C
- ISO 26262
- ISO/IEC TS 17961
- OWASP Top 10
- CERT C
- CWE Top 25
CIS PostgreSQL Benchmark v1, 19 Dec 2019 was used to verify the PGD stack.
cis_policy.yml configuration available as an option with
Trusted Postgres Architect gives the following results for the Scored tests:
|1.4||PASS||Ensure systemd Service Files Are Enabled|
|1.5||PASS||Ensure Data Cluster Initialized Successfully|
|2.1||PASS||Ensure the file permissions mask is correct|
|2.2||PASS||Ensure the PostgreSQL pg_wheel group membership is correct|
|3.1.2||PASS||Ensure the log destinations are set correctly|
|3.1.3||PASS||Ensure the logging collector is enabled|
|3.1.4||PASS||Ensure the log file destination directory is set correctly|
|3.1.5||PASS||Ensure the filename pattern for log files is set correctly|
|3.1.6||PASS||Ensure the log file permissions are set correctly|
|3.1.7||PASS||Ensure 'log_truncate_on_rotation' is enabled|
|3.1.8||PASS||Ensure the maximum log file lifetime is set correctly|
|3.1.9||PASS||Ensure the maximum log file size is set correctly|
|3.1.10||PASS||Ensure the correct syslog facility is selected|
|3.1.11||PASS||Ensure the program name for PostgreSQL syslog messages is correct|
|3.1.14||PASS||Ensure 'debug_print_parse' is disabled|
|3.1.15||PASS||Ensure 'debug_print_rewritten' is disabled|
|3.1.16||PASS||Ensure 'debug_print_plan' is disabled|
|3.1.17||PASS||Ensure 'debug_pretty_print' is enabled|
|3.1.18||PASS||Ensure 'log_connections' is enabled|
|3.1.19||PASS||Ensure 'log_disconnections' is enabled|
|3.1.21||PASS||Ensure 'log_hostname' is set correctly|
|3.1.23||PASS||Ensure 'log_statement' is set correctly|
|3.1.24||PASS||Ensure 'log_timezone' is set correctly|
|3.2||PASS||Ensure the PostgreSQL Audit Extension (pgAudit) is enabled|
|4.1||PASS||Ensure sudo is configured correctly|
|4.2||PASS||Ensure excessive administrative privileges are revoked|
|4.3||PASS||Ensure excessive function privileges are revoked|
|4.4||PASS||Tested Ensure excessive DML privileges are revoked|
|5.2||Not Tested||Ensure login via 'host' TCP/IP Socket is configured correctly|
|6.2||PASS||Ensure 'backend' runtime parameters are configured correctly|
|6.7||Not Tested||Ensure FIPS 140-2 OpenSSL Cryptography Is Used|
|6.8||PASS||Ensure SSL is enabled and configured correctly|
|7.3||PASS||Ensure WAL archiving is configured and functional|
Test 5.2 can PASS if audited manually, but it doesn't have an automated test.
Test 6.7 succeeds on default deployments using CentOS, but it requires extra packages on Debian variants.