On Fri, 7 Nov 2025 at 02:43, li carol <[email protected]> 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
From f7f64e05f0498261b164594b194183198e4483ab Mon Sep 17 00:00:00 2001
From: Bryan Green <[email protected]>
Date: Fri, 24 Oct 2025 11:29:50 -0500
Subject: [PATCH] Add functions to generate DDL for recreating roles.
pg_get_role_ddl(regrole) returns the DDL needed to recreate a role
as a single text string. pg_get_role_ddl_statements(regrole) returns
the same thing as a set of rows, one per DDL statement.
The output includes the CREATE ROLE statement with all role attributes,
plus any ALTER ROLE SET configuration parameters (both role-wide and
database-specific settings).
Passwords cannot be included, since we can only see the hashed values.
System roles (names starting with "pg_") are rejected, since users
shouldn't be recreating those anyway.
Co-authored-by: Mario Gonzalez <[email protected]>
Co-authored-by: Bryan Green <[email protected]>
Reviewed-by: li carol <[email protected]>
---
doc/src/sgml/func/func-info.sgml | 56 ++++-
src/backend/utils/adt/ruleutils.c | 325 +++++++++++++++++++++++++
src/include/catalog/pg_proc.dat | 24 ++
src/test/regress/expected/role_ddl.out | 90 +++++++
src/test/regress/parallel_schedule | 4 +
src/test/regress/sql/role_ddl.sql | 57 +++++
6 files changed, 555 insertions(+), 1 deletion(-)
create mode 100644 src/test/regress/expected/role_ddl.out
create mode 100644 src/test/regress/sql/role_ddl.sql
diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml
index d4508114a48..2d806ecec70 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -2174,7 +2174,61 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
</tbody>
</tgroup>
</table>
-
+ <table id="functions-object-ddl-table">
+ <title>Object DDL Functions</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">Function</para>
+ <para>Description</para>
+ </entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <indexterm>
+ <primary>pg_get_role_ddl</primary>
+ </indexterm>
+ <function>pg_get_role_ddl</function> ( <parameter>role</parameter> <type>regrole</type> )
+ <returnvalue>text</returnvalue>
+ </para>
+ <para>
+ Returns the DDL commands that would recreate the given role as a single text string.
+ The result includes the <command>CREATE ROLE</command> statement and any
+ <command>ALTER ROLE</command> statements needed to set role configuration parameters.
+ Password information is never included in the output.
+ </para>
+ <para>
+ Returns <literal>NULL</literal> if the role does not exist.
+ </para>
+ </entry>
+ </row>
+ <row>
+ <entry role="func_table_entry">
+ <para role="func_signature">
+ <indexterm>
+ <primary>pg_get_role_ddl_statements</primary>
+ </indexterm>
+ <function>pg_get_role_ddl_statements</function> ( <parameter>role</parameter> <type>regrole</type> )
+ <returnvalue>setof text</returnvalue>
+ </para>
+ <para>
+ Returns the DDL commands that would recreate the given role as a set of rows,
+ with each statement returned as a separate row. The first row contains the
+ <command>CREATE ROLE</command> statement, followed by any <command>ALTER ROLE</command>
+ statements needed to set role configuration parameters. This format is useful for
+ programmatic processing or when you want to filter or analyze individual statements.
+ </para>
+ <para>
+ Returns an empty set if the role does not exist.
+ </para>
+ </entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
<para>
Most of the functions that reconstruct (decompile) database objects
have an optional <parameter>pretty</parameter> flag, which
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c
index 556ab057e5a..41db9f10f5d 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -28,6 +28,7 @@
#include "catalog/pg_authid.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_constraint.h"
+#include "catalog/pg_db_role_setting.h"
#include "catalog/pg_depend.h"
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
@@ -59,6 +60,7 @@
#include "rewrite/rewriteSupport.h"
#include "utils/array.h"
#include "utils/builtins.h"
+#include "utils/datetime.h"
#include "utils/fmgroids.h"
#include "utils/guc.h"
#include "utils/hsearch.h"
@@ -549,6 +551,329 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
+/*
+ * pg_get_role_ddl_internal
+ * Generate DDL statements to recreate a role
+ *
+ * Returns a List of palloc'd strings, each being a complete SQL statement.
+ * The first list element is always the CREATE ROLE statement; subsequent
+ * elements are ALTER ROLE SET statements for any role-specific or
+ * role-in-database configuration settings.
+ *
+ * Returns NIL if the role OID is invalid. This can happen if the role was
+ * dropped concurrently, or if we're passed a OID that doesn't match
+ * any role.
+ */
+static List *
+pg_get_role_ddl_internal(Oid roleid)
+{
+ HeapTuple tuple;
+ Form_pg_authid roleform;
+ StringInfoData buf;
+ char *rolname;
+ Datum rolevaliduntil;
+ bool isnull;
+ Relation rel;
+ ScanKeyData scankey;
+ SysScanDesc scan;
+ HeapTuple setting_tuple;
+ List *statements = NIL;
+ const char *separator = " ";
+
+ tuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid));
+ if (!HeapTupleIsValid(tuple))
+ return NIL;
+
+ roleform = (Form_pg_authid) GETSTRUCT(tuple);
+ rolname = pstrdup(NameStr(roleform->rolname));
+
+ /*
+ * We don't support generating DDL for system roles. The primary reason
+ * for this is that users shouldn't be recreating them.
+ */
+ if (strncmp(rolname, "pg_", 3) == 0)
+ ereport(ERROR,
+ (errcode(ERRCODE_RESERVED_NAME),
+ errmsg("role name \"%s\" is reserved", rolname),
+ errdetail("Role names starting with \"pg_\" are reserved for system roles.")));
+
+ initStringInfo(&buf);
+ appendStringInfo(&buf, "CREATE ROLE %s", quote_identifier(rolname));
+
+ /*
+ * Append role attributes. The order here follows the same sequence as
+ * you'd typically write them in a CREATE ROLE command, though any order
+ * is actually acceptable to the parser.
+ */
+ appendStringInfo(&buf, "%s%s", separator,
+ roleform->rolcanlogin ? "LOGIN" : "NOLOGIN");
+
+ appendStringInfo(&buf, "%s%s", separator,
+ roleform->rolsuper ? "SUPERUSER" : "NOSUPERUSER");
+
+ appendStringInfo(&buf, "%s%s", separator,
+ roleform->rolcreatedb ? "CREATEDB" : "NOCREATEDB");
+
+ appendStringInfo(&buf, "%s%s", separator,
+ roleform->rolcreaterole ? "CREATEROLE" : "NOCREATEROLE");
+
+ appendStringInfo(&buf, "%s%s", separator,
+ roleform->rolinherit ? "INHERIT" : "NOINHERIT");
+
+ appendStringInfo(&buf, "%s%s", separator,
+ roleform->rolreplication ? "REPLICATION" : "NOREPLICATION");
+
+ appendStringInfo(&buf, "%s%s", separator,
+ roleform->rolbypassrls ? "BYPASSRLS" : "NOBYPASSRLS");
+
+ /*
+ * CONNECTION LIMIT is only interesting if it's not -1 (the default,
+ * meaning no limit).
+ */
+ if (roleform->rolconnlimit >= 0)
+ appendStringInfo(&buf, "%sCONNECTION LIMIT %d",
+ separator, roleform->rolconnlimit);
+
+ rolevaliduntil = SysCacheGetAttr(AUTHOID, tuple,
+ Anum_pg_authid_rolvaliduntil,
+ &isnull);
+ if (!isnull)
+ {
+ struct pg_tm tm;
+ fsec_t fsec;
+ char ts_str[MAXDATELEN + 1];
+
+ if (timestamp2tm(rolevaliduntil, NULL, &tm, &fsec, NULL, NULL) == 0)
+ {
+ EncodeDateTime(&tm, fsec, false, 0, "UTC", USE_ISO_DATES, ts_str);
+ appendStringInfo(&buf, "%sVALID UNTIL %s",
+ separator, quote_literal_cstr(ts_str));
+ }
+ }
+
+ /*
+ * We intentionally omit PASSWORD. There's no way to retrieve the
+ * original password text from the stored hash, and even if we could,
+ * exposing passwords through a SQL function would be a security issue.
+ * Users must set passwords separately after recreating roles.
+ */
+
+ appendStringInfoChar(&buf, ';');
+
+ statements = lappend(statements, pstrdup(buf.data));
+
+ ReleaseSysCache(tuple);
+
+ /*
+ * Now scan pg_db_role_setting for ALTER ROLE SET configurations.
+ *
+ * These can be role-wide (setdatabase = 0) or specific to a particular
+ * database (setdatabase = a valid DB OID). We generate one ALTER
+ * statement per setting, which isn't as compact as it could be, but is
+ * straightforward and matches how users typically set these up.
+ */
+ rel = table_open(DbRoleSettingRelationId, AccessShareLock);
+ ScanKeyInit(&scankey,
+ Anum_pg_db_role_setting_setrole,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(roleid));
+ scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true,
+ NULL, 1, &scankey);
+
+ while (HeapTupleIsValid(setting_tuple = systable_getnext(scan)))
+ {
+ Form_pg_db_role_setting setting = (Form_pg_db_role_setting) GETSTRUCT(setting_tuple);
+ Oid datid = setting->setdatabase;
+ Datum datum;
+ bool setting_isnull;
+ ArrayType *settings;
+ int i;
+ char *datname = NULL;
+
+ /*
+ * The setconfig column is a text array in "name=value" format. It
+ * should never be null for a valid row, but be defensive.
+ */
+ datum = heap_getattr(setting_tuple, Anum_pg_db_role_setting_setconfig,
+ RelationGetDescr(rel), &setting_isnull);
+ if (setting_isnull)
+ continue;
+
+ settings = DatumGetArrayTypeP(datum);
+
+ /*
+ * If setdatabase is valid, this is a role-in-database setting;
+ * otherwise it's a role-wide setting. Look up the database name once
+ * for all settings in this row.
+ */
+ if (OidIsValid(datid))
+ {
+ datname = get_database_name(datid);
+ if (datname == NULL)
+ {
+ /*
+ * Database has been dropped; skip all settings in this row.
+ */
+ continue;
+ }
+ }
+
+ /* Process each setting in the array */
+ for (i = 1; i <= ArrayGetNItems(ARR_NDIM(settings), ARR_DIMS(settings)); i++)
+ {
+ Datum setting_datum;
+ bool setting_elem_isnull;
+ char *setting_str;
+ char *equals_pos;
+
+ setting_datum = array_ref(settings, 1, &i,
+ -1 /* varlenarray */ ,
+ -1 /* TEXT's typlen */ ,
+ false /* TEXT's typbyval */ ,
+ TYPALIGN_INT /* TEXT's typalign */ ,
+ &setting_elem_isnull);
+
+ if (setting_elem_isnull)
+ continue;
+
+ setting_str = TextDatumGetCString(setting_datum);
+
+ /*
+ * Parse out the parameter name and value. The format should
+ * always be "name=value" but check anyway to avoid a crash if the
+ * catalog is corrupted.
+ */
+ equals_pos = strchr(setting_str, '=');
+ if (equals_pos == NULL)
+ {
+ pfree(setting_str);
+ continue;
+ }
+
+ *equals_pos = '\0';
+
+ /* Build a fresh ALTER ROLE statement for this setting */
+ resetStringInfo(&buf);
+ appendStringInfo(&buf, "ALTER ROLE %s", quote_identifier(rolname));
+
+ if (datname != NULL)
+ appendStringInfo(&buf, " IN DATABASE %s",
+ quote_identifier(datname));
+
+ appendStringInfo(&buf, " SET %s TO %s;",
+ quote_identifier(setting_str),
+ quote_literal_cstr(equals_pos + 1));
+
+ statements = lappend(statements, pstrdup(buf.data));
+
+ pfree(setting_str);
+ }
+
+ if (datname != NULL)
+ pfree(datname);
+ }
+
+ systable_endscan(scan);
+ table_close(rel, AccessShareLock);
+
+ pfree(buf.data);
+ pfree(rolname);
+
+ return statements;
+}
+
+
+/*
+ * pg_get_role_ddl
+ * Return DDL to recreate a role as a single text string
+ *
+ * This is the main user-facing function. It calls pg_get_role_ddl_internal
+ * to get the list of statements, then concatenates them with newlines.
+ *
+ * Returns NULL if the role OID doesn't exist. This can only happen if
+ * you pass a OID rather than using the regrole type, or if there's
+ * a race condition with a concurrent DROP ROLE.
+ */
+Datum
+pg_get_role_ddl(PG_FUNCTION_ARGS)
+{
+ Oid roleid = PG_GETARG_OID(0);
+ List *statements;
+ StringInfoData result;
+ ListCell *lc;
+ bool first = true;
+
+ statements = pg_get_role_ddl_internal(roleid);
+
+ if (statements == NIL)
+ PG_RETURN_NULL();
+
+ initStringInfo(&result);
+
+ foreach(lc, statements)
+ {
+ char *stmt = (char *) lfirst(lc);
+
+ if (!first)
+ appendStringInfoChar(&result, '\n');
+ appendStringInfoString(&result, stmt);
+ first = false;
+ }
+
+ list_free_deep(statements);
+
+ PG_RETURN_TEXT_P(cstring_to_text(result.data));
+}
+
+/*
+ * pg_get_role_ddl_statements
+ * Return DDL to recreate a role as a set of rows
+ *
+ * This is similar to pg_get_role_ddl, but returns each statement as a
+ * separate row. This is useful for programmatic processing or when you
+ * want to filter/analyze individual statements.
+ */
+Datum
+pg_get_role_ddl_statements(PG_FUNCTION_ARGS)
+{
+ FuncCallContext *funcctx;
+ List *statements;
+ ListCell *lc;
+
+ if (SRF_IS_FIRSTCALL())
+ {
+ MemoryContext oldcontext;
+ Oid roleid = PG_GETARG_OID(0);
+
+ funcctx = SRF_FIRSTCALL_INIT();
+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+ statements = pg_get_role_ddl_internal(roleid);
+ funcctx->user_fctx = statements;
+ funcctx->max_calls = list_length(statements);
+
+ MemoryContextSwitchTo(oldcontext);
+ }
+
+ funcctx = SRF_PERCALL_SETUP();
+ statements = (List *) funcctx->user_fctx;
+
+ if (funcctx->call_cntr < funcctx->max_calls)
+ {
+ char *stmt;
+
+ lc = list_nth_cell(statements, funcctx->call_cntr);
+ stmt = (char *) lfirst(lc);
+
+ SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
+ }
+ else
+ {
+ list_free_deep(statements);
+ SRF_RETURN_DONE(funcctx);
+ }
+}
+
/* ----------
* pg_get_ruledef - Do it all and return a text
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 5cf9e12fcb9..6f86cc1e0eb 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12604,4 +12604,28 @@
proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
prosrc => 'pg_get_aios' },
+# pg_get_role_ddl - return DDL to recreate a role (compact format)
+{ oid => '9991', descr => 'get SQL commands to recreate role',
+ proname => 'pg_get_role_ddl', pronamespace => 'pg_catalog',
+ proowner => 'POSTGRES', prolang => 'internal', procost => '1', prorows => '0',
+ provariadic => '0', prosupport => '0', prokind => 'f', prosecdef => 'f',
+ proleakproof => 'f', proisstrict => 't', proretset => 'f', provolatile => 's',
+ proparallel => 's', pronargs => '1', pronargdefaults => '0',
+ prorettype => 'text', proargtypes => 'regrole', proallargtypes => '_null_',
+ proargmodes => '_null_', proargnames => '_null_', proargdefaults => '_null_',
+ protrftypes => '_null_', prosrc => 'pg_get_role_ddl', probin => '_null_',
+ prosqlbody => '_null_', proconfig => '_null_', proacl => '_null_' },
+
+# pg_get_role_ddl_statements - return DDL as separate statements (compact format)
+{ oid => '9992', descr => 'get SQL commands to recreate role as row set',
+ proname => 'pg_get_role_ddl_statements', pronamespace => 'pg_catalog',
+ proowner => 'POSTGRES', prolang => 'internal', procost => '1',
+ prorows => '10', provariadic => '0', prosupport => '0', prokind => 'f',
+ prosecdef => 'f', proleakproof => 'f', proisstrict => 't', proretset => 't',
+ provolatile => 's', proparallel => 's', pronargs => '1',
+ pronargdefaults => '0', prorettype => 'text', proargtypes => 'regrole',
+ proallargtypes => '_null_', proargmodes => '_null_', proargnames => '_null_',
+ proargdefaults => '_null_', protrftypes => '_null_',
+ prosrc => 'pg_get_role_ddl_statements', probin => '_null_',
+ prosqlbody => '_null_', proconfig => '_null_', proacl => '_null_' },
]
diff --git a/src/test/regress/expected/role_ddl.out b/src/test/regress/expected/role_ddl.out
new file mode 100644
index 00000000000..4c2a008e4f1
--- /dev/null
+++ b/src/test/regress/expected/role_ddl.out
@@ -0,0 +1,90 @@
+-- Set fixed timezone for consistent test results
+SET timezone = 'UTC';
+-- Create test database
+CREATE DATABASE regression_role_ddl_test;
+-- Test 1: Basic role
+CREATE ROLE regress_role_ddl_test1;
+SELECT pg_get_role_ddl('regress_role_ddl_test1');
+ pg_get_role_ddl
+-------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test1 NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION NOBYPASSRLS;
+(1 row)
+
+-- Test 2: Role with LOGIN
+CREATE ROLE regress_role_ddl_test2 LOGIN;
+SELECT pg_get_role_ddl('regress_role_ddl_test2');
+ pg_get_role_ddl
+-----------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test2 LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION NOBYPASSRLS;
+(1 row)
+
+-- Test 3: Role with multiple privileges
+CREATE ROLE regress_role_ddl_test3
+ LOGIN
+ SUPERUSER
+ CREATEDB
+ CREATEROLE
+ CONNECTION LIMIT 5
+ VALID UNTIL '2030-12-31 23:59:59+00';
+SELECT pg_get_role_ddl('regress_role_ddl_test3');
+ pg_get_role_ddl
+----------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test3 LOGIN SUPERUSER CREATEDB CREATEROLE INHERIT NOREPLICATION NOBYPASSRLS CONNECTION LIMIT 5 VALID UNTIL '2030-12-31 23:59:59';
+(1 row)
+
+-- Test 4: Role with configuration parameters
+CREATE ROLE regress_role_ddl_test4;
+ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB';
+ALTER ROLE regress_role_ddl_test4 SET search_path TO 'myschema, public';
+SELECT pg_get_role_ddl('regress_role_ddl_test4');
+ pg_get_role_ddl
+-------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test4 NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION NOBYPASSRLS;+
+ ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB'; +
+ ALTER ROLE regress_role_ddl_test4 SET search_path TO '"myschema, public"';
+(1 row)
+
+-- Test 5: Role with database-specific configuration
+CREATE ROLE regress_role_ddl_test5;
+ALTER ROLE regress_role_ddl_test5 IN DATABASE regression_role_ddl_test SET work_mem TO '128MB';
+SELECT pg_get_role_ddl('regress_role_ddl_test5');
+ pg_get_role_ddl
+-------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test5 NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION NOBYPASSRLS;+
+ ALTER ROLE regress_role_ddl_test5 IN DATABASE regression_role_ddl_test SET work_mem TO '128MB';
+(1 row)
+
+-- Test 6: Test pg_get_role_ddl_statements function
+SELECT * FROM pg_get_role_ddl_statements('regress_role_ddl_test4');
+ pg_get_role_ddl_statements
+-------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test4 NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION NOBYPASSRLS;
+ ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB';
+ ALTER ROLE regress_role_ddl_test4 SET search_path TO '"myschema, public"';
+(3 rows)
+
+-- Test 7: Role with special characters (requires quoting)
+CREATE ROLE "regress_role-with-dash";
+SELECT pg_get_role_ddl('regress_role-with-dash');
+ pg_get_role_ddl
+---------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE "regress_role-with-dash" NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION NOBYPASSRLS;
+(1 row)
+
+-- Test 8: Non-existent role (should return NULL)
+SELECT pg_get_role_ddl(9999999::oid);
+ pg_get_role_ddl
+-----------------
+
+(1 row)
+
+-- Cleanup
+DROP ROLE regress_role_ddl_test1;
+DROP ROLE regress_role_ddl_test2;
+DROP ROLE regress_role_ddl_test3;
+DROP ROLE regress_role_ddl_test4;
+DROP ROLE regress_role_ddl_test5;
+DROP ROLE "regress_role-with-dash";
+DROP DATABASE regression_role_ddl_test;
+-- Reset timezone to default
+RESET timezone;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index a0f5fab0f5d..34c9e98ce9c 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -125,6 +125,10 @@ test: plancache limit plpgsql copy2 temp domain rangefuncs prepare conversion tr
# ----------
test: partition_join partition_prune reloptions hash_part indexing partition_aggregate partition_info tuplesort explain compression compression_lz4 memoize stats predicate numa eager_aggregate
+# Tests role_ddl functions to create statements needed
+# to reproduce a role
+test: role_ddl
+
# event_trigger depends on create_am and cannot run concurrently with
# any test that runs DDL
# oidjoins is read-only, though, and should run late for best coverage
diff --git a/src/test/regress/sql/role_ddl.sql b/src/test/regress/sql/role_ddl.sql
new file mode 100644
index 00000000000..b0811f0f72d
--- /dev/null
+++ b/src/test/regress/sql/role_ddl.sql
@@ -0,0 +1,57 @@
+-- Set fixed timezone for consistent test results
+SET timezone = 'UTC';
+
+-- Create test database
+CREATE DATABASE regression_role_ddl_test;
+
+-- Test 1: Basic role
+CREATE ROLE regress_role_ddl_test1;
+SELECT pg_get_role_ddl('regress_role_ddl_test1');
+
+-- Test 2: Role with LOGIN
+CREATE ROLE regress_role_ddl_test2 LOGIN;
+SELECT pg_get_role_ddl('regress_role_ddl_test2');
+
+-- Test 3: Role with multiple privileges
+CREATE ROLE regress_role_ddl_test3
+ LOGIN
+ SUPERUSER
+ CREATEDB
+ CREATEROLE
+ CONNECTION LIMIT 5
+ VALID UNTIL '2030-12-31 23:59:59+00';
+SELECT pg_get_role_ddl('regress_role_ddl_test3');
+
+-- Test 4: Role with configuration parameters
+CREATE ROLE regress_role_ddl_test4;
+ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB';
+ALTER ROLE regress_role_ddl_test4 SET search_path TO 'myschema, public';
+SELECT pg_get_role_ddl('regress_role_ddl_test4');
+
+-- Test 5: Role with database-specific configuration
+CREATE ROLE regress_role_ddl_test5;
+ALTER ROLE regress_role_ddl_test5 IN DATABASE regression_role_ddl_test SET work_mem TO '128MB';
+SELECT pg_get_role_ddl('regress_role_ddl_test5');
+
+-- Test 6: Test pg_get_role_ddl_statements function
+SELECT * FROM pg_get_role_ddl_statements('regress_role_ddl_test4');
+
+-- Test 7: Role with special characters (requires quoting)
+CREATE ROLE "regress_role-with-dash";
+SELECT pg_get_role_ddl('regress_role-with-dash');
+
+-- Test 8: Non-existent role (should return NULL)
+SELECT pg_get_role_ddl(9999999::oid);
+
+-- Cleanup
+DROP ROLE regress_role_ddl_test1;
+DROP ROLE regress_role_ddl_test2;
+DROP ROLE regress_role_ddl_test3;
+DROP ROLE regress_role_ddl_test4;
+DROP ROLE regress_role_ddl_test5;
+DROP ROLE "regress_role-with-dash";
+
+DROP DATABASE regression_role_ddl_test;
+
+-- Reset timezone to default
+RESET timezone;
--
2.39.5
