Thread

Commits

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

Add infrastructure for pg_get_*_ddl functions
- 4881981f9202 19 (unreleased) landed
Add pg_get_role_ddl() function
- 76e514ebb4b5 19 (unreleased) landed

[PATCH] Add pg_get_role_ddl() functions for role recreation

Bryan Green <dbryan.green@gmail.com> — 2025-10-24T20:03:06Z

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

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

Quan Zongliang <quanzongliang@yeah.net> — 2025-11-06T07:20:25Z


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

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

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

Quan Zongliang <quanzongliang@yeah.net> — 2025-11-07T03:36:33Z


On 11/7/25 12:20 AM, Bryan Green wrote:

>>
> The rebased patch is attached.
> 
> Thanks,
> 
Currently, we have the "CREATE USER" command. Should we consider 
outputting users with the "LOGIN" attribute as "CREATE USER"?
Otherwise, it might look a little strange.

postgres=# CREATE USER testuser;
CREATE ROLE
postgres=# SELECT pg_get_role_ddl('testuser');
                                           pg_get_role_ddl
---------------------------------------------------------------------------------------------------
  CREATE ROLE testuser LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT 
NOREPLICATION NOBYPASSRLS;
(1 row)

The drawback of doing this is that the command "CREATE ROLE xxx LOGIN" 
will be converted to "CREATE USER". But I still think this is better.

Regards,

--
Quan Zongliang

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

li carol <carol.li2025@outlook.com> — 2025-11-07T05:42:52Z

Hello Bryan,

I reviewed your patch and found one potential issue, please check it.
In pg_get_role_ddl_internal, the variable rolname is assigned from NameStr(roleform->rolname) (line 588), which means it points directly into the tuple returned from pg_authid.  After constructing the initial CREATE ROLE statement, the code calls ReleaseSysCache(tuple); (line 665), so the memory holding that NameData now belongs to the cache again. However, the function continues to use rolname when building the subsequent ALTER ROLE statements (lines 756–765).  Because the tuple has already been released, rolname is a dangling pointer and we risk reading garbage or crashing later. To fix this, copy the role name before releasing the syscache, e.g. rolname = pstrdup(NameStr(roleform->rolname));, and free it at the end.

BR,
Yuan Li (Carol)


-----邮件原件-----
发件人: Bryan Green <dbryan.green@gmail.com> 
发送时间: 2025年11月7日 0:21
收件人: Quan Zongliang <quanzongliang@yeah.net>; pgsql-hackers@lists.postgresql.org
主题: Re: [PATCH] Add pg_get_role_ddl() functions for role recreation

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

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

Mario Gonzalez <gonzalemario@gmail.com> — 2025-11-07T17:46:08Z

On Fri, 7 Nov 2025 at 02:43, li carol <carol.li2025@outlook.com> wrote:
>
> Hello Bryan,
>
> I reviewed your patch and found one potential issue, please check it.
> In pg_get_role_ddl_internal, the variable rolname is assigned from NameStr(roleform->rolname) (line 588), which means it points directly into the tuple returned from pg_authid.  After constructing the initial CREATE ROLE statement, the code calls ReleaseSysCache(tuple); (line 665), so the memory holding that NameData now belongs to the cache again. However, the function continues to use rolname when building the subsequent ALTER ROLE statements (lines 756–765).  Because the tuple has already been released, rolname is a dangling pointer and we risk reading garbage or crashing later. To fix this, copy the role name before releasing the syscache, e.g. rolname = pstrdup(NameStr(roleform->rolname));, and free it at the end.
>

Good catch, I didn't know NameStr returned a pointer, for some reason
I've assumed I was working with a copy. Attaching the patch with the
changes: (also I added you in "Reviewed-by")
diff --git a/src/backend/utils/adt/ruleutils.c
b/src/backend/utils/adt/ruleutils.c
index 584438d05ad..41db9f10f5d 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -585,7 +585,7 @@ pg_get_role_ddl_internal(Oid roleid)
                return NIL;

        roleform = (Form_pg_authid) GETSTRUCT(tuple);
-       rolname = NameStr(roleform->rolname);
+       rolname = pstrdup(NameStr(roleform->rolname));

        /*
         * We don't support generating DDL for system roles.  The primary reason
@@ -777,6 +777,7 @@ pg_get_role_ddl_internal(Oid roleid)
        table_close(rel, AccessShareLock);

        pfree(buf.data);
+       pfree(rolname);

        return statements;

https://cirrus-ci.com/build/4813271540170752


> BR,
> Yuan Li (Carol)
>
>

[...]
> >>
> >> Co-authored-by: Mario Gonzalez and Bryan Green.
> >>
> >> Comments?
> >>
> >> BG
> >
> The rebased patch is attached.
>
> Thanks,
>
> --
> Bryan Green
> EDB: https://www.enterprisedb.com



-- 
Mario Gonzalez
EDB: https://www.enterprisedb.com