Physical Backup Privileges CheckIn order to perform a physical backup, PostgreSQL requires a role that is allowed to perform several operations, mainly invoke
On old PostgreSQL versions, before version 10, only superusers can invoke the above backup functions and, therefore, can do a physical backup. Since PostgreSQL 10 things have changed and nowdays there are more fine grain permissions. In particular there are a few default roles that can be used to set up a backup role.
This is what I usually do, since working with superuser role can be dangerous, so I do create low profile roles and assign them the required privileges.
Depending on the backup solution you are going to implement, these privileges can be different, so I decided to create a view that can help inspecting the status of the roles available in the database.
The view works as follows:
- extract a set of flags;
- merge with logical
- provide some additional flags.
Using the view results in something like the following:
backupdb=> select * from vw_role_backup_privileges WHERE rolname IN ( 'luca', 'backup' ); -[ RECORD 1 ]------------|------- rolname | backup can_do_backup | t can_monitor_backup | t can_create_restore_point | t can_switch_wal | t -[ RECORD 2 ]------------|------- rolname | luca can_do_backup | f can_monitor_backup | f can_create_restore_point | f can_switch_wal | f
As you can see, the
backuprole, even if not a superuser, can do backups, can monitor them, can create restore points and force a WAL switch.
Of course, the above is not a one size fits all solution, since every backup solution could require different permissions, however this is a possible starting point to check the status of the users.
In the following I describe the single pieces of the view.
What is required to do a physical backup?The minimal set of privileges required to perform a backup are:
- permission to start a replication;
- permission to invoke
This is done by the part:
f.can_start_replication AND f.pg_start_backup AND ( f.pg_stop_backup OR f.pg_stop_backup_exclusive )
where I check the above requirements.
Additinal requirementsbeing able to start and stop a backup could not suffice: the user could be required to monitor the backup. Monitoring always means being able to query the statistic data and the configuration of the cluster. The former can be used to see if the replication is working fine, while the latter to check the archiving setup.
PostgreSQL provides a
pg_monitorrole that can do the above queries, otherwise the user could need two different roles, namely
pg_monitorincludes the above two roles, assigning
pg_monitoris equivalent to assign the latter two roles. It could also be required to be able to query the
pg_is_in_backup()function, that indicates if the cluster is actually in physical backup mode.
This means that I need to check:
f.pg_monitor OR ( f.pg_read_all_settings AND f.pg_read_all_stats AND f.pg_is_in_backup )
Switch WALs and create restore pointsStarting a backup could also require the user to issue an immediate switch of the WALs in order to quickly start the backup.
Moreover, it could be required to create a restore point, for example to mark in the WALs that the backup has started at a specific point in time.
This mean that the check is:
f.pg_create_restore_point AS can_create_restore_point , f.pg_switch_wal AS can_switch_wal
Putting everything togetherHaving stated the above list of requirements, the query can be split into two parts:
- a CTE that extracts the flags;
- a query that composes the flags.
To extract the flags, the following CTE can be used:
WITH flags AS ( SELECT a.rolname , a.rolsuper AS is_superuser , a.rolreplication AS can_start_replication , pg_has_role( a.rolname, 'pg_monitor', 'USAGE' ) AS pg_monitor , pg_has_role( a.rolname, 'pg_read_all_settings', 'USAGE' ) as pg_read_all_settings , pg_has_role( a.rolname, 'pg_read_all_stats', 'USAGE' ) as pg_read_all_stats , has_function_privilege( a.rolname, 'pg_start_backup( text, bool, bool )', 'EXECUTE' ) as pg_start_backup , has_function_privilege( a.rolname, 'pg_stop_backup( bool, bool )', 'EXECUTE' ) as pg_stop_backup , has_function_privilege( a.rolname, 'pg_stop_backup()', 'EXECUTE' ) as pg_stop_backup_exclusive , has_function_privilege( a.rolname, 'pg_create_restore_point( text )', 'EXECUTE' ) as pg_create_restore_point , has_function_privilege( a.rolname, 'pg_is_in_backup()', 'EXECUTE' ) as pg_is_in_backup , has_function_privilege( a.rolname, 'pg_switch_wal()', 'EXECUTE' ) as pg_switch_wal FROM -- use pg_roles instead of pg_authid -- to allow non-superuser roles to query pg_roles a )
I do query
pg_rolesthat contain all the information that is found in
pg_authidbut do not require superuser privileges to be queried.
Please note that I check role group membership with the
USAGEprivilege, that means that the role does not have to do an explicit
SET ROLEto gain access to the privileges from the group it belongs to, that is it has been created
Then, composing the flags is as simple as:
SELECT f.rolname , f.is_superuser OR ( f.can_start_replication AND f.pg_start_backup AND ( f.pg_stop_backup OR f.pg_stop_backup_exclusive ) ) AS can_do_backup , f.pg_monitor OR ( f.pg_read_all_settings AND f.pg_read_all_stats AND f.pg_is_in_backup ) AS can_monitor_backup , f.pg_create_restore_point AS can_create_restore_point , f.pg_switch_wal AS can_switch_wal FROM flags f;