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

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;

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;

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)

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