Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit a7a7be1

Browse filesBrowse files
nmischnmisch
committed
Dump public schema ownership and security labels.
As a side effect, this corrects dumps of public schema ACLs in databases where the DBA dropped and recreated that schema. Reviewed by Asif Rehman. Discussion: https://postgr.es/m/20201229134924.GA1431748@rfd.leadboat.com
1 parent 14b2ffa commit a7a7be1
Copy full SHA for a7a7be1

File tree

7 files changed

+177
-43
lines changed
Filter options

7 files changed

+177
-43
lines changed

‎src/bin/pg_dump/dumputils.c

Copy file name to clipboardExpand all lines: src/bin/pg_dump/dumputils.c
+10-5Lines changed: 10 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -725,6 +725,7 @@ void
725725
buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
726726
PQExpBuffer init_acl_subquery, PQExpBuffer init_racl_subquery,
727727
const char *acl_column, const char *acl_owner,
728+
const char *initprivs_expr,
728729
const char *obj_kind, bool binary_upgrade)
729730
{
730731
/*
@@ -765,23 +766,25 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
765766
"WITH ORDINALITY AS perm(acl,row_n) "
766767
"WHERE NOT EXISTS ( "
767768
"SELECT 1 FROM "
768-
"pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s))) "
769+
"pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) "
769770
"AS init(init_acl) WHERE acl = init_acl)) as foo)",
770771
acl_column,
771772
obj_kind,
772773
acl_owner,
774+
initprivs_expr,
773775
obj_kind,
774776
acl_owner);
775777

776778
printfPQExpBuffer(racl_subquery,
777779
"(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM "
778780
"(SELECT acl, row_n FROM "
779-
"pg_catalog.unnest(coalesce(pip.initprivs,pg_catalog.acldefault(%s,%s))) "
781+
"pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) "
780782
"WITH ORDINALITY AS initp(acl,row_n) "
781783
"WHERE NOT EXISTS ( "
782784
"SELECT 1 FROM "
783785
"pg_catalog.unnest(coalesce(%s,pg_catalog.acldefault(%s,%s))) "
784786
"AS permp(orig_acl) WHERE acl = orig_acl)) as foo)",
787+
initprivs_expr,
785788
obj_kind,
786789
acl_owner,
787790
acl_column,
@@ -807,12 +810,13 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
807810
printfPQExpBuffer(init_acl_subquery,
808811
"CASE WHEN privtype = 'e' THEN "
809812
"(SELECT pg_catalog.array_agg(acl ORDER BY row_n) FROM "
810-
"(SELECT acl, row_n FROM pg_catalog.unnest(pip.initprivs) "
813+
"(SELECT acl, row_n FROM pg_catalog.unnest(%s) "
811814
"WITH ORDINALITY AS initp(acl,row_n) "
812815
"WHERE NOT EXISTS ( "
813816
"SELECT 1 FROM "
814817
"pg_catalog.unnest(pg_catalog.acldefault(%s,%s)) "
815818
"AS privm(orig_acl) WHERE acl = orig_acl)) as foo) END",
819+
initprivs_expr,
816820
obj_kind,
817821
acl_owner);
818822

@@ -823,10 +827,11 @@ buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
823827
"pg_catalog.unnest(pg_catalog.acldefault(%s,%s)) "
824828
"WITH ORDINALITY AS privp(acl,row_n) "
825829
"WHERE NOT EXISTS ( "
826-
"SELECT 1 FROM pg_catalog.unnest(pip.initprivs) "
830+
"SELECT 1 FROM pg_catalog.unnest(%s) "
827831
"AS initp(init_acl) WHERE acl = init_acl)) as foo) END",
828832
obj_kind,
829-
acl_owner);
833+
acl_owner,
834+
initprivs_expr);
830835
}
831836
else
832837
{

‎src/bin/pg_dump/dumputils.h

Copy file name to clipboardExpand all lines: src/bin/pg_dump/dumputils.h
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -54,6 +54,7 @@ extern void emitShSecLabels(PGconn *conn, PGresult *res,
5454
extern void buildACLQueries(PQExpBuffer acl_subquery, PQExpBuffer racl_subquery,
5555
PQExpBuffer init_acl_subquery, PQExpBuffer init_racl_subquery,
5656
const char *acl_column, const char *acl_owner,
57+
const char *initprivs_expr,
5758
const char *obj_kind, bool binary_upgrade);
5859

5960
extern bool variable_is_guc_list_quote(const char *name);

‎src/bin/pg_dump/pg_backup_archiver.c

Copy file name to clipboardExpand all lines: src/bin/pg_dump/pg_backup_archiver.c
+14-7Lines changed: 14 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -3538,10 +3538,12 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData)
35383538
* Actually print the definition.
35393539
*
35403540
* Really crude hack for suppressing AUTHORIZATION clause that old pg_dump
3541-
* versions put into CREATE SCHEMA. We have to do this when --no-owner
3542-
* mode is selected. This is ugly, but I see no other good way ...
3541+
* versions put into CREATE SCHEMA. Don't mutate the variant for schema
3542+
* "public" that is a comment. We have to do this when --no-owner mode is
3543+
* selected. This is ugly, but I see no other good way ...
35433544
*/
3544-
if (ropt->noOwner && strcmp(te->desc, "SCHEMA") == 0)
3545+
if (ropt->noOwner &&
3546+
strcmp(te->desc, "SCHEMA") == 0 && strncmp(te->defn, "--", 2) != 0)
35453547
{
35463548
ahprintf(AH, "CREATE SCHEMA %s;\n\n\n", fmtId(te->tag));
35473549
}
@@ -3553,11 +3555,16 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, bool isData)
35533555

