Skip to content

Commit a20a9f2

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 9407dbb commit a20a9f2

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
@@ -3664,7 +3664,7 @@ dumpBlobs(Archive *fout, const void *arg)
36643664

36653665
/*
36663666
* getPolicies
3667-
* get information about policies on a dumpable table.
3667+
* get information about all RLS policies on dumpable tables.
36683668
*/
36693669
void
36703670
getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
@@ -3674,6 +3674,7 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
36743674
PolicyInfo *polinfo;
36753675
int i_oid;
36763676
int i_tableoid;
3677+
int i_polrelid;
36773678
int i_polname;
36783679
int i_polcmd;
36793680
int i_polpermissive;
@@ -3689,6 +3690,10 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
36893690

36903691
query = createPQExpBuffer();
36913692

3693+
/*
3694+
* First, check which tables have RLS enabled. We represent RLS being
3695+
* enabled on a table by creating a PolicyInfo object with null polname.
3696+
*/
36923697
for (i = 0; i < numTables; i++)
36933698
{
36943699
TableInfo *tbinfo = &tblinfo[i];
@@ -3697,15 +3702,6 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
36973702
if (!(tbinfo->dobj.dump & DUMP_COMPONENT_POLICY))
36983703
continue;
36993704

3700-
pg_log_info("reading row security enabled for table \"%s.%s\"",
3701-
tbinfo->dobj.namespace->dobj.name,
3702-
tbinfo->dobj.name);
3703-
3704-
/*
3705-
* Get row security enabled information for the table. We represent
3706-
* RLS being enabled on a table by creating a PolicyInfo object with
3707-
* null polname.
3708-
*/
37093705
if (tbinfo->rowsec)
37103706
{
37113707
/*
@@ -3727,51 +3723,35 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
37273723
polinfo->polqual = NULL;
37283724
polinfo->polwithcheck = NULL;
37293725
}
3726+
}
37303727

3731-
pg_log_info("reading policies for table \"%s.%s\"",
3732-
tbinfo->dobj.namespace->dobj.name,
3733-
tbinfo->dobj.name);
3734-
3735-
resetPQExpBuffer(query);
3736-
3737-
/* Get the policies for the table. */
3738-
if (fout->remoteVersion >= 100000)
3739-
appendPQExpBuffer(query,
3740-
"SELECT oid, tableoid, pol.polname, pol.polcmd, pol.polpermissive, "
3741-
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
3742-
" 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, "
3743-
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
3744-
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
3745-
"FROM pg_catalog.pg_policy pol "
3746-
"WHERE polrelid = '%u'",
3747-
tbinfo->dobj.catId.oid);
3748-
else
3749-
appendPQExpBuffer(query,
3750-
"SELECT oid, tableoid, pol.polname, pol.polcmd, 't' as polpermissive, "
3751-
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
3752-
" 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, "
3753-
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
3754-
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
3755-
"FROM pg_catalog.pg_policy pol "
3756-
"WHERE polrelid = '%u'",
3757-
tbinfo->dobj.catId.oid);
3758-
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
3728+
/*
3729+
* Now, read all RLS policies, and create PolicyInfo objects for all those
3730+
* that are of interest.
3731+
*/
3732+
pg_log_info("reading row-level security policies");
37593733

3760-
ntups = PQntuples(res);
3734+
printfPQExpBuffer(query,
3735+
"SELECT oid, tableoid, pol.polrelid, pol.polname, pol.polcmd, ");
3736+
if (fout->remoteVersion >= 100000)
3737+
appendPQExpBuffer(query, "pol.polpermissive, ");
3738+
else
3739+
appendPQExpBuffer(query, "'t' as polpermissive, ");
3740+
appendPQExpBuffer(query,
3741+
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
3742+
" 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, "
3743+
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
3744+
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
3745+
"FROM pg_catalog.pg_policy pol");
37613746

3762-
if (ntups == 0)
3763-
{
3764-
/*
3765-
* No explicit policies to handle (only the default-deny policy,
3766-
* which is handled as part of the table definition). Clean up
3767-
* and return.
3768-
*/
3769-
PQclear(res);
3770-
continue;
3771-
}
3747+
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
37723748

3749+
ntups = PQntuples(res);
3750+
if (ntups > 0)
3751+
{
37733752
i_oid = PQfnumber(res, "oid");
37743753
i_tableoid = PQfnumber(res, "tableoid");
3754+
i_polrelid = PQfnumber(res, "polrelid");
37753755
i_polname = PQfnumber(res, "polname");
37763756
i_polcmd = PQfnumber(res, "polcmd");
37773757
i_polpermissive = PQfnumber(res, "polpermissive");
@@ -3783,6 +3763,16 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
37833763

37843764
for (j = 0; j < ntups; j++)
37853765
{
3766+
Oid polrelid = atooid(PQgetvalue(res, j, i_polrelid));
3767+
TableInfo *tbinfo = findTableByOid(polrelid);
3768+
3769+
/*
3770+
* Ignore row security on tables not to be dumped. (This will
3771+
* result in some harmless wasted slots in polinfo[].)
3772+
*/
3773+
if (!(tbinfo->dobj.dump & DUMP_COMPONENT_POLICY))
3774+
continue;
3775+
37863776
polinfo[j].dobj.objType = DO_POLICY;
37873777
polinfo[j].dobj.catId.tableoid =
37883778
atooid(PQgetvalue(res, j, i_tableoid));
@@ -3812,8 +3802,10 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
38123802
polinfo[j].polwithcheck
38133803
= pg_strdup(PQgetvalue(res, j, i_polwithcheck));
38143804
}
3815-
PQclear(res);
38163805
}
3806+
3807+
PQclear(res);
3808+
38173809
destroyPQExpBuffer(query);
38183810
}
38193811

0 commit comments

Comments
 (0)