Skip to content

Commit db11b4a

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 904ce45 commit db11b4a

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

35793579
/*
35803580
* getPolicies
3581-
* get information about policies on a dumpable table.
3581+
* get information about all RLS policies on dumpable tables.
35823582
*/
35833583
void
35843584
getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
@@ -3588,6 +3588,7 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
35883588
PolicyInfo *polinfo;
35893589
int i_oid;
35903590
int i_tableoid;
3591+
int i_polrelid;
35913592
int i_polname;
35923593
int i_polcmd;
35933594
int i_polpermissive;
@@ -3603,6 +3604,10 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
36033604

36043605
query = createPQExpBuffer();
36053606

3607+
/*
3608+
* First, check which tables have RLS enabled. We represent RLS being
3609+
* enabled on a table by creating a PolicyInfo object with null polname.
3610+
*/
36063611
for (i = 0; i < numTables; i++)
36073612
{
36083613
TableInfo *tbinfo = &tblinfo[i];
@@ -3611,15 +3616,6 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
36113616
if (!(tbinfo->dobj.dump & DUMP_COMPONENT_POLICY))
36123617
continue;
36133618

3614-
pg_log_info("reading row security enabled for table \"%s.%s\"",
3615-
tbinfo->dobj.namespace->dobj.name,
3616-
tbinfo->dobj.name);
3617-
3618-
/*
3619-
* Get row security enabled information for the table. We represent
3620-
* RLS being enabled on a table by creating a PolicyInfo object with
3621-
* null polname.
3622-
*/
36233619
if (tbinfo->rowsec)
36243620
{
36253621
/*
@@ -3641,51 +3637,35 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
36413637
polinfo->polqual = NULL;
36423638
polinfo->polwithcheck = NULL;
36433639
}
3640+
}
36443641

3645-
pg_log_info("reading policies for table \"%s.%s\"",
3646-
tbinfo->dobj.namespace->dobj.name,
3647-
tbinfo->dobj.name);
3648-
3649-
resetPQExpBuffer(query);
3650-
3651-
/* Get the policies for the table. */
3652-
if (fout->remoteVersion >= 100000)
3653-
appendPQExpBuffer(query,
3654-
"SELECT oid, tableoid, pol.polname, pol.polcmd, pol.polpermissive, "
3655-
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
3656-
" 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, "
3657-
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
3658-
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
3659-
"FROM pg_catalog.pg_policy pol "
3660-
"WHERE polrelid = '%u'",
3661-
tbinfo->dobj.catId.oid);
3662-
else
3663-
appendPQExpBuffer(query,
3664-
"SELECT oid, tableoid, pol.polname, pol.polcmd, 't' as polpermissive, "
3665-
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
3666-
" 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, "
3667-
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
3668-
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
3669-
"FROM pg_catalog.pg_policy pol "
3670-
"WHERE polrelid = '%u'",
3671-
tbinfo->dobj.catId.oid);
3672-
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
3642+
/*
3643+
* Now, read all RLS policies, and create PolicyInfo objects for all those
3644+
* that are of interest.
3645+
*/
3646+
pg_log_info("reading row-level security policies");
36733647

3674-
ntups = PQntuples(res);
3648+
printfPQExpBuffer(query,
3649+
"SELECT oid, tableoid, pol.polrelid, pol.polname, pol.polcmd, ");
3650+
if (fout->remoteVersion >= 100000)
3651+
appendPQExpBuffer(query, "pol.polpermissive, ");
3652+
else
3653+
appendPQExpBuffer(query, "'t' as polpermissive, ");
3654+
appendPQExpBuffer(query,
3655+
"CASE WHEN pol.polroles = '{0}' THEN NULL ELSE "
3656+
" 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, "
3657+
"pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
3658+
"pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
3659+
"FROM pg_catalog.pg_policy pol");
36753660

3676-
if (ntups == 0)
3677-
{
3678-
/*
3679-
* No explicit policies to handle (only the default-deny policy,
3680-
* which is handled as part of the table definition). Clean up
3681-
* and return.
3682-
*/
3683-
PQclear(res);
3684-
continue;
3685-
}
3661+
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
36863662

3663+
ntups = PQntuples(res);
3664+
if (ntups > 0)
3665+
{
36873666
i_oid = PQfnumber(res, "oid");
36883667
i_tableoid = PQfnumber(res, "tableoid");
3668+
i_polrelid = PQfnumber(res, "polrelid");
36893669
i_polname = PQfnumber(res, "polname");
36903670
i_polcmd = PQfnumber(res, "polcmd");
36913671
i_polpermissive = PQfnumber(res, "polpermissive");
@@ -3697,6 +3677,16 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
36973677

36983678
for (j = 0; j < ntups; j++)
36993679
{
3680+
Oid polrelid = atooid(PQgetvalue(res, j, i_polrelid));
3681+
TableInfo *tbinfo = findTableByOid(polrelid);
3682+
3683+
/*
3684+
* Ignore row security on tables not to be dumped. (This will
3685+
* result in some harmless wasted slots in polinfo[].)
3686+
*/
3687+
if (!(tbinfo->dobj.dump & DUMP_COMPONENT_POLICY))
3688+
continue;
3689+
37003690
polinfo[j].dobj.objType = DO_POLICY;
37013691
polinfo[j].dobj.catId.tableoid =
37023692
atooid(PQgetvalue(res, j, i_tableoid));
@@ -3726,8 +3716,10 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
37263716
polinfo[j].polwithcheck
37273717
= pg_strdup(PQgetvalue(res, j, i_polwithcheck));
37283718
}
3729-
PQclear(res);
37303719
}
3720+
3721+
PQclear(res);
3722+
37313723
destroyPQExpBuffer(query);
37323724
}
37333725

0 commit comments

Comments
 (0)