On 2026-04-02 Th 9:35 AM, David G. Johnston wrote:
On Thursday, April 2, 2026, Japin Li <[email protected]> wrote:v3-0004 ======== 1. + append_ddl_option(&buf, pretty, 4, "WITH TEMPLATE = template0"); I'm curious why WITH TEMPLATE = template0 is hardcoded. For example: [local]:1374846 postgres=# create database db01 IS_TEMPLATE true; CREATE DATABASE [local]:1374846 postgres=# create database db02 template db01; CREATE DATABASE [local]:1374846 postgres=# select pg_get_database_ddl('db02'); pg_get_database_ddl ----------------------------------------------------------------------------------------------------------------- CREATE DATABASE db02 WITH TEMPLATE = template0 ENCODING = 'UTF8' LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8'; ALTER DATABASE db02 OWNER TO japin; (2 rows) Is this working as expected? It seems there's no way to reconstruct the WITH TEMPLATE clause, right? A comment here would help.There is no way or use in constructing the original template clause, though I agree it’s worth a comment. At the end of the day the catalog data that was found in the db01 database already exists in the db02 database when executing these DLL reconstruction functions against the existing db02 database. Taking nothing from the template is the correct behavior - hence template0.
OK, here's a v4. cheers andrew -- Andrew Dunstan EDB:https://www.enterprisedb.com
From 09508ffa3a627f3fe92efeee1ae4b932bbf34cfe Mon Sep 17 00:00:00 2001 From: Andrew Dunstan <[email protected]> Date: Thu, 19 Mar 2026 09:50:41 -0400 Subject: [PATCH v4 1/4] Add infrastructure for pg_get_*_ddl functions Add parse_ddl_options(), append_ddl_option(), and append_guc_value() helper functions in a new ddlutils.c file that provide common option parsing and output formatting for the pg_get_*_ddl family of functions which will follow in later patches. These accept VARIADIC text arguments as alternating name/value pairs. Callers declare an array of DdlOption descriptors specifying the accepted option names and their types (boolean, text, or integer). parse_ddl_options() matches each supplied pair against the array, validates the value, and fills in the result fields. This descriptor-based scheme is based on an idea from Euler Taveira. This is placed in a new ddlutils.c file which will contain the pg_get_*_ddl functions. Author: Akshay Joshi <[email protected]> Co-authored-by: Andrew Dunstan <[email protected]> Co-authored-by: Euler Taveira <[email protected]> Discussion: https://postgr.es/m/CAKWEB6rmnmGKUA87Zmq-s=b3scsnj02c0kobqjnbl2ajfpw...@mail.gmail.com Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/canxoldc6fhbyjvcgonzys+jf0nuo3lq_83-rttbujgs9id_...@mail.gmail.com Discussion: https://postgr.es/m/[email protected] --- src/backend/utils/adt/Makefile | 1 + src/backend/utils/adt/ddlutils.c | 275 ++++++++++++++++++++++++++++++ src/backend/utils/adt/meson.build | 1 + src/tools/pgindent/typedefs.list | 2 + 4 files changed, 279 insertions(+) create mode 100644 src/backend/utils/adt/ddlutils.c diff --git a/src/backend/utils/adt/Makefile b/src/backend/utils/adt/Makefile index a8fd680589f..0c7621957c1 100644 --- a/src/backend/utils/adt/Makefile +++ b/src/backend/utils/adt/Makefile @@ -31,6 +31,7 @@ OBJS = \ datetime.o \ datum.o \ dbsize.o \ + ddlutils.o \ domains.o \ encode.o \ enum.o \ diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c new file mode 100644 index 00000000000..4bf7d9c38ae --- /dev/null +++ b/src/backend/utils/adt/ddlutils.c @@ -0,0 +1,275 @@ +/*------------------------------------------------------------------------- + * + * ddlutils.c + * Utility functions for generating DDL statements + * + * This file contains the pg_get_*_ddl family of functions that generate + * DDL statements to recreate database objects such as roles, tablespaces, + * and databases, along with common infrastructure for option parsing and + * pretty-printing. + * + * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + * IDENTIFICATION + * src/backend/utils/adt/ddlutils.c + * + *------------------------------------------------------------------------- + */ +#include "postgres.h" + +#include "utils/builtins.h" +#include "utils/guc.h" +#include "utils/varlena.h" + +/* Option value types for DDL option parsing */ +typedef enum +{ + DDL_OPT_BOOL, + DDL_OPT_TEXT, + DDL_OPT_INT, +} DdlOptType; + +/* + * A single DDL option descriptor: caller fills in name and type, + * parse_ddl_options fills in isset + the appropriate value field. + */ +typedef struct DdlOption +{ + const char *name; /* option name (case-insensitive match) */ + DdlOptType type; /* expected value type */ + bool isset; /* true if caller supplied this option */ + /* fields for specific option types */ + union + { + bool boolval; /* filled in for DDL_OPT_BOOL */ + char *textval; /* filled in for DDL_OPT_TEXT (palloc'd) */ + int intval; /* filled in for DDL_OPT_INT */ + }; +} DdlOption; + + +static void parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start, + DdlOption *opts, int nopts); +static void append_ddl_option(StringInfo buf, bool pretty, int indent, + const char *fmt,...) + pg_attribute_printf(4, 5); +static void append_guc_value(StringInfo buf, const char *name, + const char *value); + + +/* + * parse_ddl_options + * Parse variadic name/value option pairs + * + * Options are passed as alternating key/value text pairs. The caller + * provides an array of DdlOption descriptors specifying the accepted + * option names and their types; this function matches each supplied + * pair against the array, validates the value, and fills in the + * result fields. + */ +static void +parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start, + DdlOption *opts, int nopts) +{ + Datum *args; + bool *nulls; + Oid *types; + int nargs; + + /* Clear all output fields */ + for (int i = 0; i < nopts; i++) + { + opts[i].isset = false; + switch (opts[i].type) + { + case DDL_OPT_BOOL: + opts[i].boolval = false; + break; + case DDL_OPT_TEXT: + opts[i].textval = NULL; + break; + case DDL_OPT_INT: + opts[i].intval = 0; + break; + } + } + + nargs = extract_variadic_args(fcinfo, variadic_start, true, + &args, &types, &nulls); + + if (nargs <= 0) + return; + + /* Handle DEFAULT NULL case */ + if (nargs == 1 && nulls[0]) + return; + + if (nargs % 2 != 0) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("variadic arguments must be name/value pairs"), + errhint("Provide an even number of variadic arguments that can be divided into pairs."))); + + /* + * For each option name/value pair, find corresponding positional option + * for the option name, and assign the option value. + */ + for (int i = 0; i < nargs; i += 2) + { + char *name; + char *valstr; + DdlOption *opt = NULL; + + if (nulls[i]) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("option name at variadic position %d is null", i + 1))); + + name = TextDatumGetCString(args[i]); + + if (nulls[i + 1]) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("value for option \"%s\" must not be null", name))); + + /* Find matching option descriptor */ + for (int j = 0; j < nopts; j++) + { + if (pg_strcasecmp(name, opts[j].name) == 0) + { + opt = &opts[j]; + break; + } + } + + if (opt == NULL) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("unrecognized option: \"%s\"", name))); + + if (opt->isset) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("option \"%s\" is specified more than once", + name))); + + valstr = TextDatumGetCString(args[i + 1]); + + switch (opt->type) + { + case DDL_OPT_BOOL: + if (!parse_bool(valstr, &opt->boolval)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid value for boolean option \"%s\": %s", + name, valstr))); + break; + + case DDL_OPT_TEXT: + opt->textval = valstr; + valstr = NULL; /* don't pfree below */ + break; + + case DDL_OPT_INT: + { + char *endp; + long val; + + errno = 0; + val = strtol(valstr, &endp, 10); + if (*endp != '\0' || errno == ERANGE || + val < PG_INT32_MIN || val > PG_INT32_MAX) + ereport(ERROR, + (errcode(ERRCODE_INVALID_PARAMETER_VALUE), + errmsg("invalid value for integer option \"%s\": %s", + name, valstr))); + opt->intval = (int) val; + } + break; + } + + opt->isset = true; + + if (valstr) + pfree(valstr); + pfree(name); + } +} + +/* + * Helper to append a formatted string with optional pretty-printing. + */ +static void +append_ddl_option(StringInfo buf, bool pretty, int indent, + const char *fmt,...) +{ + if (pretty) + { + appendStringInfoChar(buf, '\n'); + appendStringInfoSpaces(buf, indent); + } + else + appendStringInfoChar(buf, ' '); + + for (;;) + { + va_list args; + int needed; + + va_start(args, fmt); + needed = appendStringInfoVA(buf, fmt, args); + va_end(args); + if (needed == 0) + break; + enlargeStringInfo(buf, needed); + } +} + +/* + * append_guc_value + * Append a GUC setting value to buf, handling GUC_LIST_QUOTE properly. + * + * Variables marked GUC_LIST_QUOTE were already fully quoted before they + * were stored in the setconfig array. We break the list value apart + * and re-quote the elements as string literals. For all other variables + * we simply quote the value as a single string literal. + * + * The caller has already appended "SET <name> TO " to buf. + */ +static void +append_guc_value(StringInfo buf, const char *name, const char *value) +{ + char *rawval; + + rawval = pstrdup(value); + + if (GetConfigOptionFlags(name, true) & GUC_LIST_QUOTE) + { + List *namelist; + bool first = true; + + /* Parse string into list of identifiers */ + if (!SplitGUCList(rawval, ',', &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)); + } + list_free(namelist); + } + else + appendStringInfoString(buf, quote_literal_cstr(rawval)); + + pfree(rawval); +} diff --git a/src/backend/utils/adt/meson.build b/src/backend/utils/adt/meson.build index fb8294d7e4a..d793f8145f6 100644 --- a/src/backend/utils/adt/meson.build +++ b/src/backend/utils/adt/meson.build @@ -30,6 +30,7 @@ backend_sources += files( 'datetime.c', 'datum.c', 'dbsize.c', + 'ddlutils.c', 'domains.c', 'encode.c', 'enum.c', diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list index 5bc517602b1..c1a5948b191 100644 --- a/src/tools/pgindent/typedefs.list +++ b/src/tools/pgindent/typedefs.list @@ -626,6 +626,8 @@ DSMREntryType DSMRegistryCtxStruct DSMRegistryEntry DWORD +DdlOptType +DdlOption DataDirSyncMethod DataDumperPtr DataPageDeleteStack -- 2.43.0
From eab56e0ba2e06490cac4e654dc442d968deb96ac Mon Sep 17 00:00:00 2001 From: Andrew Dunstan <[email protected]> Date: Thu, 19 Mar 2026 09:52:25 -0400 Subject: [PATCH v4 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 options are supported: pretty (boolean) for formatted output and memberships (boolean) to include GRANT statements for role memberships and membership options. 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. Password information is never included in the output. The caller must have SELECT privilege on pg_authid. Author: Mario Gonzalez <[email protected]> Author: Bryan Green <[email protected]> Co-authored-by: Andrew Dunstan <[email protected]> Co-authored-by: Euler Taveira <[email protected]> Reviewed-by: Japin Li <[email protected]> Reviewed-by: Quan Zongliang <[email protected]> Reviewed-by: jian he <[email protected]> Discussion: https://postgr.es/m/[email protected] Discussion: https://postgr.es/m/[email protected] --- doc/src/sgml/func/func-info.sgml | 56 ++++ src/backend/utils/adt/ddlutils.c | 361 +++++++++++++++++++++++++ src/include/catalog/pg_proc.dat | 8 + src/test/regress/expected/role_ddl.out | 143 ++++++++++ src/test/regress/parallel_schedule | 2 + src/test/regress/sql/role_ddl.sql | 96 +++++++ 6 files changed, 666 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..7cb020b6fd3 100644 --- a/doc/src/sgml/func/func-info.sgml +++ b/doc/src/sgml/func/func-info.sgml @@ -3860,4 +3860,60 @@ 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 options are supported: <literal>pretty</literal> (boolean) + for pretty-printed output and <literal>memberships</literal> (boolean, + default true) to include <command>GRANT</command> statements for + role memberships and their options. + </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 4bf7d9c38ae..bcea1ec5981 100644 --- a/src/backend/utils/adt/ddlutils.c +++ b/src/backend/utils/adt/ddlutils.c @@ -18,8 +18,25 @@ */ #include "postgres.h" +#include "access/genam.h" +#include "access/htup_details.h" +#include "access/relation.h" +#include "access/table.h" +#include "catalog/pg_auth_members.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 */ @@ -56,6 +73,8 @@ static void append_ddl_option(StringInfo buf, bool pretty, int indent, pg_attribute_printf(4, 5); static void append_guc_value(StringInfo buf, const char *name, const char *value); +static List *pg_get_role_ddl_internal(Oid roleid, bool pretty, + bool memberships); /* @@ -273,3 +292,345 @@ append_guc_value(StringInfo buf, const char *name, const char *value) pfree(rawval); } + +/* + * 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. If memberships is true, + * GRANT statements for role memberships are appended. + */ +static List * +pg_get_role_ddl_internal(Oid roleid, bool pretty, bool memberships) +{ + 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)); + + /* User must have SELECT privilege on pg_authid. */ + if (pg_class_aclcheck(AuthIdRelationId, GetUserId(), ACL_SELECT) != ACLCHECK_OK) + { + ReleaseSysCache(tuple); + ereport(ERROR, + (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), + errmsg("permission denied for role %s", 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, USE_ISO_DATES, 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 *role_settings; + 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; + + role_settings = DatumGetArrayTypeP(datum); + + deconstruct_array_builtin(role_settings, 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)); + + append_guc_value(&buf, s, p); + + appendStringInfoChar(&buf, ';'); + + statements = lappend(statements, pstrdup(buf.data)); + + pfree(s); + } + + pfree(settings); + pfree(nulls); + pfree(role_settings); + + if (datname != NULL) + pfree(datname); + } + + systable_endscan(scan); + table_close(rel, AccessShareLock); + + /* + * Scan pg_auth_members for role memberships. We look for rows where + * member = roleid, meaning this role has been granted membership in other + * roles. + */ + if (memberships) + { + rel = table_open(AuthMemRelationId, AccessShareLock); + ScanKeyInit(&scankey, + Anum_pg_auth_members_member, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(roleid)); + scan = systable_beginscan(rel, AuthMemMemRoleIndexId, true, + NULL, 1, &scankey); + + while (HeapTupleIsValid(tuple = systable_getnext(scan))) + { + Form_pg_auth_members memform = (Form_pg_auth_members) GETSTRUCT(tuple); + char *granted_role; + char *grantor; + + granted_role = GetUserNameFromId(memform->roleid, false); + grantor = GetUserNameFromId(memform->grantor, false); + + resetStringInfo(&buf); + appendStringInfo(&buf, "GRANT %s TO %s", + quote_identifier(granted_role), + quote_identifier(rolname)); + appendStringInfo(&buf, " WITH ADMIN %s, INHERIT %s, SET %s", + memform->admin_option ? "TRUE" : "FALSE", + memform->inherit_option ? "TRUE" : "FALSE", + memform->set_option ? "TRUE" : "FALSE"); + appendStringInfo(&buf, " GRANTED BY %s;", + quote_identifier(grantor)); + + statements = lappend(statements, pstrdup(buf.data)); + + pfree(granted_role); + pfree(grantor); + } + + 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 + * and optionally GRANT statements for role memberships. + * 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}, + {"memberships", 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, + !opts[1].isset || opts[1].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 3579cec5744..d1985826263 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..575111da55c --- /dev/null +++ b/src/test/regress/expected/role_ddl.out @@ -0,0 +1,143 @@ +-- 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 +-- Role with memberships +CREATE ROLE regress_role_ddl_grantor CREATEROLE; +CREATE ROLE regress_role_ddl_group1; +CREATE ROLE regress_role_ddl_group2; +CREATE ROLE regress_role_ddl_member; +GRANT regress_role_ddl_group1 TO regress_role_ddl_grantor WITH ADMIN TRUE; +GRANT regress_role_ddl_group2 TO regress_role_ddl_grantor WITH ADMIN TRUE; +SET ROLE regress_role_ddl_grantor; +GRANT regress_role_ddl_group1 TO regress_role_ddl_member WITH INHERIT TRUE, SET FALSE; +GRANT regress_role_ddl_group2 TO regress_role_ddl_member WITH ADMIN TRUE; +RESET ROLE; +SELECT * FROM pg_get_role_ddl('regress_role_ddl_member'); + pg_get_role_ddl +----------------------------------------------------------------------------------------------------------------------------------------- + CREATE ROLE regress_role_ddl_member NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS; + GRANT regress_role_ddl_group1 TO regress_role_ddl_member WITH ADMIN FALSE, INHERIT TRUE, SET FALSE GRANTED BY regress_role_ddl_grantor; + GRANT regress_role_ddl_group2 TO regress_role_ddl_member WITH ADMIN TRUE, INHERIT TRUE, SET TRUE GRANTED BY regress_role_ddl_grantor; +(3 rows) + +-- Role with memberships suppressed +SELECT * FROM pg_get_role_ddl('regress_role_ddl_member', 'memberships', 'false'); + pg_get_role_ddl +-------------------------------------------------------------------------------------------------------------------- + CREATE ROLE regress_role_ddl_member NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB NOLOGIN NOREPLICATION NOBYPASSRLS; +(1 row) + +-- Non-existent role (should error) +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) + +-- Permission check: revoke SELECT on pg_authid +CREATE ROLE regress_role_ddl_noaccess; +REVOKE SELECT ON pg_authid FROM PUBLIC; +SET ROLE regress_role_ddl_noaccess; +SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1'); -- should fail +ERROR: permission denied for role regress_role_ddl_test1 +RESET ROLE; +GRANT SELECT ON pg_authid TO PUBLIC; +DROP ROLE regress_role_ddl_noaccess; +-- 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"; +SET ROLE regress_role_ddl_grantor; +REVOKE regress_role_ddl_group1 FROM regress_role_ddl_member; +REVOKE regress_role_ddl_group2 FROM regress_role_ddl_member; +RESET ROLE; +DROP ROLE regress_role_ddl_member; +DROP ROLE regress_role_ddl_group1; +DROP ROLE regress_role_ddl_group2; +DROP ROLE regress_role_ddl_grantor; +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 3a044ffd8bf..84efbf8c104 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..3d0142242ec --- /dev/null +++ b/src/test/regress/sql/role_ddl.sql @@ -0,0 +1,96 @@ +-- 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 + +-- Role with memberships +CREATE ROLE regress_role_ddl_grantor CREATEROLE; +CREATE ROLE regress_role_ddl_group1; +CREATE ROLE regress_role_ddl_group2; +CREATE ROLE regress_role_ddl_member; +GRANT regress_role_ddl_group1 TO regress_role_ddl_grantor WITH ADMIN TRUE; +GRANT regress_role_ddl_group2 TO regress_role_ddl_grantor WITH ADMIN TRUE; +SET ROLE regress_role_ddl_grantor; +GRANT regress_role_ddl_group1 TO regress_role_ddl_member WITH INHERIT TRUE, SET FALSE; +GRANT regress_role_ddl_group2 TO regress_role_ddl_member WITH ADMIN TRUE; +RESET ROLE; +SELECT * FROM pg_get_role_ddl('regress_role_ddl_member'); + +-- Role with memberships suppressed +SELECT * FROM pg_get_role_ddl('regress_role_ddl_member', 'memberships', 'false'); + +-- Non-existent role (should error) +SELECT * FROM pg_get_role_ddl(9999999::oid); + +-- NULL input (should return no rows) +SELECT * FROM pg_get_role_ddl(NULL); + +-- Permission check: revoke SELECT on pg_authid +CREATE ROLE regress_role_ddl_noaccess; +REVOKE SELECT ON pg_authid FROM PUBLIC; +SET ROLE regress_role_ddl_noaccess; +SELECT * FROM pg_get_role_ddl('regress_role_ddl_test1'); -- should fail +RESET ROLE; +GRANT SELECT ON pg_authid TO PUBLIC; +DROP ROLE regress_role_ddl_noaccess; + +-- 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"; +SET ROLE regress_role_ddl_grantor; +REVOKE regress_role_ddl_group1 FROM regress_role_ddl_member; +REVOKE regress_role_ddl_group2 FROM regress_role_ddl_member; +RESET ROLE; +DROP ROLE regress_role_ddl_member; +DROP ROLE regress_role_ddl_group1; +DROP ROLE regress_role_ddl_group2; +DROP ROLE regress_role_ddl_grantor; + +DROP DATABASE regression_role_ddl_test; + +-- Reset timezone to default +RESET timezone; -- 2.43.0
From b3ac20e3c091a3c3f21eccf73cde0421adaaeb36 Mon Sep 17 00:00:00 2001 From: Andrew Dunstan <[email protected]> Date: Thu, 19 Mar 2026 09:55:16 -0400 Subject: [PATCH v4 3/4] Add pg_get_tablespace_ddl() function MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Add a new SQL-callable function that returns the DDL statements needed to recreate a tablespace. It takes a tablespace name or OID and an optional VARIADIC text argument for options that are specified as alternating name/value pairs. The following options are supported: pretty (boolean) for formatted output and owner (boolean) to include OWNER. (It includes two variants because there is no regtablespace pseudotype.) The return is one or multiple rows where the first row is a CREATE TABLESPACE statement and subsequent rows are ALTER TABLESPACE statements to set some tablespace properties. The caller must have SELECT privilege on pg_tablespace. get_reloptions() in ruleutils.c is made non-static so it can be called from the new ddlutils.c file. Author: Nishant Sharma <[email protected]> Author: Manni Wood <[email protected]> Co-authored-by: Andrew Dunstan <[email protected]> Co-authored-by: Euler Taveira <[email protected]> Reviewed-by: Jim Jones <[email protected]> Reviewed-by: Álvaro Herrera <[email protected]> Reviewed-by: Chao Li <[email protected]> Discussion: https://postgr.es/m/CAKWEB6rmnmGKUA87Zmq-s=b3scsnj02c0kobqjnbl2ajfpw...@mail.gmail.com Discussion: https://postgr.es/m/[email protected] --- doc/src/sgml/func/func-info.sgml | 28 +++ src/backend/utils/adt/ddlutils.c | 206 ++++++++++++++++++- src/backend/utils/adt/ruleutils.c | 4 +- src/include/catalog/pg_proc.dat | 16 ++ src/include/utils/ruleutils.h | 1 + src/test/regress/expected/tablespace_ddl.out | 84 ++++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/tablespace_ddl.sql | 58 ++++++ 8 files changed, 395 insertions(+), 4 deletions(-) create mode 100644 src/test/regress/expected/tablespace_ddl.out create mode 100644 src/test/regress/sql/tablespace_ddl.sql diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml index 7cb020b6fd3..e14c209bf14 100644 --- a/doc/src/sgml/func/func-info.sgml +++ b/doc/src/sgml/func/func-info.sgml @@ -3910,6 +3910,34 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres} role memberships and their options. </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_get_tablespace_ddl</primary> + </indexterm> + <function>pg_get_tablespace_ddl</function> + ( <parameter>tablespace</parameter> <type>oid</type> + <optional>, <literal>VARIADIC</literal> <parameter>options</parameter> + <type>text</type> </optional> ) + <returnvalue>setof text</returnvalue> + </para> + <para> + <function>pg_get_tablespace_ddl</function> + ( <parameter>tablespace</parameter> <type>name</type> + <optional>, <literal>VARIADIC</literal> <parameter>options</parameter> + <type>text</type> </optional> ) + <returnvalue>setof text</returnvalue> + </para> + <para> + Reconstructs the <command>CREATE TABLESPACE</command> statement for + the specified tablespace (by OID or name). If the tablespace has + options set, an <command>ALTER TABLESPACE ... SET</command> statement + is also returned. Each statement is returned as a separate row. + The following options are supported: <literal>pretty</literal> (boolean) + for formatted output and <literal>owner</literal> (boolean) to include + <literal>OWNER</literal>. + </para></entry> + </row> </tbody> </tgroup> </table> diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c index bcea1ec5981..d953963a712 100644 --- a/src/backend/utils/adt/ddlutils.c +++ b/src/backend/utils/adt/ddlutils.c @@ -20,11 +20,13 @@ #include "access/genam.h" #include "access/htup_details.h" -#include "access/relation.h" #include "access/table.h" #include "catalog/pg_auth_members.h" #include "catalog/pg_authid.h" #include "catalog/pg_db_role_setting.h" +#include "catalog/pg_tablespace.h" +#include "commands/tablespace.h" +#include "common/relpath.h" #include "funcapi.h" #include "miscadmin.h" #include "utils/acl.h" @@ -35,6 +37,7 @@ #include "utils/guc.h" #include "utils/lsyscache.h" #include "utils/rel.h" +#include "utils/ruleutils.h" #include "utils/syscache.h" #include "utils/timestamp.h" #include "utils/varlena.h" @@ -75,6 +78,8 @@ static void append_guc_value(StringInfo buf, const char *name, const char *value); static List *pg_get_role_ddl_internal(Oid roleid, bool pretty, bool memberships); +static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner); +static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull); /* @@ -634,3 +639,202 @@ pg_get_role_ddl(PG_FUNCTION_ARGS) SRF_RETURN_DONE(funcctx); } } + +/* + * pg_get_tablespace_ddl_internal + * Generate DDL statements to recreate a tablespace. + * + * Returns a List of palloc'd strings. The first element is the + * CREATE TABLESPACE statement; if the tablespace has reloptions, + * a second element with ALTER TABLESPACE SET (...) is appended. + */ +static List * +pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner) +{ + HeapTuple tuple; + Form_pg_tablespace tspForm; + StringInfoData buf; + char *spcname; + char *spcowner; + char *path; + bool isNull; + Datum datum; + List *statements = NIL; + + tuple = SearchSysCache1(TABLESPACEOID, ObjectIdGetDatum(tsid)); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("tablespace with OID %u does not exist", + tsid))); + + tspForm = (Form_pg_tablespace) GETSTRUCT(tuple); + spcname = pstrdup(NameStr(tspForm->spcname)); + + /* User must have SELECT privilege on pg_tablespace. */ + if (pg_class_aclcheck(TableSpaceRelationId, GetUserId(), ACL_SELECT) != ACLCHECK_OK) + { + ReleaseSysCache(tuple); + aclcheck_error(ACLCHECK_NO_PRIV, OBJECT_TABLESPACE, spcname); + } + + /* + * We don't support generating DDL for system tablespaces. The primary + * reason for this is that users shouldn't be recreating them. + */ + if (IsReservedName(spcname)) + ereport(ERROR, + (errcode(ERRCODE_RESERVED_NAME), + errmsg("tablespace name \"%s\" is reserved", spcname), + errdetail("Tablespace names starting with \"pg_\" are reserved for system tablespaces."))); + + initStringInfo(&buf); + + /* Start building the CREATE TABLESPACE statement */ + appendStringInfo(&buf, "CREATE TABLESPACE %s", quote_identifier(spcname)); + + /* Add OWNER clause */ + if (!no_owner) + { + spcowner = GetUserNameFromId(tspForm->spcowner, false); + append_ddl_option(&buf, pretty, 4, "OWNER %s", + quote_identifier(spcowner)); + pfree(spcowner); + } + + /* Find tablespace directory path */ + path = get_tablespace_location(tsid); + + /* Add directory LOCATION (path), if it exists */ + if (path[0] != '\0') + { + /* + * Special case: if the tablespace was created with GUC + * "allow_in_place_tablespaces = true" and "LOCATION ''", path will + * begin with "pg_tblspc/". In that case, show "LOCATION ''" as the + * user originally specified. + */ + if (strncmp(PG_TBLSPC_DIR_SLASH, path, strlen(PG_TBLSPC_DIR_SLASH)) == 0) + append_ddl_option(&buf, pretty, 4, "LOCATION ''"); + else + append_ddl_option(&buf, pretty, 4, "LOCATION %s", + quote_literal_cstr(path)); + } + pfree(path); + + appendStringInfoChar(&buf, ';'); + statements = lappend(statements, pstrdup(buf.data)); + + /* Check for tablespace options */ + datum = SysCacheGetAttr(TABLESPACEOID, tuple, + Anum_pg_tablespace_spcoptions, &isNull); + if (!isNull) + { + resetStringInfo(&buf); + appendStringInfo(&buf, "ALTER TABLESPACE %s SET (", + quote_identifier(spcname)); + get_reloptions(&buf, datum); + appendStringInfoString(&buf, ");"); + statements = lappend(statements, pstrdup(buf.data)); + } + + ReleaseSysCache(tuple); + pfree(spcname); + pfree(buf.data); + + return statements; +} + +/* + * pg_get_tablespace_ddl_srf - common SRF logic for tablespace DDL + */ +static Datum +pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull) +{ + FuncCallContext *funcctx; + List *statements; + + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + DdlOption opts[] = { + {"pretty", DDL_OPT_BOOL}, + {"owner", DDL_OPT_BOOL}, + }; + + funcctx = SRF_FIRSTCALL_INIT(); + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + if (isnull) + { + MemoryContextSwitchTo(oldcontext); + SRF_RETURN_DONE(funcctx); + } + + parse_ddl_options(fcinfo, 1, opts, lengthof(opts)); + + statements = pg_get_tablespace_ddl_internal(tsid, + opts[0].isset && opts[0].boolval, + opts[1].isset && !opts[1].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 = (char *) list_nth(statements, funcctx->call_cntr); + + SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt)); + } + else + { + list_free_deep(statements); + SRF_RETURN_DONE(funcctx); + } +} + +/* + * pg_get_tablespace_ddl_oid + * Return DDL to recreate a tablespace, taking OID. + */ +Datum +pg_get_tablespace_ddl_oid(PG_FUNCTION_ARGS) +{ + Oid tsid = InvalidOid; + bool isnull; + + isnull = PG_ARGISNULL(0); + if (!isnull) + tsid = PG_GETARG_OID(0); + + return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull); +} + +/* + * pg_get_tablespace_ddl_name + * Return DDL to recreate a tablespace, taking name. + */ +Datum +pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS) +{ + Oid tsid = InvalidOid; + Name tspname; + bool isnull; + + isnull = PG_ARGISNULL(0); + + if (!isnull) + { + tspname = PG_GETARG_NAME(0); + tsid = get_tablespace_oid(NameStr(*tspname), false); + } + + return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull); +} diff --git a/src/backend/utils/adt/ruleutils.c b/src/backend/utils/adt/ruleutils.c index aec5556b008..35083fcc733 100644 --- a/src/backend/utils/adt/ruleutils.c +++ b/src/backend/utils/adt/ruleutils.c @@ -547,7 +547,7 @@ static void add_cast_to(StringInfo buf, Oid typid); static char *generate_qualified_type_name(Oid typid); static text *string_to_text(char *str); static char *flatten_reloptions(Oid relid); -static void get_reloptions(StringInfo buf, Datum reloptions); +void get_reloptions(StringInfo buf, Datum reloptions); static void get_json_path_spec(Node *path_spec, deparse_context *context, bool showimplicit); static void get_json_table_columns(TableFunc *tf, JsonTablePathScan *scan, @@ -14240,7 +14240,7 @@ string_to_text(char *str) /* * Generate a C string representing a relation options from text[] datum. */ -static void +void get_reloptions(StringInfo buf, Datum reloptions) { Datum *options; diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat index d1985826263..984cf620bc8 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -8611,6 +8611,22 @@ proallargtypes => '{regrole,text}', pronargdefaults => '1', proargdefaults => '{NULL}', prosrc => 'pg_get_role_ddl' }, +{ oid => '8758', descr => 'get DDL to recreate a tablespace', + proname => 'pg_get_tablespace_ddl', provariadic => 'text', proisstrict => 'f', + provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text', + proargtypes => 'oid text', + proargmodes => '{i,v}', + proallargtypes => '{oid,text}', + pronargdefaults => '1', proargdefaults => '{NULL}', + prosrc => 'pg_get_tablespace_ddl_oid' }, +{ oid => '8759', descr => 'get DDL to recreate a tablespace', + proname => 'pg_get_tablespace_ddl', provariadic => 'text', proisstrict => 'f', + provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text', + proargtypes => 'name text', + proargmodes => '{i,v}', + proallargtypes => '{name,text}', + pronargdefaults => '1', proargdefaults => '{NULL}', + prosrc => 'pg_get_tablespace_ddl_name' }, { oid => '2509', descr => 'deparse an encoded expression with pretty-print option', proname => 'pg_get_expr', provolatile => 's', prorettype => 'text', diff --git a/src/include/utils/ruleutils.h b/src/include/utils/ruleutils.h index 059e6474f3b..25c05e2f649 100644 --- a/src/include/utils/ruleutils.h +++ b/src/include/utils/ruleutils.h @@ -51,6 +51,7 @@ extern char *get_window_frame_options_for_explain(int frameOptions, extern char *generate_collation_name(Oid collid); extern char *generate_opclass_name(Oid opclass); extern char *get_range_partbound_string(List *bound_datums); +extern void get_reloptions(StringInfo buf, Datum reloptions); extern char *pg_get_statisticsobjdef_string(Oid statextid); diff --git a/src/test/regress/expected/tablespace_ddl.out b/src/test/regress/expected/tablespace_ddl.out new file mode 100644 index 00000000000..e52043273a9 --- /dev/null +++ b/src/test/regress/expected/tablespace_ddl.out @@ -0,0 +1,84 @@ +-- +-- Tests for pg_get_tablespace_ddl() +-- +SET allow_in_place_tablespaces = true; +CREATE ROLE regress_tblspc_ddl_user; +-- error: non-existent tablespace by name +SELECT * FROM pg_get_tablespace_ddl('regress_nonexistent_tblsp'); +ERROR: tablespace "regress_nonexistent_tblsp" does not exist +-- error: non-existent tablespace by OID +SELECT * FROM pg_get_tablespace_ddl(0::oid); +ERROR: tablespace with OID 0 does not exist +-- NULL input returns no rows (name variant) +SELECT * FROM pg_get_tablespace_ddl(NULL::name); + pg_get_tablespace_ddl +----------------------- +(0 rows) + +-- NULL input returns no rows (OID variant) +SELECT * FROM pg_get_tablespace_ddl(NULL::oid); + pg_get_tablespace_ddl +----------------------- +(0 rows) + +-- tablespace name requiring quoting +CREATE TABLESPACE "regress_ tblsp" OWNER regress_tblspc_ddl_user LOCATION ''; +SELECT * FROM pg_get_tablespace_ddl('regress_ tblsp'); + pg_get_tablespace_ddl +------------------------------------------------------------------------------- + CREATE TABLESPACE "regress_ tblsp" OWNER regress_tblspc_ddl_user LOCATION ''; +(1 row) + +DROP TABLESPACE "regress_ tblsp"; +-- tablespace with multiple options +CREATE TABLESPACE regress_allopt_tblsp OWNER regress_tblspc_ddl_user LOCATION '' + WITH (seq_page_cost = '1.5', random_page_cost = '1.1234567890', + effective_io_concurrency = '17', maintenance_io_concurrency = '18'); +SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp'); + pg_get_tablespace_ddl +------------------------------------------------------------------------------------------------------------------------------------------------------------------- + CREATE TABLESPACE regress_allopt_tblsp OWNER regress_tblspc_ddl_user LOCATION ''; + ALTER TABLESPACE regress_allopt_tblsp SET (seq_page_cost='1.5', random_page_cost='1.1234567890', effective_io_concurrency='17', maintenance_io_concurrency='18'); +(2 rows) + +-- pretty-printed output +\pset format unaligned +SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'pretty', 'true'); +pg_get_tablespace_ddl +CREATE TABLESPACE regress_allopt_tblsp + OWNER regress_tblspc_ddl_user + LOCATION ''; +ALTER TABLESPACE regress_allopt_tblsp SET (seq_page_cost='1.5', random_page_cost='1.1234567890', effective_io_concurrency='17', maintenance_io_concurrency='18'); +(2 rows) +\pset format aligned +-- tablespace with owner suppressed +SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'owner', 'false'); + pg_get_tablespace_ddl +------------------------------------------------------------------------------------------------------------------------------------------------------------------- + CREATE TABLESPACE regress_allopt_tblsp LOCATION ''; + ALTER TABLESPACE regress_allopt_tblsp SET (seq_page_cost='1.5', random_page_cost='1.1234567890', effective_io_concurrency='17', maintenance_io_concurrency='18'); +(2 rows) + +DROP TABLESPACE regress_allopt_tblsp; +-- test by OID +CREATE TABLESPACE regress_oid_tblsp OWNER regress_tblspc_ddl_user LOCATION ''; +SELECT oid AS tsid FROM pg_tablespace WHERE spcname = 'regress_oid_tblsp' \gset +SELECT * FROM pg_get_tablespace_ddl(:tsid); + pg_get_tablespace_ddl +-------------------------------------------------------------------------------- + CREATE TABLESPACE regress_oid_tblsp OWNER regress_tblspc_ddl_user LOCATION ''; +(1 row) + +DROP TABLESPACE regress_oid_tblsp; +-- Permission check: revoke SELECT on pg_tablespace +CREATE TABLESPACE regress_acl_tblsp OWNER regress_tblspc_ddl_user LOCATION ''; +CREATE ROLE regress_tblspc_ddl_noaccess; +REVOKE SELECT ON pg_tablespace FROM PUBLIC; +SET ROLE regress_tblspc_ddl_noaccess; +SELECT * FROM pg_get_tablespace_ddl('regress_acl_tblsp'); -- should fail +ERROR: permission denied for tablespace regress_acl_tblsp +RESET ROLE; +GRANT SELECT ON pg_tablespace TO PUBLIC; +DROP TABLESPACE regress_acl_tblsp; +DROP ROLE regress_tblspc_ddl_noaccess; +DROP ROLE regress_tblspc_ddl_user; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index 84efbf8c104..fabaebf2c78 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -130,7 +130,7 @@ 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 +test: role_ddl tablespace_ddl # event_trigger_login cannot run concurrently with any other tests because # on-login event handling could catch connection of a concurrent test. diff --git a/src/test/regress/sql/tablespace_ddl.sql b/src/test/regress/sql/tablespace_ddl.sql new file mode 100644 index 00000000000..ee3cc6e2e1e --- /dev/null +++ b/src/test/regress/sql/tablespace_ddl.sql @@ -0,0 +1,58 @@ +-- +-- Tests for pg_get_tablespace_ddl() +-- + +SET allow_in_place_tablespaces = true; +CREATE ROLE regress_tblspc_ddl_user; + +-- error: non-existent tablespace by name +SELECT * FROM pg_get_tablespace_ddl('regress_nonexistent_tblsp'); + +-- error: non-existent tablespace by OID +SELECT * FROM pg_get_tablespace_ddl(0::oid); + +-- NULL input returns no rows (name variant) +SELECT * FROM pg_get_tablespace_ddl(NULL::name); + +-- NULL input returns no rows (OID variant) +SELECT * FROM pg_get_tablespace_ddl(NULL::oid); + +-- tablespace name requiring quoting +CREATE TABLESPACE "regress_ tblsp" OWNER regress_tblspc_ddl_user LOCATION ''; +SELECT * FROM pg_get_tablespace_ddl('regress_ tblsp'); +DROP TABLESPACE "regress_ tblsp"; + +-- tablespace with multiple options +CREATE TABLESPACE regress_allopt_tblsp OWNER regress_tblspc_ddl_user LOCATION '' + WITH (seq_page_cost = '1.5', random_page_cost = '1.1234567890', + effective_io_concurrency = '17', maintenance_io_concurrency = '18'); +SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp'); + +-- pretty-printed output +\pset format unaligned +SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'pretty', 'true'); +\pset format aligned + +-- tablespace with owner suppressed +SELECT * FROM pg_get_tablespace_ddl('regress_allopt_tblsp', 'owner', 'false'); + +DROP TABLESPACE regress_allopt_tblsp; + +-- test by OID +CREATE TABLESPACE regress_oid_tblsp OWNER regress_tblspc_ddl_user LOCATION ''; +SELECT oid AS tsid FROM pg_tablespace WHERE spcname = 'regress_oid_tblsp' \gset +SELECT * FROM pg_get_tablespace_ddl(:tsid); +DROP TABLESPACE regress_oid_tblsp; + +-- Permission check: revoke SELECT on pg_tablespace +CREATE TABLESPACE regress_acl_tblsp OWNER regress_tblspc_ddl_user LOCATION ''; +CREATE ROLE regress_tblspc_ddl_noaccess; +REVOKE SELECT ON pg_tablespace FROM PUBLIC; +SET ROLE regress_tblspc_ddl_noaccess; +SELECT * FROM pg_get_tablespace_ddl('regress_acl_tblsp'); -- should fail +RESET ROLE; +GRANT SELECT ON pg_tablespace TO PUBLIC; +DROP TABLESPACE regress_acl_tblsp; +DROP ROLE regress_tblspc_ddl_noaccess; + +DROP ROLE regress_tblspc_ddl_user; -- 2.43.0
From 3a0f3c76f1c35ffa56295bf1ea9a2d2c8efce928 Mon Sep 17 00:00:00 2001 From: Andrew Dunstan <[email protected]> Date: Thu, 19 Mar 2026 09:57:35 -0400 Subject: [PATCH v4 4/4] Add pg_get_database_ddl() function MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit Add a new SQL-callable function that returns the DDL statements needed to recreate a database. It takes a regdatabase argument and an optional VARIADIC text argument for options that are specified as alternating name/value pairs. The following options are supported: pretty (boolean) for formatted output, owner (boolean) to include OWNER and tablespace (boolean) to include TABLESPACE. The return is one or multiple rows where the first row is a CREATE DATABASE statement and subsequent rows are ALTER DATABASE statements to set some database properties. The caller must have CONNECT privilege on the target database. Author: Akshay Joshi <[email protected]> Co-authored-by: Andrew Dunstan <[email protected]> Co-authored-by: Euler Taveira <[email protected]> Reviewed-by: Japin Li <[email protected]> Reviewed-by: Chao Li <[email protected]> Reviewed-by: Álvaro Herrera <[email protected]> Reviewed-by: Quan Zongliang <[email protected]> Discussion: https://postgr.es/m/canxoldc6fhbyjvcgonzys+jf0nuo3lq_83-rttbujgs9id_...@mail.gmail.com Discussion: https://postgr.es/m/[email protected] --- doc/src/sgml/func/func-info.sgml | 23 ++ src/backend/utils/adt/ddlutils.c | 330 +++++++++++++++++++++ src/include/catalog/pg_proc.dat | 8 + src/test/regress/expected/database_ddl.out | 88 ++++++ src/test/regress/parallel_schedule | 2 +- src/test/regress/sql/database_ddl.sql | 66 +++++ 6 files changed, 516 insertions(+), 1 deletion(-) create mode 100644 src/test/regress/expected/database_ddl.out create mode 100644 src/test/regress/sql/database_ddl.sql diff --git a/doc/src/sgml/func/func-info.sgml b/doc/src/sgml/func/func-info.sgml index e14c209bf14..80cf11083d6 100644 --- a/doc/src/sgml/func/func-info.sgml +++ b/doc/src/sgml/func/func-info.sgml @@ -3938,6 +3938,29 @@ acl | {postgres=arwdDxtm/postgres,foo=r/postgres} <literal>OWNER</literal>. </para></entry> </row> + <row> + <entry role="func_table_entry"><para role="func_signature"> + <indexterm> + <primary>pg_get_database_ddl</primary> + </indexterm> + <function>pg_get_database_ddl</function> + ( <parameter>database</parameter> <type>regdatabase</type> + <optional>, <literal>VARIADIC</literal> <parameter>options</parameter> + <type>text</type> </optional> ) + <returnvalue>setof text</returnvalue> + </para> + <para> + Reconstructs the <command>CREATE DATABASE</command> statement for the + specified database, followed by <command>ALTER DATABASE</command> + statements for connection limit, template status, and configuration + settings. Each statement is returned as a separate row. + The following options are supported: + <literal>pretty</literal> (boolean) for formatted output, + <literal>owner</literal> (boolean) to include <literal>OWNER</literal>, + and <literal>tablespace</literal> (boolean) to include + <literal>TABLESPACE</literal>. + </para></entry> + </row> </tbody> </tgroup> </table> diff --git a/src/backend/utils/adt/ddlutils.c b/src/backend/utils/adt/ddlutils.c index d953963a712..5ff15bc2cf1 100644 --- a/src/backend/utils/adt/ddlutils.c +++ b/src/backend/utils/adt/ddlutils.c @@ -23,11 +23,14 @@ #include "access/table.h" #include "catalog/pg_auth_members.h" #include "catalog/pg_authid.h" +#include "catalog/pg_collation.h" +#include "catalog/pg_database.h" #include "catalog/pg_db_role_setting.h" #include "catalog/pg_tablespace.h" #include "commands/tablespace.h" #include "common/relpath.h" #include "funcapi.h" +#include "mb/pg_wchar.h" #include "miscadmin.h" #include "utils/acl.h" #include "utils/array.h" @@ -36,6 +39,7 @@ #include "utils/fmgroids.h" #include "utils/guc.h" #include "utils/lsyscache.h" +#include "utils/pg_locale.h" #include "utils/rel.h" #include "utils/ruleutils.h" #include "utils/syscache.h" @@ -80,6 +84,8 @@ static List *pg_get_role_ddl_internal(Oid roleid, bool pretty, bool memberships); static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner); static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull); +static List *pg_get_database_ddl_internal(Oid dbid, bool pretty, + bool no_owner, bool no_tablespace); /* @@ -838,3 +844,327 @@ pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS) return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull); } + +/* + * pg_get_database_ddl_internal + * Generate DDL statements to recreate a database. + * + * Returns a List of palloc'd strings. The first element is the + * CREATE DATABASE statement; subsequent elements are ALTER DATABASE + * statements for properties and configuration settings. + */ +static List * +pg_get_database_ddl_internal(Oid dbid, bool pretty, + bool no_owner, bool no_tablespace) +{ + HeapTuple tuple; + Form_pg_database dbform; + StringInfoData buf; + bool isnull; + Datum datum; + const char *encoding; + char *dbname; + char *collate; + char *ctype; + Relation rel; + ScanKeyData scankey[2]; + SysScanDesc scan; + List *statements = NIL; + AclResult aclresult; + + tuple = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbid)); + if (!HeapTupleIsValid(tuple)) + ereport(ERROR, + (errcode(ERRCODE_UNDEFINED_OBJECT), + errmsg("database with OID %u does not exist", dbid))); + + /* User must have connect privilege for target database. */ + aclresult = object_aclcheck(DatabaseRelationId, dbid, GetUserId(), ACL_CONNECT); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, OBJECT_DATABASE, + get_database_name(dbid)); + + dbform = (Form_pg_database) GETSTRUCT(tuple); + dbname = pstrdup(NameStr(dbform->datname)); + + /* + * We don't support generating DDL for system databases. The primary + * reason for this is that users shouldn't be recreating them. + */ + if (strcmp(dbname, "template0") == 0 || strcmp(dbname, "template1") == 0) + ereport(ERROR, + (errcode(ERRCODE_RESERVED_NAME), + errmsg("database \"%s\" is a system database", dbname), + errdetail("DDL generation is not supported for template0 and template1."))); + + initStringInfo(&buf); + + /* --- Build CREATE DATABASE statement --- */ + appendStringInfo(&buf, "CREATE DATABASE %s", quote_identifier(dbname)); + + /* + * Always use template0: the target database already contains the catalog + * data from whatever template was used originally, so we must start from + * the pristine template to avoid duplication. + */ + append_ddl_option(&buf, pretty, 4, "WITH TEMPLATE = template0"); + + /* ENCODING */ + encoding = pg_encoding_to_char(dbform->encoding); + if (strlen(encoding) > 0) + append_ddl_option(&buf, pretty, 4, "ENCODING = %s", + quote_literal_cstr(encoding)); + + /* LOCALE_PROVIDER */ + if (dbform->datlocprovider == COLLPROVIDER_BUILTIN || + dbform->datlocprovider == COLLPROVIDER_ICU || + dbform->datlocprovider == COLLPROVIDER_LIBC) + append_ddl_option(&buf, pretty, 4, "LOCALE_PROVIDER = %s", + collprovider_name(dbform->datlocprovider)); + else + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("unrecognized locale provider: %c", + dbform->datlocprovider))); + + /* LOCALE, LC_COLLATE, LC_CTYPE */ + datum = SysCacheGetAttr(DATABASEOID, tuple, + Anum_pg_database_datcollate, &isnull); + collate = isnull ? NULL : TextDatumGetCString(datum); + datum = SysCacheGetAttr(DATABASEOID, tuple, + Anum_pg_database_datctype, &isnull); + ctype = isnull ? NULL : TextDatumGetCString(datum); + if (collate != NULL && ctype != NULL && strcmp(collate, ctype) == 0) + { + append_ddl_option(&buf, pretty, 4, "LOCALE = %s", + quote_literal_cstr(collate)); + } + else + { + if (collate != NULL) + append_ddl_option(&buf, pretty, 4, "LC_COLLATE = %s", + quote_literal_cstr(collate)); + if (ctype != NULL) + append_ddl_option(&buf, pretty, 4, "LC_CTYPE = %s", + quote_literal_cstr(ctype)); + } + + /* LOCALE (provider-specific) */ + datum = SysCacheGetAttr(DATABASEOID, tuple, + Anum_pg_database_datlocale, &isnull); + if (!isnull) + { + const char *locale = TextDatumGetCString(datum); + + if (dbform->datlocprovider == COLLPROVIDER_BUILTIN) + append_ddl_option(&buf, pretty, 4, "BUILTIN_LOCALE = %s", + quote_literal_cstr(locale)); + else if (dbform->datlocprovider == COLLPROVIDER_ICU) + append_ddl_option(&buf, pretty, 4, "ICU_LOCALE = %s", + quote_literal_cstr(locale)); + } + + /* ICU_RULES */ + datum = SysCacheGetAttr(DATABASEOID, tuple, + Anum_pg_database_daticurules, &isnull); + if (!isnull && dbform->datlocprovider == COLLPROVIDER_ICU) + append_ddl_option(&buf, pretty, 4, "ICU_RULES = %s", + quote_literal_cstr(TextDatumGetCString(datum))); + + /* TABLESPACE */ + if (!no_tablespace && OidIsValid(dbform->dattablespace)) + { + char *spcname = get_tablespace_name(dbform->dattablespace); + + if (pg_strcasecmp(spcname, "pg_default") != 0) + append_ddl_option(&buf, pretty, 4, "TABLESPACE = %s", + quote_identifier(spcname)); + } + + appendStringInfoChar(&buf, ';'); + statements = lappend(statements, pstrdup(buf.data)); + + /* OWNER */ + if (!no_owner && OidIsValid(dbform->datdba)) + { + char *owner = GetUserNameFromId(dbform->datdba, false); + + resetStringInfo(&buf); + appendStringInfo(&buf, "ALTER DATABASE %s OWNER TO %s;", + quote_identifier(dbname), quote_identifier(owner)); + pfree(owner); + statements = lappend(statements, pstrdup(buf.data)); + } + + /* CONNECTION LIMIT */ + if (dbform->datconnlimit != -1) + { + resetStringInfo(&buf); + appendStringInfo(&buf, "ALTER DATABASE %s CONNECTION LIMIT = %d;", + quote_identifier(dbname), dbform->datconnlimit); + statements = lappend(statements, pstrdup(buf.data)); + } + + /* IS_TEMPLATE */ + if (dbform->datistemplate) + { + resetStringInfo(&buf); + appendStringInfo(&buf, "ALTER DATABASE %s IS_TEMPLATE = true;", + quote_identifier(dbname)); + statements = lappend(statements, pstrdup(buf.data)); + } + + /* ALLOW_CONNECTIONS */ + if (!dbform->datallowconn) + { + resetStringInfo(&buf); + appendStringInfo(&buf, "ALTER DATABASE %s ALLOW_CONNECTIONS = false;", + quote_identifier(dbname)); + statements = lappend(statements, pstrdup(buf.data)); + } + + ReleaseSysCache(tuple); + + /* + * Now scan pg_db_role_setting for ALTER DATABASE SET configurations. + * + * It is only database-wide (setrole = 0). It generates one ALTER + * statement per setting. + */ + rel = table_open(DbRoleSettingRelationId, AccessShareLock); + ScanKeyInit(&scankey[0], + Anum_pg_db_role_setting_setdatabase, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(dbid)); + ScanKeyInit(&scankey[1], + Anum_pg_db_role_setting_setrole, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(InvalidOid)); + + scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true, + NULL, 2, scankey); + + while (HeapTupleIsValid(tuple = systable_getnext(scan))) + { + ArrayType *dbconfig; + Datum *settings; + bool *nulls; + int nsettings; + + /* + * 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; + + dbconfig = DatumGetArrayTypeP(datum); + + deconstruct_array_builtin(dbconfig, 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'; + + resetStringInfo(&buf); + appendStringInfo(&buf, "ALTER DATABASE %s SET %s TO ", + quote_identifier(dbname), + quote_identifier(s)); + + append_guc_value(&buf, s, p); + + appendStringInfoChar(&buf, ';'); + + statements = lappend(statements, pstrdup(buf.data)); + + pfree(s); + } + + pfree(settings); + pfree(nulls); + pfree(dbconfig); + } + + systable_endscan(scan); + table_close(rel, AccessShareLock); + + pfree(buf.data); + pfree(dbname); + + return statements; +} + +/* + * pg_get_database_ddl + * Return DDL to recreate a database as a set of text rows. + */ +Datum +pg_get_database_ddl(PG_FUNCTION_ARGS) +{ + FuncCallContext *funcctx; + List *statements; + + if (SRF_IS_FIRSTCALL()) + { + MemoryContext oldcontext; + Oid dbid; + DdlOption opts[] = { + {"pretty", DDL_OPT_BOOL}, + {"owner", DDL_OPT_BOOL}, + {"tablespace", DDL_OPT_BOOL}, + }; + + funcctx = SRF_FIRSTCALL_INIT(); + oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx); + + if (PG_ARGISNULL(0)) + { + MemoryContextSwitchTo(oldcontext); + SRF_RETURN_DONE(funcctx); + } + + dbid = PG_GETARG_OID(0); + parse_ddl_options(fcinfo, 1, opts, lengthof(opts)); + + statements = pg_get_database_ddl_internal(dbid, + opts[0].isset && opts[0].boolval, + opts[1].isset && !opts[1].boolval, + opts[2].isset && !opts[2].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 984cf620bc8..787deb82a3a 100644 --- a/src/include/catalog/pg_proc.dat +++ b/src/include/catalog/pg_proc.dat @@ -8627,6 +8627,14 @@ proallargtypes => '{name,text}', pronargdefaults => '1', proargdefaults => '{NULL}', prosrc => 'pg_get_tablespace_ddl_name' }, +{ oid => '8762', descr => 'get DDL to recreate a database', + proname => 'pg_get_database_ddl', provariadic => 'text', proisstrict => 'f', + provolatile => 's', proretset => 't', prorows => '10', prorettype => 'text', + proargtypes => 'regdatabase text', + proargmodes => '{i,v}', + proallargtypes => '{regdatabase,text}', + pronargdefaults => '1', proargdefaults => '{NULL}', + prosrc => 'pg_get_database_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/database_ddl.out b/src/test/regress/expected/database_ddl.out new file mode 100644 index 00000000000..5081c1a2b53 --- /dev/null +++ b/src/test/regress/expected/database_ddl.out @@ -0,0 +1,88 @@ +-- +-- Tests for pg_get_database_ddl() +-- +-- To produce stable regression test output, strip locale/collation details +-- from the DDL output. Uses a plain SQL function to avoid a PL/pgSQL +-- dependency. +CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT) +RETURNS TEXT LANGUAGE sql AS $$ +SELECT regexp_replace( + regexp_replace( + regexp_replace( + regexp_replace( + regexp_replace( + ddl_input, + '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)', '', 'gi'), + '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1', '', 'gi'), + '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1', '', 'gi'), + '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1', '', 'gi'), + '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1', '', 'gi') +$$; +CREATE ROLE regress_datdba; +CREATE DATABASE regress_database_ddl + ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0 + OWNER regress_datdba; +ALTER DATABASE regress_database_ddl CONNECTION_LIMIT 123; +ALTER DATABASE regress_database_ddl SET random_page_cost = 2.0; +ALTER ROLE regress_datdba IN DATABASE regress_database_ddl SET random_page_cost = 1.1; +-- Database doesn't exist +SELECT * FROM pg_get_database_ddl('regression_database'); +ERROR: database "regression_database" does not exist +LINE 1: SELECT * FROM pg_get_database_ddl('regression_database'); + ^ +-- NULL value +SELECT * FROM pg_get_database_ddl(NULL); + pg_get_database_ddl +--------------------- +(0 rows) + +-- Invalid option value (should error) +SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'invalid'); +ERROR: invalid value for boolean option "owner": invalid +-- Duplicate option (should error) +SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false', 'owner', 'true'); +ERROR: option "owner" is specified more than once +-- Without options +SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl'); + ddl_filter +----------------------------------------------------------------------------------- + CREATE DATABASE regress_database_ddl WITH TEMPLATE = template0 ENCODING = 'UTF8'; + ALTER DATABASE regress_database_ddl OWNER TO regress_datdba; + ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123; + ALTER DATABASE regress_database_ddl SET random_page_cost TO '2.0'; +(4 rows) + +-- With owner +SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'true'); + ddl_filter +----------------------------------------------------------------------------------- + CREATE DATABASE regress_database_ddl WITH TEMPLATE = template0 ENCODING = 'UTF8'; + ALTER DATABASE regress_database_ddl OWNER TO regress_datdba; + ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123; + ALTER DATABASE regress_database_ddl SET random_page_cost TO '2.0'; +(4 rows) + +-- Pretty-printed output +\pset format unaligned +SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'pretty', 'true', 'tablespace', 'false'); +ddl_filter +CREATE DATABASE regress_database_ddl + WITH TEMPLATE = template0 + ENCODING = 'UTF8'; +ALTER DATABASE regress_database_ddl OWNER TO regress_datdba; +ALTER DATABASE regress_database_ddl CONNECTION LIMIT = 123; +ALTER DATABASE regress_database_ddl SET random_page_cost TO '2.0'; +(4 rows) +\pset format aligned +-- Permission check: revoke CONNECT on database +CREATE ROLE regress_db_ddl_noaccess; +REVOKE CONNECT ON DATABASE regress_database_ddl FROM PUBLIC; +SET ROLE regress_db_ddl_noaccess; +SELECT * FROM pg_get_database_ddl('regress_database_ddl'); -- should fail +ERROR: permission denied for database regress_database_ddl +RESET ROLE; +GRANT CONNECT ON DATABASE regress_database_ddl TO PUBLIC; +DROP ROLE regress_db_ddl_noaccess; +DROP DATABASE regress_database_ddl; +DROP FUNCTION ddl_filter(text); +DROP ROLE regress_datdba; diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule index fabaebf2c78..cc365393bb7 100644 --- a/src/test/regress/parallel_schedule +++ b/src/test/regress/parallel_schedule @@ -130,7 +130,7 @@ 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 tablespace_ddl +test: role_ddl tablespace_ddl database_ddl # event_trigger_login cannot run concurrently with any other tests because # on-login event handling could catch connection of a concurrent test. diff --git a/src/test/regress/sql/database_ddl.sql b/src/test/regress/sql/database_ddl.sql new file mode 100644 index 00000000000..093ccc0029e --- /dev/null +++ b/src/test/regress/sql/database_ddl.sql @@ -0,0 +1,66 @@ +-- +-- Tests for pg_get_database_ddl() +-- + +-- To produce stable regression test output, strip locale/collation details +-- from the DDL output. Uses a plain SQL function to avoid a PL/pgSQL +-- dependency. + +CREATE OR REPLACE FUNCTION ddl_filter(ddl_input TEXT) +RETURNS TEXT LANGUAGE sql AS $$ +SELECT regexp_replace( + regexp_replace( + regexp_replace( + regexp_replace( + regexp_replace( + ddl_input, + '\s*\mLOCALE_PROVIDER\M\s*=\s*([''"]?[^''"\s]+[''"]?)', '', 'gi'), + '\s*LC_COLLATE\s*=\s*([''"])[^''"]*\1', '', 'gi'), + '\s*LC_CTYPE\s*=\s*([''"])[^''"]*\1', '', 'gi'), + '\s*\S*LOCALE\S*\s*=?\s*([''"])[^''"]*\1', '', 'gi'), + '\s*\S*COLLATION\S*\s*=?\s*([''"])[^''"]*\1', '', 'gi') +$$; + +CREATE ROLE regress_datdba; +CREATE DATABASE regress_database_ddl + ENCODING utf8 LC_COLLATE "C" LC_CTYPE "C" TEMPLATE template0 + OWNER regress_datdba; +ALTER DATABASE regress_database_ddl CONNECTION_LIMIT 123; +ALTER DATABASE regress_database_ddl SET random_page_cost = 2.0; +ALTER ROLE regress_datdba IN DATABASE regress_database_ddl SET random_page_cost = 1.1; + +-- Database doesn't exist +SELECT * FROM pg_get_database_ddl('regression_database'); + +-- NULL value +SELECT * FROM pg_get_database_ddl(NULL); + +-- Invalid option value (should error) +SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'invalid'); + +-- Duplicate option (should error) +SELECT * FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'false', 'owner', 'true'); + +-- Without options +SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl'); + +-- With owner +SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'owner', 'true'); + +-- Pretty-printed output +\pset format unaligned +SELECT ddl_filter(pg_get_database_ddl) FROM pg_get_database_ddl('regress_database_ddl', 'pretty', 'true', 'tablespace', 'false'); +\pset format aligned + +-- Permission check: revoke CONNECT on database +CREATE ROLE regress_db_ddl_noaccess; +REVOKE CONNECT ON DATABASE regress_database_ddl FROM PUBLIC; +SET ROLE regress_db_ddl_noaccess; +SELECT * FROM pg_get_database_ddl('regress_database_ddl'); -- should fail +RESET ROLE; +GRANT CONNECT ON DATABASE regress_database_ddl TO PUBLIC; +DROP ROLE regress_db_ddl_noaccess; + +DROP DATABASE regress_database_ddl; +DROP FUNCTION ddl_filter(text); +DROP ROLE regress_datdba; -- 2.43.0
