@@ -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+ else if (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 else if (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 else if (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+ else if (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 uint32 dbminmxid = 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