From cb94896caa6915a7980b07f70f40d936ce95eb10 Mon Sep 17 00:00:00 2001 From: Andrew Dunstan Date: Thu, 19 Mar 2026 09:52:25 -0400 Subject: [PATCH v2 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 Author: Bryan Green Co-authored-by: Andrew Dunstan Reviewed-by: Euler Taveira Reviewed-by: Japin Li Reviewed-by: Quan Zongliang Reviewed-by: jian he 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 | 328 +++++++++++++++++++++++++ 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, 555 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} + + Get Object DDL Functions + + + The functions shown in + 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 VARIADIC options + take alternating name/value text pairs; values are parsed as boolean, + integer or text. + + + + Get Object DDL Functions + + + + + Function + + + Description + + + + + + + + + pg_get_role_ddl + + pg_get_role_ddl + ( role regrole + , VARIADIC options + text ) + setof text + + + Reconstructs the CREATE ROLE statement and any + ALTER ROLE ... SET 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: pretty (boolean) + for pretty-printed output. + + + + +
+ + + diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c index a15d1e00d79..fd85edd7eeb 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 */ @@ -54,6 +70,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); /* @@ -224,3 +241,314 @@ 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; + bool first = true; + + /* 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_ptr(char, curname, namelist) + { + if (first) + first = false; + else + appendStringInfoString(&buf, ", "); + appendStringInfoString(&buf, quote_literal_cstr(curname)); + } + } + 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; + + 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; + + stmt = list_nth(statements, funcctx->call_cntr); + + 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 0118e970dda..fbd400b5a67 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -8603,6 +8603,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.39.5