30.3. Separation of Duties between Privileged DBMS Users #
The configuration of all PostgreSQL-based DBMS has the superuser role, which is needed at the initial database loading, but when later used for regular database operations, it carries information security risks. As the superuser account has a wide range of system privileges, it can become a target for cybercriminals and can cause adverse consequences, such as:
Unauthorized access to sensitive data.
Data leak.
Dangerous changes to the DBMS configuration.
DBMS failures.
Among the measures to resist such hazards, we can consider separation of duties, with reducing the number of operations that require superuser privileges. This is necessary to address risks both related to the trust in the superuser and to access of malicious users to the superuser account.
30.3.1. Overview #
The Postgres Professional company has developed a model that separates superuser duties between two additional administrative roles — DBMS Administrator and Database Administrator (DB Administrator). Besides, mechanisms of protection against self-elevation of privileges and audit of all users' operations have been strengthened.
The DBMS Administrator is responsible for:
The server management.
Setup of the data replication and backups.
Creation of databases.
Setup of connections with the Postgres Pro database.
Assignment of DB Administrators.
The DB Administrator is responsible for:
Backup of the specific database.
Creation of tables and other objects within the specific database.
Creation of database users.
Granting users with access rights.
Once most of the superuser's regular tasks are delegated to the new controlled administrators, the company can give up active use of this highly risky role. To do this, the infrastructure administrator must add rows that block superuser connections to the pg_hba.conf file and must also block updating this file by other administrators.
If superuser privileges must be temporarily returned to solve some rare complicated problem, this can be done in a single mode involving the infrastructure administrator. To switch the DBMS to a single mode, it should be stopped and then restarted as follows:
postgres --single -D DBMS_data_directory other_options DB_name
See Section 16.2 for how to install additional supplied modules and extensions without superuser permissions.
The pg_proaudit extension, which enables logging various security events, is described in Section F.38.
30.3.2. Creation of Additional Administrators #
30.3.2.1. Creating the DBMS Administrator Role #
This section describes creation of the PGPRO_DBMS_ADMIN role with the appropriate permissions.
The following script creates the PGPRO_DBMS_ADMIN role and the user with this role. It is run by the superuser postgres. Note that the script uses these predefined roles:
pg_create_tablespaceallows executing theCREATE TABLESPACEcommand without superuser rights.pg_manage_profilesallows executing theCREATE PROFILE,ALTER PROFILE, andDROP PROFILEcommands without superuser rights.
$ psql postgres CREATE ROLE PGPRO_DBMS_ADMIN WITH CREATEDB CREATEROLE INHERIT LOGIN REPLICATION; REVOKE ALL ON DATABASE postgres FROM PUBLIC; GRANT CONNECT ON DATABASE postgres TO PGPRO_DBMS_ADMIN; CREATE USER dbms_admin WITH CREATEDB CREATEROLE INHERIT LOGIN REPLICATION; -- Do not forget to set initial password GRANT PGPRO_DBMS_ADMIN TO dbms_admin; GRANT pg_read_all_settings TO PGPRO_DBMS_ADMIN WITH ADMIN OPTION; GRANT pg_read_all_stats TO PGPRO_DBMS_ADMIN WITH ADMIN OPTION; GRANT pg_stat_scan_tables TO PGPRO_DBMS_ADMIN WITH ADMIN OPTION; GRANT pg_monitor TO PGPRO_DBMS_ADMIN WITH ADMIN OPTION; GRANT pg_signal_backend TO PGPRO_DBMS_ADMIN WITH ADMIN OPTION; GRANT pg_checkpoint TO PGPRO_DBMS_ADMIN WITH ADMIN OPTION; GRANT pg_create_tablespace TO PGPRO_DBMS_ADMIN WITH ADMIN OPTION; GRANT pg_manage_profiles TO PGPRO_DBMS_ADMIN WITH ADMIN OPTION; EXIT;
The following commands grant permissions to manage the configuration and to perform logging and backup/restore operations to the PGPRO_DBMS_ADMIN role. It is run by the superuser postgres.
$ psql postgres GRANT EXECUTE ON FUNCTION pg_reload_conf TO PGPRO_DBMS_ADMIN; GRANT EXECUTE ON FUNCTION pg_rotate_logfile TO PGPRO_DBMS_ADMIN; GRANT EXECUTE ON FUNCTION pg_create_restore_point TO PGPRO_DBMS_ADMIN; GRANT EXECUTE ON FUNCTION pg_backup_start TO PGPRO_DBMS_ADMIN; GRANT EXECUTE ON FUNCTION pg_backup_stop TO PGPRO_DBMS_ADMIN; GRANT EXECUTE ON FUNCTION pg_switch_wal TO PGPRO_DBMS_ADMIN; GRANT EXECUTE ON FUNCTION pg_promote TO PGPRO_DBMS_ADMIN; GRANT EXECUTE ON FUNCTION pg_wal_replay_pause TO PGPRO_DBMS_ADMIN; GRANT EXECUTE ON FUNCTION pg_wal_replay_resume TO PGPRO_DBMS_ADMIN; EXIT;
30.3.2.2. Creating the DB Administrator Role #
The PGPRO_DB_DBNAME_ADMIN role can include several users for one database. One user can also be included in several different PGPRO_DB_DBNAME_ADMIN roles if the user's job description includes management of several different databases.
This section describes creation of a database by example of the DB1 database, its DB Administrator role and the DB1_ADMIN user granted appropriate rights. At this point, rights of the user with the PGPRO_DBMS_ADMIN role, which was created earlier, are sufficient for all the involved operations.
The following script creates the DB1 database, the PGPRO_DB_1_ADMIN role and the user with this role. It is run by the DBMS Administrator:
$ psql postgres -U dbms_admin SET ROLE PGPRO_DBMS_ADMIN; CREATE ROLE PGPRO_DB_1_ADMIN WITH CREATEROLE INHERIT; CREATE USER db1_admin WITH CREATEROLE INHERIT; -- Do not forget to set initial password GRANT PGPRO_DB_1_ADMIN TO db1_admin; GRANT PGPRO_DB_1_ADMIN TO PGPRO_DBMS_ADMIN; GRANT pg_read_all_settings TO PGPRO_DB_1_ADMIN; GRANT pg_read_all_stats TO PGPRO_DB_1_ADMIN; GRANT pg_stat_scan_tables TO PGPRO_DB_1_ADMIN; GRANT pg_monitor TO PGPRO_DB_1_ADMIN; CREATE DATABASE db1 OWNER PGPRO_DB_1_ADMIN; REVOKE CONNECT, TEMPORARY ON DATABASE db1 FROM PUBLIC;
30.3.3. Revoking Superuser Access #
To block superuser connections, the infrastructure administrator adds these lines to pg_hba.conf:
TYPE DATABASE USER ADDRESS METHOD local all postgres reject host all postgres 127.0.0.1/32 reject host all postgres ::1/128 reject
The root infrastructure administrator disallows the postgres user to change pg_hba.conf:
# chown root pg_hba.conf # chmod 640 pg_hba.conf
As a result, the postgres superuser cannot solely regain the possibility to connect to the DBMS:
# ls -lh pg_hba.conf -rw-r----- 1 root postgres .......... pg_hba.conf
Restart of the DBMS server is needed for the new restrictions to take effect:
# pg_ctl restart