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.
Install python3 library psycopg2:
# pip install psycopg2
Extract script files (see Releases page)
$ tar xzf pgroleddl-<version>.tar.gz
Change pgroleddl_conf.py for PostgreSQL connection setting - local or remote.
$ python3 ./pgroleddl.py
When launched, the script displays a list of available databases and a list of roles with their main attributes (Login, Superuser).
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;
---------------------------------------------------------
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent