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 96 : 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 312 : for (int i = 0; i < nopts; i++)
112 : {
113 216 : opts[i].isset = false;
114 216 : switch (opts[i].type)
115 : {
116 216 : case DDL_OPT_BOOL:
117 216 : opts[i].boolval = false;
118 216 : 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 96 : nargs = extract_variadic_args(fcinfo, variadic_start, true,
129 : &args, &types, &nulls);
130 :
131 96 : if (nargs <= 0)
132 64 : return;
133 :
134 : /* Handle DEFAULT NULL case */
135 96 : if (nargs == 1 && nulls[0])
136 64 : return;
137 :
138 32 : 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 64 : for (int i = 0; i < nargs; i += 2)
149 : {
150 : char *name;
151 : char *valstr;
152 40 : DdlOption *opt = NULL;
153 :
154 40 : 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 40 : name = TextDatumGetCString(args[i]);
160 :
161 40 : 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 72 : for (int j = 0; j < nopts; j++)
168 : {
169 72 : if (pg_strcasecmp(name, opts[j].name) == 0)
170 : {
171 40 : opt = &opts[j];
172 40 : break;
173 : }
174 : }
175 :
176 40 : if (opt == NULL)
177 0 : ereport(ERROR,
178 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
179 : errmsg("unrecognized option: \"%s\"", name)));
180 :
181 40 : if (opt->isset)
182 4 : ereport(ERROR,
183 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
184 : errmsg("option \"%s\" is specified more than once",
185 : name)));
186 :
187 36 : valstr = TextDatumGetCString(args[i + 1]);
188 :
189 36 : switch (opt->type)
190 : {
191 36 : case DDL_OPT_BOOL:
192 36 : if (!parse_bool(valstr, &opt->boolval))
193 4 : ereport(ERROR,
194 : (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
195 : errmsg("invalid value for boolean option \"%s\": %s",
196 : name, valstr)));
197 32 : 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 32 : opt->isset = true;
223 :
224 32 : if (valstr)
225 32 : pfree(valstr);
226 32 : pfree(name);
227 : }
228 : }
229 :
230 : /*
231 : * Helper to append a formatted string with optional pretty-printing.
232 : */
233 : static void
234 355 : append_ddl_option(StringInfo buf, bool pretty, int indent,
235 : const char *fmt,...)
236 : {
237 355 : if (pretty)
238 : {
239 61 : appendStringInfoChar(buf, '\n');
240 61 : appendStringInfoSpaces(buf, indent);
241 : }
242 : else
243 294 : appendStringInfoChar(buf, ' ');
244 :
245 : for (;;)
246 0 : {
247 : va_list args;
248 : int needed;
249 :
250 355 : va_start(args, fmt);
251 355 : needed = appendStringInfoVA(buf, fmt, args);
252 355 : va_end(args);
253 355 : if (needed == 0)
254 355 : break;
255 0 : enlargeStringInfo(buf, needed);
256 : }
257 355 : }
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 24 : append_guc_value(StringInfo buf, const char *name, const char *value)
272 : {
273 : char *rawval;
274 :
275 24 : rawval = pstrdup(value);
276 :
277 24 : if (GetConfigOptionFlags(name, true) & GUC_LIST_QUOTE)
278 : {
279 : List *namelist;
280 4 : bool first = true;
281 :
282 : /* Parse string into list of identifiers */
283 4 : 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 4 : if (namelist == NIL)
290 0 : appendStringInfoString(buf, "NULL");
291 16 : foreach_ptr(char, curname, namelist)
292 : {
293 8 : if (first)
294 4 : first = false;
295 : else
296 4 : appendStringInfoString(buf, ", ");
297 8 : appendStringInfoString(buf, quote_literal_cstr(curname));
298 : }
299 4 : list_free(namelist);
300 : }
301 : else
302 20 : appendStringInfoString(buf, quote_literal_cstr(rawval));
303 :
304 24 : pfree(rawval);
305 24 : }
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 44 : 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 44 : List *statements = NIL;
330 :
331 44 : tuple = SearchSysCache1(AUTHOID, ObjectIdGetDatum(roleid));
332 44 : if (!HeapTupleIsValid(tuple))
333 4 : ereport(ERROR,
334 : (errcode(ERRCODE_UNDEFINED_OBJECT),
335 : errmsg("role with OID %u does not exist", roleid)));
336 :
337 40 : roleform = (Form_pg_authid) GETSTRUCT(tuple);
338 40 : rolname = pstrdup(NameStr(roleform->rolname));
339 :
340 : /* User must have SELECT privilege on pg_authid. */
341 40 : if (pg_class_aclcheck(AuthIdRelationId, GetUserId(), ACL_SELECT) != ACLCHECK_OK)
342 : {
343 4 : ReleaseSysCache(tuple);
344 4 : 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 36 : 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 36 : initStringInfo(&buf);
360 36 : 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 36 : append_ddl_option(&buf, pretty, 4, "%s",
368 36 : roleform->rolsuper ? "SUPERUSER" : "NOSUPERUSER");
369 :
370 36 : append_ddl_option(&buf, pretty, 4, "%s",
371 36 : roleform->rolinherit ? "INHERIT" : "NOINHERIT");
372 :
373 36 : append_ddl_option(&buf, pretty, 4, "%s",
374 36 : roleform->rolcreaterole ? "CREATEROLE" : "NOCREATEROLE");
375 :
376 36 : append_ddl_option(&buf, pretty, 4, "%s",
377 36 : roleform->rolcreatedb ? "CREATEDB" : "NOCREATEDB");
378 :
379 36 : append_ddl_option(&buf, pretty, 4, "%s",
380 36 : roleform->rolcanlogin ? "LOGIN" : "NOLOGIN");
381 :
382 36 : append_ddl_option(&buf, pretty, 4, "%s",
383 36 : roleform->rolreplication ? "REPLICATION" : "NOREPLICATION");
384 :
385 36 : append_ddl_option(&buf, pretty, 4, "%s",
386 36 : 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 36 : if (roleform->rolconnlimit >= 0)
393 8 : append_ddl_option(&buf, pretty, 4, "CONNECTION LIMIT %d",
394 : roleform->rolconnlimit);
395 :
396 36 : rolevaliduntil = SysCacheGetAttr(AUTHOID, tuple,
397 : Anum_pg_authid_rolvaliduntil,
398 : &isnull);
399 36 : 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 8 : ts = DatumGetTimestampTz(rolevaliduntil);
409 8 : if (TIMESTAMP_NOT_FINITE(ts))
410 0 : EncodeSpecialTimestamp(ts, ts_str);
411 8 : else if (timestamp2tm(ts, &tz, &tm, &fsec, &tzn, NULL) == 0)
412 8 : 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 8 : append_ddl_option(&buf, pretty, 4, "VALID UNTIL %s",
419 : quote_literal_cstr(ts_str));
420 : }
421 :
422 36 : 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 36 : appendStringInfoChar(&buf, ';');
432 :
433 36 : 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 36 : rel = table_open(DbRoleSettingRelationId, AccessShareLock);
443 36 : ScanKeyInit(&scankey,
444 : Anum_pg_db_role_setting_setrole,
445 : BTEqualStrategyNumber, F_OIDEQ,
446 : ObjectIdGetDatum(roleid));
447 36 : scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true,
448 : NULL, 1, &scankey);
449 :
450 44 : while (HeapTupleIsValid(tuple = systable_getnext(scan)))
451 : {
452 8 : Form_pg_db_role_setting setting = (Form_pg_db_role_setting) GETSTRUCT(tuple);
453 8 : Oid datid = setting->setdatabase;
454 : Datum datum;
455 : ArrayType *role_settings;
456 : Datum *settings;
457 : bool *nulls;
458 : int nsettings;
459 8 : 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 8 : if (OidIsValid(datid))
467 : {
468 4 : datname = get_database_name(datid);
469 : /* Database has been dropped; skip all settings in this row. */
470 4 : 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 8 : datum = heap_getattr(tuple, Anum_pg_db_role_setting_setconfig,
479 : RelationGetDescr(rel), &isnull);
480 8 : if (isnull)
481 0 : continue;
482 :
483 8 : role_settings = DatumGetArrayTypeP(datum);
484 :
485 8 : deconstruct_array_builtin(role_settings, TEXTOID, &settings, &nulls, &nsettings);
486 :
487 20 : for (int i = 0; i < nsettings; i++)
488 : {
489 : char *s,
490 : *p;
491 :
492 12 : if (nulls[i])
493 0 : continue;
494 :
495 12 : s = TextDatumGetCString(settings[i]);
496 12 : p = strchr(s, '=');
497 12 : if (p == NULL)
498 : {
499 0 : pfree(s);
500 0 : continue;
501 : }
502 12 : *p++ = '\0';
503 :
504 : /* Build a fresh ALTER ROLE statement for this setting */
505 12 : resetStringInfo(&buf);
506 12 : appendStringInfo(&buf, "ALTER ROLE %s", quote_identifier(rolname));
507 :
508 12 : if (datname != NULL)
509 4 : appendStringInfo(&buf, " IN DATABASE %s",
510 : quote_identifier(datname));
511 :
512 12 : appendStringInfo(&buf, " SET %s TO ",
513 : quote_identifier(s));
514 :
515 12 : append_guc_value(&buf, s, p);
516 :
517 12 : appendStringInfoChar(&buf, ';');
518 :
519 12 : statements = lappend(statements, pstrdup(buf.data));
520 :
521 12 : pfree(s);
522 : }
523 :
524 8 : pfree(settings);
525 8 : pfree(nulls);
526 8 : pfree(role_settings);
527 :
528 8 : if (datname != NULL)
529 4 : pfree(datname);
530 : }
531 :
532 36 : systable_endscan(scan);
533 36 : 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 36 : if (memberships)
541 : {
542 32 : rel = table_open(AuthMemRelationId, AccessShareLock);
543 32 : ScanKeyInit(&scankey,
544 : Anum_pg_auth_members_member,
545 : BTEqualStrategyNumber, F_OIDEQ,
546 : ObjectIdGetDatum(roleid));
547 32 : scan = systable_beginscan(rel, AuthMemMemRoleIndexId, true,
548 : NULL, 1, &scankey);
549 :
550 40 : while (HeapTupleIsValid(tuple = systable_getnext(scan)))
551 : {
552 8 : Form_pg_auth_members memform = (Form_pg_auth_members) GETSTRUCT(tuple);
553 : char *granted_role;
554 : char *grantor;
555 :
556 8 : granted_role = GetUserNameFromId(memform->roleid, false);
557 8 : grantor = GetUserNameFromId(memform->grantor, false);
558 :
559 8 : resetStringInfo(&buf);
560 8 : appendStringInfo(&buf, "GRANT %s TO %s",
561 : quote_identifier(granted_role),
562 : quote_identifier(rolname));
563 24 : appendStringInfo(&buf, " WITH ADMIN %s, INHERIT %s, SET %s",
564 8 : memform->admin_option ? "TRUE" : "FALSE",
565 8 : memform->inherit_option ? "TRUE" : "FALSE",
566 8 : memform->set_option ? "TRUE" : "FALSE");
567 8 : appendStringInfo(&buf, " GRANTED BY %s;",
568 : quote_identifier(grantor));
569 :
570 8 : statements = lappend(statements, pstrdup(buf.data));
571 :
572 8 : pfree(granted_role);
573 8 : pfree(grantor);
574 : }
575 :
576 32 : systable_endscan(scan);
577 32 : table_close(rel, AccessShareLock);
578 : }
579 :
580 36 : pfree(buf.data);
581 36 : pfree(rolname);
582 :
583 36 : 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 104 : pg_get_role_ddl(PG_FUNCTION_ARGS)
597 : {
598 : FuncCallContext *funcctx;
599 : List *statements;
600 :
601 104 : if (SRF_IS_FIRSTCALL())
602 : {
603 : MemoryContext oldcontext;
604 : Oid roleid;
605 48 : DdlOption opts[] = {
606 : {"pretty", DDL_OPT_BOOL},
607 : {"memberships", DDL_OPT_BOOL},
608 : };
609 :
610 48 : funcctx = SRF_FIRSTCALL_INIT();
611 48 : oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
612 :
613 48 : if (PG_ARGISNULL(0))
614 : {
615 4 : MemoryContextSwitchTo(oldcontext);
616 4 : SRF_RETURN_DONE(funcctx);
617 : }
618 :
619 44 : roleid = PG_GETARG_OID(0);
620 44 : parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
621 :
622 88 : statements = pg_get_role_ddl_internal(roleid,
623 44 : opts[0].isset && opts[0].boolval,
624 88 : !opts[1].isset || opts[1].boolval);
625 36 : funcctx->user_fctx = statements;
626 36 : funcctx->max_calls = list_length(statements);
627 :
628 36 : MemoryContextSwitchTo(oldcontext);
629 : }
630 :
631 92 : funcctx = SRF_PERCALL_SETUP();
632 92 : statements = (List *) funcctx->user_fctx;
633 :
634 92 : if (funcctx->call_cntr < funcctx->max_calls)
635 : {
636 : char *stmt;
637 :
638 56 : stmt = list_nth(statements, funcctx->call_cntr);
639 :
640 56 : SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
641 : }
642 : else
643 : {
644 36 : list_free_deep(statements);
645 36 : 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 28 : 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 28 : List *statements = NIL;
669 :
670 28 : tuple = SearchSysCache1(TABLESPACEOID, ObjectIdGetDatum(tsid));
671 28 : if (!HeapTupleIsValid(tuple))
672 4 : ereport(ERROR,
673 : (errcode(ERRCODE_UNDEFINED_OBJECT),
674 : errmsg("tablespace with OID %u does not exist",
675 : tsid)));
676 :
677 24 : tspForm = (Form_pg_tablespace) GETSTRUCT(tuple);
678 24 : spcname = pstrdup(NameStr(tspForm->spcname));
679 :
680 : /* User must have SELECT privilege on pg_tablespace. */
681 24 : if (pg_class_aclcheck(TableSpaceRelationId, GetUserId(), ACL_SELECT) != ACLCHECK_OK)
682 : {
683 4 : ReleaseSysCache(tuple);
684 4 : 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 20 : 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 20 : initStringInfo(&buf);
698 :
699 : /* Start building the CREATE TABLESPACE statement */
700 20 : appendStringInfo(&buf, "CREATE TABLESPACE %s", quote_identifier(spcname));
701 :
702 : /* Add OWNER clause */
703 20 : if (!no_owner)
704 : {
705 16 : spcowner = GetUserNameFromId(tspForm->spcowner, false);
706 16 : append_ddl_option(&buf, pretty, 4, "OWNER %s",
707 : quote_identifier(spcowner));
708 16 : pfree(spcowner);
709 : }
710 :
711 : /* Find tablespace directory path */
712 20 : path = get_tablespace_location(tsid);
713 :
714 : /* Add directory LOCATION (path), if it exists */
715 20 : 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 20 : if (strncmp(PG_TBLSPC_DIR_SLASH, path, strlen(PG_TBLSPC_DIR_SLASH)) == 0)
724 20 : 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 20 : pfree(path);
730 :
731 20 : appendStringInfoChar(&buf, ';');
732 20 : statements = lappend(statements, pstrdup(buf.data));
733 :
734 : /* Check for tablespace options */
735 20 : datum = SysCacheGetAttr(TABLESPACEOID, tuple,
736 : Anum_pg_tablespace_spcoptions, &isNull);
737 20 : if (!isNull)
738 : {
739 12 : resetStringInfo(&buf);
740 12 : appendStringInfo(&buf, "ALTER TABLESPACE %s SET (",
741 : quote_identifier(spcname));
742 12 : get_reloptions(&buf, datum);
743 12 : appendStringInfoString(&buf, ");");
744 12 : statements = lappend(statements, pstrdup(buf.data));
745 : }
746 :
747 20 : ReleaseSysCache(tuple);
748 20 : pfree(spcname);
749 20 : pfree(buf.data);
750 :
751 20 : return statements;
752 : }
753 :
754 : /*
755 : * pg_get_tablespace_ddl_srf - common SRF logic for tablespace DDL
756 : */
757 : static Datum
758 68 : pg_get_tablespace_ddl_srf(FunctionCallInfo fcinfo, Oid tsid, bool isnull)
759 : {
760 : FuncCallContext *funcctx;
761 : List *statements;
762 :
763 68 : if (SRF_IS_FIRSTCALL())
764 : {
765 : MemoryContext oldcontext;
766 36 : DdlOption opts[] = {
767 : {"pretty", DDL_OPT_BOOL},
768 : {"owner", DDL_OPT_BOOL},
769 : };
770 :
771 36 : funcctx = SRF_FIRSTCALL_INIT();
772 36 : oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
773 :
774 36 : if (isnull)
775 : {
776 8 : MemoryContextSwitchTo(oldcontext);
777 8 : SRF_RETURN_DONE(funcctx);
778 : }
779 :
780 28 : parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
781 :
782 56 : statements = pg_get_tablespace_ddl_internal(tsid,
783 28 : opts[0].isset && opts[0].boolval,
784 56 : opts[1].isset && !opts[1].boolval);
785 20 : funcctx->user_fctx = statements;
786 20 : funcctx->max_calls = list_length(statements);
787 :
788 20 : MemoryContextSwitchTo(oldcontext);
789 : }
790 :
791 52 : funcctx = SRF_PERCALL_SETUP();
792 52 : statements = (List *) funcctx->user_fctx;
793 :
794 52 : if (funcctx->call_cntr < funcctx->max_calls)
795 : {
796 : char *stmt;
797 :
798 32 : stmt = (char *) list_nth(statements, funcctx->call_cntr);
799 :
800 32 : SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
801 : }
802 : else
803 : {
804 20 : list_free_deep(statements);
805 20 : 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 16 : pg_get_tablespace_ddl_oid(PG_FUNCTION_ARGS)
815 : {
816 16 : Oid tsid = InvalidOid;
817 : bool isnull;
818 :
819 16 : isnull = PG_ARGISNULL(0);
820 16 : if (!isnull)
821 12 : tsid = PG_GETARG_OID(0);
822 :
823 16 : 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 56 : pg_get_tablespace_ddl_name(PG_FUNCTION_ARGS)
832 : {
833 56 : Oid tsid = InvalidOid;
834 : Name tspname;
835 : bool isnull;
836 :
837 56 : isnull = PG_ARGISNULL(0);
838 :
839 56 : if (!isnull)
840 : {
841 52 : tspname = PG_GETARG_NAME(0);
842 52 : tsid = get_tablespace_oid(NameStr(*tspname), false);
843 : }
844 :
845 52 : 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 16 : 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 16 : List *statements = NIL;
873 : AclResult aclresult;
874 :
875 16 : tuple = SearchSysCache1(DATABASEOID, ObjectIdGetDatum(dbid));
876 16 : 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 16 : aclresult = object_aclcheck(DatabaseRelationId, dbid, GetUserId(), ACL_CONNECT);
883 16 : if (aclresult != ACLCHECK_OK)
884 4 : aclcheck_error(aclresult, OBJECT_DATABASE,
885 4 : get_database_name(dbid));
886 :
887 12 : dbform = (Form_pg_database) GETSTRUCT(tuple);
888 12 : dbname = pstrdup(NameStr(dbform->datname));
889 :
890 : /*
891 : * We don't support generating DDL for system databases. The primary
892 : * reason for this is that users shouldn't be recreating them.
893 : */
894 12 : if (strcmp(dbname, "template0") == 0 || strcmp(dbname, "template1") == 0)
895 0 : ereport(ERROR,
896 : (errcode(ERRCODE_RESERVED_NAME),
897 : errmsg("database \"%s\" is a system database", dbname),
898 : errdetail("DDL generation is not supported for template0 and template1.")));
899 :
900 12 : initStringInfo(&buf);
901 :
902 : /* --- Build CREATE DATABASE statement --- */
903 12 : appendStringInfo(&buf, "CREATE DATABASE %s", quote_identifier(dbname));
904 :
905 : /*
906 : * Always use template0: the target database already contains the catalog
907 : * data from whatever template was used originally, so we must start from
908 : * the pristine template to avoid duplication.
909 : */
910 12 : append_ddl_option(&buf, pretty, 4, "WITH TEMPLATE = template0");
911 :
912 : /* ENCODING */
913 12 : encoding = pg_encoding_to_char(dbform->encoding);
914 12 : if (strlen(encoding) > 0)
915 12 : append_ddl_option(&buf, pretty, 4, "ENCODING = %s",
916 : quote_literal_cstr(encoding));
917 :
918 : /* LOCALE_PROVIDER */
919 12 : if (dbform->datlocprovider == COLLPROVIDER_BUILTIN ||
920 9 : dbform->datlocprovider == COLLPROVIDER_ICU ||
921 9 : dbform->datlocprovider == COLLPROVIDER_LIBC)
922 12 : append_ddl_option(&buf, pretty, 4, "LOCALE_PROVIDER = %s",
923 12 : collprovider_name(dbform->datlocprovider));
924 : else
925 0 : ereport(ERROR,
926 : (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
927 : errmsg("unrecognized locale provider: %c",
928 : dbform->datlocprovider)));
929 :
930 : /* LOCALE, LC_COLLATE, LC_CTYPE */
931 12 : datum = SysCacheGetAttr(DATABASEOID, tuple,
932 : Anum_pg_database_datcollate, &isnull);
933 12 : collate = isnull ? NULL : TextDatumGetCString(datum);
934 12 : datum = SysCacheGetAttr(DATABASEOID, tuple,
935 : Anum_pg_database_datctype, &isnull);
936 12 : ctype = isnull ? NULL : TextDatumGetCString(datum);
937 12 : if (collate != NULL && ctype != NULL && strcmp(collate, ctype) == 0)
938 : {
939 12 : append_ddl_option(&buf, pretty, 4, "LOCALE = %s",
940 : quote_literal_cstr(collate));
941 : }
942 : else
943 : {
944 0 : if (collate != NULL)
945 0 : append_ddl_option(&buf, pretty, 4, "LC_COLLATE = %s",
946 : quote_literal_cstr(collate));
947 0 : if (ctype != NULL)
948 0 : append_ddl_option(&buf, pretty, 4, "LC_CTYPE = %s",
949 : quote_literal_cstr(ctype));
950 : }
951 :
952 : /* LOCALE (provider-specific) */
953 12 : datum = SysCacheGetAttr(DATABASEOID, tuple,
954 : Anum_pg_database_datlocale, &isnull);
955 12 : if (!isnull)
956 : {
957 3 : const char *locale = TextDatumGetCString(datum);
958 :
959 3 : if (dbform->datlocprovider == COLLPROVIDER_BUILTIN)
960 3 : append_ddl_option(&buf, pretty, 4, "BUILTIN_LOCALE = %s",
961 : quote_literal_cstr(locale));
962 0 : else if (dbform->datlocprovider == COLLPROVIDER_ICU)
963 0 : append_ddl_option(&buf, pretty, 4, "ICU_LOCALE = %s",
964 : quote_literal_cstr(locale));
965 : }
966 :
967 : /* ICU_RULES */
968 12 : datum = SysCacheGetAttr(DATABASEOID, tuple,
969 : Anum_pg_database_daticurules, &isnull);
970 12 : if (!isnull && dbform->datlocprovider == COLLPROVIDER_ICU)
971 0 : append_ddl_option(&buf, pretty, 4, "ICU_RULES = %s",
972 0 : quote_literal_cstr(TextDatumGetCString(datum)));
973 :
974 : /* TABLESPACE */
975 12 : if (!no_tablespace && OidIsValid(dbform->dattablespace))
976 : {
977 8 : char *spcname = get_tablespace_name(dbform->dattablespace);
978 :
979 8 : if (pg_strcasecmp(spcname, "pg_default") != 0)
980 0 : append_ddl_option(&buf, pretty, 4, "TABLESPACE = %s",
981 : quote_identifier(spcname));
982 : }
983 :
984 12 : appendStringInfoChar(&buf, ';');
985 12 : statements = lappend(statements, pstrdup(buf.data));
986 :
987 : /* OWNER */
988 12 : if (!no_owner && OidIsValid(dbform->datdba))
989 : {
990 12 : char *owner = GetUserNameFromId(dbform->datdba, false);
991 :
992 12 : resetStringInfo(&buf);
993 12 : appendStringInfo(&buf, "ALTER DATABASE %s OWNER TO %s;",
994 : quote_identifier(dbname), quote_identifier(owner));
995 12 : pfree(owner);
996 12 : statements = lappend(statements, pstrdup(buf.data));
997 : }
998 :
999 : /* CONNECTION LIMIT */
1000 12 : if (dbform->datconnlimit != -1)
1001 : {
1002 12 : resetStringInfo(&buf);
1003 12 : appendStringInfo(&buf, "ALTER DATABASE %s CONNECTION LIMIT = %d;",
1004 : quote_identifier(dbname), dbform->datconnlimit);
1005 12 : statements = lappend(statements, pstrdup(buf.data));
1006 : }
1007 :
1008 : /* IS_TEMPLATE */
1009 12 : if (dbform->datistemplate)
1010 : {
1011 0 : resetStringInfo(&buf);
1012 0 : appendStringInfo(&buf, "ALTER DATABASE %s IS_TEMPLATE = true;",
1013 : quote_identifier(dbname));
1014 0 : statements = lappend(statements, pstrdup(buf.data));
1015 : }
1016 :
1017 : /* ALLOW_CONNECTIONS */
1018 12 : if (!dbform->datallowconn)
1019 : {
1020 0 : resetStringInfo(&buf);
1021 0 : appendStringInfo(&buf, "ALTER DATABASE %s ALLOW_CONNECTIONS = false;",
1022 : quote_identifier(dbname));
1023 0 : statements = lappend(statements, pstrdup(buf.data));
1024 : }
1025 :
1026 12 : ReleaseSysCache(tuple);
1027 :
1028 : /*
1029 : * Now scan pg_db_role_setting for ALTER DATABASE SET configurations.
1030 : *
1031 : * It is only database-wide (setrole = 0). It generates one ALTER
1032 : * statement per setting.
1033 : */
1034 12 : rel = table_open(DbRoleSettingRelationId, AccessShareLock);
1035 12 : ScanKeyInit(&scankey[0],
1036 : Anum_pg_db_role_setting_setdatabase,
1037 : BTEqualStrategyNumber, F_OIDEQ,
1038 : ObjectIdGetDatum(dbid));
1039 12 : ScanKeyInit(&scankey[1],
1040 : Anum_pg_db_role_setting_setrole,
1041 : BTEqualStrategyNumber, F_OIDEQ,
1042 : ObjectIdGetDatum(InvalidOid));
1043 :
1044 12 : scan = systable_beginscan(rel, DbRoleSettingDatidRolidIndexId, true,
1045 : NULL, 2, scankey);
1046 :
1047 24 : while (HeapTupleIsValid(tuple = systable_getnext(scan)))
1048 : {
1049 : ArrayType *dbconfig;
1050 : Datum *settings;
1051 : bool *nulls;
1052 : int nsettings;
1053 :
1054 : /*
1055 : * The setconfig column is a text array in "name=value" format. It
1056 : * should never be null for a valid row, but be defensive.
1057 : */
1058 12 : datum = heap_getattr(tuple, Anum_pg_db_role_setting_setconfig,
1059 : RelationGetDescr(rel), &isnull);
1060 12 : if (isnull)
1061 0 : continue;
1062 :
1063 12 : dbconfig = DatumGetArrayTypeP(datum);
1064 :
1065 12 : deconstruct_array_builtin(dbconfig, TEXTOID, &settings, &nulls, &nsettings);
1066 :
1067 24 : for (int i = 0; i < nsettings; i++)
1068 : {
1069 : char *s,
1070 : *p;
1071 :
1072 12 : if (nulls[i])
1073 0 : continue;
1074 :
1075 12 : s = TextDatumGetCString(settings[i]);
1076 12 : p = strchr(s, '=');
1077 12 : if (p == NULL)
1078 : {
1079 0 : pfree(s);
1080 0 : continue;
1081 : }
1082 12 : *p++ = '\0';
1083 :
1084 12 : resetStringInfo(&buf);
1085 12 : appendStringInfo(&buf, "ALTER DATABASE %s SET %s TO ",
1086 : quote_identifier(dbname),
1087 : quote_identifier(s));
1088 :
1089 12 : append_guc_value(&buf, s, p);
1090 :
1091 12 : appendStringInfoChar(&buf, ';');
1092 :
1093 12 : statements = lappend(statements, pstrdup(buf.data));
1094 :
1095 12 : pfree(s);
1096 : }
1097 :
1098 12 : pfree(settings);
1099 12 : pfree(nulls);
1100 12 : pfree(dbconfig);
1101 : }
1102 :
1103 12 : systable_endscan(scan);
1104 12 : table_close(rel, AccessShareLock);
1105 :
1106 12 : pfree(buf.data);
1107 12 : pfree(dbname);
1108 :
1109 12 : return statements;
1110 : }
1111 :
1112 : /*
1113 : * pg_get_database_ddl
1114 : * Return DDL to recreate a database as a set of text rows.
1115 : */
1116 : Datum
1117 76 : pg_get_database_ddl(PG_FUNCTION_ARGS)
1118 : {
1119 : FuncCallContext *funcctx;
1120 : List *statements;
1121 :
1122 76 : if (SRF_IS_FIRSTCALL())
1123 : {
1124 : MemoryContext oldcontext;
1125 : Oid dbid;
1126 28 : DdlOption opts[] = {
1127 : {"pretty", DDL_OPT_BOOL},
1128 : {"owner", DDL_OPT_BOOL},
1129 : {"tablespace", DDL_OPT_BOOL},
1130 : };
1131 :
1132 28 : funcctx = SRF_FIRSTCALL_INIT();
1133 28 : oldcontext = MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
1134 :
1135 28 : if (PG_ARGISNULL(0))
1136 : {
1137 4 : MemoryContextSwitchTo(oldcontext);
1138 4 : SRF_RETURN_DONE(funcctx);
1139 : }
1140 :
1141 24 : dbid = PG_GETARG_OID(0);
1142 24 : parse_ddl_options(fcinfo, 1, opts, lengthof(opts));
1143 :
1144 48 : statements = pg_get_database_ddl_internal(dbid,
1145 16 : opts[0].isset && opts[0].boolval,
1146 16 : opts[1].isset && !opts[1].boolval,
1147 48 : opts[2].isset && !opts[2].boolval);
1148 12 : funcctx->user_fctx = statements;
1149 12 : funcctx->max_calls = list_length(statements);
1150 :
1151 12 : MemoryContextSwitchTo(oldcontext);
1152 : }
1153 :
1154 60 : funcctx = SRF_PERCALL_SETUP();
1155 60 : statements = (List *) funcctx->user_fctx;
1156 :
1157 60 : if (funcctx->call_cntr < funcctx->max_calls)
1158 : {
1159 : char *stmt;
1160 :
1161 48 : stmt = list_nth(statements, funcctx->call_cntr);
1162 :
1163 48 : SRF_RETURN_NEXT(funcctx, CStringGetTextDatum(stmt));
1164 : }
1165 : else
1166 : {
1167 12 : list_free_deep(statements);
1168 12 : SRF_RETURN_DONE(funcctx);
1169 : }
1170 : }
|
