Skip to content

Commit bd3611d

Browse files
tglsfdctglsfdc
committed
In pg_dump, avoid doing per-table queries for RLS policies.
For no particularly good reason, getPolicies() queried pg_policy separately for each table. We can collect all the policies in a single query instead, and attach them to the correct TableInfo objects using findTableByOid() lookups. On the regression database, this reduces the number of queries substantially, and provides a visible savings even when running against a local server. Per complaint from Hubert Depesz Lubaczewski. Since this is such a simple fix and can have a visible performance benefit, back-patch to all supported branches. Discussion: https://postgr.es/m/20210826084430.GA26282@depesz.com
1 parent 6c450a8 commit bd3611d

File tree

1 file changed

+42
-50
lines changed

1 file changed

+42
-50
lines changed

‎src/bin/pg_dump/pg_dump.c

Lines changed: 42 additions & 50 deletions
Original file line numberDiff line numberDiff line change
@@ -3656,7 +3656,7 @@ dumpBlobs(Archive *fout, const void *arg)
36563656

36573657
/*
36583658
* getPolicies
3659-
* get information about policies on a dumpable table.
3659+
* get information about all RLS policies on dumpable tables.
36603660
*/
36613661
void
36623662
getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
@@ -3666,6 +3666,7 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
36663666
PolicyInfo *polinfo;
36673667
int i_oid;
36683668
int i_tableoid;
3669+
int i_polrelid;
36693670
int i_polname;
36703671
int i_polcmd;
36713672
int i_polpermissive;
@@ -3681,6 +3682,10 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
36813682

36823683
query = createPQExpBuffer();
36833684

3685+
/*
3686+
* First, check which tables have RLS enabled. We represent RLS being
3687+
* enabled on a table by creating a PolicyInfo object with null polname.
3688+
*/
36843689
for (i = 0; i < numTables; i++)
36853690
{
36863691
TableInfo *tbinfo = &tblinfo[i];
@@ -3689,15 +3694,6 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
36893694
if (!(tbinfo->dobj.dump & DUMP_COMPONENT_POLICY))
36903695
continue;
36913696

3692-
pg_log_info("reading row security enabled for table \"%s.%s\"",
3693-
tbinfo->dobj.namespace->dobj.name,
3694-
tbinfo->dobj.name);
3695-
3696-
/*
3697-
* Get row security enabled information for the table. We represent
3698-
* RLS being enabled on a table by creating a PolicyInfo object with
3699-
* null polname.
3700-
*/
37013697
if (tbinfo->rowsec)
37023698
{
37033699
/*
@@ -3719,51 +3715,35 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
37193715
polinfo->polqual = NULL;
37203716
polinfo->polwithcheck = NULL;
37213717
}
3718+
}
37223719

3723-
pg_log_info("reading policies for table \"%s.%s\"",
3724-
tbinfo->dobj.namespace->dobj.name,
3725-
tbinfo->dobj.name);
3726-
3727-
resetPQExpBuffer(query);
3728-
3729-
/* Get the policies for the table. */
3730-
if (fout->remoteVersion >= 100000)
3731-
appendPQExpBuffer(query,
3732-
"SELECT oid, tableoid, pol.polname, pol.polcmd, pol.polpermissive, "
3733-
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
3734-
" pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(rolname) from pg_catalog.pg_roles WHERE oid = ANY(pol.polroles)), ', ') END AS polroles, "
3735-
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
3736-
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
3737-
"FROM pg_catalog.pg_policy pol "
3738-
"WHERE polrelid = '%u'",
3739-
tbinfo->dobj.catId.oid);
3740-
else
3741-
appendPQExpBuffer(query,
3742-
"SELECT oid, tableoid, pol.polname, pol.polcmd, 't' as polpermissive, "
3743-
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
3744-
" pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(rolname) from pg_catalog.pg_roles WHERE oid = ANY(pol.polroles)), ', ') END AS polroles, "
3745-
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
3746-
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
3747-
"FROM pg_catalog.pg_policy pol "
3748-
"WHERE polrelid = '%u'",
3749-
tbinfo->dobj.catId.oid);
3750-
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
3720+
/*
3721+
* Now, read all RLS policies, and create PolicyInfo objects for all those
3722+
* that are of interest.
3723+
*/
3724+
pg_log_info("reading row-level security policies");
37513725

3752-
ntups = PQntuples(res);
3726+
printfPQExpBuffer(query,
3727+
"SELECT oid, tableoid, pol.polrelid, pol.polname, pol.polcmd, ");
3728+
if (fout->remoteVersion >= 100000)
3729+
appendPQExpBuffer(query, "pol.polpermissive, ");
3730+
else
3731+
appendPQExpBuffer(query, "'t' as polpermissive, ");
3732+
appendPQExpBuffer(query,
3733+
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
3734+
" pg_catalog.array_to_string(ARRAY(SELECT pg_catalog.quote_ident(rolname) from pg_catalog.pg_roles WHERE oid = ANY(pol.polroles)), ', ') END AS polroles, "
3735+
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
3736+
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
3737+
"FROM pg_catalog.pg_policy pol");
37533738

3754-
if (ntups == 0)
3755-
{
3756-
/*
3757-
* No explicit policies to handle (only the default-deny policy,
3758-
* which is handled as part of the table definition). Clean up
3759-
* and return.
3760-
*/
3761-
PQclear(res);
3762-
continue;
3763-
}
3739+
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
37643740

3741+
ntups = PQntuples(res);
3742+
if (ntups > 0)
3743+
{
37653744
i_oid = PQfnumber(res, "oid");
37663745
i_tableoid = PQfnumber(res, "tableoid");
3746+
i_polrelid = PQfnumber(res, "polrelid");
37673747
i_polname = PQfnumber(res, "polname");
37683748
i_polcmd = PQfnumber(res, "polcmd");
37693749
i_polpermissive = PQfnumber(res, "polpermissive");
@@ -3775,6 +3755,16 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
37753755

37763756
for (j = 0; j < ntups; j++)
37773757
{
3758+
Oid polrelid = atooid(PQgetvalue(res, j, i_polrelid));
3759+
TableInfo *tbinfo = findTableByOid(polrelid);
3760+
3761+
/*
3762+
* Ignore row security on tables not to be dumped. (This will
3763+
* result in some harmless wasted slots in polinfo[].)
3764+
*/
3765+
if (!(tbinfo->dobj.dump & DUMP_COMPONENT_POLICY))
3766+
continue;
3767+
37783768
polinfo[j].dobj.objType = DO_POLICY;
37793769
polinfo[j].dobj.catId.tableoid =
37803770
atooid(PQgetvalue(res, j, i_tableoid));
@@ -3804,8 +3794,10 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
38043794
polinfo[j].polwithcheck
38053795
= pg_strdup(PQgetvalue(res, j, i_polwithcheck));
38063796
}
3807-
PQclear(res);
38083797
}
3798+
3799+
PQclear(res);
3800+
38093801
destroyPQExpBuffer(query);
38103802
}
38113803

0 commit comments

Comments
 (0)