0002-Add-pg_get_role_ddl-function.patch

From b615f89b98b2e09f275cb82cdea557bed0a2b68d Mon Sep 17 00:00:00 2001
From: Andrew Dunstan <andrew@dunslane.net>
Date: Thu, 19 Mar 2026 09:52:25 -0400
Subject: [PATCH 2/4] Add pg_get_role_ddl() function

Add a new SQL-callable function that returns the DDL statements needed
to recreate a role. It takes a regrole argument and an optional VARIADIC
text argument for options that are specified as alternating name/value
pairs. The following option is supported: pretty (boolean) for
formatted output. The return is one or multiple rows where the first row
is a CREATE ROLE statement and subsequent rows are ALTER ROLE statements
to set some role properties.

Author: Mario Gonzalez <gonzalemario@gmail.com>
Author: Bryan Green <dbryan.green@gmail.com>
Co-authored-by: Andrew Dunstan <andrew@dunslane.net>
Reviewed-by: Euler Taveira <euler@eulerto.com>
Reviewed-by: Japin Li <japinli@hotmail.com>
Reviewed-by: Quan Zongliang <quanzongliang@yeah.net>
Reviewed-by: jian he <jian.universality@gmail.com>
Discussion: https://www.postgresql.org/message-id/flat/4c5f895e-3281-48f8-b943-9228b7da6471@gmail.com
---
 doc/src/sgml/func/func-info.sgml       |  54 ++++
 src/backend/utils/adt/ddlutils.c       | 330 +++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat        |   8 +
 src/test/regress/expected/role_ddl.out | 100 ++++++++
 src/test/regress/parallel_schedule     |   2 +
 src/test/regress/sql/role_ddl.sql      |  63 +++++
 6 files changed, 557 insertions(+)
 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 5b5f1f3c5df..acd1a7cfeed 100644
--- a/doc/src/sgml/func/func-info.sgml
+++ b/doc/src/sgml/func/func-info.sgml
@@ -3860,4 +3860,58 @@ acl      | {postgres=arwdDxtm/postgres,foo=r/postgres}
 
   </sect2>
 
+  <sect2 id="functions-get-object-ddl">
+   <title>Get Object DDL Functions</title>
+
+   <para>
+    The functions shown in <xref linkend="functions-get-object-ddl-table"/>
+    reconstruct DDL statements for various global database objects.
+    Each function returns a set of text rows, one SQL statement per row.
+    (This is a decompiled reconstruction, not the original text of the
+    command.)  Functions that accept <literal>VARIADIC</literal> options
+    take alternating name/value text pairs; values are parsed as boolean,
+    integer or text.
+   </para>
+
+   <table id="functions-get-object-ddl-table">
+    <title>Get 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>
+        <optional>, <literal>VARIADIC</literal> <parameter>options</parameter>
+        <type>text</type> </optional> )
+        <returnvalue>setof text</returnvalue>
+       </para>
+       <para>
+        Reconstructs the <command>CREATE ROLE</command> statement and any
+        <command>ALTER ROLE ... SET</command> statements for the given role.
+        Each statement is returned as a separate row.
+        Password information is never included in the output.
+        The following option is supported: <literal>pretty</literal> (boolean)
+        for pretty-printed output.
+       </para></entry>
+      </row>
+     </tbody>
+    </tgroup>
+   </table>
+
+  </sect2>
+
   </sect1>
diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c
index 1ed44cbcb1a..c067de0a3d2 100644
--- a/src/backend/utils/adt/ddlutils.c
+++ b/src/backend/utils/adt/ddlutils.c
@@ -18,8 +18,24 @@
  */
 #include "postgres.h"
 
+#include "access/genam.h"
+#include "access/htup_details.h"
+#include "access/relation.h"
+#include "access/table.h"
+#include "catalog/pg_authid.h"
+#include "catalog/pg_db_role_setting.h"
+#include "funcapi.h"
+#include "miscadmin.h"
+#include "utils/acl.h"
+#include "utils/array.h"
 #include "utils/builtins.h"
+#include "utils/datetime.h"
+#include "utils/fmgroids.h"
 #include "utils/guc.h"
+#include "utils/lsyscache.h"
+#include "utils/rel.h"
+#include "utils/syscache.h"
+#include "utils/timestamp.h"
 #include "utils/varlena.h"
 
 /* Option value types for DDL option parsing */
