PGRoleDDL

PostgreSQL Role DDL script - show role DDL with objects grants and linked roles.

The script is created to get the DDL of the selected role, its objects in all databases and its associated roles. Linked roles are listed separately, with their objects and DDL. Own parser of ACL (Access Control List) rights to objects. The script is tested on PostgreSQL 12.

Installation

Install python3 library psycopg2:

# pip install psycopg2

Extract script files (see Releases page)

$ tar xzf pgroleddl-<version>.tar.gz

Setup

Change pgroleddl_conf.py for PostgreSQL connection setting - local or remote.

Using

$ python3 ./pgroleddl.py

When launched, the script displays a list of available databases and a list of roles with their main attributes (Login, Superuser).

Sample output

Databases:
------------------
Name         oid     
postgres     14187   
appdb        16785   
collectd     16799   
apptest      26064   

Role list:
----------------------------------------------------------
Role                           oid      Login    Superuser
app                            16784    True     False    
backup                         25175    True     False    
monitoring                     16833    True     False    
postgres                       10       True     True     
rep_user                       37271    True     False    
pg_execute_server_program      4571     False    False    
pg_monitor                     3373     False    False    
pg_read_all_settings           3374     False    False    
pg_read_all_stats              3375     False    False    
pg_read_server_files           4569     False    False    
pg_signal_backend              4200     False    False    
pg_stat_scan_tables            3377     False    False    
pg_write_server_files          4570     False    False    
role_jit2                      36679    False    False    

Enter role name for details: app

------------ Database: postgres (datid: 14187) ------------
Find linked role [role_jit2] for scanned role [app]:

------------ Database: appdb (datid: 16785) ------------
- (1) Schema_name [jit], object_name [t_jit], object_type [Table], object_owner [app]
- (1) Grantee [app], privs [INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER], privswgo []. Grantor [app}]

Find linked role [role_jit2] for scanned role [app]:
- (1) Schema_name [jit], object_name [t_jit2], object_type [Table], object_owner [postgres]
- (1) Grantee [role_jit2], privs [INSERT, UPDATE, DELETE], privswgo [SELECT]. Grantor [postgres}]

------------ Database: collectd (datid: 16799) ------------
Find linked role [role_jit2] for scanned role [app]:

------------ Database: apptest (datid: 26064) ------------
- (1) Schema_name [jit], object_name [t_jit], object_type [Table], object_owner [app]
- (1) Grantee [app], privs [INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER], privswgo []. Grantor [app}]

- (2) Schema_name [jit], object_name [t_jit2], object_type [Table], object_owner [app]
- (2) Grantee [app], privs [INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER], privswgo []. Grantor [app}]

Find linked role [role_jit2] for scanned role [app]:


------------ Role 'app' DDL ------------
-- DROP ROLE app;

CREATE ROLE app WITH
  NOSUPERUSER
  INHERIT
  NOCREATEROLE
  NOCREATEDB
  LOGIN
  NOREPLICATION
  ENCRYPTED PASSWORD 'md5cac6e0576d309fb8466a2791f0b6ccc7';

GRANT role_jit2 TO app;

-- Grants for role

-- Database: appdb
\c appdb
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLE jit.t_jit TO app;
GRANT INSERT, UPDATE, DELETE ON TABLE jit.t_jit2 TO role_jit2;
GRANT SELECT ON TABLE jit.t_jit2 TO role_jit2 WITH GRANT OPTION;

-- Database: apptest
\c apptest
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLE jit.t_jit TO app;
GRANT INSERT, SELECT, UPDATE, DELETE, TRUNCATE, REFERENCES, TRIGGER ON TABLE jit.t_jit2 TO app;

------------ Linked Role 'role_jit2' DDL ------------
-- DROP ROLE role_jit2;

CREATE ROLE role_jit2 WITH
  NOSUPERUSER
  INHERIT
  NOCREATEROLE
  NOCREATEDB
  NOLOGIN
  NOREPLICATION
  ;

GRANT role_jit2 TO app;

-- Grants for role

-- Database: appdb
\c appdb
GRANT INSERT, UPDATE, DELETE ON TABLE jit.t_jit2 TO role_jit2;
GRANT SELECT ON TABLE jit.t_jit2 TO role_jit2 WITH GRANT OPTION;

---------------------------------------------------------