Security and roles v4

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 BDR doesn't require superuser access, nor is that recommended. The privileges required by BDR are split across the following default/predefined roles, named similarly to the PostgreSQL default/predefined roles:

• bdr_superuser — The highest-privileged role, having access to all BDR 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 BDR.
• 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 BDR.
• bdr_read_all_conflicts — Can view all conflicts in bdr.conflict_history.

These BDR roles are created when the BDR extension is installed. See BDR default roles for more details.

Managing BDR 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 BDR.conflict_history_summary view.

Catalog tables

System catalog and Information Schema tables are always excluded from replication by BDR.

In addition, tables owned by extensions are excluded from replication.

BDR functions and operators

All BDR functions are exposed in the bdr schema. Any calls to these functions must be schema qualified, rather than putting bdr in the search_path.

All BDR operators are available by way of the pg_catalog schema to allow users to exclude the public schema from the search_path without problems.

Granting privileges on catalog objects

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 BDR 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 BDR (and therefore of the BDR 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 is joining.

Role management

Users are global objects in a PostgreSQL instance. CREATE USER and CREATE ROLE commands are replicated automatically if they are executed in the database where BDR 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 BDR database.

When a new BDR node joins the BDR group, existing users aren't automatically copied unless the node is added using bdr_init_physical. This is intentional and is an important security feature. PostgreSQL allows users to access multiple databases, with the default being to access any database. BDR doesn't know which users access which database and so can't safely decide which users to copy across to the new node.

PostgreSQL allows you to dump all users with the command:

pg_dumpall --roles-only > roles.sql

The file roles.sql can then be edited 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.

Roles and replication

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 do not 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 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 BDR and can add or remove any table from any replication set. bdr_superuser doesn't need any privileges over individual tables, nor is this recommended. If you need to restrict access to replication set functions, restricted versions of these functions can be implemented as SECURITY DEFINER functions and granted to the appropriate users.

Connection role

When allocating a new BDR 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.

BDR 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 REPLICATION attribute.
• It is 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, the permissions can be further reduced to just the following to still allow DML and DDL replication:

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

Privilege restrictions

BDR 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 ALL.

Foreign key privileges

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 BDR. 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 is typically too expensive to use successfully.

Triggers

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 BDR, 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."

BDR mitigates this problem by using stricter rules on who can create a trigger on a table:

• superuser
• bdr_superuser
• 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 is owned by the same owner as the table and they satisfy standard PostgreSQL rules (again must have EXECUTE privilege on the function). So if 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, it is assumed that it is 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 BDR.

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 BDR 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 earlier versions.

BDR replication apply uses the system-level default search_path only. Replica triggers, stream triggers, and index expression functions might assume other search_path settings which then fail when they execute on apply. To ensure this doesn't occur, resolve object references clearly using either the default search_path only (always use fully qualified references to objects, e.g., schema.objectname), or set the search path for a function using ALTER FUNCTION ... SET search_path = ... for the functions affected.

BDR default/predefined roles

BDR predefined roles are created when the BDR extension is installed. After BDR extension is dropped from a database, the roles continue to exist and need to be dropped manually if required. This allows BDR to be used in multiple databases on the same PostgreSQL instance without problem.

The GRANT ROLE DDL statement doesn't participate in BDR replication. Thus, execute this on each node of a cluster.

bdr_superuser

• ALL PRIVILEGES ON ALL TABLES IN SCHEMA BDR
• ALL PRIVILEGES ON ALL ROUTINES IN SCHEMA BDR

bdr_read_all_stats

SELECT privilege on

• bdr.conflict_history_summary
• bdr.ddl_epoch
• bdr.ddl_replication
• bdr.global_consensus_journal_details
• bdr.global_lock
• bdr.global_locks
• bdr.local_consensus_state
• bdr.local_node_summary
• bdr.node
• bdr.node_catchup_info
• bdr.node_conflict_resolvers
• bdr.node_group
• bdr.node_local_info
• bdr.node_peer_progress
• bdr.node_slots
• bdr.node_summary
• bdr.replication_sets
• bdr.sequences
• bdr.state_journal_details
• bdr.stat_relation
• bdr.stat_subscription
• bdr.subscription
• bdr.subscription_summary
• bdr.tables
• bdr.worker_errors

EXECUTE privilege on

• bdr.bdr_version
• bdr.bdr_version_num
• bdr.decode_message_payload
• bdr.get_global_locks
• bdr.get_raft_status
• bdr.get_relation_stats
• bdr.get_slot_flush_timestamp
• bdr.get_sub_progress_timestamp
• bdr.get_subscription_stats
• bdr.peer_state_name
• bdr.show_subscription_status

bdr_monitor

All privileges from bdr_read_all_stats, plus

EXECUTE privilege on

• bdr.monitor_group_versions
• bdr.monitor_group_raft
• bdr.monitor_local_replslots

bdr_application

EXECUTE privilege on

• All functions for column_timestamps datatypes
• All functions for CRDT datatypes
• bdr.alter_sequence_set_kind
• bdr.create_conflict_trigger
• bdr.create_transform_trigger
• bdr.drop_trigger
• bdr.get_configured_camo_partner
• bdr.global_lock_table
• bdr.is_camo_partner_connected
• bdr.is_camo_partner_ready
• bdr.logical_transaction_status
• bdr.ri_fkey_trigger
• bdr.seq_nextval
• bdr.seq_currval
• bdr.seq_lastval
• bdr.trigger_get_committs
• bdr.trigger_get_conflict_type
• bdr.trigger_get_origin_node_id
• bdr.trigger_get_row
• bdr.trigger_get_type
• bdr.trigger_get_xid
• bdr.wait_for_camo_partner_queue
• bdr.wait_slot_confirm_lsn

Many of these functions have additional privileges required before you can use them. For example, you must be the table owner to successfully execute bdr.alter_sequence_set_kind. These additional rules are described with each specific function.

bdr_read_all_conflicts

BDR logs conflicts into the bdr.conflict_history table. Conflicts are visible to table owners only, 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.

Verification

BDR was verified using the following tools and approaches.

Coverity

Coverity Scan was used to verify the BDR 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
• AUTOSAR

CIS Benchmark

CIS PostgreSQL Benchmark v1, 19 Dec 2019 was used to verify the BDR stack. Using the cis_policy.yml configuration available as an option with TPAexec gives the following results for the Scored tests:

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.

• On this page
• Catalog tables
• BDR functions and operators
• Granting privileges on catalog objects
• Role management
• Roles and replication
• Connection role
• Privilege restrictions
• BDR default/predefined roles
• Verification