Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commita20a9f2

Browse files
committed
In pg_dump, avoid doing per-table queries for RLS policies.
For no particularly good reason, getPolicies() queried pg_policyseparately for each table. We can collect all the policies ina single query instead, and attach them to the correct TableInfoobjects using findTableByOid() lookups. On the regressiondatabase, this reduces the number of queries substantially, andprovides a visible savings even when running against a localserver.Per complaint from Hubert Depesz Lubaczewski. Since this is sucha simple fix and can have a visible performance benefit, back-patchto all supported branches.Discussion:https://postgr.es/m/20210826084430.GA26282@depesz.com
1 parent9407dbb commita20a9f2

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 onadumpabletable.
3667+
* get information aboutall RLSpolicies on dumpabletables.
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
inti_oid;
36763676
inti_tableoid;
3677+
inti_polrelid;
36773678
inti_polname;
36783679
inti_polcmd;
36793680
inti_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+
Oidpolrelid = 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)
[8]ページ先頭
©2009-2025 Movatter.jp