[PATCH] Add pg_get_role_ddl() functions for role recreation

From fe3aa2942abb4ac2dc2bc3c34abd968303d6afbb Mon Sep 17 00:00:00 2001
From: Bryan Green <dbryan.green@gmail.com>
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 <gonzalemario@gmail.com>
Co-authored-by: Bryan Green <dbryan.green@gmail.com>
---
 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 d4508114a4..2d806ecec7 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 556ab057e5..584438d05a 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,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 34b7fddb0e..f85fb54133 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..4c2a008e4f
--- /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..b0811f0f72
--- /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

From f7f64e05f0498261b164594b194183198e4483ab Mon Sep 17 00:00:00 2001
From: Bryan Green <dbryan.green@gmail.com>
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 <gonzalemario@gmail.com>
Co-authored-by: Bryan Green <dbryan.green@gmail.com>
Reviewed-by: li carol <carol.li2025@outlook.com>
---
 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

From f7f64e05f0498261b164594b194183198e4483ab Mon Sep 17 00:00:00 2001
From: Bryan Green <dbryan.green@gmail.com>
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 <gonzalemario@gmail.com>
Co-authored-by: Bryan Green <dbryan.green@gmail.com>
Reviewed-by: li carol <carol.li2025@outlook.com>
---
 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_merge partition_split 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

From 4ac888cfa0be2669b1f8c4e8dde10046e07e0c98 Mon Sep 17 00:00:00 2001
From: Bryan Green <dbryan.green@gmail.com>
Date: Fri, 24 Oct 2025 11:29:50 -0500
Subject: [PATCH v5] 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 <gonzalemario@gmail.com>
Co-authored-by: Bryan Green <dbryan.green@gmail.com>
Reviewed-by: li carol <carol.li2025@outlook.com>
Reviewed-by: Japin Li <japinli@hotmail.com>
Discussion: https://www.postgresql.org/message-id/flat/4c5f895e-3281-48f8-b943-9228b7da6471%40gmail.com
---
 doc/src/sgml/func/func-info.sgml       |  56 ++++-
 src/backend/utils/adt/ruleutils.c      | 321 +++++++++++++++++++++++++
 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, 551 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 175f18315cd..fc3b7001050 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 416f1a21ae4..645a91799d0 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,325 @@ 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;
+
+	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",
+					 roleform->rolsuper ? "SUPERUSER" : "NOSUPERUSER");
+
+	appendStringInfo(&buf, " %s",
+					 roleform->rolcreatedb ? "CREATEDB" : "NOCREATEDB");
+
+	appendStringInfo(&buf, " %s",
+					 roleform->rolcreaterole ? "CREATEROLE" : "NOCREATEROLE");
+
+	appendStringInfo(&buf, " %s",
+					 roleform->rolinherit ? "INHERIT" : "NOINHERIT");
+
+	appendStringInfo(&buf, " %s",
+					 roleform->rolcanlogin ? "LOGIN" : "NOLOGIN");
+
+	appendStringInfo(&buf, " %s",
+					 roleform->rolreplication ? "REPLICATION" : "NOREPLICATION");
+
+	appendStringInfo(&buf, " %s",
+					 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, " CONNECTION LIMIT %d",
+						 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, " VALID UNTIL %s",
+							 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;
+	bool		first = true;
+
+	statements = pg_get_role_ddl_internal(roleid);
+
+	if (statements == NIL)
+		PG_RETURN_NULL();
+
+	initStringInfo(&result);
+
+	foreach_ptr(char, stmt, statements)
+	{
+		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 2ac69bf2df5..ca3834c3653 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -12683,4 +12683,28 @@
   proname => 'hashoid8extended', prorettype => 'int8',
   proargtypes => 'oid8 int8', prosrc => 'hashoid8extended' },
 
+# 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..50a9f3f13b1
--- /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 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOLOGIN 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 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT LOGIN 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 SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN 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 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOLOGIN 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 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOLOGIN 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 NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOLOGIN 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" NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOLOGIN 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 021d57f66bb..d290fc14055 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_merge partition_split 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