Line data Source code
1 : /*-------------------------------------------------------------------------
2 : *
3 : * ddlutils.c
4 : * Utility functions for generating DDL statements
5 : *
6 : * This file contains the pg_get_*_ddl family of functions that generate
7 : * DDL statements to recreate database objects such as roles, tablespaces,
8 : * and databases, along with common infrastructure for option parsing and
9 : * pretty-printing.
10 : *
11 : * Portions Copyright (c) 1996-2026, PostgreSQL Global Development Group
12 : * Portions Copyright (c) 1994, Regents of the University of California
13 : *
14 : * IDENTIFICATION
15 : * src/backend/utils/adt/ddlutils.c
16 : *
17 : *-------------------------------------------------------------------------
18 : */
19 : #include "postgres.h"
20 :
21 : #include "access/genam.h"
22 : #include "access/htup_details.h"
23 : #include "access/table.h"
24 : #include "catalog/pg_auth_members.h"
25 : #include "catalog/pg_authid.h"
26 : #include "catalog/pg_collation.h"
27 : #include "catalog/pg_database.h"
28 : #include "catalog/pg_db_role_setting.h"
29 : #include "catalog/pg_tablespace.h"
30 : #include "commands/tablespace.h"
31 : #include "common/relpath.h"
32 : #include "funcapi.h"
33 : #include "mb/pg_wchar.h"
34 : #include "miscadmin.h"
35 : #include "utils/acl.h"
36 : #include "utils/array.h"
37 : #include "utils/builtins.h"
38 : #include "utils/datetime.h"
39 : #include "utils/fmgroids.h"
40 : #include "utils/guc.h"
41 : #include "utils/lsyscache.h"
42 : #include "utils/pg_locale.h"
43 : #include "utils/rel.h"
44 : #include "utils/ruleutils.h"
45 : #include "utils/syscache.h"
46 : #include "utils/timestamp.h"
47 : #include "utils/varlena.h"
48 :
49 : /* Option value types for DDL option parsing */
50 : typedef enum
51 : {
52 : DDL_OPT_BOOL,
53 : DDL_OPT_TEXT,
54 : DDL_OPT_INT,
55 : } DdlOptType;
56 :
57 : /*
58 : * A single DDL option descriptor: caller fills in name and type,
59 : * parse_ddl_options fills in isset + the appropriate value field.
60 : */
61 : typedef struct DdlOption
62 : {
63 : const char *name; /* option name (case-insensitive match) */
64 : DdlOptType type; /* expected value type */
65 : bool isset; /* true if caller supplied this option */
66 : /* fields for specific option types */
67 : union
68 : {
69 : bool boolval; /* filled in for DDL_OPT_BOOL */
70 : char *textval; /* filled in for DDL_OPT_TEXT (palloc'd) */
71 : int intval; /* filled in for DDL_OPT_INT */
72 : };
73 : } DdlOption;
74 :
75 :
76 : static void parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start,
77 : DdlOption *opts, int nopts);
78 : static void append_ddl_option(StringInfo buf, bool pretty, int indent,
79 : const char *fmt,...)
80 : pg_attribute_printf(4, 5);
81 : static void append_guc_value(StringInfo buf, const char *name,
82 : const char *value);
83 : static List *pg_get_role_ddl_internal(Oid roleid, bool pretty,
84 : bool memberships);
85 : static List *pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner);
86 : static Datum pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull);
87 : static List *pg_get_database_ddl_internal(Oid dbid, bool pretty,
88 : bool no_owner, bool no_tablespace);
89 :
90 :
91 : /*
92 : * parse_ddl_options
93 : * Parse variadic name/value option pairs
94 : *
95 : * Options are passed as alternating key/value text pairs. The caller
96 : * provides an array of DdlOption descriptors specifying the accepted
97 : * option names and their types; this function matches each supplied
98 : * pair against the array, validates the value, and fills in the
99 : * result fields.
100 : */
101 : static void
102 22 : parse_ddl_options(FunctionCallInfo fcinfo, int variadic_start,
103 : DdlOption *opts, int nopts)
104 : {
105 : Datum *args;
106 : bool *nulls;
107 : Oid *types;
108 : int nargs;
109 :
110 : /* Clear all output fields */
111 71 : for (int i = 0; i < nopts; i++)
112 : {
113 49 : opts[i].isset = false;
114 49 : switch (opts[i].type)
115 : {
116 49 : case DDL_OPT_BOOL:
117 49 : opts[i].boolval = false;
118 49 : break;
119 0 : case DDL_OPT_TEXT:
120 0 : opts[i].textval = NULL;
121 0 : break;
122 0 : case DDL_OPT_INT:
123 0 : opts[i].intval = 0;
124 0 : break;
125 : }
126 : }
127 :
128 22 : nargs = extract_variadic_args(fcinfo, variadic_start, true,
129 : &args, &types, &nulls);
130 :
131 22 : if (nargs <= 0)
132 15 : return;
133 :
134 : /* Handle DEFAULT NULL case */
135 22 : if (nargs == 1 && nulls[0])
136 15 : return;
137 :
138 7 : if (nargs % 2 != 0)
139 0 : ereport(ERROR,
140 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
141 : errmsg("variadic arguments must be name/value pairs"),
142 : errhint("Provide an even number of variadic arguments that can be divided into pairs.")));
143 :
144 : /*
145 : * For each option name/value pair, find corresponding positional option
146 : * for the option name, and assign the option value.
147 : */
148 14 : for (int i = 0; i < nargs; i += 2)
149 : {
150 : char *name;
151 : char *valstr;
152 9 : DdlOption *opt = NULL;
153 :
154 9 : if (nulls[i])
155 0 : ereport(ERROR,
156 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
157 : errmsg("option name at variadic position %d is null", i + 1)));
158 :
159 9 : name = TextDatumGetCString(args[i]);
160 :
161 9 : if (nulls[i + 1])
162 0 : ereport(ERROR,
163 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
164 : errmsg("value for option \"%s\" must not be null", name)));
165 :
166 : /* Find matching option descriptor */
167 16 : for (int j = 0; j < nopts; j++)
168 : {
169 16 : if (pg_strcasecmp(name, opts[j].name) == 0)
170 : {
171 9 : opt = &opts[j];
172 9 : break;
173 : }
174 : }
175 :
176 9 : if (opt == NULL)
177 0 : ereport(ERROR,
178 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
179 : errmsg("unrecognized option: \"%s\"", name)));
180 :
181 9 : if (opt->isset)
182 1 : ereport(ERROR,
183 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
184 : errmsg("option \"%s\" is specified more than once",
185 : name)));
186 :
187 8 : valstr = TextDatumGetCString(args[i + 1]);
188 :
189 8 : switch (opt->type)
190 : {
191 8 : case DDL_OPT_BOOL:
192 8 : if (!parse_bool(valstr, &opt->boolval))
193 1 : ereport(ERROR,
194 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
195 : errmsg("invalid value for boolean option \"%s\": %s",
196 : name, valstr)));
197 7 : break;
198 :
199 0 : case DDL_OPT_TEXT:
200 0 : opt->textval = valstr;
201 0 : valstr = NULL; /* don't pfree below */
202 0 : break;
203 :
204 0 : case DDL_OPT_INT:
205 : {
206 : char *endp;
207 : long val;
208 :
209 0 : errno = 0;
210 0 : val = strtol(valstr, &endp, 10);
211 0 : if (*endp != '\0' || errno == ERANGE ||
212 0 : val < PG_INT32_MIN || val > PG_INT32_MAX)
213 0 : ereport(ERROR,
214 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
215 : errmsg("invalid value for integer option \"%s\": %s",
216 : name, valstr)));
217 0 : opt->intval = (int) val;
218 : }
219 0 : break;
220 : }
221 :
222 7 : opt->isset = true;
223 :
224 7 : if (valstr)
225 7 : pfree(valstr);
226 7 : pfree(name);
227 : }
228 : }
229 :
230 : /*
231 : * Helper to append a formatted string with optional pretty-printing.
232 : */
233 : static void
234 79 : append_ddl_option(StringInfo buf, bool pretty, int indent,
235 : const char *fmt,...)
236 : {
237 79 : if (pretty)
238 : {
239 15 : appendStringInfoChar(buf, '\n');
240 15 : appendStringInfoSpaces(buf, indent);
241 : }
242 : else
243 64 : appendStringInfoChar(buf, ' ');
244 :
245 : for (;;)
246 0 : {
247 : va_list args;
248 : int needed;
249 :
250 79 : va_start(args, fmt);
251 79 : needed = appendStringInfoVA(buf, fmt, args);
252 79 : va_end(args);
253 79 : if (needed == 0)
254 79 : break;
255 0 : enlargeStringInfo(buf, needed);
256 : }
257 79 : }
258 :
259 : /*
260 : * append_guc_value
261 : * Append a GUC setting value to buf, handling GUC_LIST_QUOTE properly.
262 : *
263 : * Variables marked GUC_LIST_QUOTE were already fully quoted before they
264 : * were stored in the setconfig array. We break the list value apart
265 : * and re-quote the elements as string literals. For all other variables
266 : * we simply quote the value as a single string literal.
267 : *
268 : * The caller has already appended "SET <name> TO " to buf.
269 : */
270 : static void
271 6 : append_guc_value(StringInfo buf, const char *name, const char *value)
272 : {
273 : char *rawval;
274 :
275 6 : rawval = pstrdup(value);
276 :
277 6 : if (GetConfigOptionFlags(name, true) & GUC_LIST_QUOTE)
278 : {
279 : List *namelist;
280 1 : bool first = true;
281 :
282 : /* Parse string into list of identifiers */
283 1 : if (!SplitGUCList(rawval, ',', &namelist))
284 : {
285 : /* this shouldn't fail really */
286 0 : elog(ERROR, "invalid list syntax in setconfig item");
287 : }
288 : /* Special case: represent an empty list as NULL */
289 1 : if (namelist == NIL)
290 0 : appendStringInfoString(buf, "NULL");
291 4 : foreach_ptr(char, curname, namelist)
292 : {
293 2 : if (first)
294 1 : first = false;
295 : else
296 1 : appendStringInfoString(buf, ", ");
297 2 : appendStringInfoString(buf, quote_literal_cstr(curname));
298 : }
299 1 : list_free(namelist);
300 : }
301 : else
302 5 : appendStringInfoString(buf, quote_literal_cstr(rawval));
303 :
304 6 : pfree(rawval);
305 6 : }
306 :
307 : /*
308 : * pg_get_role_ddl_internal
309 : * Generate DDL statements to recreate a role
310 : *
311 : * Returns a List of palloc'd strings, each being a complete SQL statement.
312 : * The first list element is always the CREATE ROLE statement; subsequent
313 : * elements are ALTER ROLE SET statements for any role-specific or
314 : * role-in-database configuration settings. If memberships is true,
315 : * GRANT statements for role memberships are appended.
316 : */
317 : static List *
318 10 : pg_get_role_ddl_internal(Oid roleid, bool pretty, bool memberships)
319 : {
320 : HeapTuple tuple;
321 : Form_pg_authid roleform;
322 : StringInfoData buf;
323 : char *rolname;
324 : Datum rolevaliduntil;
325 : bool isnull;
326 : Relation rel;
327 : ScanKeyData scankey;
328 : SysScanDesc scan;
329 10 : List *statements = NIL;
330 :
331 10 : tuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid));
332 10 : if (!HeapTupleIsValid(tuple))
333 1 : ereport(ERROR,
334 : (errcode(ERRCODE_UNDEFINED_OBJECT),
335 : errmsg("role with OID %u does not exist", roleid)));
336 :
337 9 : roleform = (Form_pg_authid) GETSTRUCT(tuple);
338 9 : rolname = pstrdup(NameStr(roleform->rolname));
339 :
340 : /* User must have SELECT privilege on pg_authid. */
341 9 : if (pg_class_aclcheck(AuthIdRelationId, GetUserId(), ACL_SELECT) != ACLCHECK_OK)
342 : {
343 1 : ReleaseSysCache(tuple);
344 1 : ereport(ERROR,
345 : (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
346 : errmsg("permission denied for role %s", rolname)));
347 : }
348 :
349 : /*
350 : * We don't support generating DDL for system roles. The primary reason
351 : * for this is that users shouldn't be recreating them.
352 : */
353 8 : if (IsReservedName(rolname))
354 0 : ereport(ERROR,
355 : (errcode(ERRCODE_RESERVED_NAME),
356 : errmsg("role name \"%s\" is reserved", rolname),
357 : errdetail("Role names starting with \"pg_\" are reserved for system roles.")));
358 :
359 8 : initStringInfo(&buf);
360 8 : appendStringInfo(&buf, "CREATE ROLE %s", quote_identifier(rolname));
361 :
362 : /*
363 : * Append role attributes. The order here follows the same sequence as
364 : * you'd typically write them in a CREATE ROLE command, though any order
365 : * is actually acceptable to the parser.
366 : */
367 8 : append_ddl_option(&buf, pretty, 4, "%s",
368 8 : roleform->rolsuper ? "SUPERUSER" : "NOSUPERUSER");
369 :
370 8 : append_ddl_option(&buf, pretty, 4, "%s",
371 8 : roleform->rolinherit ? "INHERIT" : "NOINHERIT");
372 :
373 8 : append_ddl_option(&buf, pretty, 4, "%s",
374 8 : roleform->rolcreaterole ? "CREATEROLE" : "NOCREATEROLE");
375 :
376 8 : append_ddl_option(&buf, pretty, 4, "%s",
377 8 : roleform->rolcreatedb ? "CREATEDB" : "NOCREATEDB");
378 :
379 8 : append_ddl_option(&buf, pretty, 4, "%s",
380 8 : roleform->rolcanlogin ? "LOGIN" : "NOLOGIN");
381 :
382 8 : append_ddl_option(&buf, pretty, 4, "%s",
383 8 : roleform->rolreplication ? "REPLICATION" : "NOREPLICATION");
384 :
385 8 : append_ddl_option(&buf, pretty, 4, "%s",
386 8 : roleform->rolbypassrls ? "BYPASSRLS" : "NOBYPASSRLS");
387 :
388 : /*
389 : * CONNECTION LIMIT is only interesting if it's not -1 (the default,
390 : * meaning no limit).
391 : */
392 8 : if (roleform->rolconnlimit >= 0)
393 3 : append_ddl_option(&buf, pretty, 4, "CONNECTION LIMIT %d",
394 : roleform->rolconnlimit);
395 :
396 8 : rolevaliduntil = SysCacheGetAttr(AUTHOID, tuple,
397 : Anum_pg_authid_rolvaliduntil,
398 : &isnull);
399 8 : if (!isnull)
400 : {
401 : TimestampTz ts;
402 : int tz;
403 : struct pg_tm tm;
404 : fsec_t fsec;
405 : const char *tzn;
406 : char ts_str[MAXDATELEN + 1];
407 :
408 3 : ts = DatumGetTimestampTz(rolevaliduntil);
409 3 : if (TIMESTAMP_NOT_FINITE(ts))
410 0 : EncodeSpecialTimestamp(ts, ts_str);
411 3 : else if (timestamp2tm(ts, &tz, &tm, &fsec, &tzn, NULL) == 0)
412 3 : EncodeDateTime(&tm, fsec, true, tz, tzn, USE_ISO_DATES, ts_str);
413 : else
414 0 : ereport(ERROR,
415 : (errcode(ERRCODE_DATETIME_VALUE_OUT_OF_RANGE),
416 : errmsg("timestamp out of range")));
417 :
418 3 : append_ddl_option(&buf, pretty, 4, "VALID UNTIL %s",
419 : quote_literal_cstr(ts_str));
420 : }
421 :
422 8 : ReleaseSysCache(tuple);
423 :
424 : /*
425 : * We intentionally omit PASSWORD. There's no way to retrieve the
426 : * original password text from the stored hash, and even if we could,
427 : * exposing passwords through a SQL function would be a security issue.
428 : * Users must set passwords separately after recreating roles.
429 : */
430 :
431 8 : appendStringInfoChar(&buf, ';');
432 :
433 8 : statements = lappend(statements, pstrdup(buf.data));
434 :
435 : /*
436 : * Now scan pg_db_role_setting for ALTER ROLE SET configurations.
437 : *
438 : * These can be role-wide (setdatabase = 0) or specific to a particular
439 : * database (setdatabase = a valid DB OID). It generates one ALTER
440 : * statement per setting.
441 : */
442 8 : rel = table_open(DbRoleSettingRelationId, AccessShareLock);
443 8 : ScanKeyInit(&scankey,
444 : Anum_pg_db_role_setting_setrole,
445 : BTEqualStrategyNumber, F_OIDEQ,
446 : ObjectIdGetDatum(roleid));
447 8 : scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true,
448 : NULL, 1, &scankey);
449 :
450 11 : while (HeapTupleIsValid(tuple = systable_getnext(scan)))
451 : {
452 3 : Form_pg_db_role_setting setting = (Form_pg_db_role_setting) GETSTRUCT(tuple);
453 3 : Oid datid = setting->setdatabase;
454 : Datum datum;
455 : ArrayType *role_settings;
456 : Datum *settings;
457 : bool *nulls;
458 : int nsettings;
459 3 : char *datname = NULL;
460 :
461 : /*
462 : * If setdatabase is valid, this is a role-in-database setting;
463 : * otherwise it's a role-wide setting. Look up the database name once
464 : * for all settings in this row.
465 : */
466 3 : if (OidIsValid(datid))
467 : {
468 2 : datname = get_database_name(datid);
469 : /* Database has been dropped; skip all settings in this row. */
470 2 : if (datname == NULL)
471 0 : continue;
472 : }
473 :
474 : /*
475 : * The setconfig column is a text array in "name=value" format. It
476 : * should never be null for a valid row, but be defensive.
477 : */
478 3 : datum = heap_getattr(tuple, Anum_pg_db_role_setting_setconfig,
479 : RelationGetDescr(rel), &isnull);
480 3 : if (isnull)
481 0 : continue;
482 :
483 3 : role_settings = DatumGetArrayTypePCopy(datum);
484 :
485 3 : deconstruct_array_builtin(role_settings, TEXTOID, &settings, &nulls, &nsettings);
486 :
487 7 : for (int i = 0; i < nsettings; i++)
488 : {
489 : char *s,
490 : *p;
491 :
492 4 : if (nulls[i])
493 0 : continue;
494 :
495 4 : s = TextDatumGetCString(settings[i]);
496 4 : p = strchr(s, '=');
497 4 : if (p == NULL)
498 : {
499 0 : pfree(s);
500 0 : continue;
501 : }
502 4 : *p++ = '\0';
503 :
504 : /* Build a fresh ALTER ROLE statement for this setting */
505 4 : resetStringInfo(&buf);
506 4 : appendStringInfo(&buf, "ALTER ROLE %s", quote_identifier(rolname));
507 :
508 4 : if (datname != NULL)
509 2 : appendStringInfo(&buf, " IN DATABASE %s",
510 : quote_identifier(datname));
511 :
512 4 : appendStringInfo(&buf, " SET %s TO ",
513 : quote_identifier(s));
514 :
515 4 : append_guc_value(&buf, s, p);
516 :
517 4 : appendStringInfoChar(&buf, ';');
518 :
519 4 : statements = lappend(statements, pstrdup(buf.data));
520 :
521 4 : pfree(s);
522 : }
523 :
524 3 : pfree(settings);
525 3 : pfree(nulls);
526 3 : pfree(role_settings);
527 :
528 3 : if (datname != NULL)
529 2 : pfree(datname);
530 : }
531 :
532 8 : systable_endscan(scan);
533 8 : table_close(rel, AccessShareLock);
534 :
535 : /*
536 : * Scan pg_auth_members for role memberships. We look for rows where
537 : * member = roleid, meaning this role has been granted membership in other
538 : * roles.
539 : */
540 8 : if (memberships)
541 : {
542 7 : rel = table_open(AuthMemRelationId, AccessShareLock);
543 7 : ScanKeyInit(&scankey,
544 : Anum_pg_auth_members_member,
545 : BTEqualStrategyNumber, F_OIDEQ,
546 : ObjectIdGetDatum(roleid));
547 7 : scan = systable_beginscan(rel, AuthMemMemRoleIndexId, true,
548 : NULL, 1, &scankey);
549 :
550 9 : while (HeapTupleIsValid(tuple = systable_getnext(scan)))
551 : {
552 2 : Form_pg_auth_members memform = (Form_pg_auth_members) GETSTRUCT(tuple);
553 : char *granted_role;
554 : char *grantor;
555 :
556 2 : granted_role = GetUserNameFromId(memform->roleid, false);
557 2 : grantor = GetUserNameFromId(memform->grantor, false);
558 :
559 2 : resetStringInfo(&buf);
560 2 : appendStringInfo(&buf, "GRANT %s TO %s",
561 : quote_identifier(granted_role),
562 : quote_identifier(rolname));
563 6 : appendStringInfo(&buf, " WITH ADMIN %s, INHERIT %s, SET %s",
564 2 : memform->admin_option ? "TRUE" : "FALSE",
565 2 : memform->inherit_option ? "TRUE" : "FALSE",
566 2 : memform->set_option ? "TRUE" : "FALSE");
567 2 : appendStringInfo(&buf, " GRANTED BY %s;",
568 : quote_identifier(grantor));
569 :
570 2 : statements = lappend(statements, pstrdup(buf.data));
571 :
572 2 : pfree(granted_role);
573 2 : pfree(grantor);
574 : }
575 :
576 7 : systable_endscan(scan);
577 7 : table_close(rel, AccessShareLock);
578 : }
579 :
580 8 : pfree(buf.data);
581 8 : pfree(rolname);
582 :
583 8 : return statements;
584 : }
585 :
586 : /*
587 : * pg_get_role_ddl
588 : * Return DDL to recreate a role as a set of text rows.
589 : *
590 : * Each row is a complete SQL statement. The first row is always the
591 : * CREATE ROLE statement; subsequent rows are ALTER ROLE SET statements
592 : * and optionally GRANT statements for role memberships.
593 : * Returns no rows if the role argument is NULL.
594 : */
595 : Datum
596 25 : pg_get_role_ddl(PG_FUNCTION_ARGS)
597 : {
598 : FuncCallContext *funcctx;
599 : List *statements;
600 :
601 25 : if (SRF_IS_FIRSTCALL())
602 : {
603 : MemoryContext oldcontext;
604 : Oid roleid;
605 11 : DdlOption opts[] = {
606 : {"pretty", DDL_OPT_BOOL},
607 : {"memberships", DDL_OPT_BOOL},
608 : };
609 :
610 11 : funcctx = SRF_FIRSTCALL_INIT();
611 11 : oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
612 :
613 11 : if (PG_ARGISNULL(0))
614 : {
615 1 : MemoryContextSwitchTo(oldcontext);
616 1 : SRF_RETURN_DONE(funcctx);
617 : }
618 :
619 10 : roleid = PG_GETARG_OID(0);
620 10 : parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
621 :
622 20 : statements = pg_get_role_ddl_internal(roleid,
623 10 : opts[0].isset && opts[0].boolval,
624 20 : !opts[1].isset || opts[1].boolval);
625 8 : funcctx->user_fctx = statements;
626 8 : funcctx->max_calls = list_length(statements);
627 :
628 8 : MemoryContextSwitchTo(oldcontext);
629 : }
630 :
631 22 : funcctx = SRF_PERCALL_SETUP();
632 22 : statements = (List *) funcctx->user_fctx;
633 :
634 22 : if (funcctx->call_cntr < funcctx->max_calls)
635 : {
636 : char *stmt;
637 :
638 14 : stmt = list_nth(statements, funcctx->call_cntr);
639 :
640 14 : SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
641 : }
642 : else
643 : {
644 8 : list_free_deep(statements);
645 8 : SRF_RETURN_DONE(funcctx);
646 : }
647 : }
648 :
649 : /*
650 : * pg_get_tablespace_ddl_internal
651 : * Generate DDL statements to recreate a tablespace.
652 : *
653 : * Returns a List of palloc'd strings. The first element is the
654 : * CREATE TABLESPACE statement; if the tablespace has reloptions,
655 : * a second element with ALTER TABLESPACE SET (...) is appended.
656 : */
657 : static List *
658 7 : pg_get_tablespace_ddl_internal(Oid tsid, bool pretty, bool no_owner)
659 : {
660 : HeapTuple tuple;
661 : Form_pg_tablespace tspForm;
662 : StringInfoData buf;
663 : char *spcname;
664 : char *spcowner;
665 : char *path;
666 : bool isNull;
667 : Datum datum;
668 7 : List *statements = NIL;
669 :
670 7 : tuple = SearchSysCache1(TABLESPACEOID, ObjectIdGetDatum(tsid));
671 7 : if (!HeapTupleIsValid(tuple))
672 1 : ereport(ERROR,
673 : (errcode(ERRCODE_UNDEFINED_OBJECT),
674 : errmsg("tablespace with OID %u does not exist",
675 : tsid)));
676 :
677 6 : tspForm = (Form_pg_tablespace) GETSTRUCT(tuple);
678 6 : spcname = pstrdup(NameStr(tspForm->spcname));
679 :
680 : /* User must have SELECT privilege on pg_tablespace. */
681 6 : if (pg_class_aclcheck(TableSpaceRelationId, GetUserId(), ACL_SELECT) != ACLCHECK_OK)
682 : {
683 1 : ReleaseSysCache(tuple);
684 1 : aclcheck_error(ACLCHECK_NO_PRIV, OBJECT_TABLESPACE, spcname);
685 : }
686 :
687 : /*
688 : * We don't support generating DDL for system tablespaces. The primary
689 : * reason for this is that users shouldn't be recreating them.
690 : */
691 5 : if (IsReservedName(spcname))
692 0 : ereport(ERROR,
693 : (errcode(ERRCODE_RESERVED_NAME),
694 : errmsg("tablespace name \"%s\" is reserved", spcname),
695 : errdetail("Tablespace names starting with \"pg_\" are reserved for system tablespaces.")));
696 :
697 5 : initStringInfo(&buf);
698 :
699 : /* Start building the CREATE TABLESPACE statement */
700 5 : appendStringInfo(&buf, "CREATE TABLESPACE %s", quote_identifier(spcname));
701 :
702 : /* Add OWNER clause */
703 5 : if (!no_owner)
704 : {
705 4 : spcowner = GetUserNameFromId(tspForm->spcowner, false);
706 4 : append_ddl_option(&buf, pretty, 4, "OWNER %s",
707 : quote_identifier(spcowner));
708 4 : pfree(spcowner);
709 : }
710 :
711 : /* Find tablespace directory path */
712 5 : path = get_tablespace_location(tsid);
713 :
714 : /* Add directory LOCATION (path), if it exists */
715 5 : if (path[0] != '\0')
716 : {
717 : /*
718 : * Special case: if the tablespace was created with GUC
719 : * "allow_in_place_tablespaces = true" and "LOCATION ''", path will
720 : * begin with "pg_tblspc/". In that case, show "LOCATION ''" as the
721 : * user originally specified.
722 : */
723 5 : if (strncmp(PG_TBLSPC_DIR_SLASH, path, strlen(PG_TBLSPC_DIR_SLASH)) == 0)
724 5 : append_ddl_option(&buf, pretty, 4, "LOCATION ''");
725 : else
726 0 : append_ddl_option(&buf, pretty, 4, "LOCATION %s",
727 : quote_literal_cstr(path));
728 : }
729 5 : pfree(path);
730 :
731 5 : appendStringInfoChar(&buf, ';');
732 5 : statements = lappend(statements, pstrdup(buf.data));
733 :
734 : /* Check for tablespace options */
735 5 : datum = SysCacheGetAttr(TABLESPACEOID, tuple,
736 : Anum_pg_tablespace_spcoptions, &isNull);
737 5 : if (!isNull)
738 : {
739 4 : resetStringInfo(&buf);
740 4 : appendStringInfo(&buf, "ALTER TABLESPACE %s SET (",
741 : quote_identifier(spcname));
742 4 : get_reloptions(&buf, datum);
743 4 : appendStringInfoString(&buf, ");");
744 4 : statements = lappend(statements, pstrdup(buf.data));
745 : }
746 :
747 5 : ReleaseSysCache(tuple);
748 5 : pfree(spcname);
749 5 : pfree(buf.data);
750 :
751 5 : return statements;
752 : }
753 :
754 : /*
755 : * pg_get_tablespace_ddl_srf - common SRF logic for tablespace DDL
756 : */
757 : static Datum
758 18 : pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull)
759 : {
760 : FuncCallContext *funcctx;
761 : List *statements;
762 :
763 18 : if (SRF_IS_FIRSTCALL())
764 : {
765 : MemoryContext oldcontext;
766 9 : DdlOption opts[] = {
767 : {"pretty", DDL_OPT_BOOL},
768 : {"owner", DDL_OPT_BOOL},
769 : };
770 :
771 9 : funcctx = SRF_FIRSTCALL_INIT();
772 9 : oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
773 :
774 9 : if (isnull)
775 : {
776 2 : MemoryContextSwitchTo(oldcontext);
777 2 : SRF_RETURN_DONE(funcctx);
778 : }
779 :
780 7 : parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
781 :
782 14 : statements = pg_get_tablespace_ddl_internal(tsid,
783 7 : opts[0].isset && opts[0].boolval,
784 14 : opts[1].isset && !opts[1].boolval);
785 5 : funcctx->user_fctx = statements;
786 5 : funcctx->max_calls = list_length(statements);
787 :
788 5 : MemoryContextSwitchTo(oldcontext);
789 : }
790 :
791 14 : funcctx = SRF_PERCALL_SETUP();
792 14 : statements = (List *) funcctx->user_fctx;
793 :
794 14 : if (funcctx->call_cntr < funcctx->max_calls)
795 : {
796 : char *stmt;
797 :
798 9 : stmt = (char *) list_nth(statements, funcctx->call_cntr);
799 :
800 9 : SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
801 : }
802 : else
803 : {
804 5 : list_free_deep(statements);
805 5 : SRF_RETURN_DONE(funcctx);
806 : }
807 : }
808 :
809 : /*
810 : * pg_get_tablespace_ddl_oid
811 : * Return DDL to recreate a tablespace, taking OID.
812 : */
813 : Datum
814 5 : pg_get_tablespace_ddl_oid(PG_FUNCTION_ARGS)
815 : {
816 5 : Oid tsid = InvalidOid;
817 : bool isnull;
818 :
819 5 : isnull = PG_ARGISNULL(0);
820 5 : if (!isnull)
821 4 : tsid = PG_GETARG_OID(0);
822 :
823 5 : return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull);
824 : }
825 :
826 : /*
827 : * pg_get_tablespace_ddl_name
828 : * Return DDL to recreate a tablespace, taking name.
829 : */
830 : Datum
831 14 : pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS)
832 : {
833 14 : Oid tsid = InvalidOid;
834 : Name tspname;
835 : bool isnull;
836 :
837 14 : isnull = PG_ARGISNULL(0);
838 :
839 14 : if (!isnull)
840 : {
841 13 : tspname = PG_GETARG_NAME(0);
842 13 : tsid = get_tablespace_oid(NameStr(*tspname), false);
843 : }
844 :
845 13 : return pg_get_tablespace_ddl_srf(fcinfo, tsid, isnull);
846 : }
847 :
848 : /*
849 : * pg_get_database_ddl_internal
850 : * Generate DDL statements to recreate a database.
851 : *
852 : * Returns a List of palloc'd strings. The first element is the
853 : * CREATE DATABASE statement; subsequent elements are ALTER DATABASE
854 : * statements for properties and configuration settings.
855 : */
856 : static List *
857 3 : pg_get_database_ddl_internal(Oid dbid, bool pretty,
858 : bool no_owner, bool no_tablespace)
859 : {
860 : HeapTuple tuple;
861 : Form_pg_database dbform;
862 : StringInfoData buf;
863 : bool isnull;
864 : Datum datum;
865 : const char *encoding;
866 : char *dbname;
867 : char *collate;
868 : char *ctype;
869 : Relation rel;
870 : ScanKeyData scankey[2];
871 : SysScanDesc scan;
872 3 : List *statements = NIL;
873 : AclResult aclresult;
874 :
875 3 : tuple = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbid));
876 3 : if (!HeapTupleIsValid(tuple))
877 0 : ereport(ERROR,
878 : (errcode(ERRCODE_UNDEFINED_OBJECT),
879 : errmsg("database with OID %u does not exist", dbid)));
880 :
881 : /* User must have connect privilege for target database. */
882 3 : aclresult = object_aclcheck(DatabaseRelationId, dbid, GetUserId(), ACL_CONNECT);
883 3 : if (aclresult != ACLCHECK_OK)
884 1 : aclcheck_error(aclresult, OBJECT_DATABASE,
885 1 : get_database_name(dbid));
886 :
887 2 : dbform = (Form_pg_database) GETSTRUCT(tuple);
888 2 : dbname = pstrdup(NameStr(dbform->datname));
889 :
890 : /*
891 : * Reject invalid databases. Deparsing a pg_database row in invalid state
892 : * can produce SQL that is not executable, such as CONNECTION LIMIT = -2.
893 : */
894 2 : if (database_is_invalid_form(dbform))
895 0 : ereport(ERROR,
896 : (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
897 : errmsg("cannot generate DDL for invalid database \"%s\"",
898 : dbname)));
899 :
900 : /*
901 : * We don't support generating DDL for system databases. The primary
902 : * reason for this is that users shouldn't be recreating them.
903 : */
904 2 : if (strcmp(dbname, "template0") == 0 || strcmp(dbname, "template1") == 0)
905 0 : ereport(ERROR,
906 : (errcode(ERRCODE_RESERVED_NAME),
907 : errmsg("database \"%s\" is a system database", dbname),
908 : errdetail("DDL generation is not supported for template0 and template1.")));
909 :
910 2 : initStringInfo(&buf);
911 :
912 : /* --- Build CREATE DATABASE statement --- */
913 2 : appendStringInfo(&buf, "CREATE DATABASE %s", quote_identifier(dbname));
914 :
915 : /*
916 : * Always use template0: the target database already contains the catalog
917 : * data from whatever template was used originally, so we must start from
918 : * the pristine template to avoid duplication.
919 : */
920 2 : append_ddl_option(&buf, pretty, 4, "WITH TEMPLATE = template0");
921 :
922 : /* ENCODING */
923 2 : encoding = pg_encoding_to_char(dbform->encoding);
924 2 : if (strlen(encoding) > 0)
925 2 : append_ddl_option(&buf, pretty, 4, "ENCODING = %s",
926 : quote_literal_cstr(encoding));
927 :
928 : /* LOCALE_PROVIDER */
929 2 : if (dbform->datlocprovider == COLLPROVIDER_BUILTIN ||
930 2 : dbform->datlocprovider == COLLPROVIDER_ICU ||
931 2 : dbform->datlocprovider == COLLPROVIDER_LIBC)
932 2 : append_ddl_option(&buf, pretty, 4, "LOCALE_PROVIDER = %s",
933 2 : collprovider_name(dbform->datlocprovider));
934 : else
935 0 : ereport(ERROR,
936 : (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
937 : errmsg("unrecognized locale provider: %c",
938 : dbform->datlocprovider)));
939 :
940 : /* LOCALE, LC_COLLATE, LC_CTYPE */
941 2 : datum = SysCacheGetAttr(DATABASEOID, tuple,
942 : Anum_pg_database_datcollate, &isnull);
943 2 : collate = isnull ? NULL : TextDatumGetCString(datum);
944 2 : datum = SysCacheGetAttr(DATABASEOID, tuple,
945 : Anum_pg_database_datctype, &isnull);
946 2 : ctype = isnull ? NULL : TextDatumGetCString(datum);
947 2 : if (collate != NULL && ctype != NULL && strcmp(collate, ctype) == 0)
948 : {
949 2 : append_ddl_option(&buf, pretty, 4, "LOCALE = %s",
950 : quote_literal_cstr(collate));
951 : }
952 : else
953 : {
954 0 : if (collate != NULL)
955 0 : append_ddl_option(&buf, pretty, 4, "LC_COLLATE = %s",
956 : quote_literal_cstr(collate));
957 0 : if (ctype != NULL)
958 0 : append_ddl_option(&buf, pretty, 4, "LC_CTYPE = %s",
959 : quote_literal_cstr(ctype));
960 : }
961 :
962 : /* LOCALE (provider-specific) */
963 2 : datum = SysCacheGetAttr(DATABASEOID, tuple,
964 : Anum_pg_database_datlocale, &isnull);
965 2 : if (!isnull)
966 : {
967 0 : const char *locale = TextDatumGetCString(datum);
968 :
969 0 : if (dbform->datlocprovider == COLLPROVIDER_BUILTIN)
970 0 : append_ddl_option(&buf, pretty, 4, "BUILTIN_LOCALE = %s",
971 : quote_literal_cstr(locale));
972 0 : else if (dbform->datlocprovider == COLLPROVIDER_ICU)
973 0 : append_ddl_option(&buf, pretty, 4, "ICU_LOCALE = %s",
974 : quote_literal_cstr(locale));
975 : }
976 :
977 : /* ICU_RULES */
978 2 : datum = SysCacheGetAttr(DATABASEOID, tuple,
979 : Anum_pg_database_daticurules, &isnull);
980 2 : if (!isnull && dbform->datlocprovider == COLLPROVIDER_ICU)
981 0 : append_ddl_option(&buf, pretty, 4, "ICU_RULES = %s",
982 0 : quote_literal_cstr(TextDatumGetCString(datum)));
983 :
984 : /* TABLESPACE */
985 2 : if (!no_tablespace && OidIsValid(dbform->dattablespace))
986 : {
987 1 : char *spcname = get_tablespace_name(dbform->dattablespace);
988 :
989 1 : if (spcname == NULL)
990 0 : ereport(ERROR,
991 : (errcode(ERRCODE_UNDEFINED_OBJECT),
992 : errmsg("tablespace with OID %u does not exist",
993 : dbform->dattablespace),
994 : errdetail("It may have been concurrently dropped.")));
995 :
996 1 : if (pg_strcasecmp(spcname, "pg_default") != 0)
997 0 : append_ddl_option(&buf, pretty, 4, "TABLESPACE = %s",
998 : quote_identifier(spcname));
999 : }
1000 :
1001 2 : appendStringInfoChar(&buf, ';');
1002 2 : statements = lappend(statements, pstrdup(buf.data));
1003 :
1004 : /* OWNER */
1005 2 : if (!no_owner && OidIsValid(dbform->datdba))
1006 : {
1007 2 : char *owner = GetUserNameFromId(dbform->datdba, false);
1008 :
1009 2 : resetStringInfo(&buf);
1010 2 : appendStringInfo(&buf, "ALTER DATABASE %s OWNER TO %s;",
1011 : quote_identifier(dbname), quote_identifier(owner));
1012 2 : pfree(owner);
1013 2 : statements = lappend(statements, pstrdup(buf.data));
1014 : }
1015 :
1016 : /* CONNECTION LIMIT */
1017 2 : if (dbform->datconnlimit != -1)
1018 : {
1019 2 : resetStringInfo(&buf);
1020 2 : appendStringInfo(&buf, "ALTER DATABASE %s CONNECTION LIMIT = %d;",
1021 : quote_identifier(dbname), dbform->datconnlimit);
1022 2 : statements = lappend(statements, pstrdup(buf.data));
1023 : }
1024 :
1025 : /* IS_TEMPLATE */
1026 2 : if (dbform->datistemplate)
1027 : {
1028 0 : resetStringInfo(&buf);
1029 0 : appendStringInfo(&buf, "ALTER DATABASE %s IS_TEMPLATE = true;",
1030 : quote_identifier(dbname));
1031 0 : statements = lappend(statements, pstrdup(buf.data));
1032 : }
1033 :
1034 : /* ALLOW_CONNECTIONS */
1035 2 : if (!dbform->datallowconn)
1036 : {
1037 0 : resetStringInfo(&buf);
1038 0 : appendStringInfo(&buf, "ALTER DATABASE %s ALLOW_CONNECTIONS = false;",
1039 : quote_identifier(dbname));
1040 0 : statements = lappend(statements, pstrdup(buf.data));
1041 : }
1042 :
1043 2 : ReleaseSysCache(tuple);
1044 :
1045 : /*
1046 : * Now scan pg_db_role_setting for ALTER DATABASE SET configurations.
1047 : *
1048 : * It is only database-wide (setrole = 0). It generates one ALTER
1049 : * statement per setting.
1050 : */
1051 2 : rel = table_open(DbRoleSettingRelationId, AccessShareLock);
1052 2 : ScanKeyInit(&scankey[0],
1053 : Anum_pg_db_role_setting_setdatabase,
1054 : BTEqualStrategyNumber, F_OIDEQ,
1055 : ObjectIdGetDatum(dbid));
1056 2 : ScanKeyInit(&scankey[1],
1057 : Anum_pg_db_role_setting_setrole,
1058 : BTEqualStrategyNumber, F_OIDEQ,
1059 : ObjectIdGetDatum(InvalidOid));
1060 :
1061 2 : scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true,
1062 : NULL, 2, scankey);
1063 :
1064 4 : while (HeapTupleIsValid(tuple = systable_getnext(scan)))
1065 : {
1066 : ArrayType *dbconfig;
1067 : Datum *settings;
1068 : bool *nulls;
1069 : int nsettings;
1070 :
1071 : /*
1072 : * The setconfig column is a text array in "name=value" format. It
1073 : * should never be null for a valid row, but be defensive.
1074 : */
1075 2 : datum = heap_getattr(tuple, Anum_pg_db_role_setting_setconfig,
1076 : RelationGetDescr(rel), &isnull);
1077 2 : if (isnull)
1078 0 : continue;
1079 :
1080 2 : dbconfig = DatumGetArrayTypePCopy(datum);
1081 :
1082 2 : deconstruct_array_builtin(dbconfig, TEXTOID, &settings, &nulls, &nsettings);
1083 :
1084 4 : for (int i = 0; i < nsettings; i++)
1085 : {
1086 : char *s,
1087 : *p;
1088 :
1089 2 : if (nulls[i])
1090 0 : continue;
1091 :
1092 2 : s = TextDatumGetCString(settings[i]);
1093 2 : p = strchr(s, '=');
1094 2 : if (p == NULL)
1095 : {
1096 0 : pfree(s);
1097 0 : continue;
1098 : }
1099 2 : *p++ = '\0';
1100 :
1101 2 : resetStringInfo(&buf);
1102 2 : appendStringInfo(&buf, "ALTER DATABASE %s SET %s TO ",
1103 : quote_identifier(dbname),
1104 : quote_identifier(s));
1105 :
1106 2 : append_guc_value(&buf, s, p);
1107 :
1108 2 : appendStringInfoChar(&buf, ';');
1109 :
1110 2 : statements = lappend(statements, pstrdup(buf.data));
1111 :
1112 2 : pfree(s);
1113 : }
1114 :
1115 2 : pfree(settings);
1116 2 : pfree(nulls);
1117 2 : pfree(dbconfig);
1118 : }
1119 :
1120 2 : systable_endscan(scan);
1121 2 : table_close(rel, AccessShareLock);
1122 :
1123 2 : pfree(buf.data);
1124 2 : pfree(dbname);
1125 :
1126 2 : return statements;
1127 : }
1128 :
1129 : /*
1130 : * pg_get_database_ddl
1131 : * Return DDL to recreate a database as a set of text rows.
1132 : */
1133 : Datum
1134 14 : pg_get_database_ddl(PG_FUNCTION_ARGS)
1135 : {
1136 : FuncCallContext *funcctx;
1137 : List *statements;
1138 :
1139 14 : if (SRF_IS_FIRSTCALL())
1140 : {
1141 : MemoryContext oldcontext;
1142 : Oid dbid;
1143 6 : DdlOption opts[] = {
1144 : {"pretty", DDL_OPT_BOOL},
1145 : {"owner", DDL_OPT_BOOL},
1146 : {"tablespace", DDL_OPT_BOOL},
1147 : };
1148 :
1149 6 : funcctx = SRF_FIRSTCALL_INIT();
1150 6 : oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
1151 :
1152 6 : if (PG_ARGISNULL(0))
1153 : {
1154 1 : MemoryContextSwitchTo(oldcontext);
1155 1 : SRF_RETURN_DONE(funcctx);
1156 : }
1157 :
1158 5 : dbid = PG_GETARG_OID(0);
1159 5 : parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
1160 :
1161 9 : statements = pg_get_database_ddl_internal(dbid,
1162 3 : opts[0].isset && opts[0].boolval,
1163 3 : opts[1].isset && !opts[1].boolval,
1164 9 : opts[2].isset && !opts[2].boolval);
1165 2 : funcctx->user_fctx = statements;
1166 2 : funcctx->max_calls = list_length(statements);
1167 :
1168 2 : MemoryContextSwitchTo(oldcontext);
1169 : }
1170 :
1171 10 : funcctx = SRF_PERCALL_SETUP();
1172 10 : statements = (List *) funcctx->user_fctx;
1173 :
1174 10 : if (funcctx->call_cntr < funcctx->max_calls)
1175 : {
1176 : char *stmt;
1177 :
1178 8 : stmt = list_nth(statements, funcctx->call_cntr);
1179 :
1180 8 : SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
1181 : }
1182 : else
1183 : {
1184 2 : list_free_deep(statements);
1185 2 : SRF_RETURN_DONE(funcctx);
1186 : }
1187 : }
|
