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
From 8d9e7d59ea216ae5f008c925313ee16240820377 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]>
---
doc/src/sgml/func/func-info.sgml | 56 ++++-
src/backend/utils/adt/ruleutils.c | 324 +++++++++++++++++++++++++
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, 554 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 c393832d94..b98c2dae22 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 79ec136231..6c46580a0e 100644
--- a/src/backend/utils/adt/ruleutils.c
+++ b/src/backend/utils/adt/ruleutils.c
@@ -28,8 +28,9 @@
#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"
#include "catalog/pg_operator.h"
#include "catalog/pg_partitioned_table.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,328 @@ 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 = 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);
+
+ 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 eecb43ec6f..0d0511589f 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 0000000000..cbc4167a72
--- /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 a0f5fab0f5..34c9e98ce9 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 0000000000..a27ff44137
--- /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.46.0.windows.1