@@ -50,6 +66,7 @@ static void parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start,
 static void append_ddl_option(StringInfo buf, bool pretty, int indent,
 							  const char *fmt,...)
 			pg_attribute_printf(4, 5);
+static List *pg_get_role_ddl_internal(Oid roleid, bool pretty);
 
 
 /*
@@ -211,3 +228,316 @@ append_ddl_option(StringInfo buf, bool pretty, int indent,
 		enlargeStringInfo(buf, needed);
 	}
 }
+
+/*
+ * 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.
+ */
+static List *
+pg_get_role_ddl_internal(Oid roleid, bool pretty)
+{
+	HeapTuple	tuple;
+	Form_pg_authid roleform;
+	StringInfoData buf;
+	char	   *rolname;
+	Datum		rolevaliduntil;
+	bool		isnull;
+	Relation	rel;
+	ScanKeyData scankey;
+	SysScanDesc scan;
+	List	   *statements = NIL;
+
+	tuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid));
+	if (!HeapTupleIsValid(tuple))
+		ereport(ERROR,
+				(errcode(ERRCODE_UNDEFINED_OBJECT),
+				 errmsg("role with OID %u does not exist", roleid)));
+
+	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 (IsReservedName(rolname))
+		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.
+	 */
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolsuper ? "SUPERUSER" : "NOSUPERUSER");
+
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolinherit ? "INHERIT" : "NOINHERIT");
+
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolcreaterole ? "CREATEROLE" : "NOCREATEROLE");
+
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolcreatedb ? "CREATEDB" : "NOCREATEDB");
+
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolcanlogin ? "LOGIN" : "NOLOGIN");
+
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolreplication ? "REPLICATION" : "NOREPLICATION");
+
+	append_ddl_option(&buf, pretty, 4, "%s",
+					  roleform->rolbypassrls ? "BYPASSRLS" : "NOBYPASSRLS");
+
+	/*
+	 * CONNECTION LIMIT is only interesting if it's not -1 (the default,
+	 * meaning no limit).
+	 */
+	if (roleform->rolconnlimit >= 0)
+		append_ddl_option(&buf, pretty, 4, "CONNECTION LIMIT %d",
+						  roleform->rolconnlimit);
+
+	rolevaliduntil = SysCacheGetAttr(AUTHOID, tuple,
+									 Anum_pg_authid_rolvaliduntil,
+									 &isnull);
+	if (!isnull)
+	{
+		TimestampTz ts;
+		int			tz;
+		struct pg_tm tm;
+		fsec_t		fsec;
+		const char *tzn;
+		char		ts_str[MAXDATELEN + 1];
+
+		ts = DatumGetTimestampTz(rolevaliduntil);
+		if (TIMESTAMP_NOT_FINITE(ts))
+			EncodeSpecialTimestamp(ts, ts_str);
+		else if (timestamp2tm(ts, &tz, &tm, &fsec, &tzn, NULL) == 0)
+			EncodeDateTime(&tm, fsec, true, tz, tzn, DateStyle, ts_str);
+		else
+			ereport(ERROR,
+					(errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
+					 errmsg("timestamp out of range")));
+
+		append_ddl_option(&buf, pretty, 4, "VALID UNTIL %s",
+						  quote_literal_cstr(ts_str));
+	}
+
+	ReleaseSysCache(tuple);
+
+	/*
+	 * 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));
+
+	/*
+	 * 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).  It generates one ALTER
+	 * statement per setting.
+	 */
+	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(tuple = systable_getnext(scan)))
+	{
+		Form_pg_db_role_setting setting = (Form_pg_db_role_setting) GETSTRUCT(tuple);
+		Oid			datid = setting->setdatabase;
+		Datum		datum;
+		ArrayType  *reloptions;
+		Datum	   *settings;
+		bool	   *nulls;
+		int			nsettings;
+		char	   *datname = NULL;
+
+		/*
+		 * 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);
+			/* Database has been dropped; skip all settings in this row. */
+			if (datname == NULL)
+				continue;
+		}
+
+		/*
+		 * 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(tuple, Anum_pg_db_role_setting_setconfig,
+							 RelationGetDescr(rel), &isnull);
+		if (isnull)
+			continue;
+
+		reloptions = DatumGetArrayTypeP(datum);
+
+		deconstruct_array_builtin(reloptions, TEXTOID, &settings, &nulls, &nsettings);
+
+		for (int i = 0; i < nsettings; i++)
+		{
+			char	   *s,
+					   *p;
+
+			if (nulls[i])
+				continue;
+
+			s = TextDatumGetCString(settings[i]);
+			p = strchr(s, '=');
+			if (p == NULL)
+			{
+				pfree(s);
+				continue;
+			}
+			*p++ = '\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 ",
+							 quote_identifier(s));
+
+			/*
+			 * Variables that are marked GUC_LIST_QUOTE were already fully
+			 * quoted before they were put into the setconfig array.  Break
+			 * the list value apart and then quote the elements as string
+			 * literals.
+			 */
+			if (GetConfigOptionFlags(s, true) & GUC_LIST_QUOTE)
+			{
+				List	   *namelist;
+				ListCell   *lc;
+
+				/* Parse string into list of identifiers */
+				if (!SplitGUCList(p, ',', &namelist))
+				{
+					/* this shouldn't fail really */
+					elog(ERROR, "invalid list syntax in setconfig item");
+				}
+				/* Special case: represent an empty list as NULL */
+				if (namelist == NIL)
+					appendStringInfoString(&buf, "NULL");
+				foreach(lc, namelist)
+				{
+					char	   *curname = (char *) lfirst(lc);
+
+					appendStringInfoString(&buf, quote_literal_cstr(curname));
+					if (lnext(namelist, lc))
+						appendStringInfoString(&buf, ", ");
+				}
+			}
+			else
+				appendStringInfoString(&buf, quote_literal_cstr(p));
+
+			appendStringInfoChar(&buf, ';');
+
+			statements = lappend(statements, pstrdup(buf.data));
+
+			pfree(s);
+		}
+
+		pfree(settings);
+		pfree(reloptions);
+
+		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 set of text rows.
+ *
+ * Each row is a complete SQL statement.  The first row is always the
+ * CREATE ROLE statement; subsequent rows are ALTER ROLE SET statements.
+ * Returns no rows if the role argument is NULL.
+ */
+Datum
+pg_get_role_ddl(PG_FUNCTION_ARGS)
+{
+	FuncCallContext *funcctx;
+	List	   *statements;
+	ListCell   *lc;
+
+	if (SRF_IS_FIRSTCALL())
+	{
+		MemoryContext oldcontext;
+		Oid			roleid;
+		DdlOption	opts[] = {{"pretty", DDL_OPT_BOOL}};
+
+		funcctx = SRF_FIRSTCALL_INIT();
+		oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
+
+		if (PG_ARGISNULL(0))
+		{
+			MemoryContextSwitchTo(oldcontext);
+			SRF_RETURN_DONE(funcctx);
+		}
+
+		roleid = PG_GETARG_OID(0);
+		parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
+
+		statements = pg_get_role_ddl_internal(roleid,
+											  opts[0].isset && opts[0].boolval);
+		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);
+	}
+}
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fc8d82665b8..e908146645f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -8588,6 +8588,14 @@
 { oid => '2508', descr => 'constraint description with pretty-print option',
   proname => 'pg_get_constraintdef', provolatile => 's', prorettype => 'text',
   proargtypes => 'oid bool', prosrc => 'pg_get_constraintdef_ext' },
