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