From: Bryan Green <dbryan.green@gmail.com>
To: pgsql-hackers@lists.postgresql.org
Subject: [PATCH] Add pg_get_role_ddl() functions for role recreation
Date: Fri, 24 Oct 2025 15:03:06 -0500
Message-ID: <4c5f895e-3281-48f8-b943-9228b7da6471@gmail.com> (raw)
Attached is a patch adding two new functions for generating DDL to
recreate roles: pg_get_role_ddl() and pg_get_role_ddl_statements().
These functions return the CREATE ROLE statement and any ALTER ROLE SET
configuration parameters needed to recreate a role. The former returns
everything as a single text string, while the latter returns each
statement as a separate row for easier programmatic processing.
The main use case is dumping role definitions for migration or backup
purposes without needing pg_dumpall. The functions handle all role
attributes (LOGIN, SUPERUSER, etc.) and both role-wide and
database-specific configuration parameters.
We intentionally don't include passwords, since we can only see the
hashed values. System roles (names starting with "pg_") are rejected
with an error, as users shouldn't be recreating those anyway.
To test:
CREATE ROLE testrole LOGIN CREATEDB CONNECTION LIMIT 5;
ALTER ROLE testrole SET work_mem TO '64MB';
SELECT pg_get_role_ddl('testrole');
Should produce:
CREATE ROLE testrole LOGIN NOSUPERUSER CREATEDB NOCREATEROLE INHERIT
NOREPLICATION NOBYPASSRLS CONNECTION LIMIT 5;
ALTER ROLE testrole SET work_mem TO '64MB';
The patch includes regression tests covering various role configurations.
Co-authored-by: Mario Gonzalez and Bryan Green.
Comments?
BG
Attachments:v1-0001-Add-functions-to-generate-DDL-for-recreating-roles.patch (text/plain, 24.2K) download
From 8d9e7d59ea216ae5f008c925313ee16240820377 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.sgmlindex c393832d94..b98c2dae22 100644--- a/doc/src/sgml/func/func-info.sgml+++ b/doc/src/sgml/func/func-info.sgml@@ -2174,7 +2174,61 @@ SELECT currval(pg_get_serial_sequence('sometable', 'id'));
</tbody>
</tgroup>
</table>
-+ <table id="functions-object-ddl-table">+ <title>Object DDL Functions</title>+ <tgroup cols="1">+ <thead>+ <row>+ <entry role="func_table_entry"><para role="func_signature">Function</para>+ <para>Description</para>+ </entry>+ </row>+ </thead>+ <tbody>+ <row>+ <entry role="func_table_entry">+ <para role="func_signature">+ <indexterm>+ <primary>pg_get_role_ddl</primary>+ </indexterm>+ <function>pg_get_role_ddl</function> ( <parameter>role</parameter> <type>regrole</type> )+ <returnvalue>text</returnvalue>+ </para>+ <para>+ Returns the DDL commands that would recreate the given role as a single text string.+ The result includes the <command>CREATE ROLE</command> statement and any+ <command>ALTER ROLE</command> statements needed to set role configuration parameters.+ Password information is never included in the output.+ </para>+ <para>+ Returns <literal>NULL</literal> if the role does not exist.+ </para>+ </entry>+ </row>+ <row>+ <entry role="func_table_entry">+ <para role="func_signature">+ <indexterm>+ <primary>pg_get_role_ddl_statements</primary>+ </indexterm>+ <function>pg_get_role_ddl_statements</function> ( <parameter>role</parameter> <type>regrole</type> )+ <returnvalue>setof text</returnvalue>+ </para>+ <para>+ Returns the DDL commands that would recreate the given role as a set of rows,+ with each statement returned as a separate row. The first row contains the+ <command>CREATE ROLE</command> statement, followed by any <command>ALTER ROLE</command>+ statements needed to set role configuration parameters. This format is useful for+ programmatic processing or when you want to filter or analyze individual statements.+ </para>+ <para>+ Returns an empty set if the role does not exist.+ </para>+ </entry>+ </row>+ </tbody>+ </tgroup>+ </table>
<para>
Most of the functions that reconstruct (decompile) database objects
have an optional <parameter>pretty</parameter> flag, which
diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.cindex 79ec136231..6c46580a0e 100644--- a/src/backend/utils/adt/ruleutils.c+++ b/src/backend/utils/adt/ruleutils.c@@ -28,8 +28,9 @@
#include "catalog/pg_authid.h"
#include "catalog/pg_collation.h"
#include "catalog/pg_constraint.h"
+#include "catalog/pg_db_role_setting.h"
#include "catalog/pg_depend.h"
#include "catalog/pg_language.h"
#include "catalog/pg_opclass.h"
#include "catalog/pg_operator.h"
#include "catalog/pg_partitioned_table.h"
@@ -59,6 +60,7 @@
#include "rewrite/rewriteSupport.h"
#include "utils/array.h"
#include "utils/builtins.h"
+#include "utils/datetime.h"
#include "utils/fmgroids.h"
#include "utils/guc.h"
#include "utils/hsearch.h"
@@ -549,6 +551,328 @@ static void get_json_table_nested_columns(TableFunc *tf, JsonTablePlan *plan,
#define only_marker(rte) ((rte)->inh ? "" : "ONLY ")
+/*+ * pg_get_role_ddl_internal+ * Generate DDL statements to recreate a role+ *+ * Returns a List of palloc'd strings, each being a complete SQL statement.+ * The first list element is always the CREATE ROLE statement; subsequent+ * elements are ALTER ROLE SET statements for any role-specific or+ * role-in-database configuration settings.+ *+ * Returns NIL if the role OID is invalid. This can happen if the role was+ * dropped concurrently, or if we're passed a OID that doesn't match+ * any role.+ */+static List *+pg_get_role_ddl_internal(Oid roleid)+{+ HeapTuple tuple;+ Form_pg_authid roleform;+ StringInfoData buf;+ char *rolname;+ Datum rolevaliduntil;+ bool isnull;+ Relation rel;+ ScanKeyData scankey;+ SysScanDesc scan;+ HeapTuple setting_tuple;+ List *statements = NIL;+ const char *separator = " ";++ tuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid));+ if (!HeapTupleIsValid(tuple))+ return NIL;++ roleform = (Form_pg_authid) GETSTRUCT(tuple);+ rolname = NameStr(roleform->rolname);++ /*+ * We don't support generating DDL for system roles. The primary reason+ * for this is that users shouldn't be recreating them.+ */+ if (strncmp(rolname, "pg_", 3) == 0)+ ereport(ERROR,+ (errcode(ERRCODE_RESERVED_NAME),+ errmsg("role name \"%s\" is reserved", rolname),+ errdetail("Role names starting with \"pg_\" are reserved for system roles.")));++ initStringInfo(&buf);+ appendStringInfo(&buf, "CREATE ROLE %s", quote_identifier(rolname));++ /*+ * Append role attributes. The order here follows the same sequence as+ * you'd typically write them in a CREATE ROLE command, though any order+ * is actually acceptable to the parser.+ */+ appendStringInfo(&buf, "%s%s", separator,+ roleform->rolcanlogin ? "LOGIN" : "NOLOGIN");++ appendStringInfo(&buf, "%s%s", separator,+ roleform->rolsuper ? "SUPERUSER" : "NOSUPERUSER");++ appendStringInfo(&buf, "%s%s", separator,+ roleform->rolcreatedb ? "CREATEDB" : "NOCREATEDB");++ appendStringInfo(&buf, "%s%s", separator,+ roleform->rolcreaterole ? "CREATEROLE" : "NOCREATEROLE");++ appendStringInfo(&buf, "%s%s", separator,+ roleform->rolinherit ? "INHERIT" : "NOINHERIT");++ appendStringInfo(&buf, "%s%s", separator,+ roleform->rolreplication ? "REPLICATION" : "NOREPLICATION");++ appendStringInfo(&buf, "%s%s", separator,+ roleform->rolbypassrls ? "BYPASSRLS" : "NOBYPASSRLS");++ /*+ * CONNECTION LIMIT is only interesting if it's not -1 (the default,+ * meaning no limit).+ */+ if (roleform->rolconnlimit >= 0)+ appendStringInfo(&buf, "%sCONNECTION LIMIT %d",+ separator, roleform->rolconnlimit);++ rolevaliduntil = SysCacheGetAttr(AUTHOID, tuple,+ Anum_pg_authid_rolvaliduntil,+ &isnull);+ if (!isnull)+ {+ struct pg_tm tm;+ fsec_t fsec;+ char ts_str[MAXDATELEN + 1];++ if (timestamp2tm(rolevaliduntil, NULL, &tm, &fsec, NULL, NULL) == 0)+ {+ EncodeDateTime(&tm, fsec, false, 0, "UTC", USE_ISO_DATES, ts_str);+ appendStringInfo(&buf, "%sVALID UNTIL %s",+ separator, quote_literal_cstr(ts_str));+ }+ }++ /*+ * We intentionally omit PASSWORD. There's no way to retrieve the+ * original password text from the stored hash, and even if we could,+ * exposing passwords through a SQL function would be a security issue.+ * Users must set passwords separately after recreating roles.+ */++ appendStringInfoChar(&buf, ';');++ statements = lappend(statements, pstrdup(buf.data));++ ReleaseSysCache(tuple);++ /*+ * Now scan pg_db_role_setting for ALTER ROLE SET configurations.+ *+ * These can be role-wide (setdatabase = 0) or specific to a particular+ * database (setdatabase = a valid DB OID). We generate one ALTER+ * statement per setting, which isn't as compact as it could be, but is+ * straightforward and matches how users typically set these up.+ */+ rel = table_open(DbRoleSettingRelationId, AccessShareLock);+ ScanKeyInit(&scankey,+ Anum_pg_db_role_setting_setrole,+ BTEqualStrategyNumber, F_OIDEQ,+ ObjectIdGetDatum(roleid));+ scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true,+ NULL, 1, &scankey);++ while (HeapTupleIsValid(setting_tuple = systable_getnext(scan)))+ {+ Form_pg_db_role_setting setting = (Form_pg_db_role_setting) GETSTRUCT(setting_tuple);+ Oid datid = setting->setdatabase;+ Datum datum;+ bool setting_isnull;+ ArrayType *settings;+ int i;+ char *datname = NULL;++ /*+ * The setconfig column is a text array in "name=value" format. It+ * should never be null for a valid row, but be defensive.+ */+ datum = heap_getattr(setting_tuple, Anum_pg_db_role_setting_setconfig,+ RelationGetDescr(rel), &setting_isnull);+ if (setting_isnull)+ continue;++ settings = DatumGetArrayTypeP(datum);++ /*+ * If setdatabase is valid, this is a role-in-database setting;+ * otherwise it's a role-wide setting. Look up the database name once+ * for all settings in this row.+ */+ if (OidIsValid(datid))+ {+ datname = get_database_name(datid);+ if (datname == NULL)+ {+ /*+ * Database has been dropped; skip all settings in this row.+ */+ continue;+ }+ }++ /* Process each setting in the array */+ for (i = 1; i <= ArrayGetNItems(ARR_NDIM(settings), ARR_DIMS(settings)); i++)+ {+ Datum setting_datum;+ bool setting_elem_isnull;+ char *setting_str;+ char *equals_pos;++ setting_datum = array_ref(settings, 1, &i,+ -1 /* varlenarray */ ,+ -1 /* TEXT's typlen */ ,+ false /* TEXT's typbyval */ ,+ TYPALIGN_INT /* TEXT's typalign */ ,+ &setting_elem_isnull);++ if (setting_elem_isnull)+ continue;++ setting_str = TextDatumGetCString(setting_datum);++ /*+ * Parse out the parameter name and value. The format should+ * always be "name=value" but check anyway to avoid a crash if the+ * catalog is corrupted.+ */+ equals_pos = strchr(setting_str, '=');+ if (equals_pos == NULL)+ {+ pfree(setting_str);+ continue;+ }++ *equals_pos = '\0';++ /* Build a fresh ALTER ROLE statement for this setting */+ resetStringInfo(&buf);+ appendStringInfo(&buf, "ALTER ROLE %s", quote_identifier(rolname));++ if (datname != NULL)+ appendStringInfo(&buf, " IN DATABASE %s",+ quote_identifier(datname));++ appendStringInfo(&buf, " SET %s TO %s;",+ quote_identifier(setting_str),+ quote_literal_cstr(equals_pos + 1));++ statements = lappend(statements, pstrdup(buf.data));++ pfree(setting_str);+ }++ if (datname != NULL)+ pfree(datname);+ }++ systable_endscan(scan);+ table_close(rel, AccessShareLock);++ pfree(buf.data);++ return statements;+}+++/*+ * pg_get_role_ddl+ * Return DDL to recreate a role as a single text string+ *+ * This is the main user-facing function. It calls pg_get_role_ddl_internal+ * to get the list of statements, then concatenates them with newlines.+ *+ * Returns NULL if the role OID doesn't exist. This can only happen if+ * you pass a OID rather than using the regrole type, or if there's+ * a race condition with a concurrent DROP ROLE.+ */+Datum+pg_get_role_ddl(PG_FUNCTION_ARGS)+{+ Oid roleid = PG_GETARG_OID(0);+ List *statements;+ StringInfoData result;+ ListCell *lc;+ bool first = true;++ statements = pg_get_role_ddl_internal(roleid);++ if (statements == NIL)+ PG_RETURN_NULL();++ initStringInfo(&result);++ foreach(lc, statements)+ {+ char *stmt = (char *) lfirst(lc);++ if (!first)+ appendStringInfoChar(&result, '\n');+ appendStringInfoString(&result, stmt);+ first = false;+ }++ list_free_deep(statements);++ PG_RETURN_TEXT_P(cstring_to_text(result.data));+}++/*+ * pg_get_role_ddl_statements+ * Return DDL to recreate a role as a set of rows+ *+ * This is similar to pg_get_role_ddl, but returns each statement as a+ * separate row. This is useful for programmatic processing or when you+ * want to filter/analyze individual statements.+ */+Datum+pg_get_role_ddl_statements(PG_FUNCTION_ARGS)+{+ FuncCallContext *funcctx;+ List *statements;+ ListCell *lc;++ if (SRF_IS_FIRSTCALL())+ {+ MemoryContext oldcontext;+ Oid roleid = PG_GETARG_OID(0);++ funcctx = SRF_FIRSTCALL_INIT();+ oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);++ statements = pg_get_role_ddl_internal(roleid);+ funcctx->user_fctx = statements;+ funcctx->max_calls = list_length(statements);++ MemoryContextSwitchTo(oldcontext);+ }++ funcctx = SRF_PERCALL_SETUP();+ statements = (List *) funcctx->user_fctx;++ if (funcctx->call_cntr < funcctx->max_calls)+ {+ char *stmt;++ lc = list_nth_cell(statements, funcctx->call_cntr);+ stmt = (char *) lfirst(lc);++ SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));+ }+ else+ {+ list_free_deep(statements);+ SRF_RETURN_DONE(funcctx);+ }+}+
/* ----------
* pg_get_ruledef - Do it all and return a text
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.datindex eecb43ec6f..0d0511589f 100644--- a/src/include/catalog/pg_proc.dat+++ b/src/include/catalog/pg_proc.dat@@ -12604,4 +12604,28 @@
proargnames => '{pid,io_id,io_generation,state,operation,off,length,target,handle_data_len,raw_result,result,target_desc,f_sync,f_localmem,f_buffered}',
prosrc => 'pg_get_aios' },
+# pg_get_role_ddl - return DDL to recreate a role (compact format)+{ oid => '9991', descr => 'get SQL commands to recreate role',+ proname => 'pg_get_role_ddl', pronamespace => 'pg_catalog',+ proowner => 'POSTGRES', prolang => 'internal', procost => '1', prorows => '0',+ provariadic => '0', prosupport => '0', prokind => 'f', prosecdef => 'f',+ proleakproof => 'f', proisstrict => 't', proretset => 'f', provolatile => 's',+ proparallel => 's', pronargs => '1', pronargdefaults => '0',+ prorettype => 'text', proargtypes => 'regrole', proallargtypes => '_null_',+ proargmodes => '_null_', proargnames => '_null_', proargdefaults => '_null_',+ protrftypes => '_null_', prosrc => 'pg_get_role_ddl', probin => '_null_',+ prosqlbody => '_null_', proconfig => '_null_', proacl => '_null_' },++# pg_get_role_ddl_statements - return DDL as separate statements (compact format)+{ oid => '9992', descr => 'get SQL commands to recreate role as row set',+ proname => 'pg_get_role_ddl_statements', pronamespace => 'pg_catalog',+ proowner => 'POSTGRES', prolang => 'internal', procost => '1',+ prorows => '10', provariadic => '0', prosupport => '0', prokind => 'f',+ prosecdef => 'f', proleakproof => 'f', proisstrict => 't', proretset => 't',+ provolatile => 's', proparallel => 's', pronargs => '1',+ pronargdefaults => '0', prorettype => 'text', proargtypes => 'regrole',+ proallargtypes => '_null_', proargmodes => '_null_', proargnames => '_null_',+ proargdefaults => '_null_', protrftypes => '_null_',+ prosrc => 'pg_get_role_ddl_statements', probin => '_null_',+ prosqlbody => '_null_', proconfig => '_null_', proacl => '_null_' },
]
diff --git a/src/test/regress/expected/role_ddl.out b/src/test/regress/expected/role_ddl.outnew file mode 100644index 0000000000..cbc4167a72--- /dev/null+++ b/src/test/regress/expected/role_ddl.out@@ -0,0 +1,90 @@+-- Set fixed timezone for consistent test results+SET timezone = 'UTC';+-- Create test database+CREATE DATABASE regression_role_ddl_test;+-- Test 1: Basic role+CREATE ROLE regress_role_ddl_test1;+SELECT pg_get_role_ddl('regress_role_ddl_test1');+ pg_get_role_ddl +-------------------------------------------------------------------------------------------------------------------+ CREATE ROLE regress_role_ddl_test1 NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION NOBYPASSRLS;+(1 row)++-- Test 2: Role with LOGIN+CREATE ROLE regress_role_ddl_test2 LOGIN;+SELECT pg_get_role_ddl('regress_role_ddl_test2');+ pg_get_role_ddl +-----------------------------------------------------------------------------------------------------------------+ CREATE ROLE regress_role_ddl_test2 LOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION NOBYPASSRLS;+(1 row)++-- Test 3: Role with multiple privileges+CREATE ROLE regress_role_ddl_test3+ LOGIN+ SUPERUSER+ CREATEDB+ CREATEROLE+ CONNECTION LIMIT 5+ VALID UNTIL '2030-12-31 23:59:59+00';+SELECT pg_get_role_ddl('regress_role_ddl_test3');+ pg_get_role_ddl +----------------------------------------------------------------------------------------------------------------------------------------------------------------+ CREATE ROLE regress_role_ddl_test3 LOGIN SUPERUSER CREATEDB CREATEROLE INHERIT NOREPLICATION NOBYPASSRLS CONNECTION LIMIT 5 VALID UNTIL '2030-12-31 23:59:59';+(1 row)++-- Test 4: Role with configuration parameters+CREATE ROLE regress_role_ddl_test4;+ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB';+ALTER ROLE regress_role_ddl_test4 SET search_path TO 'myschema, public';+SELECT pg_get_role_ddl('regress_role_ddl_test4');+ pg_get_role_ddl +-------------------------------------------------------------------------------------------------------------------+ CREATE ROLE regress_role_ddl_test4 NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION NOBYPASSRLS;++ ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB'; ++ ALTER ROLE regress_role_ddl_test4 SET search_path TO '"myschema, public"';+(1 row)++-- Test 5: Role with database-specific configuration+CREATE ROLE regress_role_ddl_test5;+ALTER ROLE regress_role_ddl_test5 IN DATABASE regression_role_ddl_test SET work_mem TO '128MB';+SELECT pg_get_role_ddl('regress_role_ddl_test5');+ pg_get_role_ddl +-------------------------------------------------------------------------------------------------------------------+ CREATE ROLE regress_role_ddl_test5 NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION NOBYPASSRLS;++ ALTER ROLE regress_role_ddl_test5 IN DATABASE regression_role_ddl_test SET work_mem TO '128MB';+(1 row)++-- Test 6: Test pg_get_role_ddl_statements function+SELECT * FROM pg_get_role_ddl_statements('regress_role_ddl_test4');+ pg_get_role_ddl_statements +-------------------------------------------------------------------------------------------------------------------+ CREATE ROLE regress_role_ddl_test4 NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION NOBYPASSRLS;+ ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB';+ ALTER ROLE regress_role_ddl_test4 SET search_path TO '"myschema, public"';+(3 rows)++-- Test 7: Role with special characters (requires quoting)+CREATE ROLE "regress_role-with-dash";+SELECT pg_get_role_ddl('regress_role-with-dash');+ pg_get_role_ddl +---------------------------------------------------------------------------------------------------------------------+ CREATE ROLE "regress_role-with-dash" NOLOGIN NOSUPERUSER NOCREATEDB NOCREATEROLE INHERIT NOREPLICATION NOBYPASSRLS;+(1 row)++-- Test 8: Non-existent role (should return NULL)+SELECT pg_get_role_ddl(9999999::oid);+ pg_get_role_ddl +-----------------+ +(1 row)++-- Cleanup+DROP ROLE regress_role_ddl_test1;+DROP ROLE regress_role_ddl_test2;+DROP ROLE regress_role_ddl_test3;+DROP ROLE regress_role_ddl_test4;+DROP ROLE regress_role_ddl_test5;+DROP ROLE "regress_role-with-dash";+DROP DATABASE regression_role_ddl_test;+-- Reset timezone to default+RESET timezone;diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_scheduleindex 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.sqlnew file mode 100644index 0000000000..a27ff44137--- /dev/null+++ b/src/test/regress/sql/role_ddl.sql@@ -0,0 +1,57 @@+-- Set fixed timezone for consistent test results+SET timezone = 'UTC';++-- Create test database+CREATE DATABASE regression_role_ddl_test;++-- Test 1: Basic role+CREATE ROLE regress_role_ddl_test1;+SELECT pg_get_role_ddl('regress_role_ddl_test1');++-- Test 2: Role with LOGIN+CREATE ROLE regress_role_ddl_test2 LOGIN;+SELECT pg_get_role_ddl('regress_role_ddl_test2');++-- Test 3: Role with multiple privileges+CREATE ROLE regress_role_ddl_test3+ LOGIN+ SUPERUSER+ CREATEDB+ CREATEROLE+ CONNECTION LIMIT 5+ VALID UNTIL '2030-12-31 23:59:59+00';+SELECT pg_get_role_ddl('regress_role_ddl_test3');++-- Test 4: Role with configuration parameters+CREATE ROLE regress_role_ddl_test4;+ALTER ROLE regress_role_ddl_test4 SET work_mem TO '256MB';+ALTER ROLE regress_role_ddl_test4 SET search_path TO 'myschema, public';+SELECT pg_get_role_ddl('regress_role_ddl_test4');++-- Test 5: Role with database-specific configuration+CREATE ROLE regress_role_ddl_test5;+ALTER ROLE regress_role_ddl_test5 IN DATABASE regression_role_ddl_test SET work_mem TO '128MB';+SELECT pg_get_role_ddl('regress_role_ddl_test5');++-- Test 6: Test pg_get_role_ddl_statements function+SELECT * FROM pg_get_role_ddl_statements('regress_role_ddl_test4');++-- Test 7: Role with special characters (requires quoting)+CREATE ROLE "regress_role-with-dash";+SELECT pg_get_role_ddl('regress_role-with-dash');++-- Test 8: Non-existent role (should return NULL)+SELECT pg_get_role_ddl(9999999::oid);++-- Cleanup+DROP ROLE regress_role_ddl_test1;+DROP ROLE regress_role_ddl_test2;+DROP ROLE regress_role_ddl_test3;+DROP ROLE regress_role_ddl_test4;+DROP ROLE regress_role_ddl_test5;+DROP ROLE "regress_role-with-dash";++DROP DATABASE regression_role_ddl_test;++-- Reset timezone to default+RESET timezone;--
2.46.0.windows.1
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: dbryan.green@gmail.com, pgsql-hackers@lists.postgresql.org
Subject: Re: [PATCH] Add pg_get_role_ddl() functions for role recreation
In-Reply-To: <4c5f895e-3281-48f8-b943-9228b7da6471@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox