Attached is a patch adding two new functions for generating DDL to
recreate roles: pg_get_role_ddl() and pg_get_role_ddl_statements().
These functions return the CREATE ROLE statement and any ALTER ROLE SET
configuration parameters needed to recreate a role. The former returns
everything as a single text string, while the latter returns each
statement as a separate row for easier programmatic processing.
The main use case is dumping role definitions for migration or backup
purposes without needing pg_dumpall. The functions handle all role
attributes (LOGIN, SUPERUSER, etc.) and both role-wide and
database-specific configuration parameters.
We intentionally don't include passwords, since we can only see the
hashed values. System roles (names starting with "pg_") are rejected
with an error, as users shouldn't be recreating those anyway.
To test:
CREATE ROLE testrole LOGIN CREATEDB CONNECTION LIMIT 5;
ALTER ROLE testrole SET work_mem TO '64MB';
SELECT pg_get_role_ddl('testrole');
Should produce:
CREATE ROLE testrole LOGIN NOSUPERUSER CREATEDB NOCREATEROLE INHERIT
NOREPLICATION NOBYPASSRLS CONNECTION LIMIT 5;
ALTER ROLE testrole SET work_mem TO '64MB';
The patch includes regression tests covering various role configurations.
Co-authored-by: Mario Gonzalez and Bryan Green.
Comments?
BG
