32.4.  Restricting DBMS Administrator's Data Access #

Based on the Separation of Duties between Privileged DBMS Users technology, the Postgres Professional company has created a mechanism to block DBMS Administrator's and DB Administrator's data access, including viewing, but allow them to continue performing administration such as backup/restore, diagnostics, DB creation, deletion, and configuring.

32.4.1. Overview #

Sensitive information is stored in tables, which are contained in DB schemas. By default, the DBMS Administrator and DB Administrators of appropriate databases can access this information, which makes a real issue. To protect data, Postgres Pro Enterprise provides an enhanced security mechanism — the Postgres Pro secured schema and defines access rules for this schema.

This schema, described in Section 5.9.3, will have a dedicated schema owner and security officer. They must be different DB users:

  • The security officer (manager of access rights) will only be able to grant or revoke user access privileges for schema objects, but will be unable to work with schema objects: create, view, or modify them.

  • The schema owner will be able to work with schema objects.

A superuser must create the owner and security officer of the secured schema. Hence, to create them and grant privileges, it is needed to switch the DBMS to a single mode and temporarily allow login of a superuser under the control of the trusted infrastructure administrator.

32.4.2. Creating Administrators and Managers of the DB Secured Schema #

32.4.2.1. Granting a Temporary postgres Superuser Right for a DB Login from the Local Console #

The infrastructure administrator root adds lines to pg_hba.conf that allow superuser connections from the local console:

TYPE    DATABASE    USER      ADDRESS       METHOD
local   all         postgres                peer
host    all         postgres  127.0.0.1/32  reject
host    all         postgres  ::1/128       reject

DBMS server restart is needed for the new restrictions to take effect:

# pg_ctl restart

32.4.2.2. Creating Managers of the Secured Schema #

The process of creating a dedicated owner (manager) of the secured schema DV_OWNER and a security officer (manager of access rights) DV_SEC_OFFICER requires involvement of a superuser because if these accounts are created by the DBMS Administrator or DB_Administrator, these administrators will still have the ADMIN OPTION right, which they even cannot revoke.

To address risks both related to the trust in administrators, who would retain the possibility to manage DV_OWNER and DV_SEC_OFFICER, and to access of malicious users to their accounts, the superuser performs the operations needed.

To simplify, this example assumes that the secured schema will be created in the DB1 database.

$ psql postgres
CREATE USER DV_OWNER WITH LOGIN; -- Do not forget to set initial password
CREATE USER DV_SEC_OFFICER WITH LOGIN; -- Do not forget to set initial password
GRANT CONNECT ON DATABASE db1 TO DV_SEC_OFFICER;
GRANT CONNECT ON DATABASE db1 TO DV_OWNER;
EXIT;

32.4.2.3. Creating the Secured Schema #

Creation of the secured schema is a critical activity, which must involve the superuser, who needs a temporary permission for the DB login.

For a role to be able to create an object that will be owned by a different role or to grant ownership of an existing object to a different role, the SET ROLE right is needed, otherwise the command ALTER ... OWNER TO or CREATE DATABASE ... OWNER will issue an error, as explained in GRANT.

As the DV_OWNER is independent, access of the DBMS Administrator or DB Administrator to this role through SET ROLE should not be granted, and these administrators should not have ownership rights for SQL objects. Hence, creation of the secured schema by the superuser is the best way.

In the commands below, pay attention to the modified syntax of the ALTER SCHEMA command. The SECURITY OFFICER/RESET SECURITY OFFICER clause sets/resets the security officer, respectively. When you set the security officer, the schema becomes the vault schema, as described in ALTER SCHEMA.

$ psql db1 -U postgres
CREATE SCHEMA vault;
GRANT ALL ON SCHEMA vault TO DV_OWNER;
GRANT USAGE ON SCHEMA vault to DV_SEC_OFFICER;
ALTER SCHEMA vault OWNER TO DV_OWNER;
ALTER SCHEMA vault SECURITY OFFICER TO DV_SEC_OFFICER;
EXIT;

32.4.2.4. Protecting Secured Schema Users from Administrators #

Users with access to the secured schema must be strongly protected. You cannot permit DBMS Administrator and DB Administrator have the ADMIN OPTION for secured schema users as in this case, these administrators may potentially get unauthorized access to the secured schema data.

To prevent from this vulnerability, the ADMIN OPTION for secured schema users must be revoked from other users by the REVOKE ADMIN OPTION command. Let's see how to do this by example of the vault_user user, which will be created by the PGPRO_DB_1_ADMIN role:

$ psql db1 -U db1_admin
CREATE USER vault_user; -- Do not forget to set passwords
GRANT CONNECT ON DATABASE db1 TO vault_user;
EXIT;

First, let's check who has ADMIN OPTION for vault_user:

$ psql db1 -U postgres
SELECT rn.rolname "role", mn.rolname member, gn.rolname grantor, am.admin_option
FROM pg_auth_members am
JOIN pg_roles rn ON rn.oid = am.roleid
JOIN pg_roles mn ON mn.oid = am.member
JOIN pg_roles gn ON gn.oid = am.grantor
WHERE rn.rolname = 'vault_user' AND am.admin_option = true
ORDER BY member;

In this example, the db1_admin role has these rights:

    role    |      member      |     grantor      | admin_option
------------+------------------+------------------+--------------
 vault_user | db1_admin        | postgres         | t
(1 row)

The postgres superuser will now revoke them from the db1_admin role:

$ psql db1 -U postgres
REVOKE ADMIN OPTION FOR vault_user FROM db1_admin CASCADE;

Finally, let's make sure that there are no extra users with the ADMIN OPTION for vault_user:

$ psql db1 -U postgres
SELECT rn.rolname "role", mn.rolname member, gn.rolname grantor, am.admin_option
FROM pg_auth_members am
JOIN pg_roles rn ON rn.oid = am.roleid
JOIN pg_roles mn ON mn.oid = am.member
JOIN pg_roles gn ON gn.oid = am.grantor
WHERE rn.rolname = 'vault_user' AND am.admin_option = true
ORDER BY member;

    role    |      member      |     grantor      | admin_option
------------+------------------+------------------+--------------
(0 rows)

32.4.2.5. Revoking Superuser Access to the Secured Schema #

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

Restart of the DBMS server is needed for the new restrictions to take effect:

# pg_ctl restart

32.4.3. Creating a Table in the Secured Schema #

As the schema manager (owner) can only work with its objects, the DV_OWNER creates tables there:

$ psql db1 -U dv_owner
CREATE TABLE vault.vault_table (user_name TEXT, balance DECIMAL(10,2));
EXIT;

32.4.4. Moving a Table to the Secured Schema #

Let's assume that the DB Administrator created a table:

$ psql db1 -U db1_admin

CREATE TABLE working_table(id INT, info TEXT);

This user can also fill this table with data:

INSERT INTO working_table(id, info) VALUES (1, 'Number One');

Later it was decided to move this table to the secured schema. To do this, the table ownership should be transferred to DV_OWNER, who will move it to the secured schema. However, PostgreSQL has a restricting requirement: the ownership of one's table can only be transferred to a user if there is a role that is member of both the old and new owning role:

ALTER TABLE working_table OWNER TO DV_OWNER;
ERROR:  must be able to SET ROLE "DV_OWNER"

Therefore, the superuser postgres must transfer the table ownership. To do this, the superuser postgres needs temporary DBMS access:

$ psql db1 -U postgres
ALTER TABLE working_table OWNER TO DV_OWNER;

After execution of this command, DBMS access must be revoked from the superuser postgres.

DV_OWNER will move the table to the secured schema:

$ psql db1 -U dv_owner
ALTER TABLE working_table SET SCHEMA vault;

32.4.5. Permitting Access to a Protected Table #

As the schema security officer (manager of access rights) can only grant users with permissions to access schema objects, the DV_SEC_OFFICER manages the privileges. Only users that are explicitly specified will be able to access data. Other users, including DBMS Administrator and DB Administrator, will not have access to the data. Appropriate GRANT commands are as follows:

$ psql db1 -U dv_sec_officer
GRANT USAGE ON SCHEMA vault TO vault_user;
GRANT INSERT,UPDATE,DELETE,SELECT,TRUNCATE ON vault.vault_table TO vault_user;
GRANT INSERT,UPDATE,DELETE,SELECT,TRUNCATE ON vault.working_table TO vault_user;
EXIT;

32.4.6. Checking Access: Filling in a Protected Table #

Connecting to the db1 database as a user that was granted access to the secured schema, we can make sure that the user's privileges are sufficient to access data in the secured schema:

$ psql db1 -U vault_user
INSERT INTO vault.vault_table( user_name, balance) VALUES( 'Ann', 200);
INSERT INTO vault.vault_table( user_name, balance) VALUES( 'Bob', 3000);
SELECT * FROM vault.vault_table;
SELECT * FROM vault.working_table;
EXIT;

32.4.7. Changes Made to a Backup Application #

When restoring a secured schema, two different administrators are needed as one of them will be unable to restore access rights, while the other one will be unable to restore schema objects. Therefore, pg_dump now enables separate dumping of the schema and data and of access privileges for the secured schema. --no-privileges and --privileges-only options, respectively, enable this separate dumping.

Dump operations with pg_dump must be performed in the following order:

  1. The DB Administrator dumps all data except the secured schema, using the --exclude-schema option.

  2. The DV_OWNER dumps the secured schema data, using the --schema and --no-privileges options.

  3. The DV_OWNER dumps the access privileges, using the --schema and --privileges-only options.

Restore operations using pg_restore must be performed in the following order:

  1. The DB Administrator restores all data except the secured schema.

  2. The DV_OWNER restores the secured schema data.

  3. The DV_SEC_OFFICER restores the access privileges for the secured schema.

32.4.8. Changes Made to the Integrity-check Utility #

The following changes have been made to pg_integrity_check:

  • The new -C option allows you to specify the database whose system catalog must be validated. Tip: specify the one where the secured schema is created.

  • The new --syslog option allows you to write checksum validation results into syslog.

  • pg_integrity_check can now log information about both successful validation with the Information importance level and unsuccessful validation with the Critical importance level.