Movatterモバイル変換

[0]ホーム
URL:

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

 Sign up
Appearance settings

Commit47f5bb9

Browse files
committed
Correctly dump database and tablespace ACLs
Dump out the appropriate GRANT/REVOKE commands for databases andtablespaces from pg_dumpall to replicate what the current state is.This was broken during the changes to buildACLCommands for 9.6+servers for pg_init_privs.
1 parentfe03f28 commit47f5bb9

File tree

3 files changed

+231
-44
lines changed

3 files changed

+231
-44
lines changed

‎src/bin/initdb/initdb.c‎

Lines changed: 3 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -2002,6 +2002,9 @@ setup_dictionary(FILE *cmdfd)
20022002
* time. This is used by pg_dump to allow users to change privileges
20032003
* on catalog objects and to have those privilege changes preserved
20042004
* across dump/reload and pg_upgrade.
2005+
*
2006+
* Note that pg_init_privs is only for per-database objects and therefore
2007+
* we don't include databases or tablespaces.
20052008
*/
20062009
staticvoid
20072010
setup_privileges(FILE*cmdfd)
@@ -2111,30 +2114,6 @@ setup_privileges(FILE *cmdfd)
21112114
" (objoid, classoid, objsubid, initprivs, privtype)"
21122115
" SELECT"
21132116
" oid,"
2114-
" (SELECT oid FROM pg_class WHERE relname = 'pg_database'),"
2115-
" 0,"
2116-
" datacl,"
2117-
" 'i'"
2118-
" FROM"
2119-
" pg_database"
2120-
" WHERE"
2121-
" datacl IS NOT NULL;",
2122-
"INSERT INTO pg_init_privs "
2123-
" (objoid, classoid, objsubid, initprivs, privtype)"
2124-
" SELECT"
2125-
" oid,"
2126-
" (SELECT oid FROM pg_class WHERE relname = 'pg_tablespace'),"
2127-
" 0,"
2128-
" spcacl,"
2129-
" 'i'"
2130-
" FROM"
2131-
" pg_tablespace"
2132-
" WHERE"
2133-
" spcacl IS NOT NULL;",
2134-
"INSERT INTO pg_init_privs "
2135-
" (objoid, classoid, objsubid, initprivs, privtype)"
2136-
" SELECT"
2137-
" oid,"
21382117
" (SELECT oid FROM pg_class WHERE "
21392118
" relname = 'pg_foreign_data_wrapper'),"
21402119
" 0,"

‎src/bin/pg_dump/pg_dumpall.c‎

Lines changed: 75 additions & 20 deletions
Original file line numberDiff line numberDiff line change
@@ -1072,11 +1072,35 @@ dumpTablespaces(PGconn *conn)
10721072
/*
10731073
* Get all tablespaces except built-in ones (which we assume are named
10741074
* pg_xxx)
1075+
*
1076+
* For the tablespace ACLs, as of 9.6, we extract both the positive (as
1077+
* spcacl) and negative (as rspcacl) ACLs, relative to the default ACL for
1078+
* tablespaces, which are then passed to buildACLCommands() below.
1079+
*
1080+
* See buildACLQueries() and buildACLCommands().
1081+
*
1082+
* Note that we do not support initial privileges (pg_init_privs) on
1083+
* tablespaces.
10751084
*/
1076-
if (server_version >=90200)
1085+
if (server_version >=90600)
10771086
res=executeQuery(conn,"SELECT oid, spcname, "
10781087
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
1079-
"pg_catalog.pg_tablespace_location(oid), spcacl, "
1088+
"pg_catalog.pg_tablespace_location(oid), "
1089+
"(SELECT pg_catalog.array_agg(acl) FROM (SELECT pg_catalog.unnest(coalesce(spcacl,pg_catalog.acldefault('t',spcowner))) AS acl "
1090+
"EXCEPT SELECT pg_catalog.unnest(pg_catalog.acldefault('t',spcowner))) as foo)"
1091+
"AS spcacl,"
1092+
"(SELECT pg_catalog.array_agg(acl) FROM (SELECT pg_catalog.unnest(pg_catalog.acldefault('t',spcowner)) AS acl "
1093+
"EXCEPT SELECT pg_catalog.unnest(coalesce(spcacl,pg_catalog.acldefault('t',spcowner)))) as foo)"
1094+
"AS rspcacl,"
1095+
"array_to_string(spcoptions, ', '),"
1096+
"pg_catalog.shobj_description(oid, 'pg_tablespace') "
1097+
"FROM pg_catalog.pg_tablespace "
1098+
"WHERE spcname !~ '^pg_' "
1099+
"ORDER BY 1");
1100+
elseif (server_version >=90200)
1101+
res=executeQuery(conn,"SELECT oid, spcname, "
1102+
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
1103+
"pg_catalog.pg_tablespace_location(oid), spcacl, '' as rspcacl, "
10801104
"array_to_string(spcoptions, ', '),"
10811105
"pg_catalog.shobj_description(oid, 'pg_tablespace') "
10821106
"FROM pg_catalog.pg_tablespace "
@@ -1085,7 +1109,7 @@ dumpTablespaces(PGconn *conn)
10851109
elseif (server_version >=90000)
10861110
res=executeQuery(conn,"SELECT oid, spcname, "
10871111
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
1088-
"spclocation, spcacl, "
1112+
"spclocation, spcacl,'' as rspcacl,"
10891113
"array_to_string(spcoptions, ', '),"
10901114
"pg_catalog.shobj_description(oid, 'pg_tablespace') "
10911115
"FROM pg_catalog.pg_tablespace "
@@ -1094,15 +1118,15 @@ dumpTablespaces(PGconn *conn)
10941118
elseif (server_version >=80200)
10951119
res=executeQuery(conn,"SELECT oid, spcname, "
10961120
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
1097-
"spclocation, spcacl, null, "
1121+
"spclocation, spcacl,'' as rspcacl,null, "
10981122
"pg_catalog.shobj_description(oid, 'pg_tablespace') "
10991123
"FROM pg_catalog.pg_tablespace "
11001124
"WHERE spcname !~ '^pg_' "
11011125
"ORDER BY 1");
11021126
else
11031127
res=executeQuery(conn,"SELECT oid, spcname, "
11041128
"pg_catalog.pg_get_userbyid(spcowner) AS spcowner, "
1105-
"spclocation, spcacl, "
1129+
"spclocation, spcacl,'' as rspcacl,"
11061130
"null, null "
11071131
"FROM pg_catalog.pg_tablespace "
11081132
"WHERE spcname !~ '^pg_' "
@@ -1119,8 +1143,9 @@ dumpTablespaces(PGconn *conn)
11191143
char*spcowner=PQgetvalue(res,i,2);
11201144
char*spclocation=PQgetvalue(res,i,3);
11211145
char*spcacl=PQgetvalue(res,i,4);
1122-
char*spcoptions=PQgetvalue(res,i,5);
1123-
char*spccomment=PQgetvalue(res,i,6);
1146+
char*rspcacl=PQgetvalue(res,i,5);
1147+
char*spcoptions=PQgetvalue(res,i,6);
1148+
char*spccomment=PQgetvalue(res,i,7);
11241149
char*fspcname;
11251150

11261151
/* needed for buildACLCommands() */
@@ -1138,7 +1163,7 @@ dumpTablespaces(PGconn *conn)
11381163
fspcname,spcoptions);
11391164

11401165
if (!skip_acls&&
1141-
!buildACLCommands(fspcname,NULL,"TABLESPACE",spcacl,"",
1166+
!buildACLCommands(fspcname,NULL,"TABLESPACE",spcacl,rspcacl,
11421167
spcowner,"",server_version,buf))
11431168
{
11441169
fprintf(stderr,_("%s: could not parse ACL list (%s) for tablespace \"%s\"\n"),
@@ -1284,14 +1309,43 @@ dumpCreateDB(PGconn *conn)
12841309

12851310
PQclear(res);
12861311

1287-
/* Now collect all the information about databases to dump */
1288-
if (server_version >=90300)
1312+
1313+
/*
1314+
* Now collect all the information about databases to dump.
1315+
*
1316+
* For the database ACLs, as of 9.6, we extract both the positive (as
1317+
* datacl) and negative (as rdatacl) ACLs, relative to the default ACL for
1318+
* databases, which are then passed to buildACLCommands() below.
1319+
*
1320+
* See buildACLQueries() and buildACLCommands().
1321+
*
1322+
* Note that we do not support initial privileges (pg_init_privs) on
1323+
* databases.
1324+
*/
1325+
if (server_version >=90600)
1326+
res=executeQuery(conn,
1327+
"SELECT datname, "
1328+
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
1329+
"pg_encoding_to_char(d.encoding), "
1330+
"datcollate, datctype, datfrozenxid, datminmxid, "
1331+
"datistemplate, "
1332+
"(SELECT pg_catalog.array_agg(acl) FROM (SELECT pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba))) AS acl "
1333+
"EXCEPT SELECT pg_catalog.unnest(pg_catalog.acldefault('d',datdba))) as foo)"
1334+
"AS datacl,"
1335+
"(SELECT pg_catalog.array_agg(acl) FROM (SELECT pg_catalog.unnest(pg_catalog.acldefault('d',datdba)) AS acl "
1336+
"EXCEPT SELECT pg_catalog.unnest(coalesce(datacl,pg_catalog.acldefault('d',datdba)))) as foo)"
1337+
"AS rdatacl,"
1338+
"datconnlimit, "
1339+
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
1340+
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
1341+
"WHERE datallowconn ORDER BY 1");
1342+
elseif (server_version >=90300)
12891343
res=executeQuery(conn,
12901344
"SELECT datname, "
12911345
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
12921346
"pg_encoding_to_char(d.encoding), "
12931347
"datcollate, datctype, datfrozenxid, datminmxid, "
1294-
"datistemplate, datacl, datconnlimit, "
1348+
"datistemplate, datacl,'' as rdatacl,datconnlimit, "
12951349
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
12961350
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
12971351
"WHERE datallowconn ORDER BY 1");
@@ -1301,7 +1355,7 @@ dumpCreateDB(PGconn *conn)
13011355
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
13021356
"pg_encoding_to_char(d.encoding), "
13031357
"datcollate, datctype, datfrozenxid, 0 AS datminmxid, "
1304-
"datistemplate, datacl, datconnlimit, "
1358+
"datistemplate, datacl,'' as rdatacl,datconnlimit, "
13051359
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
13061360
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
13071361
"WHERE datallowconn ORDER BY 1");
@@ -1311,7 +1365,7 @@ dumpCreateDB(PGconn *conn)
13111365
"coalesce(rolname, (select rolname from pg_authid where oid=(select datdba from pg_database where datname='template0'))), "
13121366
"pg_encoding_to_char(d.encoding), "
13131367
"null::text AS datcollate, null::text AS datctype, datfrozenxid, 0 AS datminmxid, "
1314-
"datistemplate, datacl, datconnlimit, "
1368+
"datistemplate, datacl,'' as rdatacl,datconnlimit, "
13151369
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
13161370
"FROM pg_database d LEFT JOIN pg_authid u ON (datdba = u.oid) "
13171371
"WHERE datallowconn ORDER BY 1");
@@ -1321,7 +1375,7 @@ dumpCreateDB(PGconn *conn)
13211375
"coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
13221376
"pg_encoding_to_char(d.encoding), "
13231377
"null::text AS datcollate, null::text AS datctype, datfrozenxid, 0 AS datminmxid, "
1324-
"datistemplate, datacl, -1 as datconnlimit, "
1378+
"datistemplate, datacl,'' as rdatacl,-1 as datconnlimit, "
13251379
"(SELECT spcname FROM pg_tablespace t WHERE t.oid = d.dattablespace) AS dattablespace "
13261380
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
13271381
"WHERE datallowconn ORDER BY 1");
@@ -1331,7 +1385,7 @@ dumpCreateDB(PGconn *conn)
13311385
"coalesce(usename, (select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
13321386
"pg_encoding_to_char(d.encoding), "
13331387
"null::text AS datcollate, null::text AS datctype, datfrozenxid, 0 AS datminmxid, "
1334-
"datistemplate, datacl, -1 as datconnlimit, "
1388+
"datistemplate, datacl,'' as rdatacl,-1 as datconnlimit, "
13351389
"'pg_default' AS dattablespace "
13361390
"FROM pg_database d LEFT JOIN pg_shadow u ON (datdba = usesysid) "
13371391
"WHERE datallowconn ORDER BY 1");
@@ -1343,7 +1397,7 @@ dumpCreateDB(PGconn *conn)
13431397
"(select usename from pg_shadow where usesysid=(select datdba from pg_database where datname='template0'))), "
13441398
"pg_encoding_to_char(d.encoding), "
13451399
"null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid, 0 AS datminmxid, "
1346-
"datistemplate, '' as datacl, -1 as datconnlimit, "
1400+
"datistemplate, '' as datacl,'' as rdatacl,-1 as datconnlimit, "
13471401
"'pg_default' AS dattablespace "
13481402
"FROM pg_database d "
13491403
"WHERE datallowconn ORDER BY 1");
@@ -1359,7 +1413,7 @@ dumpCreateDB(PGconn *conn)
13591413
"pg_encoding_to_char(d.encoding), "
13601414
"null::text AS datcollate, null::text AS datctype, 0 AS datfrozenxid, 0 AS datminmxid, "
13611415
"'f' as datistemplate, "
1362-
"'' as datacl, -1 as datconnlimit, "
1416+
"'' as datacl,'' as rdatacl,-1 as datconnlimit, "
13631417
"'pg_default' AS dattablespace "
13641418
"FROM pg_database d "
13651419
"ORDER BY 1");
@@ -1376,8 +1430,9 @@ dumpCreateDB(PGconn *conn)
13761430
uint32dbminmxid=atooid(PQgetvalue(res,i,6));
13771431
char*dbistemplate=PQgetvalue(res,i,7);
13781432
char*dbacl=PQgetvalue(res,i,8);
1379-
char*dbconnlimit=PQgetvalue(res,i,9);
1380-
char*dbtablespace=PQgetvalue(res,i,10);
1433+
char*rdbacl=PQgetvalue(res,i,9);
1434+
char*dbconnlimit=PQgetvalue(res,i,10);
1435+
char*dbtablespace=PQgetvalue(res,i,11);
13811436
char*fdbname;
13821437

13831438
fdbname=pg_strdup(fmtId(dbname));
@@ -1469,7 +1524,7 @@ dumpCreateDB(PGconn *conn)
14691524
}
14701525

14711526
if (!skip_acls&&
1472-
!buildACLCommands(fdbname,NULL,"DATABASE",dbacl,"",dbowner,
1527+
!buildACLCommands(fdbname,NULL,"DATABASE",dbacl,rdbacl,dbowner,
14731528
"",server_version,buf))
14741529
{
14751530
fprintf(stderr,_("%s: could not parse ACL list (%s) for database \"%s\"\n"),

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp