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

Commitdb11b4a

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 parent904ce45 commitdb11b4a

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 onadumpabletable.
3581+
* get information aboutall RLSpolicies on dumpabletables.
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
inti_oid;
35903590
inti_tableoid;
3591+
inti_polrelid;
35913592
inti_polname;
35923593
inti_polcmd;
35933594
inti_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+
Oidpolrelid = 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)
[8]ページ先頭
©2009-2025 Movatter.jp