| 30.3. Separation of Duties between Privileged DBMS Users | ||||
|---|---|---|---|---|
| Prev | Up | Chapter 30. Enhanced Security | Home | Next |
30.3. Separation of Duties between Privileged DBMS Users#
The configuration of allPostgreSQL-based DBMS has thesuperuser role, which is needed at the initial database loading, but when later used for regular database operations, it carries information security risks. As thesuperuser 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 thePostgres 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 thepg_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
SeeSection 16.2 for how to install additional supplied modules and extensions without superuser permissions.
Thepg_proaudit extension, which enables logging various security events, is described inSection F.39.
30.3.2. Creation of Additional Administrators#
30.3.2.1. Creating the DBMS Administrator Role#
This section describes creation of thePGPRO_DBMS_ADMIN role with the appropriate permissions.
The following script creates thePGPRO_DBMS_ADMIN role and the user with this role. It is run by the superuserpostgres. Note that the script uses thesepredefined roles:
pg_create_tablespaceallows executing theCREATE TABLESPACEcommand without superuser rights.pg_manage_profilesallows executing theCREATE PROFILE,ALTER PROFILE, andDROP PROFILEcommands without superuser rights.
$ psql postgresCREATE 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 passwordGRANT 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 thePGPRO_DBMS_ADMIN role. It is run by the superuserpostgres.
$ psql postgresGRANT 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#
ThePGPRO_DB_DBNAME_ADMIN role can include several users for one database. One user can also be included in several differentPGPRO_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 theDB1 database, its DB Administrator role and theDB1_ADMIN user granted appropriate rights. At this point, rights of the user with thePGPRO_DBMS_ADMIN role, which was created earlier, are sufficient for all the involved operations.
The following script creates theDB1 database, thePGPRO_DB_1_ADMIN role and the user with this role. It is run by the DBMS Administrator:
$ psql postgres -U dbms_adminSET 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 passwordGRANT 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 topg_hba.conf:
TYPE DATABASE USER ADDRESS METHODlocal all postgres rejecthost all postgres 127.0.0.1/32 rejecthost all postgres ::1/128 reject
Theroot infrastructure administrator disallows thepostgres user to changepg_hba.conf:
# chown root pg_hba.conf# chmod 640 pg_hba.conf
As a result, thepostgres 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