32.4. Restricting DBMS Administrator's Data Access #
- 32.4.1. Overview
- 32.4.2. Creating Administrators and Managers of the DB Secured Schema
- 32.4.3. Creating a Table in the Secured Schema
- 32.4.4. Moving a Table to the Secured Schema
- 32.4.5. Permitting Access to a Protected Table
- 32.4.6. Checking Access: Filling in a Protected Table
- 32.4.7. Changes Made to a Backup Application
- 32.4.8. Changes Made to the Integrity-check Utility
- 32.4.2. Creating Administrators and Managers of the DB Secured Schema
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:
The DB Administrator dumps all data except the secured schema, using the
--exclude-schema
option.The
DV_OWNER
dumps the secured schema data, using the--schema
and--no-privileges
options.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:
The DB Administrator restores all data except the secured schema.
The
DV_OWNER
restores the secured schema data.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 intosyslog
.pg_integrity_check can now log information about both successful validation with the
Information
importance level and unsuccessful validation with theCritical
importance level.