Jeevan, all, * Jeevan Chalke (jeevan.cha...@enterprisedb.com) wrote: > On Mon, Sep 12, 2016 at 7:27 AM, Stephen Frost <sfr...@snowman.net> wrote: > > * Robert Haas (robertmh...@gmail.com) wrote: > > > On Thu, Sep 8, 2016 at 5:21 PM, Tom Lane <t...@sss.pgh.pa.us> wrote: > > > > Stephen Frost <sfr...@snowman.net> writes: > > > >> * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: > > > >>> Can't you keep those words as Sconst or something (DefElems?) until > > the > > > >>> execution phase, so that they don't need to be keywords at all? > > > > > > > >> Seems like we could do that, though I'm not convinced that it really > > > >> gains us all that much. These are only unreserved keywords, of > > course, > > > >> so they don't impact users the way reserved keywords (of any kind) > > can. > > > >> While there may be some places where we use a string to represent a > > set > > > >> of defined options, I don't believe that's typical > > > > > > > > -1 for having to write them as string literals; but I think what Alvaro > > > > really means is to arrange for the words to just be identifiers in the > > > > grammar, which you strcmp against at execution. See for example > > > > reloption_list. (Whether you use DefElem as the internal > > representation > > > > is a minor detail, though it might help for making the parsetree > > > > copyObject-friendly.) > > > > > > > > vacuum_option_elem shows another way to avoid making a word into a > > > > keyword, although to me that one is more of an antipattern; it'd be > > better > > > > to leave the strcmp to execution, since there's so much other code that > > > > does things that way. > > > > > > There are other cases like that, too, e.g. AlterOptRoleElem; I don't > > > think it's a bad pattern. Regardless of the specifics, I do think > > > that it would be better not to bloat the keyword table with things > > > that don't really need to be keywords. > > > > The AlterOptRoleElem case is, I believe, what Tom was just suggesting as > > an antipattern, since the strcmp() is being done at parse time instead > > of at execution time. > > > > If we are concerned about having too many unreserved keywords, then I > > agree that AlterOptRoleElem is a good candidate to look at for reducing > > the number we have, as it appears to contain 3 keywords which are not > > used anywhere else (and 1 other which is only used in one other place). > > > > I do think that using IDENT for the various role attributes does make > > sense, to be clear, as there are quite a few of them, they change > > depending on major version, and those keywords are very unlikely to ever > > have utilization elsewhere. > > > > For this case, there's just 2 keywords which seem like they may be used > > again (perhaps for ALTER or DROP POLICY, or default policies if we grow > > those in the future), and we're unlikly to grow more in the future for > > that particular case (as we only have two binary boolean operators and > > that seems unlikely to change), though, should that happens, we could > > certainly revisit this. > > > > To me, adding two new keywords for two new options does not look good as it > will bloat keywords list. Per my understanding we should add keyword if and > only if we have no option than adding at, may be to avoid grammar conflicts. > > I am also inclined to think that using identifier will be a good choice > here. > However I would prefer to do the string comparison right into the grammar > itself, so that if we have wrong option as input there, then we will not > proceed further with it. We are anyway going to throw an error later then > why not at early stage.
Updated patch changes to using IDENT and an strcmp() (similar to AlterOptRoleElem and vacuum_option_elem) to check the results at parse-time, and then throwing a more specific error if an unexpected value is found (instead of just 'syntax error'). This avoids adding any keywords. Thanks! Stephen
From 11471c7921271e3c03078f3d31148dd4afd9d6e0 Mon Sep 17 00:00:00 2001
From: Stephen Frost <sfr...@snowman.net>
Date: Thu, 1 Sep 2016 02:11:30 -0400
Subject: [PATCH] Add support for restrictive RLS policies
We have had support for restrictive RLS policies since 9.5, but they
were only available through extensions which use the appropriate hooks.
This adds support into the grammer, catalog, psql and pg_dump for
restrictive RLS policies, thus reducing the cases where an extension is
necessary.
---
doc/src/sgml/ref/create_policy.sgml | 16 +++
src/backend/commands/policy.c | 9 ++
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/parser/gram.y | 34 ++++-
src/backend/rewrite/rowsecurity.c | 7 +-
src/bin/pg_dump/pg_dump.c | 38 ++++--
src/bin/pg_dump/pg_dump.h | 1 +
src/bin/pg_dump/t/002_pg_dump.pl | 39 +++++-
src/bin/psql/describe.c | 109 ++++++++++++----
src/bin/psql/tab-complete.c | 29 ++++-
src/include/catalog/pg_policy.h | 16 ++-
src/include/nodes/parsenodes.h | 1 +
src/include/rewrite/rowsecurity.h | 1 +
src/test/regress/expected/rowsecurity.out | 209 ++++++++++++++++++++++--------
src/test/regress/sql/rowsecurity.sql | 24 +++-
16 files changed, 421 insertions(+), 114 deletions(-)
diff --git a/doc/src/sgml/ref/create_policy.sgml b/doc/src/sgml/ref/create_policy.sgml
index 89d2787..d930052 100644
--- a/doc/src/sgml/ref/create_policy.sgml
+++ b/doc/src/sgml/ref/create_policy.sgml
@@ -22,6 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable class="parameter">table_name</replaceable>
+ [ AS ( PERMISSIVE | RESTRICTIVE ) ]
[ FOR { ALL | SELECT | INSERT | UPDATE | DELETE } ]
[ TO { <replaceable class="parameter">role_name</replaceable> | PUBLIC | CURRENT_USER | SESSION_USER } [, ...] ]
[ USING ( <replaceable class="parameter">using_expression</replaceable> ) ]
@@ -120,6 +121,21 @@ CREATE POLICY <replaceable class="parameter">name</replaceable> ON <replaceable
</varlistentry>
<varlistentry>
+ <term><replaceable class="parameter">permissive</replaceable></term>
+ <listitem>
+ <para>
+ If the policy is a "permissive" or "restrictive" policy. Permissive
+ policies are the default and always add visibillity- they ar "OR"d
+ together to allow the user access to all rows through any of the
+ permissive policies they have access to. Alternativly, a policy can
+ instead by "restrictive", meaning that the policy will be "AND"d
+ with other restrictive policies and with the result of all of the
+ permissive policies on the table.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><replaceable class="parameter">command</replaceable></term>
<listitem>
<para>
diff --git a/src/backend/commands/policy.c b/src/backend/commands/policy.c
index d694cf8..70e22c1 100644
--- a/src/backend/commands/policy.c
+++ b/src/backend/commands/policy.c
@@ -235,6 +235,7 @@ RelationBuildRowSecurity(Relation relation)
{
Datum value_datum;
char cmd_value;
+ bool permissive_value;
Datum roles_datum;
char *qual_value;
Expr *qual_expr;
@@ -257,6 +258,12 @@ RelationBuildRowSecurity(Relation relation)
Assert(!isnull);
cmd_value = DatumGetChar(value_datum);
+ /* Get policy permissive or restrictive */
+ value_datum = heap_getattr(tuple, Anum_pg_policy_polpermissive,
+ RelationGetDescr(catalog), &isnull);
+ Assert(!isnull);
+ permissive_value = DatumGetBool(value_datum);
+
/* Get policy name */
value_datum = heap_getattr(tuple, Anum_pg_policy_polname,
RelationGetDescr(catalog), &isnull);
@@ -298,6 +305,7 @@ RelationBuildRowSecurity(Relation relation)
policy = palloc0(sizeof(RowSecurityPolicy));
policy->policy_name = pstrdup(policy_name_value);
policy->polcmd = cmd_value;
+ policy->permissive = permissive_value;
policy->roles = DatumGetArrayTypePCopy(roles_datum);
policy->qual = copyObject(qual_expr);
policy->with_check_qual = copyObject(with_check_qual);
@@ -796,6 +804,7 @@ CreatePolicy(CreatePolicyStmt *stmt)
values[Anum_pg_policy_polname - 1] = DirectFunctionCall1(namein,
CStringGetDatum(stmt->policy_name));
values[Anum_pg_policy_polcmd - 1] = CharGetDatum(polcmd);
+ values[Anum_pg_policy_polpermissive - 1] = BoolGetDatum(stmt->permissive);
values[Anum_pg_policy_polroles - 1] = PointerGetDatum(role_ids);
/* Add qual if present. */
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 71714bc..3b197c4 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -4153,6 +4153,7 @@ _copyCreatePolicyStmt(const CreatePolicyStmt *from)
COPY_STRING_FIELD(policy_name);
COPY_NODE_FIELD(table);
COPY_STRING_FIELD(cmd_name);
+ COPY_SCALAR_FIELD(permissive);
COPY_NODE_FIELD(roles);
COPY_NODE_FIELD(qual);
COPY_NODE_FIELD(with_check);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 29a090f..b26cf83 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2124,6 +2124,7 @@ _equalCreatePolicyStmt(const CreatePolicyStmt *a, const CreatePolicyStmt *b)
COMPARE_STRING_FIELD(policy_name);
COMPARE_NODE_FIELD(table);
COMPARE_STRING_FIELD(cmd_name);
+ COMPARE_SCALAR_FIELD(permissive);
COMPARE_NODE_FIELD(roles);
COMPARE_NODE_FIELD(qual);
COMPARE_NODE_FIELD(with_check);
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 1526c73..65d4ce2 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -329,6 +329,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <str> all_Op MathOp
%type <str> row_security_cmd RowSecurityDefaultForCmd
+%type <boolean> RowSecurityDefaultPermissive
%type <node> RowSecurityOptionalWithCheck RowSecurityOptionalExpr
%type <list> RowSecurityDefaultToRole RowSecurityOptionalToRole
@@ -4625,17 +4626,18 @@ AlterUserMappingStmt: ALTER USER MAPPING FOR auth_ident SERVER name alter_generi
*****************************************************************************/
CreatePolicyStmt:
- CREATE POLICY name ON qualified_name RowSecurityDefaultForCmd
- RowSecurityDefaultToRole RowSecurityOptionalExpr
- RowSecurityOptionalWithCheck
+ CREATE POLICY name ON qualified_name RowSecurityDefaultPermissive
+ RowSecurityDefaultForCmd RowSecurityDefaultToRole
+ RowSecurityOptionalExpr RowSecurityOptionalWithCheck
{
CreatePolicyStmt *n = makeNode(CreatePolicyStmt);
n->policy_name = $3;
n->table = $5;
- n->cmd_name = $6;
- n->roles = $7;
- n->qual = $8;
- n->with_check = $9;
+ n->permissive = $6;
+ n->cmd_name = $7;
+ n->roles = $8;
+ n->qual = $9;
+ n->with_check = $10;
$$ = (Node *) n;
}
;
@@ -4699,6 +4701,24 @@ RowSecurityOptionalToRole:
| /* EMPTY */ { $$ = NULL; }
;
+RowSecurityDefaultPermissive:
+ AS IDENT
+ {
+ if (strcmp($2, "permissive") == 0)
+ $$ = true;
+ else if (strcmp($2, "restrictive") == 0)
+ $$ = false;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_SYNTAX_ERROR),
+ errmsg("unrecognized row security option \"%s\"", $1),
+ errhint("Only PERMISSIVE or RESTRICTIVE policies are supported currently."),
+ parser_errposition(@1)));
+
+ }
+ | /* EMPTY */ { $$ = true; }
+ ;
+
RowSecurityDefaultForCmd:
FOR row_security_cmd { $$ = $2; }
| /* EMPTY */ { $$ = "all"; }
diff --git a/src/backend/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.c
index e029116..eebe909 100644
--- a/src/backend/rewrite/rowsecurity.c
+++ b/src/backend/rewrite/rowsecurity.c
@@ -433,7 +433,12 @@ get_policies_for_relation(Relation relation, CmdType cmd, Oid user_id,
* the specified role.
*/
if (cmd_matches && check_role_for_policy(policy->roles, user_id))
- *permissive_policies = lappend(*permissive_policies, policy);
+ {
+ if (policy->permissive)
+ *permissive_policies = lappend(*permissive_policies, policy);
+ else
+ *restrictive_policies = lappend(*restrictive_policies, policy);
+ }
}
/*
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 51b8a1a..827bd58 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3097,6 +3097,7 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
int i_tableoid;
int i_polname;
int i_polcmd;
+ int i_polpermissive;
int i_polroles;
int i_polqual;
int i_polwithcheck;
@@ -3143,6 +3144,7 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
polinfo->poltable = tbinfo;
polinfo->polname = NULL;
polinfo->polcmd = NULL;
+ polinfo->polpermissive = NULL;
polinfo->polroles = NULL;
polinfo->polqual = NULL;
polinfo->polwithcheck = NULL;
@@ -3161,15 +3163,26 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
resetPQExpBuffer(query);
/* Get the policies for the table. */
- appendPQExpBuffer(query,
- "SELECT oid, tableoid, pol.polname, pol.polcmd, "
- "CASE WHEN pol.polroles = '{0}' THEN 'PUBLIC' ELSE "
- " 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, "
- "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
- "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
- "FROM pg_catalog.pg_policy pol "
- "WHERE polrelid = '%u'",
- tbinfo->dobj.catId.oid);
+ if (fout->remoteVersion >= 100000)
+ appendPQExpBuffer(query,
+ "SELECT oid, tableoid, pol.polname, pol.polcmd, pol.polpermissive, "
+ "CASE WHEN pol.polroles = '{0}' THEN 'PUBLIC' ELSE "
+ " 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, "
+ "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
+ "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
+ "FROM pg_catalog.pg_policy pol "
+ "WHERE polrelid = '%u'",
+ tbinfo->dobj.catId.oid);
+ else
+ appendPQExpBuffer(query,
+ "SELECT oid, tableoid, pol.polname, pol.polcmd, 't' as polpermissive, "
+ "CASE WHEN pol.polroles = '{0}' THEN 'PUBLIC' ELSE "
+ " 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, "
+ "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid) AS polqual, "
+ "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid) AS polwithcheck "
+ "FROM pg_catalog.pg_policy pol "
+ "WHERE polrelid = '%u'",
+ tbinfo->dobj.catId.oid);
res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
ntups = PQntuples(res);
@@ -3189,6 +3202,7 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
i_tableoid = PQfnumber(res, "tableoid");
i_polname = PQfnumber(res, "polname");
i_polcmd = PQfnumber(res, "polcmd");
+ i_polpermissive = PQfnumber(res, "polpermissive");
i_polroles = PQfnumber(res, "polroles");
i_polqual = PQfnumber(res, "polqual");
i_polwithcheck = PQfnumber(res, "polwithcheck");
@@ -3208,6 +3222,7 @@ getPolicies(Archive *fout, TableInfo tblinfo[], int numTables)
polinfo[j].dobj.name = pg_strdup(polinfo[j].polname);
polinfo[j].polcmd = pg_strdup(PQgetvalue(res, j, i_polcmd));
+ polinfo[j].polpermissive = pg_strdup(PQgetvalue(res, j, i_polpermissive));
polinfo[j].polroles = pg_strdup(PQgetvalue(res, j, i_polroles));
if (PQgetisnull(res, j, i_polqual))
@@ -3291,7 +3306,10 @@ dumpPolicy(Archive *fout, PolicyInfo *polinfo)
delqry = createPQExpBuffer();
appendPQExpBuffer(query, "CREATE POLICY %s", fmtId(polinfo->polname));
- appendPQExpBuffer(query, " ON %s FOR %s", fmtId(tbinfo->dobj.name), cmd);
+
+ appendPQExpBuffer(query, " ON %s AS %s FOR %s", fmtId(tbinfo->dobj.name),
+ strcmp(polinfo->polpermissive,"t") == 0 ? "PERMISSIVE" : "RESTRICTIVE",
+ cmd);
if (polinfo->polroles != NULL)
appendPQExpBuffer(query, " TO %s", polinfo->polroles);
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 2bfa2d9..eb0563f 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -548,6 +548,7 @@ typedef struct _policyInfo
TableInfo *poltable;
char *polname; /* null indicates RLS is enabled on rel */
char *polcmd;
+ char *polpermissive;
char *polroles;
char *polqual;
char *polwithcheck;
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 37cbdcd..10c54b3 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -1644,7 +1644,7 @@ my %tests = (
USING (true)
WITH CHECK (true);',
regexp => qr/^
- \QCREATE POLICY p1 ON test_table FOR ALL TO PUBLIC \E
+ \QCREATE POLICY p1 ON test_table AS PERMISSIVE FOR ALL TO PUBLIC \E
\QUSING (true) WITH CHECK (true);\E
/xm,
like => {
@@ -1673,7 +1673,7 @@ my %tests = (
create_sql => 'CREATE POLICY p2 ON dump_test.test_table
FOR SELECT TO regress_dump_test_role USING (true);',
regexp => qr/^
- \QCREATE POLICY p2 ON test_table FOR SELECT TO regress_dump_test_role \E
+ \QCREATE POLICY p2 ON test_table AS PERMISSIVE FOR SELECT TO regress_dump_test_role \E
\QUSING (true);\E
/xm,
like => {
@@ -1702,7 +1702,7 @@ my %tests = (
create_sql => 'CREATE POLICY p3 ON dump_test.test_table
FOR INSERT TO regress_dump_test_role WITH CHECK (true);',
regexp => qr/^
- \QCREATE POLICY p3 ON test_table FOR INSERT \E
+ \QCREATE POLICY p3 ON test_table AS PERMISSIVE FOR INSERT \E
\QTO regress_dump_test_role WITH CHECK (true);\E
/xm,
like => {
@@ -1731,7 +1731,7 @@ my %tests = (
create_sql => 'CREATE POLICY p4 ON dump_test.test_table FOR UPDATE
TO regress_dump_test_role USING (true) WITH CHECK (true);',
regexp => qr/^
- \QCREATE POLICY p4 ON test_table FOR UPDATE TO regress_dump_test_role \E
+ \QCREATE POLICY p4 ON test_table AS PERMISSIVE FOR UPDATE TO regress_dump_test_role \E
\QUSING (true) WITH CHECK (true);\E
/xm,
like => {
@@ -1760,7 +1760,7 @@ my %tests = (
create_sql => 'CREATE POLICY p5 ON dump_test.test_table
FOR DELETE TO regress_dump_test_role USING (true);',
regexp => qr/^
- \QCREATE POLICY p5 ON test_table FOR DELETE \E
+ \QCREATE POLICY p5 ON test_table AS PERMISSIVE FOR DELETE \E
\QTO regress_dump_test_role USING (true);\E
/xm,
like => {
@@ -1784,6 +1784,35 @@ my %tests = (
exclude_test_table => 1,
pg_dumpall_globals => 1,
pg_dumpall_globals_clean => 1, }, },
+ 'CREATE POLICY p6 ON test_table AS RESTRICTIVE' => {
+ create_order => 27,
+ create_sql => 'CREATE POLICY p6 ON dump_test.test_table AS RESTRICTIVE
+ USING (false);',
+ regexp => qr/^
+ \QCREATE POLICY p6 ON test_table AS RESTRICTIVE FOR ALL \E
+ \QTO PUBLIC USING (false);\E
+ /xm,
+ like => {
+ binary_upgrade => 1,
+ clean => 1,
+ clean_if_exists => 1,
+ createdb => 1,
+ defaults => 1,
+ exclude_test_table_data => 1,
+ no_privs => 1,
+ no_owner => 1,
+ only_dump_test_schema => 1,
+ only_dump_test_table => 1,
+ pg_dumpall_dbprivs => 1,
+ schema_only => 1,
+ section_post_data => 1,
+ test_schema_plus_blobs => 1, },
+ unlike => {
+ section_pre_data => 1,
+ exclude_dump_test_schema => 1,
+ exclude_test_table => 1,
+ pg_dumpall_globals => 1,
+ pg_dumpall_globals_clean => 1, }, },
'CREATE SCHEMA dump_test' => {
create_order => 2,
create_sql => 'CREATE SCHEMA dump_test;',
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 6275a68..fab4bff 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -887,7 +887,7 @@ permissionsList(const char *pattern)
" ), E'\\n') AS \"%s\"",
gettext_noop("Column privileges"));
- if (pset.sversion >= 90500)
+ if (pset.sversion >= 90500 && pset.sversion < 100000)
appendPQExpBuffer(&buf,
",\n pg_catalog.array_to_string(ARRAY(\n"
" SELECT polname\n"
@@ -918,6 +918,40 @@ permissionsList(const char *pattern)
" AS \"%s\"",
gettext_noop("Policies"));
+ if (pset.sversion >= 100000)
+ appendPQExpBuffer(&buf,
+ ",\n pg_catalog.array_to_string(ARRAY(\n"
+ " SELECT polname\n"
+ " || CASE WHEN polcmd != '*' THEN\n"
+ " E' (' || polcmd || E'):'\n"
+ " ELSE E':' \n"
+ " END\n"
+ " || CASE WHEN NOT polpermissive THEN\n"
+ " E' (RESTRICT)'\n"
+ " ELSE '' END\n"
+ " || CASE WHEN polqual IS NOT NULL THEN\n"
+ " E'\\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)\n"
+ " ELSE E''\n"
+ " END\n"
+ " || CASE WHEN polwithcheck IS NOT NULL THEN\n"
+ " E'\\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)\n"
+ " ELSE E''\n"
+ " END"
+ " || CASE WHEN polroles <> '{0}' THEN\n"
+ " E'\\n to: ' || pg_catalog.array_to_string(\n"
+ " ARRAY(\n"
+ " SELECT rolname\n"
+ " FROM pg_catalog.pg_roles\n"
+ " WHERE oid = ANY (polroles)\n"
+ " ORDER BY 1\n"
+ " ), E', ')\n"
+ " ELSE E''\n"
+ " END\n"
+ " FROM pg_catalog.pg_policy pol\n"
+ " WHERE polrelid = c.oid), E'\\n')\n"
+ " AS \"%s\"",
+ gettext_noop("Policies"));
+
appendPQExpBufferStr(&buf, "\nFROM pg_catalog.pg_class c\n"
" LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace\n"
"WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f')\n");
@@ -2136,21 +2170,38 @@ describeOneTableDetails(const char *schemaname,
/* print any row-level policies */
if (pset.sversion >= 90500)
{
- printfPQExpBuffer(&buf,
- "SELECT pol.polname,\n"
- "CASE WHEN pol.polroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (pol.polroles) order by 1),',') END,\n"
- "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
- "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
- "CASE pol.polcmd \n"
- "WHEN 'r' THEN 'SELECT'\n"
- "WHEN 'a' THEN 'INSERT'\n"
- "WHEN 'w' THEN 'UPDATE'\n"
- "WHEN 'd' THEN 'DELETE'\n"
- "WHEN '*' THEN 'ALL'\n"
- "END AS cmd\n"
- "FROM pg_catalog.pg_policy pol\n"
- "WHERE pol.polrelid = '%s' ORDER BY 1;",
- oid);
+ if (pset.sversion >= 100000)
+ printfPQExpBuffer(&buf,
+ "SELECT pol.polname, pol.polpermissive,\n"
+ "CASE WHEN pol.polroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (pol.polroles) order by 1),',') END,\n"
+ "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
+ "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
+ "CASE pol.polcmd \n"
+ "WHEN 'r' THEN 'SELECT'\n"
+ "WHEN 'a' THEN 'INSERT'\n"
+ "WHEN 'w' THEN 'UPDATE'\n"
+ "WHEN 'd' THEN 'DELETE'\n"
+ "WHEN '*' THEN 'ALL'\n"
+ "END AS cmd\n"
+ "FROM pg_catalog.pg_policy pol\n"
+ "WHERE pol.polrelid = '%s' ORDER BY 1;",
+ oid);
+ else
+ printfPQExpBuffer(&buf,
+ "SELECT pol.polname, 't' as polpermissive,\n"
+ "CASE WHEN pol.polroles = '{0}' THEN NULL ELSE array_to_string(array(select rolname from pg_roles where oid = any (pol.polroles) order by 1),',') END,\n"
+ "pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),\n"
+ "pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),\n"
+ "CASE pol.polcmd \n"
+ "WHEN 'r' THEN 'SELECT'\n"
+ "WHEN 'a' THEN 'INSERT'\n"
+ "WHEN 'w' THEN 'UPDATE'\n"
+ "WHEN 'd' THEN 'DELETE'\n"
+ "WHEN '*' THEN 'ALL'\n"
+ "END AS cmd\n"
+ "FROM pg_catalog.pg_policy pol\n"
+ "WHERE pol.polrelid = '%s' ORDER BY 1;",
+ oid);
result = PSQLexec(buf.data);
if (!result)
@@ -2181,26 +2232,32 @@ describeOneTableDetails(const char *schemaname,
/* Might be an empty set - that's ok */
for (i = 0; i < tuples; i++)
{
- printfPQExpBuffer(&buf, " POLICY \"%s\"",
- PQgetvalue(result, i, 0));
+ char *polpermissive = PQgetvalue(result, i, 1);
- if (!PQgetisnull(result, i, 4))
+ if (strcmp(polpermissive,"t") == 0)
+ printfPQExpBuffer(&buf, " POLICY \"%s\"",
+ PQgetvalue(result, i, 0));
+ else
+ printfPQExpBuffer(&buf, " RESTRICT POLICY \"%s\"",
+ PQgetvalue(result, i, 0));
+
+ if (!PQgetisnull(result, i, 5))
appendPQExpBuffer(&buf, " FOR %s",
- PQgetvalue(result, i, 4));
+ PQgetvalue(result, i, 5));
- if (!PQgetisnull(result, i, 1))
+ if (!PQgetisnull(result, i, 2))
{
appendPQExpBuffer(&buf, "\n TO %s",
- PQgetvalue(result, i, 1));
+ PQgetvalue(result, i, 2));
}
- if (!PQgetisnull(result, i, 2))
+ if (!PQgetisnull(result, i, 3))
appendPQExpBuffer(&buf, "\n USING (%s)",
- PQgetvalue(result, i, 2));
+ PQgetvalue(result, i, 3));
- if (!PQgetisnull(result, i, 3))
+ if (!PQgetisnull(result, i, 4))
appendPQExpBuffer(&buf, "\n WITH CHECK (%s)",
- PQgetvalue(result, i, 3));
+ PQgetvalue(result, i, 4));
printTableAddFooter(&cont, buf.data);
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index 50a45eb..fb9d1e2 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -2112,9 +2112,15 @@ psql_completion(const char *text, int start, int end)
/* Complete "CREATE POLICY <name> ON <table>" */
else if (Matches4("CREATE", "POLICY", MatchAny, "ON"))
COMPLETE_WITH_SCHEMA_QUERY(Query_for_list_of_tables, NULL);
- /* Complete "CREATE POLICY <name> ON <table> FOR|TO|USING|WITH CHECK" */
+ /* Complete "CREATE POLICY <name> ON <table> AS|FOR|TO|USING|WITH CHECK" */
else if (Matches5("CREATE", "POLICY", MatchAny, "ON", MatchAny))
- COMPLETE_WITH_LIST4("FOR", "TO", "USING (", "WITH CHECK (");
+ COMPLETE_WITH_LIST5("AS", "FOR", "TO", "USING (", "WITH CHECK (");
+ /* CREATE POLICY <name> ON <table> AS PERMISSIVE|RESTRICTIVE */
+ else if (Matches6("CREATE", "POLICY", MatchAny, "ON", MatchAny, "AS"))
+ COMPLETE_WITH_LIST2("PERMISSIVE", "RESTRICTIVE");
+ /* CREATE POLICY <name> ON <table> AS PERMISSIVE|RESTRICTIVE FOR|TO|USING|WITH CHECK */
+ else if (Matches7("CREATE", "POLICY", MatchAny, "ON", MatchAny, "AS", MatchAny))
+ COMPLETE_WITH_LIST4("FOR", "TO", "USING", "WITH CHECK");
/* CREATE POLICY <name> ON <table> FOR ALL|SELECT|INSERT|UPDATE|DELETE */
else if (Matches6("CREATE", "POLICY", MatchAny, "ON", MatchAny, "FOR"))
COMPLETE_WITH_LIST5("ALL", "SELECT", "INSERT", "UPDATE", "DELETE");
@@ -2133,6 +2139,25 @@ psql_completion(const char *text, int start, int end)
/* Complete "CREATE POLICY <name> ON <table> USING (" */
else if (Matches6("CREATE", "POLICY", MatchAny, "ON", MatchAny, "USING"))
COMPLETE_WITH_CONST("(");
+ /* CREATE POLICY <name> ON <table> AS PERMISSIVE|RESTRICTIVE FOR ALL|SELECT|INSERT|UPDATE|DELETE */
+ else if (Matches8("CREATE", "POLICY", MatchAny, "ON", MatchAny, "AS", MatchAny, "FOR"))
+ COMPLETE_WITH_LIST5("ALL", "SELECT", "INSERT", "UPDATE", "DELETE");
+ /* Complete "CREATE POLICY <name> ON <table> AS PERMISSIVE|RESTRICTIVE FOR INSERT TO|WITH CHECK" */
+ else if (Matches9("CREATE", "POLICY", MatchAny, "ON", MatchAny, "AS", MatchAny, "FOR", "INSERT"))
+ COMPLETE_WITH_LIST2("TO", "WITH CHECK (");
+ /* Complete "CREATE POLICY <name> ON <table> AS PERMISSIVE|RESTRICTIVE FOR SELECT|DELETE TO|USING" */
+ else if (Matches9("CREATE", "POLICY", MatchAny, "ON", MatchAny, "AS", MatchAny, "FOR", "SELECT|DELETE"))
+ COMPLETE_WITH_LIST2("TO", "USING (");
+ /* CREATE POLICY <name> ON <table> AS PERMISSIVE|RESTRICTIVE FOR ALL|UPDATE TO|USING|WITH CHECK */
+ else if (Matches9("CREATE", "POLICY", MatchAny, "ON", MatchAny, "AS", MatchAny, "FOR", "ALL|UPDATE"))
+ COMPLETE_WITH_LIST3("TO", "USING (", "WITH CHECK (");
+ /* Complete "CREATE POLICY <name> ON <table> AS PERMISSIVE|RESTRICTIVE TO <role>" */
+ else if (Matches8("CREATE", "POLICY", MatchAny, "ON", MatchAny, "AS", MatchAny, "TO"))
+ COMPLETE_WITH_QUERY(Query_for_list_of_grant_roles);
+ /* Complete "CREATE POLICY <name> ON <table> AS PERMISSIVE|RESTRICTIVE USING (" */
+ else if (Matches8("CREATE", "POLICY", MatchAny, "ON", MatchAny, "AS", MatchAny, "USING"))
+ COMPLETE_WITH_CONST("(");
+
/* CREATE RULE */
/* Complete "CREATE RULE <sth>" with "AS ON" */
diff --git a/src/include/catalog/pg_policy.h b/src/include/catalog/pg_policy.h
index d73e9c2..30dc367 100644
--- a/src/include/catalog/pg_policy.h
+++ b/src/include/catalog/pg_policy.h
@@ -23,6 +23,7 @@ CATALOG(pg_policy,3256)
NameData polname; /* Policy name. */
Oid polrelid; /* Oid of the relation with policy. */
char polcmd; /* One of ACL_*_CHR, or '*' for all */
+ bool polpermissive; /* restrictive or permissive policy */
#ifdef CATALOG_VARLEN
Oid polroles[1]; /* Roles associated with policy, not-NULL */
@@ -42,12 +43,13 @@ typedef FormData_pg_policy *Form_pg_policy;
* compiler constants for pg_policy
* ----------------
*/
-#define Natts_pg_policy 6
-#define Anum_pg_policy_polname 1
-#define Anum_pg_policy_polrelid 2
-#define Anum_pg_policy_polcmd 3
-#define Anum_pg_policy_polroles 4
-#define Anum_pg_policy_polqual 5
-#define Anum_pg_policy_polwithcheck 6
+#define Natts_pg_policy 6
+#define Anum_pg_policy_polname 1
+#define Anum_pg_policy_polrelid 2
+#define Anum_pg_policy_polcmd 3
+#define Anum_pg_policy_polpermissive 4
+#define Anum_pg_policy_polroles 5
+#define Anum_pg_policy_polqual 6
+#define Anum_pg_policy_polwithcheck 7
#endif /* PG_POLICY_H */
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 6de2cab..fc0a414 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -2055,6 +2055,7 @@ typedef struct CreatePolicyStmt
char *policy_name; /* Policy's name */
RangeVar *table; /* the table name the policy applies to */
char *cmd_name; /* the command name the policy applies to */
+ bool permissive; /* restrictive or permissive policy */
List *roles; /* the roles associated with the policy */
Node *qual; /* the policy's condition */
Node *with_check; /* the policy's WITH CHECK condition. */
diff --git a/src/include/rewrite/rowsecurity.h b/src/include/rewrite/rowsecurity.h
index fd0cbaff..2f3db8c 100644
--- a/src/include/rewrite/rowsecurity.h
+++ b/src/include/rewrite/rowsecurity.h
@@ -22,6 +22,7 @@ typedef struct RowSecurityPolicy
char *policy_name; /* Name of the policy */
char polcmd; /* Type of command policy is for */
ArrayType *roles; /* Array of roles policy is for */
+ bool permissive; /* restrictive or permissive policy */
Expr *qual; /* Expression to filter rows */
Expr *with_check_qual; /* Expression to limit rows allowed */
bool hassublinks; /* If either expression has sublinks */
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index abfee92..da5269a 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -7,6 +7,7 @@ SET client_min_messages TO 'warning';
DROP USER IF EXISTS regress_rls_alice;
DROP USER IF EXISTS regress_rls_bob;
DROP USER IF EXISTS regress_rls_carol;
+DROP USER IF EXISTS regress_rls_dave;
DROP USER IF EXISTS regress_rls_exempt_user;
DROP ROLE IF EXISTS regress_rls_group1;
DROP ROLE IF EXISTS regress_rls_group2;
@@ -16,6 +17,7 @@ RESET client_min_messages;
CREATE USER regress_rls_alice NOLOGIN;
CREATE USER regress_rls_bob NOLOGIN;
CREATE USER regress_rls_carol NOLOGIN;
+CREATE USER regress_rls_dave NOLOGIN;
CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN;
CREATE ROLE regress_rls_group1 NOLOGIN;
CREATE ROLE regress_rls_group2 NOLOGIN;
@@ -67,11 +69,16 @@ INSERT INTO document VALUES
( 5, 44, 2, 'regress_rls_bob', 'my second manga'),
( 6, 22, 1, 'regress_rls_carol', 'great science fiction'),
( 7, 33, 2, 'regress_rls_carol', 'great technology book'),
- ( 8, 44, 1, 'regress_rls_carol', 'great manga');
+ ( 8, 44, 1, 'regress_rls_carol', 'great manga'),
+ ( 9, 22, 1, 'regress_rls_dave', 'awseome science fiction'),
+ (10, 33, 2, 'regress_rls_dave', 'awseome technology book');
ALTER TABLE document ENABLE ROW LEVEL SECURITY;
-- user's security level must be higher than or equal to document's
-CREATE POLICY p1 ON document
+CREATE POLICY p1 ON document AS PERMISSIVE
USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
+-- but Dave isn't allowed to see manga documents
+CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave
+ USING (cid <> 44);
-- viewpoint from regress_rls_bob
SET SESSION AUTHORIZATION regress_rls_bob;
SET row_security TO ON;
@@ -80,26 +87,30 @@ NOTICE: f_leak => my first novel
NOTICE: f_leak => my first manga
NOTICE: f_leak => great science fiction
NOTICE: f_leak => great manga
- did | cid | dlevel | dauthor | dtitle
------+-----+--------+-------------------+-----------------------
+NOTICE: f_leak => awseome science fiction
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
1 | 11 | 1 | regress_rls_bob | my first novel
4 | 44 | 1 | regress_rls_bob | my first manga
6 | 22 | 1 | regress_rls_carol | great science fiction
8 | 44 | 1 | regress_rls_carol | great manga
-(4 rows)
+ 9 | 22 | 1 | regress_rls_dave | awseome science fiction
+(5 rows)
SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
NOTICE: f_leak => my first novel
NOTICE: f_leak => my first manga
NOTICE: f_leak => great science fiction
NOTICE: f_leak => great manga
- cid | did | dlevel | dauthor | dtitle | cname
------+-----+--------+-------------------+-----------------------+-----------------
- 11 | 1 | 1 | regress_rls_bob | my first novel | novel
- 44 | 4 | 1 | regress_rls_bob | my first manga | manga
- 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction
- 44 | 8 | 1 | regress_rls_carol | great manga | manga
-(4 rows)
+NOTICE: f_leak => awseome science fiction
+ cid | did | dlevel | dauthor | dtitle | cname
+-----+-----+--------+-------------------+-------------------------+-----------------
+ 11 | 1 | 1 | regress_rls_bob | my first novel | novel
+ 44 | 4 | 1 | regress_rls_bob | my first manga | manga
+ 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction
+ 44 | 8 | 1 | regress_rls_carol | great manga | manga
+ 22 | 9 | 1 | regress_rls_dave | awseome science fiction | science fiction
+(5 rows)
-- try a sampled version
SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
@@ -107,12 +118,14 @@ SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
NOTICE: f_leak => my first manga
NOTICE: f_leak => great science fiction
NOTICE: f_leak => great manga
- did | cid | dlevel | dauthor | dtitle
------+-----+--------+-------------------+-----------------------
+NOTICE: f_leak => awseome science fiction
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
4 | 44 | 1 | regress_rls_bob | my first manga
6 | 22 | 1 | regress_rls_carol | great science fiction
8 | 44 | 1 | regress_rls_carol | great manga
-(3 rows)
+ 9 | 22 | 1 | regress_rls_dave | awseome science fiction
+(4 rows)
-- viewpoint from regress_rls_carol
SET SESSION AUTHORIZATION regress_rls_carol;
@@ -125,8 +138,10 @@ NOTICE: f_leak => my second manga
NOTICE: f_leak => great science fiction
NOTICE: f_leak => great technology book
NOTICE: f_leak => great manga
- did | cid | dlevel | dauthor | dtitle
------+-----+--------+-------------------+-----------------------
+NOTICE: f_leak => awseome science fiction
+NOTICE: f_leak => awseome technology book
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
1 | 11 | 1 | regress_rls_bob | my first novel
2 | 11 | 2 | regress_rls_bob | my second novel
3 | 22 | 2 | regress_rls_bob | my science fiction
@@ -135,7 +150,9 @@ NOTICE: f_leak => great manga
6 | 22 | 1 | regress_rls_carol | great science fiction
7 | 33 | 2 | regress_rls_carol | great technology book
8 | 44 | 1 | regress_rls_carol | great manga
-(8 rows)
+ 9 | 22 | 1 | regress_rls_dave | awseome science fiction
+ 10 | 33 | 2 | regress_rls_dave | awseome technology book
+(10 rows)
SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
NOTICE: f_leak => my first novel
@@ -146,17 +163,21 @@ NOTICE: f_leak => my second manga
NOTICE: f_leak => great science fiction
NOTICE: f_leak => great technology book
NOTICE: f_leak => great manga
- cid | did | dlevel | dauthor | dtitle | cname
------+-----+--------+-------------------+-----------------------+-----------------
- 11 | 1 | 1 | regress_rls_bob | my first novel | novel
- 11 | 2 | 2 | regress_rls_bob | my second novel | novel
- 22 | 3 | 2 | regress_rls_bob | my science fiction | science fiction
- 44 | 4 | 1 | regress_rls_bob | my first manga | manga
- 44 | 5 | 2 | regress_rls_bob | my second manga | manga
- 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction
- 33 | 7 | 2 | regress_rls_carol | great technology book | technology
- 44 | 8 | 1 | regress_rls_carol | great manga | manga
-(8 rows)
+NOTICE: f_leak => awseome science fiction
+NOTICE: f_leak => awseome technology book
+ cid | did | dlevel | dauthor | dtitle | cname
+-----+-----+--------+-------------------+-------------------------+-----------------
+ 11 | 1 | 1 | regress_rls_bob | my first novel | novel
+ 11 | 2 | 2 | regress_rls_bob | my second novel | novel
+ 22 | 3 | 2 | regress_rls_bob | my science fiction | science fiction
+ 44 | 4 | 1 | regress_rls_bob | my first manga | manga
+ 44 | 5 | 2 | regress_rls_bob | my second manga | manga
+ 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction
+ 33 | 7 | 2 | regress_rls_carol | great technology book | technology
+ 44 | 8 | 1 | regress_rls_carol | great manga | manga
+ 22 | 9 | 1 | regress_rls_dave | awseome science fiction | science fiction
+ 33 | 10 | 2 | regress_rls_dave | awseome technology book | technology
+(10 rows)
-- try a sampled version
SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
@@ -165,13 +186,15 @@ NOTICE: f_leak => my first manga
NOTICE: f_leak => my second manga
NOTICE: f_leak => great science fiction
NOTICE: f_leak => great manga
- did | cid | dlevel | dauthor | dtitle
------+-----+--------+-------------------+-----------------------
+NOTICE: f_leak => awseome science fiction
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
4 | 44 | 1 | regress_rls_bob | my first manga
5 | 44 | 2 | regress_rls_bob | my second manga
6 | 22 | 1 | regress_rls_carol | great science fiction
8 | 44 | 1 | regress_rls_carol | great manga
-(4 rows)
+ 9 | 22 | 1 | regress_rls_dave | awseome science fiction
+(5 rows)
EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
QUERY PLAN
@@ -201,6 +224,74 @@ EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dt
Index Cond: (pguser = CURRENT_USER)
(11 rows)
+-- viewpoint from regress_rls_dave
+SET SESSION AUTHORIZATION regress_rls_dave;
+SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
+NOTICE: f_leak => my first novel
+NOTICE: f_leak => my second novel
+NOTICE: f_leak => my science fiction
+NOTICE: f_leak => great science fiction
+NOTICE: f_leak => great technology book
+NOTICE: f_leak => awseome science fiction
+NOTICE: f_leak => awseome technology book
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
+ 1 | 11 | 1 | regress_rls_bob | my first novel
+ 2 | 11 | 2 | regress_rls_bob | my second novel
+ 3 | 22 | 2 | regress_rls_bob | my science fiction
+ 6 | 22 | 1 | regress_rls_carol | great science fiction
+ 7 | 33 | 2 | regress_rls_carol | great technology book
+ 9 | 22 | 1 | regress_rls_dave | awseome science fiction
+ 10 | 33 | 2 | regress_rls_dave | awseome technology book
+(7 rows)
+
+SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
+NOTICE: f_leak => my first novel
+NOTICE: f_leak => my second novel
+NOTICE: f_leak => my science fiction
+NOTICE: f_leak => great science fiction
+NOTICE: f_leak => great technology book
+NOTICE: f_leak => awseome science fiction
+NOTICE: f_leak => awseome technology book
+ cid | did | dlevel | dauthor | dtitle | cname
+-----+-----+--------+-------------------+-------------------------+-----------------
+ 11 | 1 | 1 | regress_rls_bob | my first novel | novel
+ 11 | 2 | 2 | regress_rls_bob | my second novel | novel
+ 22 | 3 | 2 | regress_rls_bob | my science fiction | science fiction
+ 22 | 6 | 1 | regress_rls_carol | great science fiction | science fiction
+ 33 | 7 | 2 | regress_rls_carol | great technology book | technology
+ 22 | 9 | 1 | regress_rls_dave | awseome science fiction | science fiction
+ 33 | 10 | 2 | regress_rls_dave | awseome technology book | technology
+(7 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
+ QUERY PLAN
+----------------------------------------------------------
+ Subquery Scan on document
+ Filter: f_leak(document.dtitle)
+ -> Seq Scan on document document_1
+ Filter: ((cid <> 44) AND (dlevel <= $0))
+ InitPlan 1 (returns $0)
+ -> Index Scan using uaccount_pkey on uaccount
+ Index Cond: (pguser = CURRENT_USER)
+(7 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+ QUERY PLAN
+----------------------------------------------------------------------
+ Hash Join
+ Hash Cond: (category.cid = document.cid)
+ -> Seq Scan on category
+ -> Hash
+ -> Subquery Scan on document
+ Filter: f_leak(document.dtitle)
+ -> Seq Scan on document document_1
+ Filter: ((cid <> 44) AND (dlevel <= $0))
+ InitPlan 1 (returns $0)
+ -> Index Scan using uaccount_pkey on uaccount
+ Index Cond: (pguser = CURRENT_USER)
+(11 rows)
+
-- only owner can change policies
ALTER POLICY p1 ON document USING (true); --fail
ERROR: must be owner of relation document
@@ -318,7 +409,7 @@ SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
7 | 33 | 2 | regress_rls_carol | great technology book | |
(3 rows)
-INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge');
+INSERT INTO document VALUES (11, 33, 1, current_user, 'hoge');
-- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
SET SESSION AUTHORIZATION regress_rls_bob;
INSERT INTO document VALUES (8, 44, 1, 'regress_rls_bob', 'my third manga'); -- Must fail with unique violation, revealing presence of did we can't see
@@ -337,8 +428,8 @@ ERROR: new row violates row-level security policy for table "document"
RESET SESSION AUTHORIZATION;
SET row_security TO ON;
SELECT * FROM document;
- did | cid | dlevel | dauthor | dtitle
------+-----+--------+-------------------+-----------------------
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
1 | 11 | 1 | regress_rls_bob | my first novel
2 | 11 | 2 | regress_rls_bob | my second novel
3 | 22 | 2 | regress_rls_bob | my science fiction
@@ -347,8 +438,10 @@ SELECT * FROM document;
6 | 22 | 1 | regress_rls_carol | great science fiction
7 | 33 | 2 | regress_rls_carol | great technology book
8 | 44 | 1 | regress_rls_carol | great manga
- 10 | 33 | 1 | regress_rls_carol | hoge
-(9 rows)
+ 9 | 22 | 1 | regress_rls_dave | awseome science fiction
+ 10 | 33 | 2 | regress_rls_dave | awseome technology book
+ 11 | 33 | 1 | regress_rls_carol | hoge
+(11 rows)
SELECT * FROM category;
cid | cname
@@ -363,8 +456,8 @@ SELECT * FROM category;
RESET SESSION AUTHORIZATION;
SET row_security TO OFF;
SELECT * FROM document;
- did | cid | dlevel | dauthor | dtitle
------+-----+--------+-------------------+-----------------------
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
1 | 11 | 1 | regress_rls_bob | my first novel
2 | 11 | 2 | regress_rls_bob | my second novel
3 | 22 | 2 | regress_rls_bob | my science fiction
@@ -373,8 +466,10 @@ SELECT * FROM document;
6 | 22 | 1 | regress_rls_carol | great science fiction
7 | 33 | 2 | regress_rls_carol | great technology book
8 | 44 | 1 | regress_rls_carol | great manga
- 10 | 33 | 1 | regress_rls_carol | hoge
-(9 rows)
+ 9 | 22 | 1 | regress_rls_dave | awseome science fiction
+ 10 | 33 | 2 | regress_rls_dave | awseome technology book
+ 11 | 33 | 1 | regress_rls_carol | hoge
+(11 rows)
SELECT * FROM category;
cid | cname
@@ -389,8 +484,8 @@ SELECT * FROM category;
SET SESSION AUTHORIZATION regress_rls_exempt_user;
SET row_security TO OFF;
SELECT * FROM document;
- did | cid | dlevel | dauthor | dtitle
------+-----+--------+-------------------+-----------------------
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
1 | 11 | 1 | regress_rls_bob | my first novel
2 | 11 | 2 | regress_rls_bob | my second novel
3 | 22 | 2 | regress_rls_bob | my science fiction
@@ -399,8 +494,10 @@ SELECT * FROM document;
6 | 22 | 1 | regress_rls_carol | great science fiction
7 | 33 | 2 | regress_rls_carol | great technology book
8 | 44 | 1 | regress_rls_carol | great manga
- 10 | 33 | 1 | regress_rls_carol | hoge
-(9 rows)
+ 9 | 22 | 1 | regress_rls_dave | awseome science fiction
+ 10 | 33 | 2 | regress_rls_dave | awseome technology book
+ 11 | 33 | 1 | regress_rls_carol | hoge
+(11 rows)
SELECT * FROM category;
cid | cname
@@ -415,8 +512,8 @@ SELECT * FROM category;
SET SESSION AUTHORIZATION regress_rls_alice;
SET row_security TO ON;
SELECT * FROM document;
- did | cid | dlevel | dauthor | dtitle
------+-----+--------+-------------------+-----------------------
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
1 | 11 | 1 | regress_rls_bob | my first novel
2 | 11 | 2 | regress_rls_bob | my second novel
3 | 22 | 2 | regress_rls_bob | my science fiction
@@ -425,8 +522,10 @@ SELECT * FROM document;
6 | 22 | 1 | regress_rls_carol | great science fiction
7 | 33 | 2 | regress_rls_carol | great technology book
8 | 44 | 1 | regress_rls_carol | great manga
- 10 | 33 | 1 | regress_rls_carol | hoge
-(9 rows)
+ 9 | 22 | 1 | regress_rls_dave | awseome science fiction
+ 10 | 33 | 2 | regress_rls_dave | awseome technology book
+ 11 | 33 | 1 | regress_rls_carol | hoge
+(11 rows)
SELECT * FROM category;
cid | cname
@@ -441,8 +540,8 @@ SELECT * FROM category;
SET SESSION AUTHORIZATION regress_rls_alice;
SET row_security TO OFF;
SELECT * FROM document;
- did | cid | dlevel | dauthor | dtitle
------+-----+--------+-------------------+-----------------------
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-------------------------
1 | 11 | 1 | regress_rls_bob | my first novel
2 | 11 | 2 | regress_rls_bob | my second novel
3 | 22 | 2 | regress_rls_bob | my science fiction
@@ -451,8 +550,10 @@ SELECT * FROM document;
6 | 22 | 1 | regress_rls_carol | great science fiction
7 | 33 | 2 | regress_rls_carol | great technology book
8 | 44 | 1 | regress_rls_carol | great manga
- 10 | 33 | 1 | regress_rls_carol | hoge
-(9 rows)
+ 9 | 22 | 1 | regress_rls_dave | awseome science fiction
+ 10 | 33 | 2 | regress_rls_dave | awseome technology book
+ 11 | 33 | 1 | regress_rls_carol | hoge
+(11 rows)
SELECT * FROM category;
cid | cname
@@ -1517,6 +1618,7 @@ SELECT * FROM b1;
--
SET SESSION AUTHORIZATION regress_rls_alice;
DROP POLICY p1 ON document;
+DROP POLICY p1r ON document;
CREATE POLICY p1 ON document FOR SELECT USING (true);
CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);
CREATE POLICY p3 ON document FOR UPDATE
@@ -3461,6 +3563,7 @@ RESET client_min_messages;
DROP USER regress_rls_alice;
DROP USER regress_rls_bob;
DROP USER regress_rls_carol;
+DROP USER regress_rls_dave;
DROP USER regress_rls_exempt_user;
DROP ROLE regress_rls_group1;
DROP ROLE regress_rls_group2;
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
index 7fcefe4..98a0f1f 100644
--- a/src/test/regress/sql/rowsecurity.sql
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -10,6 +10,7 @@ SET client_min_messages TO 'warning';
DROP USER IF EXISTS regress_rls_alice;
DROP USER IF EXISTS regress_rls_bob;
DROP USER IF EXISTS regress_rls_carol;
+DROP USER IF EXISTS regress_rls_dave;
DROP USER IF EXISTS regress_rls_exempt_user;
DROP ROLE IF EXISTS regress_rls_group1;
DROP ROLE IF EXISTS regress_rls_group2;
@@ -22,6 +23,7 @@ RESET client_min_messages;
CREATE USER regress_rls_alice NOLOGIN;
CREATE USER regress_rls_bob NOLOGIN;
CREATE USER regress_rls_carol NOLOGIN;
+CREATE USER regress_rls_dave NOLOGIN;
CREATE USER regress_rls_exempt_user BYPASSRLS NOLOGIN;
CREATE ROLE regress_rls_group1 NOLOGIN;
CREATE ROLE regress_rls_group2 NOLOGIN;
@@ -80,14 +82,20 @@ INSERT INTO document VALUES
( 5, 44, 2, 'regress_rls_bob', 'my second manga'),
( 6, 22, 1, 'regress_rls_carol', 'great science fiction'),
( 7, 33, 2, 'regress_rls_carol', 'great technology book'),
- ( 8, 44, 1, 'regress_rls_carol', 'great manga');
+ ( 8, 44, 1, 'regress_rls_carol', 'great manga'),
+ ( 9, 22, 1, 'regress_rls_dave', 'awseome science fiction'),
+ (10, 33, 2, 'regress_rls_dave', 'awseome technology book');
ALTER TABLE document ENABLE ROW LEVEL SECURITY;
-- user's security level must be higher than or equal to document's
-CREATE POLICY p1 ON document
+CREATE POLICY p1 ON document AS PERMISSIVE
USING (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
+-- but Dave isn't allowed to see manga documents
+CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave
+ USING (cid <> 44);
+
-- viewpoint from regress_rls_bob
SET SESSION AUTHORIZATION regress_rls_bob;
SET row_security TO ON;
@@ -110,6 +118,14 @@ SELECT * FROM document TABLESAMPLE BERNOULLI(50) REPEATABLE(0)
EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+-- viewpoint from regress_rls_dave
+SET SESSION AUTHORIZATION regress_rls_dave;
+SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did;
+SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did;
+
+EXPLAIN (COSTS OFF) SELECT * FROM document WHERE f_leak(dtitle);
+EXPLAIN (COSTS OFF) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+
-- only owner can change policies
ALTER POLICY p1 ON document USING (true); --fail
DROP POLICY p1 ON document; --fail
@@ -147,7 +163,7 @@ DELETE FROM category WHERE cid = 33; -- fails with FK violation
-- can insert FK referencing invisible PK
SET SESSION AUTHORIZATION regress_rls_carol;
SELECT * FROM document d FULL OUTER JOIN category c on d.cid = c.cid;
-INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge');
+INSERT INTO document VALUES (11, 33, 1, current_user, 'hoge');
-- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
SET SESSION AUTHORIZATION regress_rls_bob;
@@ -517,6 +533,7 @@ SELECT * FROM b1;
SET SESSION AUTHORIZATION regress_rls_alice;
DROP POLICY p1 ON document;
+DROP POLICY p1r ON document;
CREATE POLICY p1 ON document FOR SELECT USING (true);
CREATE POLICY p2 ON document FOR INSERT WITH CHECK (dauthor = current_user);
@@ -1577,6 +1594,7 @@ RESET client_min_messages;
DROP USER regress_rls_alice;
DROP USER regress_rls_bob;
DROP USER regress_rls_carol;
+DROP USER regress_rls_dave;
DROP USER regress_rls_exempt_user;
DROP ROLE regress_rls_group1;
DROP ROLE regress_rls_group2;
--
2.7.4
signature.asc
Description: Digital signature