35543556
/*
35553557
* If we aren't using SET SESSION AUTH to determine ownership, we must
3556-
* instead issue an ALTER OWNER command. We assume that anything without
3557-
* a DROP command is not a separately ownable object. All the categories
3558-
* with DROP commands must appear in one list or the other.
3558+
* instead issue an ALTER OWNER command. Schema "public" is special; when
3559+
* a dump emits a comment in lieu of creating it, we use ALTER OWNER even
3560+
* when using SET SESSION for all other objects. We assume that anything
3561+
* without a DROP command is not a separately ownable object. All the
3562+
* categories with DROP commands must appear in one list or the other.
35593563
*/
3560-
if (!ropt->noOwner && !ropt->use_setsessauth &&
3564+
if (!ropt->noOwner &&
3565+
(!ropt->use_setsessauth ||
3566+
(strcmp(te->desc, "SCHEMA") == 0 &&
3567+
strncmp(te->defn, "--", 2) == 0)) &&
35613568
te->owner && strlen(te->owner) > 0 &&
35623569
te->dropStmt && strlen(te->dropStmt) > 0)
35633570
{

‎src/bin/pg_dump/pg_dump.c

Copy file name to clipboardExpand all lines: src/bin/pg_dump/pg_dump.c
+71-27Lines changed: 71 additions & 27 deletions
Original file line numberDiff line numberDiff line change
@@ -44,6 +44,7 @@
4444
#include "catalog/pg_aggregate_d.h"
4545
#include "catalog/pg_am_d.h"
4646
#include "catalog/pg_attribute_d.h"
47+
#include "catalog/pg_authid_d.h"
4748
#include "catalog/pg_cast_d.h"
4849
#include "catalog/pg_class_d.h"
4950
#include "catalog/pg_default_acl_d.h"
@@ -1598,6 +1599,13 @@ checkExtensionMembership(DumpableObject *dobj, Archive *fout)
15981599
static void
15991600
selectDumpableNamespace(NamespaceInfo *nsinfo, Archive *fout)
16001601
{
1602+
/*
1603+
* DUMP_COMPONENT_DEFINITION typically implies a CREATE SCHEMA statement
1604+
* and (for --clean) a DROP SCHEMA statement. (In the absence of
1605+
* DUMP_COMPONENT_DEFINITION, this value is irrelevant.)
1606+
*/
1607+
nsinfo->create = true;
1608+
16011609
/*
16021610
* If specific tables are being dumped, do not dump any complete
16031611
* namespaces. If specific namespaces are being dumped, dump just those
@@ -1633,10 +1641,15 @@ selectDumpableNamespace(NamespaceInfo *nsinfo, Archive *fout)
16331641
* no-mans-land between being a system object and a user object. We
16341642
* don't want to dump creation or comment commands for it, because
16351643
* that complicates matters for non-superuser use of pg_dump. But we
1636-
* should dump any ACL changes that have occurred for it, and of
1637-
* course we should dump contained objects.
1644+
* should dump any ownership changes, security labels, and ACL
1645+
* changes, and of course we should dump contained objects. pg_dump
1646+
* ties ownership to DUMP_COMPONENT_DEFINITION. Hence, unless the
1647+
* owner is the default, dump a "definition" bearing just a comment.
16381648
*/
1639-
nsinfo->dobj.dump = DUMP_COMPONENT_ACL;
1649+
nsinfo->create = false;
1650+
nsinfo->dobj.dump = DUMP_COMPONENT_ALL & ~DUMP_COMPONENT_COMMENT;
1651+
if (nsinfo->nspowner == BOOTSTRAP_SUPERUSERID)
1652+
nsinfo->dobj.dump &= ~DUMP_COMPONENT_DEFINITION;
16401653
nsinfo->dobj.dump_contains = DUMP_COMPONENT_ALL;
16411654
}
16421655
else
@@ -3428,8 +3441,8 @@ getBlobs(Archive *fout)
34283441
PQExpBuffer init_racl_subquery = createPQExpBuffer();
34293442

34303443
buildACLQueries(acl_subquery, racl_subquery, init_acl_subquery,
3431-
init_racl_subquery, "l.lomacl", "l.lomowner", "'L'",
3432-
dopt->binary_upgrade);
3444+
init_racl_subquery, "l.lomacl", "l.lomowner",
3445+
"pip.initprivs", "'L'", dopt->binary_upgrade);
34333446

34343447
appendPQExpBuffer(blobQry,
34353448
"SELECT l.oid, (%s l.lomowner) AS rolname, "
@@ -4830,6 +4843,7 @@ getNamespaces(Archive *fout, int *numNamespaces)
48304843
int i_tableoid;
48314844
int i_oid;
48324845
int i_nspname;
4846+
int i_nspowner;
48334847
int i_rolname;
48344848
int i_nspacl;
48354849
int i_rnspacl;
@@ -4849,11 +4863,27 @@ getNamespaces(Archive *fout, int *numNamespaces)
48494863
PQExpBuffer init_acl_subquery = createPQExpBuffer();
48504864
PQExpBuffer init_racl_subquery = createPQExpBuffer();
48514865

4866+
/*
4867+
* Bypass pg_init_privs.initprivs for the public schema. Dropping and
4868+
* recreating the schema detaches it from its pg_init_privs row, but
4869+
* an empty destination database starts with this ACL nonetheless.
4870+
* Also, we support dump/reload of public schema ownership changes.
4871+
* ALTER SCHEMA OWNER filters nspacl through aclnewowner(), but
4872+
* initprivs continues to reflect the initial owner (the bootstrap
4873+
* superuser). Hence, synthesize the value that nspacl will have
4874+
* after the restore's ALTER SCHEMA OWNER.
4875+
*/
48524876
buildACLQueries(acl_subquery, racl_subquery, init_acl_subquery,
4853-
init_racl_subquery, "n.nspacl", "n.nspowner", "'n'",
4854-
dopt->binary_upgrade);
4877+
init_racl_subquery, "n.nspacl", "n.nspowner",
4878+
"CASE WHEN n.nspname = 'public' THEN array["
4879+
" format('%s=UC/%s', "
4880+
" n.nspowner::regrole, n.nspowner::regrole),"
4881+
" format('=UC/%s', n.nspowner::regrole)]::aclitem[] "
4882+
"ELSE pip.initprivs END",
4883+
"'n'", dopt->binary_upgrade);
48554884

48564885
appendPQExpBuffer(query, "SELECT n.tableoid, n.oid, n.nspname, "
4886+
"n.nspowner, "
48574887
"(%s nspowner) AS rolname, "
48584888
"%s as nspacl, "
48594889
"%s as rnspacl, "
@@ -4878,7 +4908,7 @@ getNamespaces(Archive *fout, int *numNamespaces)
48784908
destroyPQExpBuffer(init_racl_subquery);
48794909
}
48804910
else
4881-
appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, "
4911+
appendPQExpBuffer(query, "SELECT tableoid, oid, nspname, nspowner, "
48824912
"(%s nspowner) AS rolname, "
48834913
"nspacl, NULL as rnspacl, "
48844914
"NULL AS initnspacl, NULL as initrnspacl "
@@ -4894,6 +4924,7 @@ getNamespaces(Archive *fout, int *numNamespaces)
48944924
i_tableoid = PQfnumber(res, "tableoid");
48954925
i_oid = PQfnumber(res, "oid");
48964926
i_nspname = PQfnumber(res, "nspname");
4927+
i_nspowner = PQfnumber(res, "nspowner");
48974928
i_rolname = PQfnumber(res, "rolname");
48984929
i_nspacl = PQfnumber(res, "nspacl");
48994930
i_rnspacl = PQfnumber(res, "rnspacl");
@@ -4907,6 +4938,7 @@ getNamespaces(Archive *fout, int *numNamespaces)
49074938
nsinfo[i].dobj.catId.oid = atooid(PQgetvalue(res, i, i_oid));
49084939
AssignDumpId(&nsinfo[i].dobj);
49094940
nsinfo[i].dobj.name = pg_strdup(PQgetvalue(res, i, i_nspname));
4941+
nsinfo[i].nspowner = atooid(PQgetvalue(res, i, i_nspowner));
49104942
nsinfo[i].rolname = pg_strdup(PQgetvalue(res, i, i_rolname));
49114943
nsinfo[i].nspacl = pg_strdup(PQgetvalue(res, i, i_nspacl));
49124944
nsinfo[i].rnspacl = pg_strdup(PQgetvalue(res, i, i_rnspacl));
@@ -5098,8 +5130,8 @@ getTypes(Archive *fout, int *numTypes)
50985130
PQExpBuffer initracl_subquery = createPQExpBuffer();
50995131

51005132
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
5101-
initracl_subquery, "t.typacl", "t.typowner", "'T'",
5102-
dopt->binary_upgrade);
5133+
initracl_subquery, "t.typacl", "t.typowner",
5134+
"pip.initprivs", "'T'", dopt->binary_upgrade);
51035135

