Thread

Re: [PATCH] Add pg_get_role_ddl() functions for role recreation

Bryan Green <dbryan.green@gmail.com> — 2025-11-06T16:20:44Z

On 11/6/2025 1:20 AM, Quan Zongliang wrote:
> 
> 
> On 10/25/25 4:03 AM, Bryan Green wrote:
>> Attached is a patch adding two new functions for generating DDL to
>> recreate roles: pg_get_role_ddl() and pg_get_role_ddl_statements().
>>
> It is no longer apply to the latest code. Could you rebase this?
> 
>> 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
> 
The rebased patch is attached.

Thanks,

-- 
Bryan Green
EDB: https://www.enterprisedb.com