Skip to content

Commit 6b96673

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 2f1ed9d commit 6b96673

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
@@ -3508,7 +3508,7 @@ dumpBlobs(Archive *fout, void *arg)
35083508

35093509
/*
35103510
* getPolicies
3511-
* get information about policies on a dumpable table.
3511+
* get information about all RLS policies on dumpable tables.
35123512
*/
35133513
void
35143514
getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
@@ -3518,6 +3518,7 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
35183518
PolicyInfo *polinfo;
35193519
int i_oid;
35203520
int i_tableoid;
3521+
int i_polrelid;
35213522
int i_polname;
35223523
int i_polcmd;
35233524
int i_polpermissive;
@@ -3533,6 +3534,10 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
35333534

35343535
query = createPQExpBuffer();
35353536

3537+
/*
3538+
* First, check which tables have RLS enabled. We represent RLS being
3539+
* enabled on a table by creating a PolicyInfo object with null polname.
3540+
*/
35363541
for (i = 0; i < numTables; i++)
35373542
{
35383543
TableInfo *tbinfo = &tblinfo[i];
@@ -3541,15 +3546,6 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
35413546
if (!(tbinfo->dobj.dump & DUMP_COMPONENT_POLICY))
35423547
continue;
35433548

3544-
pg_log_info("reading row security enabled for table \"%s.%s\"",
3545-
tbinfo->dobj.namespace->dobj.name,
3546-
tbinfo->dobj.name);
3547-
3548-
/*
3549-
* Get row security enabled information for the table. We represent
3550-
* RLS being enabled on a table by creating a PolicyInfo object with
3551-
* null polname.
3552-
*/
35533549
if (tbinfo->rowsec)
35543550
{
35553551
/*
@@ -3571,51 +3567,35 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
35713567
polinfo->polqual = NULL;
35723568
polinfo->polwithcheck = NULL;
35733569
}
3570+
}
35743571

3575-
pg_log_info("reading policies for table \"%s.%s\"",
3576-
tbinfo->dobj.namespace->dobj.name,
3577-
tbinfo->dobj.name);
3578-
3579-
resetPQExpBuffer(query);
3580-
3581-
/* Get the policies for the table. */
3582-
if (fout->remoteVersion >= 100000)
3583-
appendPQExpBuffer(query,
3584-
"SELECT oid, tableoid, pol.polname, pol.polcmd, pol.polpermissive, "
3585-
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
3586-
" 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, "
3587-
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
3588-
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
3589-
"FROM pg_catalog.pg_policy pol "
3590-
"WHERE polrelid = '%u'",
3591-
tbinfo->dobj.catId.oid);
3592-
else
3593-
appendPQExpBuffer(query,
3594-
"SELECT oid, tableoid, pol.polname, pol.polcmd, 't' as polpermissive, "
3595-
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
3596-
" 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, "
3597-
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
3598-
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
3599-
"FROM pg_catalog.pg_policy pol "
3600-
"WHERE polrelid = '%u'",
3601-
tbinfo->dobj.catId.oid);
3602-
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
3572+
/*
3573+
* Now, read all RLS policies, and create PolicyInfo objects for all those
3574+
* that are of interest.
3575+
*/
3576+
pg_log_info("reading row-level security policies");
36033577

3604-
ntups = PQntuples(res);
3578+
printfPQExpBuffer(query,
3579+
"SELECT oid, tableoid, pol.polrelid, pol.polname, pol.polcmd, ");
3580+
if (fout->remoteVersion >= 100000)
3581+
appendPQExpBuffer(query, "pol.polpermissive, ");
3582+
else
3583+
appendPQExpBuffer(query, "'t' as polpermissive, ");
3584+
appendPQExpBuffer(query,
3585+
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
3586+
" 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, "
3587+
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
3588+
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
3589+
"FROM pg_catalog.pg_policy pol");
36053590

3606-
if (ntups == 0)
3607-
{
3608-
/*
3609-
* No explicit policies to handle (only the default-deny policy,
3610-
* which is handled as part of the table definition). Clean up
3611-
* and return.
3612-
*/
3613-
PQclear(res);
3614-
continue;
3615-
}
3591+
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
36163592

3593+
ntups = PQntuples(res);
3594+
if (ntups > 0)
3595+
{
36173596
i_oid = PQfnumber(res, "oid");
36183597
i_tableoid = PQfnumber(res, "tableoid");
3598+
i_polrelid = PQfnumber(res, "polrelid");
36193599
i_polname = PQfnumber(res, "polname");
36203600
i_polcmd = PQfnumber(res, "polcmd");
36213601
i_polpermissive = PQfnumber(res, "polpermissive");
@@ -3627,6 +3607,16 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
36273607

36283608
for (j = 0; j < ntups; j++)
36293609
{
3610+
Oid polrelid = atooid(PQgetvalue(res, j, i_polrelid));
3611+
TableInfo *tbinfo = findTableByOid(polrelid);
3612+
3613+
/*
3614+
* Ignore row security on tables not to be dumped. (This will
3615+
* result in some harmless wasted slots in polinfo[].)
3616+
*/
3617+
if (!(tbinfo->dobj.dump & DUMP_COMPONENT_POLICY))
3618+
continue;
3619+
36303620
polinfo[j].dobj.objType = DO_POLICY;
36313621
polinfo[j].dobj.catId.tableoid =
36323622
atooid(PQgetvalue(res, j, i_tableoid));
@@ -3656,8 +3646,10 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
36563646
polinfo[j].polwithcheck
36573647
= pg_strdup(PQgetvalue(res, j, i_polwithcheck));
36583648
}
3659-
PQclear(res);
36603649
}
3650+
3651+
PQclear(res);
3652+
36613653
destroyPQExpBuffer(query);
36623654
}
36633655

0 commit comments

Comments
 (0)