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

Commitbd3611d

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 parent6c450a8 commitbd3611d

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 onadumpabletable.
3659+
* get information aboutall RLSpolicies on dumpabletables.
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
inti_oid;
36683668
inti_tableoid;
3669+
inti_polrelid;
36693670
inti_polname;
36703671
inti_polcmd;
36713672
inti_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+
Oidpolrelid = 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)
[8]ページ先頭
©2009-2025 Movatter.jp