51045136
appendPQExpBuffer(query, "SELECT t.tableoid, t.oid, t.typname, "
51055137
"t.typnamespace, "
@@ -5800,8 +5832,8 @@ getAggregates(Archive *fout, int *numAggs)
58005832
const char *agg_check;
58015833

58025834
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
5803-
initracl_subquery, "p.proacl", "p.proowner", "'f'",
5804-
dopt->binary_upgrade);
5835+
initracl_subquery, "p.proacl", "p.proowner",
5836+
"pip.initprivs", "'f'", dopt->binary_upgrade);
58055837

58065838
agg_check = (fout->remoteVersion >= 110000 ? "p.prokind = 'a'"
58075839
: "p.proisagg");
@@ -6013,8 +6045,8 @@ getFuncs(Archive *fout, int *numFuncs)
60136045
const char *not_agg_check;
60146046

60156047
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
6016-
initracl_subquery, "p.proacl", "p.proowner", "'f'",
6017-
dopt->binary_upgrade);
6048+
initracl_subquery, "p.proacl", "p.proowner",
6049+
"pip.initprivs", "'f'", dopt->binary_upgrade);
60186050

60196051
not_agg_check = (fout->remoteVersion >= 110000 ? "p.prokind <> 'a'"
60206052
: "NOT p.proisagg");
@@ -6310,13 +6342,14 @@ getTables(Archive *fout, int *numTables)
63106342

