Security and Roles¶

The BDR extension can be created only by superusers, although if desired, it is possible to set up the pgextwlist extension and configure it to allow BDR to be created by a non-superuser.

Configuring and managing BDR does not require superuser access, nor is that recommended.The privileges required by BDR are split across the following default/predefined roles, namedsimilarly 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 isinstalled. See [BDR Default Roles] below for more details.

Managing BDR does not require that administrators have access to user data.

Arrangements for securing conflicts are discussed hereLogging Conflicts to a Table.

Conflicts may be monitored 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 & Operators¶

All BDR functions are exposed in the bdr schema. Any calls to thesefunctions should be schema qualified, rather than putting bdr in thesearch_path.

All BDR operators are available via pg_catalog schema to allow usersto exclude the public schema from the search_path without problems.

Granting privileges on catalog objects¶

Administrators should not grant explicit privileges on catalogobjects such as tables, views and functions; manage access to those objectsby granting one of the roles documented in [BDRDefault Roles].

This requirement is a consequence of the flexibility that allowsjoining a node group even if the nodes on either side of the join donot have the exact same version of BDR (and therefore of the BDRcatalog).

More precisely, if privileges on individual catalog objects have beenexplicitly granted, then the bdr.join_node_group() procedure couldfail because the corresponding GRANT statements extracted from thenode 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 theyare executed in the database where BDR is running and thebdr.role_replication is turned on. However, if these commands are executedin other databases in the same PostgreSQL instance then they will not be replicated,even if those users have rights on the BDR database.

When a new BDR node joins the BDR group, existing users are not automaticallycopied unless the node is added using bdr_init_physical. This is intentionaland is an important security feature. PostgreSQL allows users to access multipledatabases, with the default being to access any database. BDR does not knowwhich users access which database and so cannot safely decidewhich 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 beforere-executing that on the newly created node.Other mechanisms are possible, depending on your identity and accessmanagement solution (IAM), but are not 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.It is recommended - but not enforced - that a table is owned by the same user on each node.

If table A is owned by user X on node1 and owned by user Y on node2, then if user Yhas higher privileges than user X, this could be viewed as a privilege escalation.Since some nodes have different use cases, we allow this but warn against itto allow the security administrator to plan and audit this situation.

On tables with row level security policies enabled, changeswill be replicated without re-enforcing policies on apply.This is equivalent to the changes being applied asNO FORCE ROW LEVEL SECURITY, even ifFORCE ROW LEVEL SECURITY is specified.If this is not desirable, specify a row_filter that avoidsreplicating all rows. It is recommended - but not enforced -that the row security policies on all nodes be identical orat least compatible.

Note that bdr_superuser controls replication for BDR and mayadd/remove any table from any replication set. bdr_superuserdoes not need, nor is it recommended to have, any privilegesover individual tables. If the need exists to restrict accessto replication set functions, restricted versions of thesefunctions can be implemented as SECURITY DEFINER functionsand GRANTed to the appropriate users.

Connection Role¶

When allocating a new BDR node, the user supplied in the DSN for thelocal_dsn argument of bdr.create_node and the join_target_dsn ofbdr.join_node_group are used frequently to refer to, create, andmanage database objects.

BDR is carefully written to prevent privilege escalation attacks evenwhen using a role with SUPERUSER rights in these DSNs.

To further reduce the attack surface, a more restricted user may bespecified in the above DSNs. At a minimum, such a user must begranted permissions on all nodes, such that following stipulations aresatisfied:

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 via

permissions from the owner role(s).

Once all nodes are joined, the permissions may be further reduced tojust 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 theuse of DDL that relies solely on TRIGGER or REFERENCES privileges.The following sub-sections explain these.

GRANT ALL will still grant both TRIGGER and REFERENCES privileges,so it is recommended that you state privileges explicitly, e.g.GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE instead of ALL.

Foreign Key Privileges¶

ALTER TABLE ... ADD FOREIGN KEY is only supported if the user hasSELECT privilege on the referenced table, or if the referenced tablehas RLS restrictions enabled which the current user cannot bypass.

Thus, the REFERENCES privilege is not sufficient to allow creationof a Foreign Key with BDR. Relying solely on the REFERENCES privilegeis not typically useful since it makes the validation check executeusing triggers rather than a table scan, so is typically too expensiveto used successfully.

Triggers¶

In PostgreSQL, triggers may be created by both the owner of a table and anyone whohas been granted the TRIGGER privilege. Triggers granted by the non-table ownerwould execute as the table owner in BDR, which could 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 triggeron a table:

```
superuser
```
```
bdr_superuser
```

Owner of the table can create triggers according to same rules as in PostgreSQL

(must have EXECUTE privilege on function used by the trigger).

```
Users who have TRIGGER privilege on the table can only create a trigger 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 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 itself both in standard PostgreSQL and BDR.

The above logic is built on the fact that in PostgreSQL, the owner of the triggeris not 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 whichare not owned by the owner of the table and do not use SECURITY DEFINER functions,it will not be possible to add it to a replication set.

These checks were added with BDR 3.6.19. An application thatrelies on the behavior of previous versions can setbdr.backwards_compatibility to 30618 (or lower) to behave likeearlier versions.

BDR replication apply uses the system-level default search_path only.Replica triggers, stream triggersand index expression functions may assume other search_path settings which will then fail when theyexecute on apply. To ensure this does not occur, resolve object references clearly using either the defaultsearch_path only (always use fully qualified references to objects, e.g. schema.objectname), or set the searchpath 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.Note that after BDR extension is dropped from a database, the roles continue to existand need to be dropped manually if required. This allows BDR to be used in multipledatabases on the same PostgreSQL instance without problem.

Remember that the GRANT ROLE DDL statement does not participate in BDR replication,thus you should 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.conflict_resolution_to_string`
```
```
`bdr.conflict_type_to_string`
```
```
`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`
```

Note that many of the above functions have additional privilegesrequired before they can be used, for example, you must bethe table owner to successfully execute bdr.alter_sequence_set_kind.These additional rules are documented with each specific function.

bdr_read_all_conflicts¶

BDR logs conflicts into the bdr.conflict_history table. Conflicts arevisible to table owners (only), so no extra privileges are requiredto read the conflict history. If it is useful to have a user that cansee conflicts for all tables, you may optionally grant the rolebdr_read_all_conflicts to that user.

Verification¶

BDR has been verified using the following tools and approaches.

Coverity¶

Coverity Scan has been used to verify the BDR stack providing coverageagainst 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 has been used to verify the BDR stack.Using the cis_policy.yml configuration available as an option with TPAexecgives the following results for the Scored tests:

:header:, Result, Description¶
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

Note that test 5.2 can PASS if audited manually, but does not have anautomatable test.

Test 6.7 succeeds on default deployments using CentOS, but itrequires extra packages on Debian variants.

Security and Roles¶

Catalog Tables¶

BDR Functions & Operators¶

Granting privileges on catalog objects¶

Role Management¶

Roles and Replication¶

Connection Role¶

Privilege Restrictions¶

Foreign Key Privileges¶

Triggers¶

BDR Default/Predefined Roles¶

bdr_superuser¶

bdr_read_all_stats¶

bdr_monitor¶

bdr_application¶

bdr_read_all_conflicts¶

Verification¶

Coverity¶

CIS Benchmark¶

Previous topic

Next topic

Table of Contents