7.2 Ensure a replication-only user is created and used for streaming replication

Information

Create a new user specifically for use by streaming replication instead of using the superuser account.
Rationale:
As it is not necessary to be a superuser to initiate a replication connection, it is proper to create an account specifically for replication. This allows further 'locking down' the uses of the superuser account and follows the general principle of using the least privileges necessary.

Solution

It will be necessary to create a new role for replication purposes:
postgres=# create user replication_user REPLICATION encrypted password 'XXX';
CREATE ROLE
postgres=# select rolname from pg_roles where rolreplication is true;
rolname
------------------
postgres
replication_user
(2 rows)
When using pg_basebackup (or other replication tools) and when configuring recovery.conf on your standby server, you would use the replication_user (and its password).
Ensure you allow the new user via your pg_hba.conf file:
# note that 'replication' in the 2nd column is required and is a special
# keyword, not a real database
hostssl replication replication_user 0.0.0.0/0 md5

See Also

https://workbench.cisecurity.org/files/2235

Item Details

Category: ACCESS CONTROL

References: 800-53|AC-2, CSCv6|5.1, CSCv7|4

Plugin: PostgreSQLDB

Control ID: a4eab9ee7b3fe15e477fd6e22b48f905267e0c26c3e58e2bfecff5a74abba81a