+{ oid => '8760', descr => 'get DDL to recreate a role',
+  proname => 'pg_get_role_ddl', provariadic => 'text', proisstrict => 'f',
+  provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text',
+  proargtypes => 'regrole text',
+  proargmodes => '{i,v}',
+  proallargtypes => '{regrole,text}',
+  pronargdefaults => '1', proargdefaults => '{NULL}',
+  prosrc => 'pg_get_role_ddl' },
 { oid => '2509',
   descr => 'deparse an encoded expression with pretty-print option',
   proname => 'pg_get_expr', provolatile => 's', prorettype => 'text',
diff --git a/src/test/regress/expected/role_ddl.out b/src/test/regress/expected/role_ddl.out
new file mode 100644
index 00000000000..98ef42c9e28
--- /dev/null
+++ b/src/test/regress/expected/role_ddl.out
@@ -0,0 +1,100 @@
+-- Consistent test results
+SET timezone TO 'UTC';
+SET DateStyle TO 'ISO, YMD';
+-- Create test database
+CREATE DATABASE regression_role_ddl_test;
+-- Basic role
+CREATE ROLE regress_role_ddl_test1;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1');
+                                                  pg_get_role_ddl                                                  
+-------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test1 NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
+(1 row)
+
+-- Role with LOGIN
+CREATE ROLE regress_role_ddl_test2 LOGIN;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test2');
+                                                 pg_get_role_ddl                                                 
+-----------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test2 NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS;
+(1 row)
+
+-- 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 * FROM pg_get_role_ddl('regress_role_ddl_test3');
+                                                                          pg_get_role_ddl                                                                          
+-------------------------------------------------------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test3 SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN NOREPLICATION NOBYPASSRLS CONNECTION LIMIT 5 VALID UNTIL '2030-12-31 23:59:59+00';
+(1 row)
+
+-- 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 * FROM pg_get_role_ddl('regress_role_ddl_test4');
+                                                  pg_get_role_ddl                                                  
+-------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test4 NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB 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)
+
+-- 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 * FROM pg_get_role_ddl('regress_role_ddl_test5');
+                                                  pg_get_role_ddl                                                  
+-------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE regress_role_ddl_test5 NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
+ ALTER ROLE regress_role_ddl_test5 IN DATABASE regression_role_ddl_test SET work_mem TO '128MB';
+(2 rows)
+
+-- Role with special characters (requires quoting)
+CREATE ROLE "regress_role-with-dash";
+SELECT * FROM pg_get_role_ddl('regress_role-with-dash');
+                                                   pg_get_role_ddl                                                   
+---------------------------------------------------------------------------------------------------------------------
+ CREATE ROLE "regress_role-with-dash" NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS;
+(1 row)
+
+-- Pretty-printed output
+\pset format unaligned
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3', 'pretty', 'true');
+pg_get_role_ddl
+CREATE ROLE regress_role_ddl_test3
+    SUPERUSER
+    INHERIT
+    CREATEROLE
+    CREATEDB
+    LOGIN
+    NOREPLICATION
+    NOBYPASSRLS
+    CONNECTION LIMIT 5
+    VALID UNTIL '2030-12-31 23:59:59+00';
+(1 row)
+\pset format aligned
+-- Non-existent role (should return no rows)
+SELECT * FROM pg_get_role_ddl(9999999::oid);
+ERROR:  role with OID 9999999 does not exist
+-- NULL input (should return no rows)
+SELECT * FROM pg_get_role_ddl(NULL);
+ pg_get_role_ddl 
+-----------------
+(0 rows)
+
+-- 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 734da057c34..7e059cef034 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -130,6 +130,8 @@ test: partition_merge partition_split partition_join partition_prune reloptions
 # oidjoins is read-only, though, and should run late for best coverage
 test: oidjoins event_trigger
 
