Check for EDB Postgres Advanced Server related programs that have been installed but not documented as approved. Open Control Program >> Programs >> Programs and Features. Look specifically for publishers of EnterpriseDB, pgAdmin, or PostgreSQL. If any programs are installed which are not documented as needed by the organization for the system, this is a finding.
The Postgres COPY command provides options for reading or writing files or running a program that the server has privileges to access. These options are only allowed for users who have been granted superuser privilege or have been granted the pg_read_server_files, pg_write_server_files, or pg_execute_server_program roles. The SUPERUSER privilege and the roles that provide access to files on the underlying server should only be granted to approved users.
To check for user and group roles that have been granted the SUPERUSER privilege, execute the following SQL statement in psql or another Postgres SQL client as enterprisedb:
WITH RECURSIVE roles( granted_role_id, granted_role_name, role_id, role_name, can_login, how_superuser, root_role_name )
AS (
SELECT NULL::oid granted_role_id
, NULL::name granted_role_name
, r1.oid role_id
, r1.rolname role_name
, r1.rolcanlogin can_login
, 'Assigned Superuser Privilege' how_superuser
, r1.rolname root_role_name
FROM pg_authid r1
WHERE r1.rolsuper = 't'
UNION
SELECT m.roleid
, r3.rolname
, m.member
, r2.rolname
, r2.rolcanlogin
, 'Granted Role with Superuser Privilege'
, r1.root_role_name
FROM pg_auth_members m
JOIN pg_authid r2
ON r2.oid = m.member
JOIN pg_authid r3
ON r3.oid = m.roleid
JOIN roles r1
ON m.roleid = r1.role_id
)
SELECT DISTINCT r.role_name, r.can_login, hs.how_superuser, gr.granted_roles, rr.root_superuser_roles
FROM roles r
JOIN ( SELECT role_name, string_agg(how_superuser, ', ') how_superuser
FROM ( SELECT DISTINCT role_name, how_superuser FROM roles ORDER BY 2 )
GROUP BY role_name
) hs
ON r.role_name = hs.role_name
JOIN ( SELECT role_name, string_agg(granted_role_name, ', ') granted_roles
FROM ( SELECT DISTINCT role_name, granted_role_name FROM roles ORDER BY 2 )
GROUP BY role_name
) gr
ON r.role_name = gr.role_name
JOIN ( SELECT role_name, string_agg(root_role_name, ', ') root_superuser_roles
FROM ( SELECT DISTINCT role_name, root_role_name FROM roles ORDER BY 2 )
GROUP BY role_name
) rr
ON r.role_name = rr.role_name
ORDER BY 3,1;
The above query will list all user and group roles that have either been granted the SUPERUSER privilege explicitly or via one of the roles in the hierarchy of roles they have been granted.
If a user or group roles has the SUPERUSER privilege either directly or via one of the roles in the hierarchy of roles it has been granted, and the role is not documented as being approved to have this privilege, this is a finding.
To check for user and group roles that have been granted any of the pg_read_server_files, pg_write_server_files, or pg_execute_server_program roles, execute the following SQL statement in psql or another Postgres SQL client as enterprisedb:
WITH RECURSIVE roles( granted_role_id, granted_role_name, role_id, role_name, can_login, root_role_name )
AS (
SELECT NULL::oid granted_role_id
, NULL::name granted_role_name
, r1.oid role_id
, r1.rolname role_name
, r1.rolcanlogin can_login
, r1.rolname root_role_name
FROM pg_authid r1
WHERE r1.rolname IN ( 'pg_read_server_files', 'pg_write_server_files', 'pg_execute_server_program' )
UNION
SELECT m.roleid
, r3.rolname
, m.member
, r2.rolname
, r2.rolcanlogin
, r1.root_role_name
FROM pg_auth_members m
JOIN pg_authid r2
ON r2.oid = m.member
JOIN pg_authid r3
ON r3.oid = m.roleid
JOIN roles r1
ON m.roleid = r1.role_id
)
SELECT DISTINCT r.role_name, r.can_login, gr.granted_roles, rr.server_os_access_roles
FROM roles r
JOIN ( SELECT role_name, string_agg(granted_role_name, ', ') granted_roles
FROM ( SELECT DISTINCT role_name, granted_role_name FROM roles ORDER BY 2 )
GROUP BY role_name
) gr
ON r.role_name = gr.role_name
JOIN ( SELECT role_name, string_agg(root_role_name, ', ') server_os_access_roles
FROM ( SELECT DISTINCT role_name, root_role_name FROM roles ORDER BY 2 )
GROUP BY role_name
) rr
ON r.role_name = rr.role_name
WHERE gr.granted_roles IS NOT NULL
ORDER BY 1;
The above query will list all user and group roles that have been granted one of these roles either explicitly or via one of the roles in the hierarchy of roles they have been granted.
If a user or group roles has been granted one of these roles either explicitly or via one of the roles in the hierarchy of roles they have been granted, and the role is not documented as being approved to have this role, this is a finding.
It is possible for a Postgres database extension to contain code that could access external executables via SQL. To list all installed extensions, execute the following SQL statement in psql or another Postgres SQL client as enterprisedb:
SELECT * FROM pg_available_extensions WHERE installed_version IS NOT NULL ORDER BY 1;
If any extensions are installed that are not documented as being approved, this is a finding.
It is possible to create database functions that are written in C or other procedural languages that reference code in externally loaded modules that may enable interaction with the OS. To list such functions, execute the following SQL statement in psql or another Postgres SQL client as enterprisedb:
SELECT n.nspname "Schema", p.proname "Function", p.prosrc "Source", p.probin "Library", l.lanname "Language"
FROM pg_proc p
JOIN pg_namespace n ON p.pronamespace = n.oid
JOIN pg_language l on p.prolang = l.oid
WHERE n.nspname NOT IN ('pg_catalog', 'sys', 'information_schema')
AND (l .lanpltrusted = 'f' AND l.lanname != 'internal' )
ORDER BY 4, 1, 2, 3;
If any C-language or other procedural language function is listed that is not documented as being approved, this is a finding.