PostgreSQL Permissions

The following scripts create some tables that store PostgreSQL permissions. The permissions come from the system tables pg_roles, pg_auth_members, and others. The scripts assume there is a database named DBA, where this information is stored.

Permissions Tables

-- Connect to the DBA database
\c DBA

-- Drop the old tables if exist
DROP TABLE IF EXISTS object_permissions;
DROP TABLE IF EXISTS role_permissions;

-- Create table for object permissions
CREATE TABLE object_permissions (
    oid serial PRIMARY KEY,
    servername text,
    databasename text,
    schemaname text,
    objectname text,
    objecttype text,
    privilege_type text,
    grantor text,
    grantee text,
    is_grantable text
);

-- Create table for role permissions
CREATE TABLE role_permissions (
    oid serial PRIMARY KEY,
    servername text,
    databasename text, -- Roles are server-wide but it might be helpful to know where the query was executed
    role_name text,
    member_name text,
    grantor text,
    admin_option boolean
);

Permissions Queries

Object Permissions

-- Connect to the target database
\c target_database_name

INSERT INTO DBA.object_permissions (servername, databasename, schemaname, objectname, objecttype, privilege_type, grantor, grantee, is_grantable)
SELECT 
    'ServerName' AS servername, -- Replace with the actual server name or hostname function based on your environment
    current_database() AS databasename,
    n.nspname AS schemaname,
    c.relname AS objectname,
    CASE c.relkind
        WHEN 'r' THEN 'TABLE'
        WHEN 'v' THEN 'VIEW'
        WHEN 'm' THEN 'MATERIALIZED VIEW'
        WHEN 'S' THEN 'SEQUENCE'
        WHEN 'f' THEN 'FUNCTION'
        WHEN 'p' THEN 'PROCEDURE'
        WHEN 'E' THEN 'FOREIGN TABLE'
        ELSE 'UNKNOWN'
    END AS objecttype,
    a.privilege_type,
    a.grantor::regrole::text,
    a.grantee::regrole::text,
    a.is_grantable
FROM 
    pg_class c 
JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN (
    SELECT 
        table_schema, table_name, privilege_type, grantor, grantee, is_grantable 
    FROM information_schema.table_privileges
    UNION ALL
    SELECT 
        routine_schema, routine_name, privilege_type, grantor, grantee, is_grantable
    FROM information_schema.routine_privileges
) AS a ON a.table_schema = n.nspname AND a.table_name = c.relname
WHERE n.nspname NOT LIKE 'pg_%' AND n.nspname != 'information_schema';

Role Permissions

-- Roles are server-wide, so you don't need to switch databases for this
INSERT INTO DBA.role_permissions (servername, databasename, role_name, member_name, grantor, admin_option)
SELECT 
    current_setting('my.server.name') AS servername, -- Replace with the actual server name or hostname function based on your environment
    current_database() AS databasename,
    r.rolname AS role_name,
    u.rolname AS member_name,
    g.rolname AS grantor,
    m.admin_option
FROM 
    pg_auth_members m
JOIN pg_roles r ON r.oid = m.roleid
JOIN pg_roles u ON u.oid = m.member
JOIN pg_roles g ON g.oid = m.grantor;