Horton

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

Quan Zongliang <quanzongliang@yeah.net>

View thread
From: Quan Zongliang <quanzongliang@yeah.net>
To: Bryan Green <dbryan.green@gmail.com>, pgsql-hackers@lists.postgresql.org
Date: 2025-11-06T07:20:25Z
Lists: pgsql-hackers

Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →

  1. Add infrastructure for pg_get_*_ddl functions

  2. Add pg_get_role_ddl() function


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