63116343
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
63126344
initracl_subquery, "c.relacl", "c.relowner",
6345+
"pip.initprivs",
63136346
"CASE WHEN c.relkind = " CppAsString2(RELKIND_SEQUENCE)
63146347
" THEN 's' ELSE 'r' END::\"char\"",
63156348
dopt->binary_upgrade);
63166349

63176350
buildACLQueries(attacl_subquery, attracl_subquery, attinitacl_subquery,
6318-
attinitracl_subquery, "at.attacl", "c.relowner", "'c'",
6319-
dopt->binary_upgrade);
6351+
attinitracl_subquery, "at.attacl", "c.relowner",
6352+
"pip.initprivs", "'c'", dopt->binary_upgrade);
63206353

63216354
appendPQExpBuffer(query,
63226355
"SELECT c.tableoid, c.oid, c.relname, "
@@ -8241,8 +8274,8 @@ getProcLangs(Archive *fout, int *numProcLangs)
82418274
PQExpBuffer initracl_subquery = createPQExpBuffer();
82428275

82438276
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
8244-
initracl_subquery, "l.lanacl", "l.lanowner", "'l'",
8245-
dopt->binary_upgrade);
8277+
initracl_subquery, "l.lanacl", "l.lanowner",
8278+
"pip.initprivs", "'l'", dopt->binary_upgrade);
82468279