+test: role_ddl
+
 # event_trigger_login cannot run concurrently with any other tests because
 # on-login event handling could catch connection of a concurrent test.
 test: event_trigger_login
diff --git a/src/test/regress/sql/role_ddl.sql b/src/test/regress/sql/role_ddl.sql
new file mode 100644
index 00000000000..c9509ae474e
--- /dev/null
+++ b/src/test/regress/sql/role_ddl.sql
@@ -0,0 +1,63 @@
+-- Consistent test results
+SET timezone TO 'UTC';
+SET DateStyle TO 'ISO, YMD';
+
+-- Create test database
+CREATE DATABASE regression_role_ddl_test;
+
+-- Basic role
+CREATE ROLE regress_role_ddl_test1;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1');
+
+-- Role with LOGIN
+CREATE ROLE regress_role_ddl_test2 LOGIN;
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test2');
+
+-- 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 * FROM pg_get_role_ddl('regress_role_ddl_test3');
+
+-- 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 * FROM pg_get_role_ddl('regress_role_ddl_test4');
+
+-- 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 * FROM pg_get_role_ddl('regress_role_ddl_test5');
+
+-- Role with special characters (requires quoting)
+CREATE ROLE "regress_role-with-dash";
+SELECT * FROM pg_get_role_ddl('regress_role-with-dash');
+
+-- Pretty-printed output
+\pset format unaligned
+SELECT * FROM pg_get_role_ddl('regress_role_ddl_test3', 'pretty', 'true');
+\pset format aligned
+
+-- Non-existent role (should return no rows)
+SELECT * FROM pg_get_role_ddl(9999999::oid);
+
+-- NULL input (should return no rows)
+SELECT * FROM pg_get_role_ddl(NULL);
+
+-- 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.43.0