82478280
/* pg_language has a laninline column */
82488281
appendPQExpBuffer(query, "SELECT l.tableoid, l.oid, "
@@ -9432,8 +9465,8 @@ getForeignDataWrappers(Archive *fout, int *numForeignDataWrappers)
94329465
PQExpBuffer initracl_subquery = createPQExpBuffer();
94339466

94349467
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
9435-
initracl_subquery, "f.fdwacl", "f.fdwowner", "'F'",
9436-
dopt->binary_upgrade);
9468+
initracl_subquery, "f.fdwacl", "f.fdwowner",
9469+
"pip.initprivs", "'F'", dopt->binary_upgrade);
94379470

94389471
appendPQExpBuffer(query, "SELECT f.tableoid, f.oid, f.fdwname, "
94399472
"(%s f.fdwowner) AS rolname, "
@@ -9599,8 +9632,8 @@ getForeignServers(Archive *fout, int *numForeignServers)
95999632
PQExpBuffer initracl_subquery = createPQExpBuffer();
96009633

96019634
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
9602-
initracl_subquery, "f.srvacl", "f.srvowner", "'S'",
9603-
dopt->binary_upgrade);
9635+
initracl_subquery, "f.srvacl", "f.srvowner",
9636+
"pip.initprivs", "'S'", dopt->binary_upgrade);
96049637

96059638
appendPQExpBuffer(query, "SELECT f.tableoid, f.oid, f.srvname, "
96069639
"(%s f.srvowner) AS rolname, "
@@ -9746,6 +9779,7 @@ getDefaultACLs(Archive *fout, int *numDefaultACLs)
97469779

97479780
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
97489781
initracl_subquery, "defaclacl", "defaclrole",
9782+
"pip.initprivs",
97499783
"CASE WHEN defaclobjtype = 'S' THEN 's' ELSE defaclobjtype END::\"char\"",
97509784
dopt->binary_upgrade);
97519785

@@ -10363,9 +10397,19 @@ dumpNamespace(Archive *fout, const NamespaceInfo *nspinfo)
1036310397

1036410398
qnspname = pg_strdup(fmtId(nspinfo->dobj.name));
1036510399

10366-
appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
10367-
10368-
appendPQExpBuffer(q, "CREATE SCHEMA %s;\n", qnspname);
10400+
if (nspinfo->create)
10401+
{
10402+
appendPQExpBuffer(delq, "DROP SCHEMA %s;\n", qnspname);
10403+
appendPQExpBuffer(q, "CREATE SCHEMA %s;\n", qnspname);
10404+
}
10405+
else
10406+
{
10407+
/* see selectDumpableNamespace() */
10408+
appendPQExpBufferStr(delq,
10409+
"-- *not* dropping schema, since initdb creates it\n");
10410+
appendPQExpBufferStr(q,
10411+
"-- *not* creating schema, since initdb creates it\n");
10412+
}
1036910413

1037010414
if (dopt->binary_upgrade)
1037110415
binary_upgrade_extension_member(q, &nspinfo->dobj,
@@ -15556,8 +15600,8 @@ dumpTable(Archive *fout, const TableInfo *tbinfo)
1555615600
PQExpBuffer initracl_subquery = createPQExpBuffer();
1555715601

1555815602
buildACLQueries(acl_subquery, racl_subquery, initacl_subquery,
15559-
initracl_subquery, "at.attacl", "c.relowner", "'c'",
15560-
dopt->binary_upgrade);
15603+
initracl_subquery, "at.attacl", "c.relowner",
15604+
"pip.initprivs", "'c'", dopt->binary_upgrade);
1556115605

1556215606
appendPQExpBuffer(query,
1556315607
"SELECT at.attname, "

‎src/bin/pg_dump/pg_dump.h

Copy file name to clipboardExpand all lines: src/bin/pg_dump/pg_dump.h
+2Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -142,6 +142,8 @@ typedef struct _dumpableObject
142142
typedef struct _namespaceInfo
143143
{
144144
DumpableObject dobj;
145+
bool create; /* CREATE SCHEMA, or just set owner? */
146+
Oid nspowner;
145147
char *rolname; /* name of owner, or empty string */
146148
char *nspacl;
147149
char *rnspacl;

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.