Row-security writer-side checks proposal
Started by Craig Ringerabout 12 years ago11 messages
Hi
I've been looking some more into write-side checks in row-security and
have a suggestion.
Even though write-side checks are actually fairly separate to read
checks, and can be done as another step, I'd like to think about them
before the catalog format and syntax are settled. I think we need fields
for write operations in pg_rowsecurity and the syntax to set them so
that the catalog information can be used by triggers to enforce write
checks. Even if, for the first cut, they're not supported by built-in
auto-created triggers.
Here's my proposal, let me know what you think:
SET ROW SECURITY FOR { ALL COMMANDS | {[SELECT,INSERT,UPDATE,DELETE}+}
in other words, you specify either:
SET ROW SECURITY FOR ALL COMMANDS
or a command-list like:
SET ROW SECURITY FOR INSERT OR UPDATE OR DELETE
(Intentionally the same as CREATE TRIGGER ... FOR INSERT OR UPDATE OR
DELETE ...)
The subtlety here is that the "SELECT" clause applies to the *read part*
of an UPDATE or DELETE too, just like the current implementation. That
protects us against leaks via RETURNING, and ensures that the
row-security policy is consistent. The "INSERT", "UPDATE" or "DELETE"
part of the policy would *only* be used by write checks that verify that
a new tuple being written meets the row-security criteria.
For INSERT, that's obvious: check the insert policy and see if the tuple
should be allowed; if not, raise permission denied. The SELECT predicate
doesn't matter since it's not reading from the target table (except
possibly via join/subquery, where it is already applied).
For UPDATE, we only ever try to update tuples the select policy allows
us to see. Row security already does this thanks to Kohei KaiGai's great
work. The write side check (just a trigger) only needs to make sure the
new tuple meets the UPDATE predicate.
For DELETE, the predicate controls whether the user can delete the
tuple, so it's possible to have row-security policies that let users
read but not delete some tuples.
If the catalog fields and syntax for setting them are included in the
patch the first time around then users can use that information in their
own triggers, and we can provide canned ones in the documentation if we
run out of time to write C triggers that are automatically created like
FK checks are.
That keeps the patch smaller, since it separates the write and read
row-security.
Opinions?
I'm cooking up an adjustment to Kohei KaiGai's RLS patch with this
change now. Meanwhile I'm attaching my most recent update of his patch,
which includes:
* Rebase on top of head
* Documentation updates and rewording
* Fixes some missed renaming of "rls" and variants to "rowsecurity"
* Additional regression tests demonstrating the problems with
handling of portals (cursors and SECURITY DEFINER functions
returning refcursor). These tests intentionally fail as their
expected file contains what _should_ happen not what does.
* Additional regression tests demonstrating that foreign key
enforcement is affected inconsistently by RLS, including the
broken example with superuser I posted previously. Again
it intentionally fails with expected containing what I think
should happen.
* Test cases to demonstrate that RS leaks information via
UNIQUE constraints and that this is expected.
* Documentation on use of pg_get_expr for decoding the
rowsecurity expressions in pg_rowsecurity into readable
SQL expressions usable via EXECUTE
The current tree is here, rebased on top of today's master:
https://github.com/ringerc/postgres/tree/rls-9.4
(this branch is rebased regularly!)
I've attached an updated squashed patch against today's master/head for
anyone who wants to give it a go or take a look. "make check" is
supposed to fail, since what should happen isn't yet what the code
actually does.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
Attachments:
0001-RLS-v4-rebased-onto-master-plus-changes-by-Craig.patchtext/x-patch; name=0001-RLS-v4-rebased-onto-master-plus-changes-by-Craig.patchDownload
>From 4f8bb1fe02f0a6ff869fff4e7f1f77424d046452 Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@2ndquadrant.com>
Date: Wed, 23 Oct 2013 16:16:55 +0800
Subject: [PATCH] RLS v4 rebased onto master plus changes by Craig
Merged by git apply --3way with manual fixups for oid
conflicts and a mismerge in the regression tests.
Add tests for SECURITY DEFINER in RLS, see 52689207.4060605@2ndquadrant.com
Add tests for RLS to show that PK / UNIQUE constraints reveal keys
Test case to demonstrate that RLS leaks information via unique_violation
Fix omitted rowlevel -> rowsecurity changes
Update and tidy up documentation
Document use of pg_get_expr for decoding rowsecurity codes
Document how to get the RLS expression from the catalogs
Test cases for cursors - expected to fail
Demonstrate problem with FK constraints and superuser exemption
---
doc/src/sgml/catalogs.sgml | 67 ++
doc/src/sgml/ref/alter_table.sgml | 43 +
doc/src/sgml/user-manag.sgml | 159 ++++
src/backend/catalog/Makefile | 4 +-
src/backend/catalog/dependency.c | 8 +
src/backend/catalog/heap.c | 1 +
src/backend/catalog/objectaddress.c | 50 ++
src/backend/catalog/pg_rowsecurity.c | 337 ++++++++
src/backend/commands/copy.c | 90 +-
src/backend/commands/event_trigger.c | 1 +
src/backend/commands/tablecmds.c | 27 +
src/backend/executor/execMain.c | 27 +-
src/backend/nodes/copyfuncs.c | 3 +
src/backend/nodes/equalfuncs.c | 3 +
src/backend/nodes/nodeFuncs.c | 12 +-
src/backend/nodes/outfuncs.c | 3 +
src/backend/nodes/readfuncs.c | 2 +
src/backend/optimizer/plan/planner.c | 23 +-
src/backend/optimizer/prep/preptlist.c | 68 +-
src/backend/optimizer/prep/prepunion.c | 95 ++-
src/backend/optimizer/util/Makefile | 2 +-
src/backend/optimizer/util/rowsecurity.c | 744 ++++++++++++++++
src/backend/parser/gram.y | 25 +
src/backend/parser/parse_agg.c | 6 +
src/backend/parser/parse_expr.c | 3 +
src/backend/rewrite/rewriteHandler.c | 16 +
src/backend/utils/adt/ri_triggers.c | 13 +-
src/backend/utils/cache/plancache.c | 32 +
src/backend/utils/cache/relcache.c | 17 +-
src/bin/pg_dump/common.c | 4 +
src/bin/pg_dump/pg_backup_archiver.c | 1 +
src/bin/pg_dump/pg_dump.c | 186 +++-
src/bin/pg_dump/pg_dump.h | 13 +-
src/bin/pg_dump/pg_dump_sort.c | 5 +
src/bin/psql/describe.c | 7 +
src/include/catalog/dependency.h | 1 +
src/include/catalog/indexing.h | 5 +
src/include/catalog/pg_class.h | 24 +-
src/include/catalog/pg_rowsecurity.h | 76 ++
src/include/miscadmin.h | 1 +
src/include/nodes/execnodes.h | 4 +
src/include/nodes/nodeFuncs.h | 1 +
src/include/nodes/parsenodes.h | 14 +-
src/include/nodes/plannodes.h | 2 +
src/include/nodes/relation.h | 6 +
src/include/optimizer/rowsecurity.h | 27 +
src/include/parser/parse_node.h | 3 +-
src/include/rewrite/rewriteHandler.h | 1 +
src/include/utils/plancache.h | 2 +
src/include/utils/rel.h | 2 +
src/test/regress/.gitignore | 4 +
src/test/regress/expected/rowsecurity.out | 1276 ++++++++++++++++++++++++++++
src/test/regress/expected/sanity_check.out | 1 +
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/rowsecurity.sql | 464 ++++++++++
56 files changed, 3965 insertions(+), 49 deletions(-)
create mode 100644 src/backend/catalog/pg_rowsecurity.c
create mode 100644 src/backend/optimizer/util/rowsecurity.c
create mode 100644 src/include/catalog/pg_rowsecurity.h
create mode 100644 src/include/optimizer/rowsecurity.h
create mode 100644 src/test/regress/expected/rowsecurity.out
create mode 100644 src/test/regress/sql/rowsecurity.sql
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 9af4697..1609477 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -234,6 +234,11 @@
</row>
<row>
+ <entry><link linkend="catalog-pg-rowsecurity"><structname>pg_rowsecurity</structname></link></entry>
+ <entry>row-level security policy of relation</entry>
+ </row>
+
+ <row>
<entry><link linkend="catalog-pg-seclabel"><structname>pg_seclabel</structname></link></entry>
<entry>security labels on database objects</entry>
</row>
@@ -1848,6 +1853,16 @@
</row>
<row>
+ <entry><structfield>relhasrowsecurity</structfield></entry>
+ <entry><type>bool</type></entry>
+ <entry></entry>
+ <entry>
+ True if table has row-security policy; see
+ <link linkend="catalog-pg-rowsecurity"><structname>pg_rowsecurity</structname></link> catalog
+ </entry>
+ </row>
+
+ <row>
<entry><structfield>relhassubclass</structfield></entry>
<entry><type>bool</type></entry>
<entry></entry>
@@ -5104,6 +5119,58 @@
</sect1>
+ <sect1 id="catalog-pg-rowsecurity">
+ <title><structname>pg_security</structname></title>
+
+ <indexterm zone="catalog-pg-rowsecurity">
+ <primary>pg_rowsecurity</primary>
+ </indexterm>
+ <para>
+ The catalog <structname>pg_rowsecurity</structname> stores expression
+ tree of row-security policy to be performed on a particular relation.
+ </para>
+ <table>
+ <title><structname>pg_rowsecurity</structname> Columns</title>
+ <tgroup cols="4">
+ <thead>
+ <row>
+ <entry>Name</entry>
+ <entry>Type</entry>
+ <entry>References</entry>
+ <entry>Description</entry>
+ </row>
+ </thead>
+ <tbody>
+ <row>
+ <entry><structfield>rsecrelid</structfield></entry>
+ <entry><type>oid</type></entry>
+ <entry><literal><link linkend="catalog-pg-class"><structname>pg_class</structname></link>.oid</literal></entry>
+ <entry>The oid from pg_class of the table this row-security entry applies to.</entry>
+ </row>
+ <row>
+ <entry><structfield>rseccmd</structfield></entry>
+ <entry><type>char</type></entry>
+ <entry></entry>
+ <entry>The command this row-security is for. 'a' meaning all is only possible value right now.</entry>
+ </row>
+ <row>
+ <entry><structfield>rsecqual</structfield></entry>
+ <entry><type>pg_node_tree</type></entry>
+ <entry></entry>
+ <entry>An expression tree to be applied as row-security policy. You can convert this to a normal SQL expression with
+<command>SELECT pg_catalog.pg_get_expr(rs.rsecqual, c.oid) FROM pg_class c INNER JOIN pg_rowsecurity rs ON (rs.rsecrelid = c.oid) WHERE c.oid = 'tablename'::regclass;</command>
+</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ <note>
+ <para>
+ <literal>pg_class.relhasrowsecurity</literal>
+ must be true if a table has row-level security policy in this catalog.
+ </para>
+ </note>
+ </sect1>
<sect1 id="catalog-pg-seclabel">
<title><structname>pg_seclabel</structname></title>
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 2609d4a..b380852 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -69,12 +69,16 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
NOT OF
OWNER TO <replaceable class="PARAMETER">new_owner</replaceable>
SET TABLESPACE <replaceable class="PARAMETER">new_tablespace</replaceable>
+ SET ROW SECURITY FOR <replaceable class="PARAMETER">rowsec_command</replaceable> TO (<replaceable class="PARAMETER">condition</replaceable>)
+ RESET ROW SECURITY FOR <replaceable class="PARAMETER">rowsec_command</replaceable>
<phrase>and <replaceable class="PARAMETER">table_constraint_using_index</replaceable> is:</phrase>
[ CONSTRAINT <replaceable class="PARAMETER">constraint_name</replaceable> ]
{ UNIQUE | PRIMARY KEY } USING INDEX <replaceable class="PARAMETER">index_name</replaceable>
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]
+<phrase>and <replaceable class="PARAMETER">rowsec_command</replaceable> is:</phrase>
+ { ALL | SELECT | INSERT | UPDATE | DELETE }
</synopsis>
</refsynopsisdiv>
@@ -580,6 +584,31 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
</varlistentry>
<varlistentry>
+ <term><literal>SET ROW SECURITY FOR <replaceable class="PARAMETER">rowsec_command</replaceable> TO (<replaceable class="PARAMETER">condition</replaceable>)</literal></term>
+ <listitem>
+ <para>
+ This form set row-level security policy of the table.
+ Supplied <replaceable class="PARAMETER">condition</replaceable> performs
+ as if it is implicitly appended to the qualifiers of <literal>WHERE</literal>
+ clause, although mechanism guarantees to evaluate this condition earlier
+ than any other user given condition.
+ <literal>ALL</> is the only supported command type right now.
+ See also <xref linkend="ROW-SECURITY">.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RESET ROW SECURITY FOR <replaceable class="PARAMETER">rowsec_command</replaceable></literal></term>
+ <listitem>
+ <para>
+ This form reset row-level security policy of the table, if exists.
+ <literal>ALL</> is the only supported command type right now.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
<term><literal>RENAME</literal></term>
<listitem>
<para>
@@ -821,6 +850,20 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="PARAMETER">condition</replaceable></term>
+ <listitem>
+ <para>
+ An expression that returns a value of type boolean. Expect for a case
+ when queries are executed with superuser privilege, only rows for which
+ this expression returns true will be fetched, updated or deleted.
+ This expression can reference columns of the relation being configured.
+ Sub-queries can be contained within expression tree, unless referenced
+ relation recursively references the same relation.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</refsect1>
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 177ac7a..75ae780 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -439,4 +439,163 @@ DROP ROLE <replaceable>name</replaceable>;
</para>
</sect1>
+ <sect1 id="row-security">
+ <title>Row-security</title>
+ <para>
+ <productname>PostgreSQL</productname> v9.4 or later provides support for row
+ security, providing the ability to control user access to data on a
+ row-by-row level. Table owners may assign a condition that acts as a
+ security policy for the table. Only rows that satisfy the condition are be
+ visible when the table is queried by a non-superuser. As the condition
+ expression may refer to <literal>current_user</literal> and other session-
+ or user-specific values this allows returned rows to be filtered according
+ to user-defined policies and rules.
+ </para>
+ <para>
+ It is reasonable to think of row security as a dynamic <literal>SECURITY
+ BARRIER</literal> <literal>WITH CHECK OPTION</literal> updateable view that
+ is wrapped around a table when the table is accessed by a non-superuser. The
+ row security condition serves as the view's <literal>WHERE</literal>
+ clause.</para>
+ <para>
+ Row-security policy is set using the <literal>SET ROW SECURITY</literal>
+ command of <xref linkend="SQL-ALTERTABLE"> statement to set an boolean
+ expression used to filter rows. The expression may contain references to
+ columns of the table being filtered so row contents may be used to control
+ which rows are visible. Row security expressions may also contain subqueries,
+ allowing the contents of other tables to be used when making security
+ decisions.
+ </para>
+ <para>
+ For example, the following <literal>customer</literal> table has
+ <literal>uname</literal> field to store the user name. Assuming we don't
+ want to expose other customers' data, the following command would set
+ <literal>current_user = uname</literal> as the row-security policy on the
+ <literal>customer</literal> table:
+<screen>
+postgres=> ALTER TABLE customer SET ROW SECURITY
+ FOR ALL TO (current_user = uname);
+ALTER TABLE
+</screen>
+ The <xref linkend="SQL-EXPLAIN"> command shows how the applied row-security policy
+ affects query plans:
+<screen>
+postgres=> EXPLAIN(costs off) SELECT * FROM customer WHERE f_leak(upasswd);
+ QUERY PLAN
+--------------------------------------------
+ Subquery Scan on customer
+ Filter: f_leak(customer.upasswd)
+ -> Seq Scan on customer customer_1
+ Filter: ("current_user"() = uname)
+(4 rows)
+</screen>
+ This query execution plan shows that the preconfigured row-security policy
+ is implicitly added, so that the sequential scan of the real target
+ <literal>customer</literal> table is wrapped in a subquery qualified by the row
+ security condition. The subquery acts like a <literal>security_barrier</literal> view,
+ ensuring that user-supplied qualifiers, including functions with side effects,
+ are never executed earlier than the row-security policy unless they are known to be
+ safe (<literal>LEAKPROOF</literal>).
+ </para>
+ <para>
+ This design protects against user-defined functions and operators that leak
+ information through side-effects when used as query qualifiers; see <xref
+ linkend="RULES-PRIVILEGES"> for details. Superusers are exempt from
+ row-security policy in order to permit <application
+ moreinfo="none">pg_dump</application> to take a complete dump of the
+ database when used with appropriate rights, and to prevent superusers from
+ accidentally executing malicious functions in row-security conditions on
+ user-defined tables.
+ </para>
+
+ <para>
+ In the case of queries on inherited tables, the row-security policy of the
+ parent relation is <emphasis>not</emphasis> applied to child relations. The
+ scope of the row-security policy is limited to the relation on which it is
+ set. This is consistent with PostgreSQL's behaviour with other properties of
+ inherited tables, like <literal>UNIQUE</literal> and <literal>CHECK</literal>
+ constraints.
+<screen>
+postgres=> EXPLAIN(costs off) SELECT * FROM t1 WHERE f_leak(y);
+ QUERY PLAN
+-------------------------------------
+ Append
+ -> Subquery Scan on t1
+ Filter: f_leak(t1.y)
+ -> Seq Scan on t1 t1_1
+ Filter: ((x % 2) = 0)
+ -> Seq Scan on t2
+ Filter: f_leak(y)
+ -> Subquery Scan on t3
+ Filter: f_leak(t3.y)
+ -> Seq Scan on t3 t3_1
+ Filter: ((x % 2) = 1)
+(11 rows)
+</screen>
+ In the above example, <literal>t1</literal> has inherited
+ child tables <literal>t2</literal> and <literal>t3</literal>.
+ A row-security policy is set on <literal>t1</literal>
+ and <literal>t3</literal> but not on <literal>t2</literal>.
+
+ The row-security policy of <literal>t1</literal>, that <literal>x</literal>
+ must be even-number, is applied only on the base table <literal>t1</literal>,
+ not on <literal>t2</literal> or <literal>t3</literal>.
+ The child table <literal>t3</literal> has a different row-security policy;
+ <literal>x</literal> must be odd-number. This policy is applied
+ <emphasis>only</emphasis> to <literal>t3</literal>;
+ <emphasis>not</emphasis> to <literal>t1</literal> or <literal>t2</literal>.
+
+ Thus, in the example above, if <literal>t1</literal> contained values 1, 2, 3,
+ 4; <literal>t2</literal> contained 5, 6, 7, and <literal>t3</literal> contained
+ 8, 9 and 10, the even-only filter on t1, lack of filter on t2, and odd-only
+ filter on t3 would produce the result set 2, 4, 5, 6, 7, 9 from a
+ <literal>SELECT * FROM t1</literal>. All even rows from <literal>t1</literal>,
+ all rows of <literal>t2</literal> and all odd rows of <literal>t3</literal> are
+ returned in accordance with each table's security policy.
+ </para>
+
+ <para>
+ Row-security feature also affects queries for write-operations that include reads,
+ like <xref linkend="SQL-UPDATE"> and <xref linkend="SQL-DELETE">. The query will be
+ rewritten so it can only affect rows that the security policy permits to be visible,
+ like in the following example:
+<screen>
+postgres=> EXPLAIN (costs off) UPDATE customer SET email = 'alice@example.com';
+ QUERY PLAN
+--------------------------------------------------
+ Update on customer
+ -> Subquery Scan on customer
+ -> Seq Scan on customer customer_1
+ Filter: ("current_user"() = uname)
+(4 rows)
+</screen>
+ However, a second check is <emphasis>not</emphasis> applied to the modified
+ row. So users may attempt to <literal>INSERT</literal> or
+ <literal>UPDATE</literal> rows that do not satisfy the row-security policy.
+ This means that users can probe for the existence of values
+ they cannot see by attempting to create a row with the same value and checking
+ whether or not a unique violation error is raised. It also permits users
+ to write rows they can not read back, which may be undesirable for
+ some applications. A <literal>BEFORE</literal> trigger can be used to
+ prevent users from inserting rows they cannot see, but a later trigger may
+ still change the row after the check is made.
+ </para>
+
+ <para>
+ There is no intention to permit different policies for different SQL
+ commands. Even if we wanted to have different policies between <xref
+ linkend="SQL-SELECT"> and <xref linkend="SQL-DELETE"> the
+ <literal>RETURNING</> clause can leak rows anyway. In future the separate
+ concept of "read" and "write" policies may be added to permit asymmetric
+ policies in a more consistent way.
+ </para>
+
+ <para>
+ You can view the row-security policy on a table using <literal>\dt+</literal> in
+ <application>psql</application>. The row-security policy is stored in the
+ <link linkend="catalog-pg-rowsecurity"><structname>pg_rowsecurity</structname></link>
+ catalog table, where it may be accessed at the SQL level.
+ </para>
+
+ </sect1>
</chapter>
diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile
index a974bd5..beb73df 100644
--- a/src/backend/catalog/Makefile
+++ b/src/backend/catalog/Makefile
@@ -15,7 +15,7 @@ OBJS = catalog.o dependency.o heap.o index.o indexing.o namespace.o aclchk.o \
pg_constraint.o pg_conversion.o \
pg_depend.o pg_enum.o pg_inherits.o pg_largeobject.o pg_namespace.o \
pg_operator.o pg_proc.o pg_range.o pg_db_role_setting.o pg_shdepend.o \
- pg_type.o storage.o toasting.o
+ pg_rowsecurity.o pg_type.o storage.o toasting.o
BKIFILES = postgres.bki postgres.description postgres.shdescription
@@ -39,7 +39,7 @@ POSTGRES_BKI_SRCS = $(addprefix $(top_srcdir)/src/include/catalog/,\
pg_ts_config.h pg_ts_config_map.h pg_ts_dict.h \
pg_ts_parser.h pg_ts_template.h pg_extension.h \
pg_foreign_data_wrapper.h pg_foreign_server.h pg_user_mapping.h \
- pg_foreign_table.h \
+ pg_foreign_table.h pg_rowsecurity.h \
pg_default_acl.h pg_seclabel.h pg_shseclabel.h pg_collation.h pg_range.h \
toasting.h indexing.h \
)
diff --git a/src/backend/catalog/dependency.c b/src/backend/catalog/dependency.c
index fe17c96..d13b2e1 100644
--- a/src/backend/catalog/dependency.c
+++ b/src/backend/catalog/dependency.c
@@ -45,6 +45,7 @@
#include "catalog/pg_opfamily.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_rewrite.h"
+#include "catalog/pg_rowsecurity.h"
#include "catalog/pg_tablespace.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_ts_config.h"
@@ -1249,6 +1250,10 @@ doDeletion(const ObjectAddress *object, int flags)
RemoveEventTriggerById(object->objectId);
break;
+ case OCLASS_ROWSECURITY:
+ RemoveRowSecurityById(object->objectId);
+ break;
+
default:
elog(ERROR, "unrecognized object class: %u",
object->classId);
@@ -2308,6 +2313,9 @@ getObjectClass(const ObjectAddress *object)
case EventTriggerRelationId:
return OCLASS_EVENT_TRIGGER;
+
+ case RowSecurityRelationId:
+ return OCLASS_ROWSECURITY;
}
/* shouldn't get here */
diff --git a/src/backend/catalog/heap.c b/src/backend/catalog/heap.c
index 64ca312..7eb7a17 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -791,6 +791,7 @@ InsertPgClassTuple(Relation pg_class_desc,
values[Anum_pg_class_relhaspkey - 1] = BoolGetDatum(rd_rel->relhaspkey);
values[Anum_pg_class_relhasrules - 1] = BoolGetDatum(rd_rel->relhasrules);
values[Anum_pg_class_relhastriggers - 1] = BoolGetDatum(rd_rel->relhastriggers);
+ values[Anum_pg_class_relhasrowsecurity - 1] = BoolGetDatum(rd_rel->relhasrowsecurity);
values[Anum_pg_class_relhassubclass - 1] = BoolGetDatum(rd_rel->relhassubclass);
values[Anum_pg_class_relispopulated - 1] = BoolGetDatum(rd_rel->relispopulated);
values[Anum_pg_class_relfrozenxid - 1] = TransactionIdGetDatum(rd_rel->relfrozenxid);
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index cecddf1..34db6fe 100644
--- a/src/backend/catalog/objectaddress.c
+++ b/src/backend/catalog/objectaddress.c
@@ -2143,6 +2143,56 @@ getObjectDescription(const ObjectAddress *object)
break;
}
+ case OCLASS_ROWSECURITY:
+ {
+ Relation rsec_rel;
+ ScanKeyData skey;
+ SysScanDesc sscan;
+ HeapTuple tuple;
+ Form_pg_rowsecurity form_rsec;
+
+ rsec_rel = heap_open(RowSecurityRelationId, AccessShareLock);
+
+ ScanKeyInit(&skey,
+ ObjectIdAttributeNumber,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(object->objectId));
+ sscan = systable_beginscan(rsec_rel, RowSecurityOidIndexId,
+ true, NULL, 1, &skey);
+ tuple = systable_getnext(sscan);
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "could not find tuple for row-security %u",
+ object->objectId);
+ form_rsec = (Form_pg_rowsecurity) GETSTRUCT(tuple);
+
+ appendStringInfo(&buffer, _("row-security of "));
+ getRelationDescription(&buffer, form_rsec->rsecrelid);
+ switch (form_rsec->rseccmd)
+ {
+ case ROWSECURITY_CMD_ALL:
+ appendStringInfo(&buffer, _(" FOR ALL"));
+ break;
+ case ROWSECURITY_CMD_SELECT:
+ appendStringInfo(&buffer, _(" FOR SELECT"));
+ break;
+ case ROWSECURITY_CMD_INSERT:
+ appendStringInfo(&buffer, _(" FOR INSERT"));
+ break;
+ case ROWSECURITY_CMD_UPDATE:
+ appendStringInfo(&buffer, _(" FOR UPDATE"));
+ break;
+ case ROWSECURITY_CMD_DELETE:
+ appendStringInfo(&buffer, _(" FOR DELETE"));
+ break;
+ default:
+ elog(ERROR, "unexpected row-security command type: %c",
+ form_rsec->rseccmd);
+ }
+ systable_endscan(sscan);
+ heap_close(rsec_rel, AccessShareLock);
+ break;
+ }
+
default:
appendStringInfo(&buffer, "unrecognized object %u %u %d",
object->classId,
diff --git a/src/backend/catalog/pg_rowsecurity.c b/src/backend/catalog/pg_rowsecurity.c
new file mode 100644
index 0000000..34d33e8
--- /dev/null
+++ b/src/backend/catalog/pg_rowsecurity.c
@@ -0,0 +1,337 @@
+/* -------------------------------------------------------------------------
+ *
+ * pg_rowsecurity.c
+ * routines to support manipulation of the pg_rowsecurity catalog
+ *
+ * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * -------------------------------------------------------------------------
+ */
+#include "postgres.h"
+#include "access/genam.h"
+#include "access/heapam.h"
+#include "access/htup_details.h"
+#include "access/sysattr.h"
+#include "catalog/dependency.h"
+#include "catalog/indexing.h"
+#include "catalog/pg_class.h"
+#include "catalog/pg_rowsecurity.h"
+#include "catalog/pg_type.h"
+#include "nodes/nodeFuncs.h"
+#include "optimizer/clauses.h"
+#include "parser/parse_clause.h"
+#include "parser/parse_node.h"
+#include "parser/parse_relation.h"
+#include "utils/builtins.h"
+#include "utils/fmgroids.h"
+#include "utils/inval.h"
+#include "utils/rel.h"
+#include "utils/syscache.h"
+#include "utils/tqual.h"
+
+/*
+ * Load row-security policy from the catalog, and keep it on
+ * the relation cache.
+ */
+void
+RelationBuildRowSecurity(Relation relation)
+{
+ Relation catalog;
+ ScanKeyData skey;
+ SysScanDesc sscan;
+ HeapTuple tuple;
+ MemoryContext oldcxt;
+ MemoryContext rscxt = NULL;
+ RowSecurityDesc *rsdesc = NULL;
+
+ catalog = heap_open(RowSecurityRelationId, AccessShareLock);
+
+ ScanKeyInit(&skey,
+ Anum_pg_rowsecurity_rsecrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(relation)));
+ sscan = systable_beginscan(catalog, RowSecurityRelidIndexId, true,
+ NULL, 1, &skey);
+ PG_TRY();
+ {
+ while (HeapTupleIsValid(tuple = systable_getnext(sscan)))
+ {
+ Datum value;
+ bool isnull;
+ char *temp;
+
+ if (!rsdesc)
+ {
+ rscxt = AllocSetContextCreate(CacheMemoryContext,
+ "Row-security descriptor",
+ ALLOCSET_SMALL_MINSIZE,
+ ALLOCSET_SMALL_INITSIZE,
+ ALLOCSET_SMALL_MAXSIZE);
+ oldcxt = MemoryContextSwitchTo(rscxt);
+ rsdesc = palloc0(sizeof(RowSecurityDesc));
+ rsdesc->rscxt = rscxt;
+ MemoryContextSwitchTo(oldcxt);
+ }
+ value = heap_getattr(tuple, Anum_pg_rowsecurity_rseccmd,
+ RelationGetDescr(catalog), &isnull);
+ Assert(!isnull);
+
+ if (DatumGetChar(value) != ROWSECURITY_CMD_ALL)
+ {
+ ereport(WARNING,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Per-command row-security not implemented")));
+ continue;
+ }
+
+ value = heap_getattr(tuple, Anum_pg_rowsecurity_rsecqual,
+ RelationGetDescr(catalog), &isnull);
+ Assert(!isnull);
+ temp = TextDatumGetCString(value);
+
+ oldcxt = MemoryContextSwitchTo(rscxt);
+ rsdesc->rsall.rsecid = HeapTupleGetOid(tuple);
+ rsdesc->rsall.qual = (Expr *) stringToNode(temp);
+ Assert(exprType((Node *)rsdesc->rsall.qual) == BOOLOID);
+ rsdesc->rsall.hassublinks
+ = contain_subplans((Node *)rsdesc->rsall.qual);
+ MemoryContextSwitchTo(oldcxt);
+
+ pfree(temp);
+ }
+ }
+ PG_CATCH();
+ {
+ if (rscxt != NULL)
+ MemoryContextDelete(rscxt);
+ PG_RE_THROW();
+ }
+ PG_END_TRY();
+
+ systable_endscan(sscan);
+ heap_close(catalog, AccessShareLock);
+
+ relation->rsdesc = rsdesc;
+}
+
+/*
+ * Parse the supplied row-security policy, and insert/update a row
+ * of pg_rowsecurity catalog.
+ */
+static void
+InsertOrUpdatePolicyRow(Relation relation, char rseccmd, Node *clause)
+{
+ Oid relationId = RelationGetRelid(relation);
+ Oid rowsecId;
+ ParseState *pstate;
+ RangeTblEntry *rte;
+ Node *qual;
+ Relation catalog;
+ ScanKeyData skeys[2];
+ SysScanDesc sscan;
+ HeapTuple oldtup;
+ HeapTuple newtup;
+ Datum values[Natts_pg_rowsecurity];
+ bool isnull[Natts_pg_rowsecurity];
+ bool replaces[Natts_pg_rowsecurity];
+ ObjectAddress target;
+ ObjectAddress myself;
+
+ /* Parse the supplied clause */
+ pstate = make_parsestate(NULL);
+
+ rte = addRangeTableEntryForRelation(pstate, relation,
+ NULL, false, false);
+ addRTEtoQuery(pstate, rte, false, true, true);
+
+ qual = transformWhereClause(pstate, copyObject(clause),
+ EXPR_KIND_ROW_SECURITY,
+ "ROW SECURITY");
+ /* zero-clear */
+ memset(values, 0, sizeof(values));
+ memset(replaces, 0, sizeof(replaces));
+ memset(isnull, 0, sizeof(isnull));
+
+ /* Update or Insert an entry to pg_rowsecurity catalog */
+ catalog = heap_open(RowSecurityRelationId, RowExclusiveLock);
+
+ ScanKeyInit(&skeys[0],
+ Anum_pg_rowsecurity_rsecrelid,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(RelationGetRelid(relation)));
+ ScanKeyInit(&skeys[1],
+ Anum_pg_rowsecurity_rseccmd,
+ BTEqualStrategyNumber, F_CHAREQ,
+ CharGetDatum(rseccmd));
+ sscan = systable_beginscan(catalog, RowSecurityRelidIndexId, true,
+ NULL, 2, skeys);
+ oldtup = systable_getnext(sscan);
+ if (HeapTupleIsValid(oldtup))
+ {
+ rowsecId = HeapTupleGetOid(oldtup);
+
+ replaces[Anum_pg_rowsecurity_rsecqual - 1] = true;
+ values[Anum_pg_rowsecurity_rsecqual - 1]
+ = CStringGetTextDatum(nodeToString(qual));
+
+ newtup = heap_modify_tuple(oldtup,
+ RelationGetDescr(catalog),
+ values, isnull, replaces);
+ simple_heap_update(catalog, &newtup->t_self, newtup);
+
+ deleteDependencyRecordsFor(RowSecurityRelationId, rowsecId, false);
+ }
+ else
+ {
+ values[Anum_pg_rowsecurity_rsecrelid - 1]
+ = ObjectIdGetDatum(relationId);
+ values[Anum_pg_rowsecurity_rseccmd - 1]
+ = CharGetDatum(rseccmd);
+ values[Anum_pg_rowsecurity_rsecqual - 1]
+ = CStringGetTextDatum(nodeToString(qual));
+ newtup = heap_form_tuple(RelationGetDescr(catalog),
+ values, isnull);
+ rowsecId = simple_heap_insert(catalog, newtup);
+ }
+ CatalogUpdateIndexes(catalog, newtup);
+
+ heap_freetuple(newtup);
+
+ /* records dependencies of row-security policy and relation/columns */
+ target.classId = RelationRelationId;
+ target.objectId = relationId;
+ target.objectSubId = 0;
+
+ myself.classId = RowSecurityRelationId;
+ myself.objectId = rowsecId;
+ myself.objectSubId = 0;
+
+ recordDependencyOn(&myself, &target, DEPENDENCY_AUTO);
+
+ recordDependencyOnExpr(&myself, qual, pstate->p_rtable,
+ DEPENDENCY_NORMAL);
+ free_parsestate(pstate);
+
+ systable_endscan(sscan);
+ heap_close(catalog, RowExclusiveLock);
+}
+
+/*
+ * Remove row-security policy row of pg_rowsecurity
+ */
+static void
+DeletePolicyRow(Relation relation, char rseccmd)
+{
+ Assert(rseccmd == ROWSECURITY_CMD_ALL);
+
+ if (relation->rsdesc)
+ {
+ ObjectAddress address;
+
+ address.classId = RowSecurityRelationId;
+ address.objectId = relation->rsdesc->rsall.rsecid;
+ address.objectSubId = 0;
+
+ performDeletion(&address, DROP_RESTRICT, 0);
+ }
+ else
+ {
+ /* Nothing to do here */
+ elog(INFO, "relation %s has no row-security policy, skipped",
+ RelationGetRelationName(relation));
+ }
+}
+
+/*
+ * Guts of row-security policy deletion.
+ */
+void
+RemoveRowSecurityById(Oid rowsecId)
+{
+ Relation catalog;
+ ScanKeyData skey;
+ SysScanDesc sscan;
+ HeapTuple tuple;
+ Relation rel;
+ Oid relid;
+
+ catalog = heap_open(RowSecurityRelationId, RowExclusiveLock);
+
+ ScanKeyInit(&skey,
+ ObjectIdAttributeNumber,
+ BTEqualStrategyNumber, F_OIDEQ,
+ ObjectIdGetDatum(rowsecId));
+ sscan = systable_beginscan(catalog, RowSecurityOidIndexId, true,
+ NULL, 1, &skey);
+ tuple = systable_getnext(sscan);
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "could not find tuple for row-security %u", rowsecId);
+
+ /*
+ * Open and exclusive-lock the relation the row-security belongs to.
+ */
+ relid = ((Form_pg_rowsecurity) GETSTRUCT(tuple))->rsecrelid;
+
+ rel = heap_open(relid, AccessExclusiveLock);
+
+ simple_heap_delete(catalog, &tuple->t_self);
+
+ /* Ensure relcache entries of other session being rebuilt */
+ CacheInvalidateRelcache(rel);
+
+ heap_close(rel, NoLock);
+
+ systable_endscan(sscan);
+ heap_close(catalog, RowExclusiveLock);
+}
+
+/*
+ * ALTER TABLE <name> SET ROW SECURITY (...) OR
+ * RESET ROW SECURITY
+ */
+void
+ATExecSetRowSecurity(Relation relation, const char *cmdname, Node *clause)
+{
+ Oid relid = RelationGetRelid(relation);
+ char rseccmd;
+
+ if (strcmp(cmdname, "all") == 0)
+ rseccmd = ROWSECURITY_CMD_ALL;
+ else
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("Row-security for \"%s\" is not implemented yet",
+ cmdname)));
+
+ if (clause != NULL)
+ {
+ InsertOrUpdatePolicyRow(relation, rseccmd, clause);
+
+ /*
+ * Also, turn on relhasrowsecurity, if not.
+ */
+ if (!RelationGetForm(relation)->relhasrowsecurity)
+ {
+ Relation class_rel = heap_open(RelationRelationId,
+ RowExclusiveLock);
+ HeapTuple tuple;
+
+ tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u", relid);
+
+ ((Form_pg_class) GETSTRUCT(tuple))->relhasrowsecurity = true;
+
+ simple_heap_update(class_rel, &tuple->t_self, tuple);
+ CatalogUpdateIndexes(class_rel, tuple);
+
+ heap_freetuple(tuple);
+ heap_close(class_rel, RowExclusiveLock);
+ }
+ }
+ else
+ DeletePolicyRow(relation, rseccmd);
+
+ CacheInvalidateRelcache(relation);
+}
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index 6b20144..b2dc5c7 100644
--- a/src/backend/commands/copy.c
+++ b/src/backend/commands/copy.c
@@ -24,6 +24,7 @@
#include "access/htup_details.h"
#include "access/sysattr.h"
#include "access/xact.h"
+#include "catalog/heap.h"
#include "catalog/namespace.h"
#include "catalog/pg_type.h"
#include "commands/copy.h"
@@ -34,15 +35,19 @@
#include "libpq/pqformat.h"
#include "mb/pg_wchar.h"
#include "miscadmin.h"
+#include "nodes/makefuncs.h"
#include "optimizer/clauses.h"
#include "optimizer/planner.h"
+#include "optimizer/rowsecurity.h"
#include "parser/parse_relation.h"
+#include "parser/parsetree.h"
#include "rewrite/rewriteHandler.h"
#include "storage/fd.h"
#include "tcop/tcopprot.h"
#include "utils/acl.h"
#include "utils/builtins.h"
#include "utils/lsyscache.h"
+#include "utils/syscache.h"
#include "utils/memutils.h"
#include "utils/portal.h"
#include "utils/rel.h"
@@ -814,6 +819,21 @@ DoCopy(const CopyStmt *stmt, const char *queryString, uint64 *processed)
rel = heap_openrv(stmt->relation,
(is_from ? RowExclusiveLock : AccessShareLock));
+ tupDesc = RelationGetDescr(rel);
+ attnums = CopyGetAttnums(tupDesc, rel, stmt->attlist);
+
+ /*
+ * We have to run regular query, if the target relation has
+ * row-level security policy
+ */
+ if (copy_row_security_policy((CopyStmt *)stmt, rel, attnums))
+ {
+ heap_close(rel, NoLock); /* close with keeping lock */
+ relid = InvalidOid;
+ rel = NULL;
+ }
+ else
+ {
relid = RelationGetRelid(rel);
rte = makeNode(RangeTblEntry);
@@ -822,8 +842,6 @@ DoCopy(const CopyStmt *stmt, const char *queryString, uint64 *processed)
rte->relkind = rel->rd_rel->relkind;
rte->requiredPerms = required_access;
- tupDesc = RelationGetDescr(rel);
- attnums = CopyGetAttnums(tupDesc, rel, stmt->attlist);
foreach(cur, attnums)
{
int attno = lfirst_int(cur) -
@@ -835,6 +853,7 @@ DoCopy(const CopyStmt *stmt, const char *queryString, uint64 *processed)
rte->selectedCols = bms_add_member(rte->selectedCols, attno);
}
ExecCheckRTPerms(list_make1(rte), true);
+ }
}
else
{
@@ -1193,6 +1212,53 @@ ProcessCopyOptions(CopyState cstate,
}
/*
+ * Adjust Query tree constructed with row-level security feature.
+ * If WITH OIDS option was supplied, it adds Var node to reference
+ * object-id system column.
+ */
+static void
+fixup_oid_of_rls_query(Query *query)
+{
+ RangeTblEntry *subrte;
+ TargetEntry *subtle;
+ Var *subvar;
+ ListCell *cell;
+ Form_pg_attribute attform
+ = SystemAttributeDefinition(ObjectIdAttributeNumber, true);
+
+ subrte = rt_fetch((Index) 1, query->rtable);
+ Assert(subrte->rtekind == RTE_RELATION);
+
+ if (!SearchSysCacheExists2(ATTNUM,
+ ObjectIdGetDatum(subrte->relid),
+ Int16GetDatum(attform->attnum)))
+ ereport(ERROR,
+ (errcode(ERRCODE_UNDEFINED_COLUMN),
+ errmsg("table \"%s\" does not have OIDs",
+ get_rel_name(subrte->relid))));
+
+ subvar = makeVar((Index) 1,
+ attform->attnum,
+ attform->atttypid,
+ attform->atttypmod,
+ attform->attcollation,
+ 0);
+ subtle = makeTargetEntry((Expr *) subvar,
+ 0,
+ pstrdup(NameStr(attform->attname)),
+ false);
+
+ query->targetList = list_concat(list_make1(subtle),
+ query->targetList);
+ /* adjust resno of TargetEntry */
+ foreach (cell, query->targetList)
+ {
+ subtle = lfirst(cell);
+ subtle->resno++;
+ }
+}
+
+/*
* Common setup routines used by BeginCopyFrom and BeginCopyTo.
*
* Iff <binary>, unload or reload in the binary format, as opposed to the
@@ -1264,6 +1330,25 @@ BeginCopy(bool is_from,
Assert(!is_from);
cstate->rel = NULL;
+ /*
+ * In case when regular COPY TO was replaced because of row-level
+ * security, "raw_query" node have already analyzed / rewritten
+ * query tree.
+ */
+ if (IsA(raw_query, Query))
+ {
+ query = (Query *) raw_query;
+
+ Assert(query->querySource == QSRC_ROW_SECURITY);
+ if (cstate->oids)
+ {
+ fixup_oid_of_rls_query(query);
+ cstate->oids = false;
+ }
+ attnamelist = NIL;
+ }
+ else
+ {
/* Don't allow COPY w/ OIDs from a select */
if (cstate->oids)
ereport(ERROR,
@@ -1288,6 +1373,7 @@ BeginCopy(bool is_from,
elog(ERROR, "unexpected rewrite result");
query = (Query *) linitial(rewritten);
+ }
/* The grammar allows SELECT INTO, but we don't support that */
if (query->utilityStmt != NULL &&
diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c
index 328e2a8..0374629 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -992,6 +992,7 @@ EventTriggerSupportsObjectClass(ObjectClass objclass)
case OCLASS_USER_MAPPING:
case OCLASS_DEFACL:
case OCLASS_EXTENSION:
+ case OCLASS_ROWSECURITY:
return true;
case MAX_OCLASS:
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 670af18..70b3b94 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -37,6 +37,7 @@
#include "catalog/pg_inherits_fn.h"
#include "catalog/pg_namespace.h"
#include "catalog/pg_opclass.h"
+#include "catalog/pg_rowsecurity.h"
#include "catalog/pg_tablespace.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -2787,6 +2788,8 @@ AlterTableGetLockLevel(List *cmds)
case AT_SetTableSpace: /* must rewrite heap */
case AT_DropNotNull: /* may change some SQL plans */
case AT_SetNotNull:
+ case AT_SetRowSecurity:
+ case AT_ResetRowSecurity:
case AT_GenericOptions:
case AT_AlterColumnGenericOptions:
cmd_lockmode = AccessExclusiveLock;
@@ -3155,6 +3158,8 @@ ATPrepCmd(List **wqueue, Relation rel, AlterTableCmd *cmd,
case AT_DropInherit: /* NO INHERIT */
case AT_AddOf: /* OF */
case AT_DropOf: /* NOT OF */
+ case AT_SetRowSecurity:
+ case AT_ResetRowSecurity:
ATSimplePermissions(rel, ATT_TABLE);
/* These commands never recurse */
/* No command-specific prep needed */
@@ -3440,6 +3445,12 @@ ATExecCmd(List **wqueue, AlteredTableInfo *tab, Relation rel,
case AT_DropOf:
ATExecDropOf(rel, lockmode);
break;
+ case AT_SetRowSecurity:
+ ATExecSetRowSecurity(rel, cmd->name, (Node *) cmd->def);
+ break;
+ case AT_ResetRowSecurity:
+ ATExecSetRowSecurity(rel, cmd->name, NULL);
+ break;
case AT_GenericOptions:
ATExecGenericOptions(rel, (List *) cmd->def);
break;
@@ -7795,6 +7806,22 @@ ATExecAlterColumnType(AlteredTableInfo *tab, Relation rel,
Assert(defaultexpr);
break;
+ case OCLASS_ROWSECURITY:
+ /*
+ * A row-level security policy can depend on a column in case
+ * when the policy clause references a particular column.
+ * Due to same reason why TRIGGER ... WHEN does not support
+ * to change column's type being referenced in clause, row-
+ * level security policy also does not support it.
+ */
+ ereport(ERROR,
+ (errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
+ errmsg("cannot alter type of a column used in a row-level security policy"),
+ errdetail("%s depends on column \"%s\"",
+ getObjectDescription(&foundObject),
+ colName)));
+ break;
+
case OCLASS_PROC:
case OCLASS_TYPE:
case OCLASS_CAST:
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 791f336..619710d 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -788,8 +788,9 @@ InitPlan(QueryDesc *queryDesc, int eflags)
foreach(l, plannedstmt->rowMarks)
{
PlanRowMark *rc = (PlanRowMark *) lfirst(l);
- Oid relid;
- Relation relation;
+ RangeTblEntry *rte = NULL;
+ Relation relation = NULL;
+ LOCKMODE lockmode = NoLock;
ExecRowMark *erm;
/* ignore "parent" rowmarks; they are irrelevant at runtime */
@@ -802,27 +803,33 @@ InitPlan(QueryDesc *queryDesc, int eflags)
case ROW_MARK_NOKEYEXCLUSIVE:
case ROW_MARK_SHARE:
case ROW_MARK_KEYSHARE:
- relid = getrelid(rc->rti, rangeTable);
- relation = heap_open(relid, RowShareLock);
+ rte = rt_fetch(rc->rti, rangeTable);
+ lockmode = RowShareLock;
break;
case ROW_MARK_REFERENCE:
- relid = getrelid(rc->rti, rangeTable);
- relation = heap_open(relid, AccessShareLock);
+ rte = rt_fetch(rc->rti, rangeTable);
+ lockmode = AccessShareLock;
break;
case ROW_MARK_COPY:
/* there's no real table here ... */
- relation = NULL;
break;
default:
elog(ERROR, "unrecognized markType: %d", rc->markType);
- relation = NULL; /* keep compiler quiet */
break;
}
/* Check that relation is a legal target for marking */
- if (relation)
+ if (rte)
+ {
+ if (rte->rtekind == RTE_RELATION)
+ relation = heap_open(rte->relid, lockmode);
+ else
+ {
+ Assert(rte->rtekind == RTE_SUBQUERY);
+ relation = heap_open(rte->rowsec_relid, lockmode);
+ }
CheckValidRowMarkRel(relation, rc->markType);
-
+ }
erm = (ExecRowMark *) palloc(sizeof(ExecRowMark));
erm->relation = relation;
erm->rti = rc->rti;
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index 65f3b98..3ea0b87 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1938,6 +1938,7 @@ _copyAppendRelInfo(const AppendRelInfo *from)
COPY_SCALAR_FIELD(parent_relid);
COPY_SCALAR_FIELD(child_relid);
+ COPY_SCALAR_FIELD(child_result);
COPY_SCALAR_FIELD(parent_reltype);
COPY_SCALAR_FIELD(child_reltype);
COPY_NODE_FIELD(translated_vars);
@@ -1979,6 +1980,7 @@ _copyRangeTblEntry(const RangeTblEntry *from)
COPY_SCALAR_FIELD(relkind);
COPY_NODE_FIELD(subquery);
COPY_SCALAR_FIELD(security_barrier);
+ COPY_SCALAR_FIELD(rowsec_relid);
COPY_SCALAR_FIELD(jointype);
COPY_NODE_FIELD(joinaliasvars);
COPY_NODE_FIELD(funcexpr);
@@ -2452,6 +2454,7 @@ _copyQuery(const Query *from)
COPY_SCALAR_FIELD(canSetTag);
COPY_NODE_FIELD(utilityStmt);
COPY_SCALAR_FIELD(resultRelation);
+ COPY_SCALAR_FIELD(sourceRelation);
COPY_SCALAR_FIELD(hasAggs);
COPY_SCALAR_FIELD(hasWindowFuncs);
COPY_SCALAR_FIELD(hasSubLinks);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 4c9b05e..894026b 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -812,6 +812,7 @@ _equalAppendRelInfo(const AppendRelInfo *a, const AppendRelInfo *b)
{
COMPARE_SCALAR_FIELD(parent_relid);
COMPARE_SCALAR_FIELD(child_relid);
+ COMPARE_SCALAR_FIELD(child_result);
COMPARE_SCALAR_FIELD(parent_reltype);
COMPARE_SCALAR_FIELD(child_reltype);
COMPARE_NODE_FIELD(translated_vars);
@@ -847,6 +848,7 @@ _equalQuery(const Query *a, const Query *b)
COMPARE_SCALAR_FIELD(canSetTag);
COMPARE_NODE_FIELD(utilityStmt);
COMPARE_SCALAR_FIELD(resultRelation);
+ COMPARE_SCALAR_FIELD(sourceRelation);
COMPARE_SCALAR_FIELD(hasAggs);
COMPARE_SCALAR_FIELD(hasWindowFuncs);
COMPARE_SCALAR_FIELD(hasSubLinks);
@@ -2234,6 +2236,7 @@ _equalRangeTblEntry(const RangeTblEntry *a, const RangeTblEntry *b)
COMPARE_SCALAR_FIELD(relkind);
COMPARE_NODE_FIELD(subquery);
COMPARE_SCALAR_FIELD(security_barrier);
+ COMPARE_SCALAR_FIELD(rowsec_relid);
COMPARE_SCALAR_FIELD(jointype);
COMPARE_NODE_FIELD(joinaliasvars);
COMPARE_NODE_FIELD(funcexpr);
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 908f397..88ae823 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -1936,8 +1936,11 @@ query_tree_walker(Query *query,
return true;
if (walker((Node *) query->withCheckOptions, context))
return true;
- if (walker((Node *) query->returningList, context))
- return true;
+ if (!(flags & QTW_IGNORE_RETURNING))
+ {
+ if (walker((Node *) query->returningList, context))
+ return true;
+ }
if (walker((Node *) query->jointree, context))
return true;
if (walker(query->setOperations, context))
@@ -2662,7 +2665,10 @@ query_tree_mutator(Query *query,
MUTATE(query->targetList, query->targetList, List *);
MUTATE(query->withCheckOptions, query->withCheckOptions, List *);
- MUTATE(query->returningList, query->returningList, List *);
+ if (!(flags & QTW_IGNORE_RETURNING))
+ MUTATE(query->returningList, query->returningList, List *);
+ else
+ query->returningList = copyObject(query->returningList);
MUTATE(query->jointree, query->jointree, FromExpr *);
MUTATE(query->setOperations, query->setOperations, Node *);
MUTATE(query->havingQual, query->havingQual, Node *);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 817b149..01a4d22 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1926,6 +1926,7 @@ _outAppendRelInfo(StringInfo str, const AppendRelInfo *node)
WRITE_UINT_FIELD(parent_relid);
WRITE_UINT_FIELD(child_relid);
+ WRITE_UINT_FIELD(child_result);
WRITE_OID_FIELD(parent_reltype);
WRITE_OID_FIELD(child_reltype);
WRITE_NODE_FIELD(translated_vars);
@@ -2240,6 +2241,7 @@ _outQuery(StringInfo str, const Query *node)
appendStringInfoString(str, " :utilityStmt <>");
WRITE_INT_FIELD(resultRelation);
+ WRITE_INT_FIELD(sourceRelation);
WRITE_BOOL_FIELD(hasAggs);
WRITE_BOOL_FIELD(hasWindowFuncs);
WRITE_BOOL_FIELD(hasSubLinks);
@@ -2375,6 +2377,7 @@ _outRangeTblEntry(StringInfo str, const RangeTblEntry *node)
case RTE_SUBQUERY:
WRITE_NODE_FIELD(subquery);
WRITE_BOOL_FIELD(security_barrier);
+ WRITE_OID_FIELD(rowsec_relid);
break;
case RTE_JOIN:
WRITE_ENUM_FIELD(jointype, JoinType);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index d325bb3..e2270bf 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -199,6 +199,7 @@ _readQuery(void)
READ_BOOL_FIELD(canSetTag);
READ_NODE_FIELD(utilityStmt);
READ_INT_FIELD(resultRelation);
+ READ_INT_FIELD(sourceRelation);
READ_BOOL_FIELD(hasAggs);
READ_BOOL_FIELD(hasWindowFuncs);
READ_BOOL_FIELD(hasSubLinks);
@@ -1214,6 +1215,7 @@ _readRangeTblEntry(void)
case RTE_SUBQUERY:
READ_NODE_FIELD(subquery);
READ_BOOL_FIELD(security_barrier);
+ READ_OID_FIELD(rowsec_relid);
break;
case RTE_JOIN:
READ_ENUM_FIELD(jointype, JoinType);
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index d8aa35d..713f980 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -33,6 +33,7 @@
#include "optimizer/planmain.h"
#include "optimizer/planner.h"
#include "optimizer/prep.h"
+#include "optimizer/rowsecurity.h"
#include "optimizer/subselect.h"
#include "optimizer/tlist.h"
#include "parser/analyze.h"
@@ -177,6 +178,7 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
glob->lastPHId = 0;
glob->lastRowMarkId = 0;
glob->transientPlan = false;
+ glob->planUserId = InvalidOid;
/* Determine what fraction of the plan is likely to be scanned */
if (cursorOptions & CURSOR_OPT_FAST_PLAN)
@@ -254,6 +256,7 @@ standard_planner(Query *parse, int cursorOptions, ParamListInfo boundParams)
result->relationOids = glob->relationOids;
result->invalItems = glob->invalItems;
result->nParamExec = glob->nParamExec;
+ result->planUserId = glob->planUserId;
return result;
}
@@ -404,6 +407,19 @@ subquery_planner(PlannerGlobal *glob, Query *parse,
expand_inherited_tables(root);
/*
+ * Apply row-security policy of the relation being referenced,
+ * if configured with either of built-in or extension's features.
+ * RangeTblEntry of the relation with row-security policy shall
+ * be replaced with a row-security subquery that has simple scan
+ * on the target relation with row-security policy qualifiers.
+ *
+ * This routine assumes PlannerInfo is already handled with
+ * expand_inherited_tables, thus, AppendRelInfo or PlanRowMark
+ * have valid information.
+ */
+ apply_row_security_policy(root);
+
+ /*
* Set hasHavingQual to remember if HAVING clause is present. Needed
* because preprocess_expression will reduce a constant-true condition to
* an empty qual list ... but "HAVING TRUE" is not a semantic no-op.
@@ -888,6 +904,8 @@ inheritance_planner(PlannerInfo *root)
newrti = list_length(subroot.parse->rtable) + 1;
ChangeVarNodes((Node *) subroot.parse, rti, newrti, 0);
ChangeVarNodes((Node *) subroot.rowMarks, rti, newrti, 0);
+ if (subroot.parse->sourceRelation == rti)
+ subroot.parse->sourceRelation = newrti;
rte = copyObject(rte);
subroot.parse->rtable = lappend(subroot.parse->rtable,
rte);
@@ -951,7 +969,10 @@ inheritance_planner(PlannerInfo *root)
root->init_plans = subroot.init_plans;
/* Build list of target-relation RT indexes */
- resultRelations = lappend_int(resultRelations, appinfo->child_relid);
+ resultRelations = lappend_int(resultRelations,
+ (appinfo->child_result > 0 ?
+ appinfo->child_result :
+ appinfo->child_relid));
/* Build lists of per-relation WCO and RETURNING targetlists */
if (parse->withCheckOptions)
diff --git a/src/backend/optimizer/prep/preptlist.c b/src/backend/optimizer/prep/preptlist.c
index fb67f9e..0cccdf5 100644
--- a/src/backend/optimizer/prep/preptlist.c
+++ b/src/backend/optimizer/prep/preptlist.c
@@ -37,8 +37,49 @@
static List *expand_targetlist(List *tlist, int command_type,
- Index result_relation, List *range_table);
+ Index result_relation, Index source_relation,
+ List *range_table);
+/*
+ * lookup_varattno
+ *
+ * This routine returns an attribute number to reference a particular
+ * attribute. In case when the target relation is really relation,
+ * we can reference arbitrary attribute (including system column)
+ * without any translations. However, we have to translate varattno
+ * of Var that references sub-queries being originated from regular
+ * relations with row-level security policy due to nature of sub-query
+ * that has no system-column.
+ */
+static AttrNumber
+lookup_varattno(AttrNumber attno, Index rt_index, List *rtables)
+{
+ RangeTblEntry *rte = rt_fetch(rt_index, rtables);
+
+ if (rte->rtekind == RTE_SUBQUERY &&
+ rte->subquery->querySource == QSRC_ROW_SECURITY)
+ {
+ ListCell *cell;
+
+ foreach (cell, rte->subquery->targetList)
+ {
+ TargetEntry *tle = lfirst(cell);
+ Var *var;
+
+ if (IsA(tle->expr, Const))
+ continue;
+
+ var = (Var *) tle->expr;
+ Assert(IsA(var, Var));
+
+ if (var->varattno == attno)
+ return tle->resno;
+ }
+ elog(ERROR, "invalid attno %d on row-security subquery target-list",
+ attno);
+ }
+ return attno;
+}
/*
* preprocess_targetlist
@@ -51,6 +92,7 @@ preprocess_targetlist(PlannerInfo *root, List *tlist)
{
Query *parse = root->parse;
int result_relation = parse->resultRelation;
+ int source_relation = parse->sourceRelation;
List *range_table = parse->rtable;
CmdType command_type = parse->commandType;
ListCell *lc;
@@ -73,8 +115,12 @@ preprocess_targetlist(PlannerInfo *root, List *tlist)
* 10/94
*/
if (command_type == CMD_INSERT || command_type == CMD_UPDATE)
+ {
tlist = expand_targetlist(tlist, command_type,
- result_relation, range_table);
+ result_relation,
+ source_relation,
+ range_table);
+ }
/*
* Add necessary junk columns for rowmarked rels. These values are needed
@@ -96,7 +142,8 @@ preprocess_targetlist(PlannerInfo *root, List *tlist)
{
/* It's a regular table, so fetch its TID */
var = makeVar(rc->rti,
- SelfItemPointerAttributeNumber,
+ lookup_varattno(SelfItemPointerAttributeNumber,
+ rc->rti, range_table),
TIDOID,
-1,
InvalidOid,
@@ -112,7 +159,8 @@ preprocess_targetlist(PlannerInfo *root, List *tlist)
if (rc->isParent)
{
var = makeVar(rc->rti,
- TableOidAttributeNumber,
+ lookup_varattno(TableOidAttributeNumber,
+ rc->rti, range_table),
OIDOID,
-1,
InvalidOid,
@@ -195,7 +243,8 @@ preprocess_targetlist(PlannerInfo *root, List *tlist)
*/
static List *
expand_targetlist(List *tlist, int command_type,
- Index result_relation, List *range_table)
+ Index result_relation, Index source_relation,
+ List *range_table)
{
List *new_tlist = NIL;
ListCell *tlist_item;
@@ -218,6 +267,9 @@ expand_targetlist(List *tlist, int command_type,
numattrs = RelationGetNumberOfAttributes(rel);
+ if (source_relation == 0)
+ source_relation = result_relation;
+
for (attrno = 1; attrno <= numattrs; attrno++)
{
Form_pg_attribute att_tup = rel->rd_att->attrs[attrno - 1];
@@ -298,8 +350,10 @@ expand_targetlist(List *tlist, int command_type,
case CMD_UPDATE:
if (!att_tup->attisdropped)
{
- new_expr = (Node *) makeVar(result_relation,
- attrno,
+ new_expr = (Node *) makeVar(source_relation,
+ lookup_varattno(attrno,
+ source_relation,
+ range_table),
atttype,
atttypmod,
attcollation,
diff --git a/src/backend/optimizer/prep/prepunion.c b/src/backend/optimizer/prep/prepunion.c
index e249628..017bc2c 100644
--- a/src/backend/optimizer/prep/prepunion.c
+++ b/src/backend/optimizer/prep/prepunion.c
@@ -55,6 +55,7 @@ typedef struct
{
PlannerInfo *root;
AppendRelInfo *appinfo;
+ bool in_returning;
} adjust_appendrel_attrs_context;
static Plan *recurse_set_operations(Node *setOp, PlannerInfo *root,
@@ -1594,6 +1595,7 @@ adjust_appendrel_attrs(PlannerInfo *root, Node *node, AppendRelInfo *appinfo)
context.root = root;
context.appinfo = appinfo;
+ context.in_returning = false;
/*
* Must be prepared to start with a Query or a bare expression tree.
@@ -1605,10 +1607,29 @@ adjust_appendrel_attrs(PlannerInfo *root, Node *node, AppendRelInfo *appinfo)
newnode = query_tree_mutator((Query *) node,
adjust_appendrel_attrs_mutator,
(void *) &context,
- QTW_IGNORE_RC_SUBQUERIES);
+ QTW_IGNORE_RC_SUBQUERIES |
+ QTW_IGNORE_RETURNING);
+ /*
+ * Returning clause on the relation being replaced with row-
+ * security subquery shall be handled in a special way, because
+ * of no system columns on subquery.
+ * Var references to system column or whole-row reference need
+ * to be adjusted to reference pseudo columns on behalf of
+ * the underlying these columns, however, RETURNGIN clause is
+ * an exception because its Var nodes are evaluated towards
+ * the "raw" target relation, not a fetched tuple.
+ */
+ context.in_returning = true;
+ newnode->returningList = (List *)
+ expression_tree_mutator((Node *) newnode->returningList,
+ adjust_appendrel_attrs_mutator,
+ (void *) &context);
if (newnode->resultRelation == appinfo->parent_relid)
{
- newnode->resultRelation = appinfo->child_relid;
+ newnode->resultRelation = (appinfo->child_result > 0 ?
+ appinfo->child_result :
+ appinfo->child_relid);
+ newnode->sourceRelation = appinfo->child_relid;
/* Fix tlist resnos too, if it's inherited UPDATE */
if (newnode->commandType == CMD_UPDATE)
newnode->targetList =
@@ -1624,6 +1645,49 @@ adjust_appendrel_attrs(PlannerInfo *root, Node *node, AppendRelInfo *appinfo)
}
static Node *
+fixup_var_on_rowsec_subquery(RangeTblEntry *rte, Var *var)
+{
+ ListCell *cell;
+
+ Assert(rte->rtekind == RTE_SUBQUERY &&
+ rte->subquery->querySource == QSRC_ROW_SECURITY);
+ /*
+ * In case when row-level security policy is applied on the referenced
+ * table, its RangeTblEntry (RTE_RELATION) is replaced with sub-query
+ * to filter out unprivileged rows of underlying relation.
+ * Even though reference to this sub-query should perform as if ones
+ * to real relations, system column has to be cared in special way
+ * due to the nature of sub-query.
+ * Target-entries that reference system columns should be added on
+ * rowlevelsec.c, so all we need to do here is looking up underlying
+ * target-list that can reference underlying system column, and fix-
+ * up varattno of the referencing Var node with resno of TargetEntry.
+ */
+ foreach (cell, rte->subquery->targetList)
+ {
+ TargetEntry *subtle = lfirst(cell);
+
+ if (IsA(subtle->expr, Var))
+ {
+ Var *subvar = (Var *) subtle->expr;
+ Var *newnode;
+
+ if (subvar->varattno == var->varattno)
+ {
+ newnode = copyObject(var);
+ newnode->varattno = subtle->resno;
+ return (Node *)newnode;
+ }
+ }
+ else
+ Assert(IsA(subtle->expr, Const));
+ }
+ elog(ERROR, "could not find pseudo column of %d, relid %u",
+ var->varattno, var->varno);
+ return NULL;
+}
+
+static Node *
adjust_appendrel_attrs_mutator(Node *node,
adjust_appendrel_attrs_context *context)
{
@@ -1638,8 +1702,12 @@ adjust_appendrel_attrs_mutator(Node *node,
if (var->varlevelsup == 0 &&
var->varno == appinfo->parent_relid)
{
- var->varno = appinfo->child_relid;
+ var->varno = (context->in_returning &&
+ appinfo->child_result > 0 ?
+ appinfo->child_result :
+ appinfo->child_relid);
var->varnoold = appinfo->child_relid;
+
if (var->varattno > 0)
{
Node *newnode;
@@ -1664,6 +1732,14 @@ adjust_appendrel_attrs_mutator(Node *node,
*/
if (OidIsValid(appinfo->child_reltype))
{
+ Query *parse = context->root->parse;
+ RangeTblEntry *rte = rt_fetch(appinfo->child_relid,
+ parse->rtable);
+ if (!context->in_returning &&
+ rte->rtekind == RTE_SUBQUERY &&
+ rte->subquery->querySource == QSRC_ROW_SECURITY)
+ var = (Var *)fixup_var_on_rowsec_subquery(rte, var);
+
Assert(var->vartype == appinfo->parent_reltype);
if (appinfo->parent_reltype != appinfo->child_reltype)
{
@@ -1708,7 +1784,18 @@ adjust_appendrel_attrs_mutator(Node *node,
return (Node *) rowexpr;
}
}
- /* system attributes don't need any other translation */
+ else
+ {
+ Query *parse = context->root->parse;
+ RangeTblEntry *rte;
+
+ rte = rt_fetch(appinfo->child_relid, parse->rtable);
+
+ if (!context->in_returning &&
+ rte->rtekind == RTE_SUBQUERY &&
+ rte->subquery->querySource == QSRC_ROW_SECURITY)
+ return fixup_var_on_rowsec_subquery(rte, var);
+ }
}
return (Node *) var;
}
diff --git a/src/backend/optimizer/util/Makefile b/src/backend/optimizer/util/Makefile
index 3b2d16b..3f5cb19 100644
--- a/src/backend/optimizer/util/Makefile
+++ b/src/backend/optimizer/util/Makefile
@@ -13,6 +13,6 @@ top_builddir = ../../../..
include $(top_builddir)/src/Makefile.global
OBJS = clauses.o joininfo.o pathnode.o placeholder.o plancat.o predtest.o \
- relnode.o restrictinfo.o tlist.o var.o
+ relnode.o restrictinfo.o tlist.o var.o rowsecurity.o
include $(top_srcdir)/src/backend/common.mk
diff --git a/src/backend/optimizer/util/rowsecurity.c b/src/backend/optimizer/util/rowsecurity.c
new file mode 100644
index 0000000..c2e5a49
--- /dev/null
+++ b/src/backend/optimizer/util/rowsecurity.c
@@ -0,0 +1,744 @@
+/*
+ * optimizer/util/rowsecurity.c
+ * Routines to support row-security feature
+ *
+ * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ */
+#include "postgres.h"
+
+#include "access/heapam.h"
+#include "access/htup_details.h"
+#include "access/sysattr.h"
+#include "catalog/pg_class.h"
+#include "catalog/pg_inherits_fn.h"
+#include "catalog/pg_rowsecurity.h"
+#include "catalog/pg_type.h"
+#include "miscadmin.h"
+#include "nodes/makefuncs.h"
+#include "nodes/nodeFuncs.h"
+#include "nodes/plannodes.h"
+#include "optimizer/clauses.h"
+#include "optimizer/prep.h"
+#include "optimizer/rowsecurity.h"
+#include "parser/parsetree.h"
+#include "rewrite/rewriteHandler.h"
+#include "utils/lsyscache.h"
+#include "utils/rel.h"
+#include "utils/syscache.h"
+#include "tcop/utility.h"
+
+/* flags to pull row-security policy */
+#define RSEC_FLAG_HAS_SUBLINKS 0x0001
+
+/* hook to allow extensions to apply their own security policy */
+row_security_policy_hook_type row_security_policy_hook = NULL;
+
+/*
+ * make_pseudo_column
+ *
+ * It makes a target-entry node that references underlying column.
+ * Its tle->expr is usualy Var node, but may be Const for dummy NULL
+ * if the supplied attribute was already dropped.
+ */
+static TargetEntry *
+make_pseudo_column(RangeTblEntry *subrte, AttrNumber attnum)
+{
+ Expr *expr;
+ char *resname;
+
+ Assert(subrte->rtekind == RTE_RELATION && OidIsValid(subrte->relid));
+ if (attnum == InvalidAttrNumber)
+ {
+ expr = (Expr *) makeWholeRowVar(subrte, (Index) 1, 0, false);
+ resname = get_rel_name(subrte->relid);
+ }
+ else
+ {
+ HeapTuple tuple;
+ Form_pg_attribute attform;
+
+ tuple = SearchSysCache2(ATTNUM,
+ ObjectIdGetDatum(subrte->relid),
+ Int16GetDatum(attnum));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for attribute %d of relation %u",
+ attnum, subrte->relid);
+ attform = (Form_pg_attribute) GETSTRUCT(tuple);
+
+ if (attform->attisdropped)
+ {
+ char namebuf[NAMEDATALEN];
+
+ /* Insert NULL just for a placeholder of dropped column */
+ expr = (Expr *) makeConst(INT4OID,
+ -1,
+ InvalidOid,
+ sizeof(int32),
+ (Datum) 0,
+ true, /* isnull */
+ true); /* byval */
+ sprintf(namebuf, "dummy-%d", (int)attform->attnum);
+ resname = pstrdup(namebuf);
+ }
+ else
+ {
+ expr = (Expr *) makeVar((Index) 1,
+ attform->attnum,
+ attform->atttypid,
+ attform->atttypmod,
+ attform->attcollation,
+ 0);
+ resname = pstrdup(NameStr(attform->attname));
+ }
+ ReleaseSysCache(tuple);
+ }
+ return makeTargetEntry(expr, -1, resname, false);
+}
+
+/*
+ * lookup_pseudo_column
+ *
+ * It looks-up resource number of the target-entry relevant to the given
+ * Var-node that references the row-security subquery. If required column
+ * is not in the subquery's target-list, this function also adds new one
+ * and returns its resource number.
+ */
+static AttrNumber
+lookup_pseudo_column(PlannerInfo *root,
+ RangeTblEntry *rte, AttrNumber varattno)
+{
+ Query *subqry;
+ RangeTblEntry *subrte;
+ TargetEntry *subtle;
+ ListCell *cell;
+
+ Assert(rte->rtekind == RTE_SUBQUERY &&
+ rte->subquery->querySource == QSRC_ROW_SECURITY);
+
+ subqry = rte->subquery;
+ foreach (cell, subqry->targetList)
+ {
+ subtle = lfirst(cell);
+
+ /*
+ * If referenced artifical column is already constructed on the
+ * target-list of row-security subquery, nothing to do any more.
+ */
+ if (IsA(subtle->expr, Var))
+ {
+ Var *subvar = (Var *)subtle->expr;
+
+ Assert(subvar->varno == 1);
+ if (subvar->varattno == varattno)
+ return subtle->resno;
+ }
+ }
+
+ /*
+ * OK, we don't have an artifical column relevant to the required ones,
+ * so let's create a new artifical column on demand.
+ */
+ subrte = rt_fetch((Index) 1, subqry->rtable);
+ subtle = make_pseudo_column(subrte, varattno);
+ subtle->resno = list_length(subqry->targetList) + 1;
+
+ subqry->targetList = lappend(subqry->targetList, subtle);
+ rte->eref->colnames = lappend(rte->eref->colnames,
+ makeString(pstrdup(subtle->resname)));
+ return subtle->resno;
+}
+
+/*
+ * fixup_varnode_walker
+ *
+ * It recursively fixes up references to the relation to be replaced by
+ * row-security sub-query, and adds pseudo columns relevant to the
+ * underlying system columns or whole row-reference on demand.
+ */
+typedef struct {
+ PlannerInfo *root;
+ int varlevelsup;
+ Index *vartrans;
+} fixup_varnode_context;
+
+static bool
+fixup_varnode_walker(Node *node, fixup_varnode_context *context)
+{
+ if (node == NULL)
+ return false;
+
+ if (IsA(node, Var))
+ {
+ Var *var = (Var *) node;
+ List *rtable = context->root->parse->rtable;
+ RangeTblEntry *rte;
+ ListCell *cell;
+
+ /*
+ * Ignore it, if Var node does not reference the Query currently
+ * we focus on.
+ */
+ if (var->varlevelsup != context->varlevelsup)
+ return false;
+
+ if (context->vartrans[var->varno] > 0)
+ {
+ Index rtindex_trans = context->vartrans[var->varno];
+
+ rte = rt_fetch(rtindex_trans, rtable);
+ Assert(rte->rtekind == RTE_SUBQUERY &&
+ rte->subquery->querySource == QSRC_ROW_SECURITY);
+
+ var->varno = var->varnoold = rtindex_trans;
+ var->varattno = lookup_pseudo_column(context->root, rte,
+ var->varattno);
+ }
+ else
+ {
+ rte = rt_fetch(var->varno, rtable);
+ if (rte->rtekind == RTE_RELATION && rte->inh)
+ {
+ foreach (cell, context->root->append_rel_list)
+ {
+ AppendRelInfo *appinfo = lfirst(cell);
+ RangeTblEntry *child_rte;
+
+ if (appinfo->parent_relid != var->varno)
+ continue;
+
+ child_rte = rt_fetch(appinfo->child_relid, rtable);
+ if (child_rte->rtekind == RTE_SUBQUERY &&
+ child_rte->subquery->querySource == QSRC_ROW_SECURITY)
+ (void) lookup_pseudo_column(context->root,
+ child_rte,
+ var->varattno);
+ }
+ }
+ }
+ }
+ else if (IsA(node, RangeTblRef))
+ {
+ RangeTblRef *rtr = (RangeTblRef *) node;
+
+ if (context->varlevelsup == 0 &&
+ context->vartrans[rtr->rtindex] != 0)
+ rtr->rtindex = context->vartrans[rtr->rtindex];
+ }
+ else if (IsA(node, Query))
+ {
+ bool result;
+
+ context->varlevelsup++;
+ result = query_tree_walker((Query *) node,
+ fixup_varnode_walker,
+ (void *) context, 0);
+ context->varlevelsup--;
+
+ return result;
+ }
+ return expression_tree_walker(node,
+ fixup_varnode_walker,
+ (void *) context);
+}
+
+/*
+ * check_infinite_recursion
+ *
+ * It is a wrong row-security configuration, if we try to expand
+ * the relation inside of row-security subquery originated from
+ * same relation!
+ */
+static void
+check_infinite_recursion(PlannerInfo *root, Oid relid)
+{
+ PlannerInfo *parent = root->parent_root;
+
+ if (parent && parent->parse->querySource == QSRC_ROW_SECURITY)
+ {
+ RangeTblEntry *rte = rt_fetch(1, parent->parse->rtable);
+
+ Assert(rte->rtekind == RTE_RELATION && OidIsValid(rte->relid));
+
+ if (relid == rte->relid)
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_OBJECT_DEFINITION),
+ errmsg("infinite recursion detected for relation \"%s\"",
+ get_rel_name(relid))));
+ check_infinite_recursion(parent, relid);
+ }
+}
+
+/*
+ * expand_rtentry_with_policy
+ *
+ * It extends a range-table entry of row-security sub-query with supplied
+ * security policy, and append it on the parse->rtable.
+ * This sub-query contains pseudo columns that reference underlying
+ * regular columns (at least, references to system column or whole of
+ * table reference shall be added on demand), and simple scan on the
+ * target relation.
+ * Any Var nodes that referenced the relation pointed by rtindex shall
+ * be adjusted to reference this sub-query instead. walker
+ */
+static Index
+expand_rtentry_with_policy(PlannerInfo *root, Index rtindex,
+ Expr *qual, int flags)
+{
+ Query *parse = root->parse;
+ RangeTblEntry *rte = rt_fetch(rtindex, parse->rtable);
+ Query *subqry;
+ RangeTblEntry *subrte;
+ RangeTblRef *subrtr;
+ TargetEntry *subtle;
+ RangeTblEntry *newrte;
+ HeapTuple tuple;
+ AttrNumber nattrs;
+ AttrNumber attnum;
+ List *targetList = NIL;
+ List *colNameList = NIL;
+ PlanRowMark *rowmark;
+
+ Assert(rte->rtekind == RTE_RELATION && !rte->inh);
+
+ /* check recursion to prevent infinite loop */
+ check_infinite_recursion(root, rte->relid);
+
+ /* Expand views inside SubLink node */
+ if (flags & RSEC_FLAG_HAS_SUBLINKS)
+ QueryRewriteExpr((Node *)qual, list_make1_oid(rte->relid));
+
+ /*
+ * Construction of sub-query
+ */
+ subqry = (Query *) makeNode(Query);
+ subqry->commandType = CMD_SELECT;
+ subqry->querySource = QSRC_ROW_SECURITY;
+
+ subrte = copyObject(rte);
+ subqry->rtable = list_make1(subrte);
+
+ subrtr = makeNode(RangeTblRef);
+ subrtr->rtindex = 1;
+ subqry->jointree = makeFromExpr(list_make1(subrtr), (Node *) qual);
+ if (flags & RSEC_FLAG_HAS_SUBLINKS)
+ subqry->hasSubLinks = true;
+
+ /*
+ * Construction of TargetEntries that reference underlying columns.
+ */
+ tuple = SearchSysCache1(RELOID, ObjectIdGetDatum(rte->relid));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for relation %u", rte->relid);
+ nattrs = ((Form_pg_class) GETSTRUCT(tuple))->relnatts;
+ ReleaseSysCache(tuple);
+
+ for (attnum = 1; attnum <= nattrs; attnum++)
+ {
+ subtle = make_pseudo_column(subrte, attnum);
+ subtle->resno = list_length(targetList) + 1;
+ Assert(subtle->resno == attnum);
+
+ targetList = lappend(targetList, subtle);
+ colNameList = lappend(colNameList,
+ makeString(pstrdup(subtle->resname)));
+ }
+ subqry->targetList = targetList;
+
+ /* Expand RengeTblEntry with this sub-query */
+ newrte = makeNode(RangeTblEntry);
+ newrte->rtekind = RTE_SUBQUERY;
+ newrte->subquery = subqry;
+ newrte->security_barrier = true;
+ newrte->rowsec_relid = rte->relid;
+ newrte->eref = makeAlias(get_rel_name(rte->relid), colNameList);
+
+ parse->rtable = lappend(parse->rtable, newrte);
+
+ /*
+ * Fix up PlanRowMark if needed, then add references to 'tableoid' and
+ * 'ctid' that shall be added to handle row-level locking.
+ * Also see preprocess_targetlist() that adds some junk attributes.
+ */
+ rowmark = get_plan_rowmark(root->rowMarks, rtindex);
+ if (rowmark)
+ {
+ if (rowmark->rti == rowmark->prti)
+ rowmark->rti = rowmark->prti = list_length(parse->rtable);
+ else
+ rowmark->rti = list_length(parse->rtable);
+
+ lookup_pseudo_column(root, newrte, SelfItemPointerAttributeNumber);
+ lookup_pseudo_column(root, newrte, TableOidAttributeNumber);
+ }
+ return list_length(parse->rtable);
+}
+
+/*
+ * pull_row_security_policy
+ *
+ * It pulls the configured row-security policy of both built-in and
+ * extensions. If any, it returns expression tree.
+ */
+static Expr *
+pull_row_security_policy(CmdType cmd, Relation relation, int *p_flags)
+{
+ Expr *quals = NULL;
+ int flags = 0;
+
+ /*
+ * Pull the row-security policy configured with built-in features,
+ * if unprivileged users. Please note that superuser can bypass it.
+ */
+ if (relation->rsdesc && !superuser())
+ {
+ RowSecurityDesc *rsdesc = relation->rsdesc;
+
+ quals = copyObject(rsdesc->rsall.qual);
+ if (rsdesc->rsall.hassublinks)
+ flags |= RSEC_FLAG_HAS_SUBLINKS;
+ }
+
+ /*
+ * Also, ask extensions whether they want to apply their own
+ * row-security policy. If both built-in and extension has
+ * their own policy, it shall be merged.
+ */
+ if (row_security_policy_hook)
+ {
+ List *temp;
+
+ temp = (*row_security_policy_hook)(cmd, relation);
+ if (temp != NIL)
+ {
+ if ((flags & RSEC_FLAG_HAS_SUBLINKS) == 0 &&
+ contain_subplans((Node *) temp))
+ flags |= RSEC_FLAG_HAS_SUBLINKS;
+
+ if (quals != NULL)
+ temp = lappend(temp, quals);
+
+ if (list_length(temp) == 1)
+ quals = (Expr *)list_head(temp);
+ else if (list_length(temp) > 1)
+ quals = makeBoolExpr(AND_EXPR, temp, -1);
+ }
+ }
+ *p_flags = flags;
+ return quals;
+}
+
+/*
+ * copy_row_security_policy
+ *
+ * It construct a row-security subquery instead of raw COPY TO statement,
+ * if target relation has a row-level security policy
+ */
+bool
+copy_row_security_policy(CopyStmt *stmt, Relation rel, List *attnums)
+{
+ Expr *quals;
+ int flags;
+ Query *parse;
+ RangeTblEntry *rte;
+ RangeTblRef *rtr;
+ TargetEntry *tle;
+ Var *var;
+ ListCell *cell;
+
+ if (stmt->is_from)
+ return false;
+
+ quals = pull_row_security_policy(CMD_SELECT, rel, &flags);
+ if (!quals)
+ return false;
+
+ parse = (Query *) makeNode(Query);
+ parse->commandType = CMD_SELECT;
+ parse->querySource = QSRC_ROW_SECURITY;
+
+ rte = makeNode(RangeTblEntry);
+ rte->rtekind = RTE_RELATION;
+ rte->relid = RelationGetRelid(rel);
+ rte->relkind = RelationGetForm(rel)->relkind;
+
+ foreach (cell, attnums)
+ {
+ HeapTuple tuple;
+ Form_pg_attribute attform;
+ AttrNumber attno = lfirst_int(cell);
+
+ tuple = SearchSysCache2(ATTNUM,
+ ObjectIdGetDatum(RelationGetRelid(rel)),
+ Int16GetDatum(attno));
+ if (!HeapTupleIsValid(tuple))
+ elog(ERROR, "cache lookup failed for attribute %d of relation %s",
+ attno, RelationGetRelationName(rel));
+ attform = (Form_pg_attribute) GETSTRUCT(tuple);
+
+ var = makeVar((Index) 1,
+ attform->attnum,
+ attform->atttypid,
+ attform->atttypmod,
+ attform->attcollation,
+ 0);
+ tle = makeTargetEntry((Expr *) var,
+ list_length(parse->targetList) + 1,
+ pstrdup(NameStr(attform->attname)),
+ false);
+ parse->targetList = lappend(parse->targetList, tle);
+
+ ReleaseSysCache(tuple);
+
+ rte->selectedCols = bms_add_member(rte->selectedCols,
+ attno - FirstLowInvalidHeapAttributeNumber);
+ }
+ rte->inFromCl = true;
+ rte->requiredPerms = ACL_SELECT;
+
+ rtr = makeNode(RangeTblRef);
+ rtr->rtindex = 1;
+
+ parse->jointree = makeFromExpr(list_make1(rtr), (Node *) quals);
+ parse->rtable = list_make1(rte);
+ if (flags & RSEC_FLAG_HAS_SUBLINKS)
+ parse->hasSubLinks = true;
+
+ stmt->query = (Node *) parse;
+
+ return true;
+}
+
+/*
+ * apply_row_security_relation
+ *
+ * It applies row-security policy on a particular relation being specified.
+ * If this relation is top of the inheritance tree, it also checks inherited
+ * children.
+ */
+static bool
+apply_row_security_relation(PlannerInfo *root, Index *vartrans,
+ CmdType cmd, Index rtindex)
+{
+ Query *parse = root->parse;
+ RangeTblEntry *rte = rt_fetch(rtindex, parse->rtable);
+ Relation rel;
+ Expr *qual;
+ int flags;
+ bool result = false;
+
+ if (!rte->inh)
+ {
+ rel = heap_open(rte->relid, NoLock);
+ qual = pull_row_security_policy(cmd, rel, &flags);
+ if (qual)
+ {
+ vartrans[rtindex]
+ = expand_rtentry_with_policy(root, rtindex, qual, flags);
+ if (parse->resultRelation == rtindex)
+ parse->sourceRelation = vartrans[rtindex];
+ result = true;
+ }
+ heap_close(rel, NoLock);
+ }
+ else
+ {
+ /*
+ * In case when relation has inherited children, we try to apply
+ * row-level security policy of them if configured.
+ * In addition to regular replacement with a sub-query, we need
+ * to adjust rtindex of AppendRelInfo and varno of translated_vars.
+ * It makes sub-queries perform like regular relations being
+ * inherited from a particular parent relation. So, a table scan
+ * may have underlying a relation scan and two sub-query scans for
+ * instance. If it is result relation of UPDATE or DELETE command,
+ * rtindex to the original relation (regular relation) has to be
+ * kept because sub-query cannot perform as an updatable relation.
+ * So, we save it on child_result of AppendRelInfo; that shall be
+ * used to track relations to be modified at inheritance_planner().
+ */
+ ListCell *lc1, *lc2;
+
+ foreach (lc1, root->append_rel_list)
+ {
+ AppendRelInfo *apinfo = lfirst(lc1);
+
+ if (apinfo->parent_relid != rtindex)
+ continue;
+
+ if (apply_row_security_relation(root, vartrans, cmd,
+ apinfo->child_relid))
+ {
+ /*
+ * Save the rtindex of actual relation to be modified,
+ * if parent relation is result relation of this query.
+ */
+ if (parse->resultRelation == rtindex)
+ apinfo->child_result = apinfo->child_relid;
+
+ apinfo->child_relid = vartrans[apinfo->child_relid];
+ /* Adjust varno to reference pseudo columns */
+ foreach (lc2, apinfo->translated_vars)
+ {
+ Var *var = lfirst(lc2);
+
+ if (var)
+ var->varno = apinfo->child_relid;
+ }
+ result = true;
+ }
+ }
+ }
+ return result;
+}
+
+/*
+ * apply_row_security_recursive
+ *
+ * It walks on the given join-tree to replace relations with row-level
+ * security policy by a simple sub-query.
+ */
+static bool
+apply_row_security_recursive(PlannerInfo *root, Index *vartrans, Node *jtnode)
+{
+ bool result = false;
+
+ if (jtnode == NULL)
+ return false;
+ if (IsA(jtnode, RangeTblRef))
+ {
+ Index rtindex = ((RangeTblRef *) jtnode)->rtindex;
+ Query *parse = root->parse;
+ RangeTblEntry *rte = rt_fetch(rtindex, parse->rtable);
+ CmdType cmd;
+
+ /* Only relation can have row-security policy */
+ if (rte->rtekind != RTE_RELATION)
+ return false;
+
+ /*
+ * Prevents infinite recursion. Please note that rtindex == 1
+ * of the row-security subquery is a relation being already
+ * processed on the upper level.
+ */
+ if (parse->querySource == QSRC_ROW_SECURITY && rtindex == 1)
+ return false;
+
+ /* Is it a result relation of UPDATE or DELETE command? */
+ if (parse->resultRelation == rtindex)
+ cmd = parse->commandType;
+ else
+ cmd = CMD_SELECT;
+
+ /* Try to apply row-security policy, if configured */
+ result = apply_row_security_relation(root, vartrans, cmd, rtindex);
+ }
+ else if (IsA(jtnode, FromExpr))
+ {
+ FromExpr *f = (FromExpr *) jtnode;
+ ListCell *l;
+
+ foreach (l, f->fromlist)
+ {
+ if (apply_row_security_recursive(root, vartrans, lfirst(l)))
+ result = true;
+ }
+ }
+ else if (IsA(jtnode, JoinExpr))
+ {
+ JoinExpr *j = (JoinExpr *) jtnode;
+
+ if (apply_row_security_recursive(root, vartrans, j->larg))
+ result = true;
+ if (apply_row_security_recursive(root, vartrans, j->rarg))
+ result = true;
+ }
+ else
+ elog(ERROR, "unexpected node type: %d", (int) nodeTag(jtnode));
+
+ return result;
+}
+
+/*
+ * apply_row_security_policy
+ *
+ * Entrypoint to apply configured row-security policy of the relation.
+ *
+ * In case when the supplied query references relations with row-security
+ * policy, its RangeTblEntry shall be replaced by a row-security subquery
+ * that has simple scan on the referenced table with policy qualifiers.
+ * Of course, security-barrier shall be set on the subquery to prevent
+ * unexpected push-down of functions without leakproof flag.
+ *
+ * For example, when table t1 has a security policy "(x % 2 = 0)", the
+ * following query:
+ * SELECT * FROM t1 WHERE f_leak(y)
+ * performs as if
+ * SELECT * FROM (
+ * SELECT x, y FROM t1 WHERE (x % 2 = 0)
+ * ) AS t1 WHERE f_leak(y)
+ * would be given. Because the sub-query has security barrier flag,
+ * configured security policy qualifier is always executed prior to
+ * user given functions.
+ */
+void
+apply_row_security_policy(PlannerInfo *root)
+{
+ Query *parse = root->parse;
+ Oid curr_userid;
+ int curr_seccxt;
+ Index *vartrans;
+
+ /*
+ * Mode checks. In case when SECURITY_ROW_LEVEL_DISABLED is set,
+ * no row-level security policy should be applied regardless
+ * whether it is built-in or extension.
+ */
+ GetUserIdAndSecContext(&curr_userid, &curr_seccxt);
+ if (curr_seccxt & SECURITY_ROW_LEVEL_DISABLED)
+ return;
+
+ vartrans = palloc0(sizeof(Index) * (list_length(parse->rtable) + 1));
+ if (apply_row_security_recursive(root, vartrans, (Node *)parse->jointree))
+ {
+ PlannerGlobal *glob = root->glob;
+ PlanInvalItem *pi_item;
+ fixup_varnode_context context;
+
+ /*
+ * Constructed Plan with row-level security policy depends on
+ * properties of current user (database superuser can bypass
+ * configured row-security policy!), thus, it has to be
+ * invalidated when its assumption was changed.
+ */
+ if (!OidIsValid(glob->planUserId))
+ {
+ /* Plan invalidation on session user-id */
+ glob->planUserId = GetUserId();
+
+ /* Plan invalidation on catalog updates of pg_authid */
+ pi_item = makeNode(PlanInvalItem);
+ pi_item->cacheId = AUTHOID;
+ pi_item->hashValue =
+ GetSysCacheHashValue1(AUTHOID,
+ ObjectIdGetDatum(glob->planUserId));
+ glob->invalItems = lappend(glob->invalItems, pi_item);
+ }
+ else
+ Assert(glob->planUserId == GetUserId());
+
+ /*
+ * Var-nodes that referenced RangeTblEntry to be replaced by
+ * row-security sub-query have to be adjusted for appropriate
+ * reference to the underlying pseudo column of the relation.
+ */
+ context.root = root;
+ context.varlevelsup = 0;
+ context.vartrans = vartrans;
+ query_tree_walker(parse,
+ fixup_varnode_walker,
+ (void *) &context,
+ QTW_IGNORE_RETURNING);
+ }
+ pfree(vartrans);
+}
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 363c603..967f6be 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -256,6 +256,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> alter_table_cmd alter_type_cmd opt_collate_clause
%type <list> alter_table_cmds alter_type_cmds
+%type <str> row_security_cmd
%type <dbehavior> opt_drop_behavior
@@ -2178,6 +2179,24 @@ alter_table_cmd:
n->def = (Node *)$2;
$$ = (Node *)n;
}
+ /* ALTER TABLE <name> SET ROW SECURITY FOR <cmd> TO (<expr>) */
+ | SET ROW SECURITY FOR row_security_cmd TO '(' a_expr ')'
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_SetRowSecurity;
+ n->name = $5;
+ n->def = (Node *) $8;
+ $$ = (Node *)n;
+ }
+ /* ALTER TABLE <name> RESET ROW SECURITY FOR <cmd> */
+ | RESET ROW SECURITY FOR row_security_cmd
+ {
+ AlterTableCmd *n = makeNode(AlterTableCmd);
+ n->subtype = AT_ResetRowSecurity;
+ n->name = $5;
+ n->def = NULL;
+ $$ = (Node *)n;
+ }
| alter_generic_options
{
AlterTableCmd *n = makeNode(AlterTableCmd);
@@ -2249,6 +2268,12 @@ reloption_elem:
}
;
+row_security_cmd: ALL { $$ = "all"; }
+ | SELECT { $$ = "select"; }
+ | INSERT { $$ = "insert"; }
+ | UPDATE { $$ = "update"; }
+ | DELETE_P { $$ = "delete"; }
+ ;
/*****************************************************************************
*
diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c
index 98cb58a..ac50a2e 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -272,6 +272,9 @@ transformAggregateCall(ParseState *pstate, Aggref *agg,
case EXPR_KIND_TRIGGER_WHEN:
err = _("aggregate functions are not allowed in trigger WHEN conditions");
break;
+ case EXPR_KIND_ROW_SECURITY:
+ err = _("aggregate functions are not allowed in row-security policy");
+ break;
/*
* There is intentionally no default: case here, so that the
@@ -547,6 +550,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc,
case EXPR_KIND_TRIGGER_WHEN:
err = _("window functions are not allowed in trigger WHEN conditions");
break;
+ case EXPR_KIND_ROW_SECURITY:
+ err = _("window functions are not allowed in row-security policy");
+ break;
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c
index 68b711d..fb09c8c 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1459,6 +1459,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink)
case EXPR_KIND_OFFSET:
case EXPR_KIND_RETURNING:
case EXPR_KIND_VALUES:
+ case EXPR_KIND_ROW_SECURITY:
/* okay */
break;
case EXPR_KIND_CHECK_CONSTRAINT:
@@ -2640,6 +2641,8 @@ ParseExprKindName(ParseExprKind exprKind)
return "EXECUTE";
case EXPR_KIND_TRIGGER_WHEN:
return "WHEN";
+ case EXPR_KIND_ROW_SECURITY:
+ return "ROW SECURITY";
/*
* There is intentionally no default: case here, so that the
diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index c52a374..7b9fd63 100644
--- a/src/backend/rewrite/rewriteHandler.c
+++ b/src/backend/rewrite/rewriteHandler.c
@@ -3282,3 +3282,19 @@ QueryRewrite(Query *parsetree)
return results;
}
+
+/*
+ * QueryRewriteExpr
+ *
+ * This routine provides an entry point of query rewriter towards
+ * a certain expression tree with SubLink node; being added after
+ * the top level query rewrite.
+ * It primarily intends to expand views appeared in the qualifiers
+ * appended with row-level security which needs to modify query
+ * tree at head of the planner stage.
+ */
+void
+QueryRewriteExpr(Node *node, List *activeRIRs)
+{
+ fireRIRonSubLink(node, activeRIRs);
+}
diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c
index 917130f..b24af71 100644
--- a/src/backend/utils/adt/ri_triggers.c
+++ b/src/backend/utils/adt/ri_triggers.c
@@ -3008,6 +3008,7 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
int spi_result;
Oid save_userid;
int save_sec_context;
+ int temp_sec_context;
Datum vals[RI_MAX_NUMKEYS * 2];
char nulls[RI_MAX_NUMKEYS * 2];
@@ -3087,8 +3088,18 @@ ri_PerformCheck(const RI_ConstraintInfo *riinfo,
/* Switch to proper UID to perform check as */
GetUserIdAndSecContext(&save_userid, &save_sec_context);
+
+ /*
+ * Row-level security should be disabled in case when foreign-key
+ * relation is queried to check existence of tuples that references
+ * the primary-key being modified.
+ */
+ temp_sec_context = save_sec_context | SECURITY_LOCAL_USERID_CHANGE;
+ if (source_is_pk)
+ temp_sec_context |= SECURITY_ROW_LEVEL_DISABLED;
+
SetUserIdAndSecContext(RelationGetForm(query_rel)->relowner,
- save_sec_context | SECURITY_LOCAL_USERID_CHANGE);
+ temp_sec_context);
/* Finally we can run the query. */
spi_result = SPI_execute_snapshot(qplan,
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index cf740a9..f1e67a5 100644
--- a/src/backend/utils/cache/plancache.c
+++ b/src/backend/utils/cache/plancache.c
@@ -53,6 +53,7 @@
#include "catalog/namespace.h"
#include "executor/executor.h"
#include "executor/spi.h"
+#include "miscadmin.h"
#include "nodes/nodeFuncs.h"
#include "optimizer/cost.h"
#include "optimizer/planmain.h"
@@ -795,6 +796,16 @@ CheckCachedPlan(CachedPlanSource *plansource)
AcquireExecutorLocks(plan->stmt_list, true);
/*
+ * If plan was constructed with assumption of a particular user-id,
+ * and it is different from the current one, the cached-plan shall
+ * be invalidated to construct suitable query plan.
+ */
+ if (plan->is_valid &&
+ OidIsValid(plan->planUserId) &&
+ plan->planUserId == GetUserId())
+ plan->is_valid = false;
+
+ /*
* If plan was transient, check to see if TransactionXmin has
* advanced, and if so invalidate it.
*/
@@ -847,6 +858,8 @@ BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
{
CachedPlan *plan;
List *plist;
+ ListCell *cell;
+ Oid planUserId = InvalidOid;
bool snapshot_set;
bool spi_pushed;
MemoryContext plan_context;
@@ -914,6 +927,24 @@ BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
PopActiveSnapshot();
/*
+ * Check whether the generated plan assumes a particular user-id, or not.
+ * In case when a valid user-id is recorded on PlannedStmt->planUserId,
+ * it should be kept and used to validation check of the cached plan
+ * under the "current" user-id.
+ */
+ foreach (cell, plist)
+ {
+ PlannedStmt *pstmt = lfirst(cell);
+
+ if (IsA(pstmt, PlannedStmt) && OidIsValid(pstmt->planUserId))
+ {
+ Assert(!OidIsValid(planUserId) || planUserId == pstmt->planUserId);
+
+ planUserId = pstmt->planUserId;
+ }
+ }
+
+ /*
* Normally we make a dedicated memory context for the CachedPlan and its
* subsidiary data. (It's probably not going to be large, but just in
* case, use the default maxsize parameter. It's transient for the
@@ -956,6 +987,7 @@ BuildCachedPlan(CachedPlanSource *plansource, List *qlist,
plan->is_oneshot = plansource->is_oneshot;
plan->is_saved = false;
plan->is_valid = true;
+ plan->planUserId = planUserId;
/* assign generation number to new plan */
plan->generation = ++(plansource->generation);
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index b4cc6ad..7fcde09 100644
--- a/src/backend/utils/cache/relcache.c
+++ b/src/backend/utils/cache/relcache.c
@@ -50,6 +50,7 @@
#include "catalog/pg_opclass.h"
#include "catalog/pg_proc.h"
#include "catalog/pg_rewrite.h"
+#include "catalog/pg_rowsecurity.h"
#include "catalog/pg_tablespace.h"
#include "catalog/pg_trigger.h"
#include "catalog/pg_type.h"
@@ -932,6 +933,11 @@ RelationBuildDesc(Oid targetRelId, bool insertIt)
else
relation->trigdesc = NULL;
+ if (relation->rd_rel->relhasrowsecurity)
+ RelationBuildRowSecurity(relation);
+ else
+ relation->rsdesc = NULL;
+
/*
* if it's an index, initialize index-related information
*/
@@ -1839,6 +1845,8 @@ RelationDestroyRelation(Relation relation)
MemoryContextDelete(relation->rd_indexcxt);
if (relation->rd_rulescxt)
MemoryContextDelete(relation->rd_rulescxt);
+ if (relation->rsdesc)
+ MemoryContextDelete(relation->rsdesc->rscxt);
if (relation->rd_fdwroutine)
pfree(relation->rd_fdwroutine);
pfree(relation);
@@ -3158,7 +3166,13 @@ RelationCacheInitializePhase3(void)
relation->rd_rel->relhastriggers = false;
restart = true;
}
-
+ if (relation->rd_rel->relhasrowsecurity && relation->rsdesc == NULL)
+ {
+ RelationBuildRowSecurity(relation);
+ if (relation->rsdesc == NULL)
+ relation->rd_rel->relhasrowsecurity = false;
+ restart = true;
+ }
/* Release hold on the relation */
RelationDecrementReferenceCount(relation);
@@ -4404,6 +4418,7 @@ load_relcache_init_file(bool shared)
rel->rd_rules = NULL;
rel->rd_rulescxt = NULL;
rel->trigdesc = NULL;
+ rel->rsdesc = NULL;
rel->rd_indexprs = NIL;
rel->rd_indpred = NIL;
rel->rd_exclops = NULL;
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 247ad92..9603ef4 100644
--- a/src/bin/pg_dump/common.c
+++ b/src/bin/pg_dump/common.c
@@ -244,6 +244,10 @@ getSchemaData(Archive *fout, int *numTablesPtr)
write_msg(NULL, "reading rewrite rules\n");
getRules(fout, &numRules);
+ if (g_verbose)
+ write_msg(NULL, "reading row-security policies\n");
+ getRowSecurity(fout, tblinfo, numTables);
+
*numTablesPtr = numTables;
return tblinfo;
}
diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 1e189fe..2d0afec 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -3133,6 +3133,7 @@ _printTocEntry(ArchiveHandle *AH, TocEntry *te, RestoreOptions *ropt, bool isDat
strcmp(te->desc, "INDEX") == 0 ||
strcmp(te->desc, "RULE") == 0 ||
strcmp(te->desc, "TRIGGER") == 0 ||
+ strcmp(te->desc, "ROW SECURITY") == 0 ||
strcmp(te->desc, "USER MAPPING") == 0)
{
/* these object types don't have separate owners */
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index bc70dd6..c09b4cc 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -250,6 +250,7 @@ static char *myFormatType(const char *typname, int32 typmod);
static void getBlobs(Archive *fout);
static void dumpBlob(Archive *fout, BlobInfo *binfo);
static int dumpBlobs(Archive *fout, void *arg);
+static void dumpRowSecurity(Archive *fout, RowSecurityInfo *rsinfo);
static void dumpDatabase(Archive *AH);
static void dumpEncoding(Archive *AH);
static void dumpStdStrings(Archive *AH);
@@ -2687,6 +2688,134 @@ dumpBlobs(Archive *fout, void *arg)
return 1;
}
+/*
+ * getRowSecurity
+ * get information about every row-security policy on a dumpable table
+ */
+void
+getRowSecurity(Archive *fout, TableInfo tblinfo[], int numTables)
+{
+ PQExpBuffer query = createPQExpBuffer();
+ PGresult *res;
+ RowSecurityInfo *rsinfo;
+ int i_oid;
+ int i_tableoid;
+ int i_rseccmd;
+ int i_rsecqual;
+ int i, j, ntups;
+
+ /* row-security is not supported prior to v9.4 */
+ if (fout->remoteVersion < 90400)
+ return;
+
+ for (i=0; i < numTables; i++)
+ {
+ TableInfo *tbinfo = &tblinfo[i];
+
+ if (!tbinfo->hasrowsec || !tbinfo->dobj.dump)
+ continue;
+
+ if (g_verbose)
+ write_msg(NULL, "reading row-security policy for table \"%s\"\n",
+ tbinfo->dobj.name);
+
+ /*
+ * select table schema to ensure regproc name is qualified if needed
+ */
+ selectSourceSchema(fout, tbinfo->dobj.namespace->dobj.name);
+
+ resetPQExpBuffer(query);
+
+ appendPQExpBuffer(query,
+ "SELECT oid, tableoid, s.rseccmd, "
+ "pg_get_expr(s.rsecqual, s.rsecrelid) AS rsecqual "
+ "FROM pg_catalog.pg_rowsecurity s "
+ "WHERE rsecrelid = '%u'",
+ tbinfo->dobj.catId.oid);
+ res = ExecuteSqlQuery(fout, query->data, PGRES_TUPLES_OK);
+
+ ntups = PQntuples(res);
+
+ i_oid = PQfnumber(res, "oid");
+ i_tableoid = PQfnumber(res, "tableoid");
+ i_rseccmd = PQfnumber(res, "rseccmd");
+ i_rsecqual = PQfnumber(res, "rsecqual");
+
+ rsinfo = pg_malloc(ntups * sizeof(RowSecurityInfo));
+ for (j=0; j < ntups; j++)
+ {
+ char namebuf[NAMEDATALEN + 1];
+
+ rsinfo[j].dobj.objType = DO_ROW_SECURITY;
+ rsinfo[j].dobj.catId.tableoid =
+ atooid(PQgetvalue(res, j, i_tableoid));
+ rsinfo[j].dobj.catId.oid = atooid(PQgetvalue(res, j, i_oid));
+ AssignDumpId(&rsinfo[j].dobj);
+ snprintf(namebuf, sizeof(namebuf), "row-security of %s",
+ tbinfo->rolname);
+ rsinfo[j].dobj.name = namebuf;
+ rsinfo[j].dobj.namespace = tbinfo->dobj.namespace;
+ rsinfo[j].rstable = tbinfo;
+ rsinfo[j].rseccmd = pg_strdup(PQgetvalue(res, j, i_rseccmd));
+ rsinfo[j].rsecqual = pg_strdup(PQgetvalue(res, j, i_rsecqual));
+ }
+ PQclear(res);
+ }
+ destroyPQExpBuffer(query);
+}
+
+/*
+ * dumpRowSecurity
+ * dump the definition of the given row-security policy
+ */
+static void
+dumpRowSecurity(Archive *fout, RowSecurityInfo *rsinfo)
+{
+ TableInfo *tbinfo = rsinfo->rstable;
+ PQExpBuffer query;
+ PQExpBuffer delqry;
+ const char *cmd;
+
+ if (dataOnly || !tbinfo->hasrowsec)
+ return;
+
+ query = createPQExpBuffer();
+ delqry = createPQExpBuffer();
+ appendPQExpBuffer(query, "ALTER TABLE %s SET ROW SECURITY ",
+ fmtId(tbinfo->dobj.name));
+ appendPQExpBuffer(delqry, "ALTER TABLE %s RESET ROW SECURITY ",
+ fmtId(tbinfo->dobj.name));
+ if (strcmp(rsinfo->rseccmd, "a") == 0)
+ cmd = "ALL";
+ else if (strcmp(rsinfo->rseccmd, "s") == 0)
+ cmd = "SELECT";
+ else if (strcmp(rsinfo->rseccmd, "i") == 0)
+ cmd = "INSERT";
+ else if (strcmp(rsinfo->rseccmd, "u") == 0)
+ cmd = "UPDATE";
+ else if (strcmp(rsinfo->rseccmd, "d") == 0)
+ cmd = "DELETE";
+ else
+ {
+ write_msg(NULL, "unexpected command type: '%s'\n", rsinfo->rseccmd);
+ exit_nicely(1);
+ }
+ appendPQExpBuffer(query, "FOR %s TO %s;\n", cmd, rsinfo->rsecqual);
+ appendPQExpBuffer(delqry, "FOR %s;\n", cmd);
+
+ ArchiveEntry(fout, rsinfo->dobj.catId, rsinfo->dobj.dumpId,
+ rsinfo->dobj.name,
+ rsinfo->dobj.namespace->dobj.name,
+ NULL,
+ tbinfo->rolname, false,
+ "ROW SECURITY", SECTION_POST_DATA,
+ query->data, delqry->data, NULL,
+ NULL, 0,
+ NULL, NULL);
+
+ destroyPQExpBuffer(query);
+}
+
static void
binary_upgrade_set_type_oids_by_type_oid(Archive *fout,
PQExpBuffer upgrade_buffer,
@@ -4215,6 +4344,7 @@ getTables(Archive *fout, int *numTables)
int i_relhastriggers;
int i_relhasindex;
int i_relhasrules;
+ int i_relhasrowsec;
int i_relhasoids;
int i_relfrozenxid;
int i_toastoid;
@@ -4253,7 +4383,45 @@ getTables(Archive *fout, int *numTables)
* we cannot correctly identify inherited columns, owned sequences, etc.
*/
- if (fout->remoteVersion >= 90300)
+ if (fout->remoteVersion >= 90400)
+ {
+ /*
+ * Left join to pick up dependency info linking sequences to their
+ * owning column, if any (note this dependency is AUTO as of 8.2)
+ */
+ appendPQExpBuffer(query,
+ "SELECT c.tableoid, c.oid, c.relname, "
+ "c.relacl, c.relkind, c.relnamespace, "
+ "(%s c.relowner) AS rolname, "
+ "c.relchecks, c.relhastriggers, "
+ "c.relhasindex, c.relhasrules, c.relhasoids, "
+ "c.relhasrowsecurity, "
+ "c.relfrozenxid, tc.oid AS toid, "
+ "tc.relfrozenxid AS tfrozenxid, "
+ "c.relpersistence, c.relispopulated, "
+ "c.relpages, "
+ "CASE WHEN c.reloftype <> 0 THEN c.reloftype::pg_catalog.regtype ELSE NULL END AS reloftype, "
+ "d.refobjid AS owning_tab, "
+ "d.refobjsubid AS owning_col, "
+ "(SELECT spcname FROM pg_tablespace t WHERE t.oid = c.reltablespace) AS reltablespace, "
+ "array_to_string(c.reloptions, ', ') AS reloptions, "
+ "array_to_string(array(SELECT 'toast.' || x FROM unnest(tc.reloptions) x), ', ') AS toast_reloptions "
+ "FROM pg_class c "
+ "LEFT JOIN pg_depend d ON "
+ "(c.relkind = '%c' AND "
+ "d.classid = c.tableoid AND d.objid = c.oid AND "
+ "d.objsubid = 0 AND "
+ "d.refclassid = c.tableoid AND d.deptype = 'a') "
+ "LEFT JOIN pg_class tc ON (c.reltoastrelid = tc.oid) "
+ "WHERE c.relkind in ('%c', '%c', '%c', '%c', '%c', '%c') "
+ "ORDER BY c.oid",
+ username_subquery,
+ RELKIND_SEQUENCE,
+ RELKIND_RELATION, RELKIND_SEQUENCE,
+ RELKIND_VIEW, RELKIND_COMPOSITE_TYPE,
+ RELKIND_MATVIEW, RELKIND_FOREIGN_TABLE);
+ }
+ else if (fout->remoteVersion >= 90300)
{
/*
* Left join to pick up dependency info linking sequences to their
@@ -4265,6 +4433,7 @@ getTables(Archive *fout, int *numTables)
"(%s c.relowner) AS rolname, "
"c.relchecks, c.relhastriggers, "
"c.relhasindex, c.relhasrules, c.relhasoids, "
+ "'f'::bool AS relhasrowsecurity, "
"c.relfrozenxid, tc.oid AS toid, "
"tc.relfrozenxid AS tfrozenxid, "
"c.relpersistence, c.relispopulated, "
@@ -4304,6 +4473,7 @@ getTables(Archive *fout, int *numTables)
"(%s c.relowner) AS rolname, "
"c.relchecks, c.relhastriggers, "
"c.relhasindex, c.relhasrules, c.relhasoids, "
+ "'f'::bool AS relhasrowsecurity, "
"c.relfrozenxid, tc.oid AS toid, "
"tc.relfrozenxid AS tfrozenxid, "
"c.relpersistence, 't' as relispopulated, "
@@ -4341,6 +4511,7 @@ getTables(Archive *fout, int *numTables)
"(%s c.relowner) AS rolname, "
"c.relchecks, c.relhastriggers, "
"c.relhasindex, c.relhasrules, c.relhasoids, "
+ "'f'::bool AS relhasrowsecurity, "
"c.relfrozenxid, tc.oid AS toid, "
"tc.relfrozenxid AS tfrozenxid, "
"'p' AS relpersistence, 't' as relispopulated, "
@@ -4377,6 +4548,7 @@ getTables(Archive *fout, int *numTables)
"(%s c.relowner) AS rolname, "
"c.relchecks, c.relhastriggers, "
"c.relhasindex, c.relhasrules, c.relhasoids, "
+ "'f'::bool AS relhasrowsecurity, "
"c.relfrozenxid, tc.oid AS toid, "
"tc.relfrozenxid AS tfrozenxid, "
"'p' AS relpersistence, 't' as relispopulated, "
@@ -4413,6 +4585,7 @@ getTables(Archive *fout, int *numTables)
"(%s c.relowner) AS rolname, "
"c.relchecks, (c.reltriggers <> 0) AS relhastriggers, "
"c.relhasindex, c.relhasrules, c.relhasoids, "
+ "'f'::bool AS relhasrowsecurity, "
"c.relfrozenxid, tc.oid AS toid, "
"tc.relfrozenxid AS tfrozenxid, "
"'p' AS relpersistence, 't' as relispopulated, "
@@ -4449,6 +4622,7 @@ getTables(Archive *fout, int *numTables)
"(%s relowner) AS rolname, "
"relchecks, (reltriggers <> 0) AS relhastriggers, "
"relhasindex, relhasrules, relhasoids, "
+ "'f'::bool AS relhasrowsecurity, "
"0 AS relfrozenxid, "
"0 AS toid, "
"0 AS tfrozenxid, "
@@ -4485,6 +4659,7 @@ getTables(Archive *fout, int *numTables)
"(%s relowner) AS rolname, "
"relchecks, (reltriggers <> 0) AS relhastriggers, "
"relhasindex, relhasrules, relhasoids, "
+ "'f'::bool AS relhasrowsecurity, "
"0 AS relfrozenxid, "
"0 AS toid, "
"0 AS tfrozenxid, "
@@ -4517,6 +4692,7 @@ getTables(Archive *fout, int *numTables)
"(%s relowner) AS rolname, "
"relchecks, (reltriggers <> 0) AS relhastriggers, "
"relhasindex, relhasrules, relhasoids, "
+ "'f'::bool AS relhasrowsecurity, "
"0 AS relfrozenxid, "
"0 AS toid, "
"0 AS tfrozenxid, "
@@ -4544,6 +4720,7 @@ getTables(Archive *fout, int *numTables)
"relchecks, (reltriggers <> 0) AS relhastriggers, "
"relhasindex, relhasrules, "
"'t'::bool AS relhasoids, "
+ "'f'::bool AS relhasrowsecurity, "
"0 AS relfrozenxid, "
"0 AS toid, "
"0 AS tfrozenxid, "
@@ -4581,6 +4758,7 @@ getTables(Archive *fout, int *numTables)
"relchecks, (reltriggers <> 0) AS relhastriggers, "
"relhasindex, relhasrules, "
"'t'::bool AS relhasoids, "
+ "'f'::bool AS relhasrowsecurity, "
"0 as relfrozenxid, "
"0 AS toid, "
"0 AS tfrozenxid, "
@@ -4628,6 +4806,7 @@ getTables(Archive *fout, int *numTables)
i_relhastriggers = PQfnumber(res, "relhastriggers");
i_relhasindex = PQfnumber(res, "relhasindex");
i_relhasrules = PQfnumber(res, "relhasrules");
+ i_relhasrowsec = PQfnumber(res, "relhasrowsecurity");
i_relhasoids = PQfnumber(res, "relhasoids");
i_relfrozenxid = PQfnumber(res, "relfrozenxid");
i_toastoid = PQfnumber(res, "toid");
@@ -4676,6 +4855,7 @@ getTables(Archive *fout, int *numTables)
tblinfo[i].hasindex = (strcmp(PQgetvalue(res, i, i_relhasindex), "t") == 0);
tblinfo[i].hasrules = (strcmp(PQgetvalue(res, i, i_relhasrules), "t") == 0);
tblinfo[i].hastriggers = (strcmp(PQgetvalue(res, i, i_relhastriggers), "t") == 0);
+ tblinfo[i].hasrowsec = (strcmp(PQgetvalue(res, i, i_relhasrowsec), "t") == 0);
tblinfo[i].hasoids = (strcmp(PQgetvalue(res, i, i_relhasoids), "t") == 0);
tblinfo[i].relispopulated = (strcmp(PQgetvalue(res, i, i_relispopulated), "t") == 0);
tblinfo[i].relpages = atoi(PQgetvalue(res, i, i_relpages));
@@ -7759,6 +7939,9 @@ dumpDumpableObject(Archive *fout, DumpableObject *dobj)
NULL, 0,
dumpBlobs, NULL);
break;
+ case DO_ROW_SECURITY:
+ dumpRowSecurity(fout, (RowSecurityInfo *) dobj);
+ break;
case DO_PRE_DATA_BOUNDARY:
case DO_POST_DATA_BOUNDARY:
/* never dumped, nothing to do */
@@ -14971,6 +15154,7 @@ addBoundaryDependencies(DumpableObject **dobjs, int numObjs,
case DO_TRIGGER:
case DO_EVENT_TRIGGER:
case DO_DEFAULT_ACL:
+ case DO_ROW_SECURITY:
/* Post-data objects: must come after the post-data boundary */
addObjectDependency(dobj, postDataBound->dumpId);
break;
diff --git a/src/bin/pg_dump/pg_dump.h b/src/bin/pg_dump/pg_dump.h
index 2c5971c..4dc00db 100644
--- a/src/bin/pg_dump/pg_dump.h
+++ b/src/bin/pg_dump/pg_dump.h
@@ -111,7 +111,8 @@ typedef enum
DO_PRE_DATA_BOUNDARY,
DO_POST_DATA_BOUNDARY,
DO_EVENT_TRIGGER,
- DO_REFRESH_MATVIEW
+ DO_REFRESH_MATVIEW,
+ DO_ROW_SECURITY,
} DumpableObjectType;
typedef struct _dumpableObject
@@ -244,6 +245,7 @@ typedef struct _tableInfo
bool hasindex; /* does it have any indexes? */
bool hasrules; /* does it have any rules? */
bool hastriggers; /* does it have any triggers? */
+ bool hasrowsec; /* does it have any row-security policy? */
bool hasoids; /* does it have OIDs? */
uint32 frozenxid; /* for restore frozen xid */
Oid toast_oid; /* for restore toast frozen xid */
@@ -482,6 +484,14 @@ typedef struct _blobInfo
char *blobacl;
} BlobInfo;
+typedef struct _rowSecurityInfo
+{
+ DumpableObject dobj;
+ TableInfo *rstable;
+ char *rseccmd;
+ char *rsecqual;
+} RowSecurityInfo;
+
/* global decls */
extern bool force_quotes; /* double-quotes for identifiers flag */
extern bool g_verbose; /* verbose flag */
@@ -573,5 +583,6 @@ extern DefaultACLInfo *getDefaultACLs(Archive *fout, int *numDefaultACLs);
extern void getExtensionMembership(Archive *fout, ExtensionInfo extinfo[],
int numExtensions);
extern EventTriggerInfo *getEventTriggers(Archive *fout, int *numEventTriggers);
+extern void getRowSecurity(Archive *fout, TableInfo tblinfo[], int numTables);
#endif /* PG_DUMP_H */
diff --git a/src/bin/pg_dump/pg_dump_sort.c b/src/bin/pg_dump/pg_dump_sort.c
index 141e713..5c5777c 100644
--- a/src/bin/pg_dump/pg_dump_sort.c
+++ b/src/bin/pg_dump/pg_dump_sort.c
@@ -1342,6 +1342,11 @@ describeDumpableObject(DumpableObject *obj, char *buf, int bufsize)
"BLOB DATA (ID %d)",
obj->dumpId);
return;
+ case DO_ROW_SECURITY:
+ snprintf(buf, bufsize,
+ "ROW-SECURITY POLICY (ID %d OID %u)",
+ obj->dumpId, obj->catId.oid);
+ return;
case DO_PRE_DATA_BOUNDARY:
snprintf(buf, bufsize,
"PRE-DATA BOUNDARY (ID %d)",
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index ed1c5fd..26c4b0e 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2704,6 +2704,10 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBuffer(&buf,
",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"",
gettext_noop("Description"));
+ if (pset.sversion >= 90300)
+ appendPQExpBuffer(&buf,
+ ",\n pg_catalog.pg_get_expr(rs.rsecqual, c.oid) as \"%s\"",
+ gettext_noop("Row-security"));
}
appendPQExpBuffer(&buf,
@@ -2713,6 +2717,9 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBuffer(&buf,
"\n LEFT JOIN pg_catalog.pg_index i ON i.indexrelid = c.oid"
"\n LEFT JOIN pg_catalog.pg_class c2 ON i.indrelid = c2.oid");
+ if (verbose && pset.sversion >= 90300)
+ appendPQExpBuffer(&buf,
+ "\n LEFT JOIN pg_rowsecurity rs ON rs.rsecrelid = c.oid");
appendPQExpBuffer(&buf, "\nWHERE c.relkind IN (");
if (showTables)
diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 3aefbb5e..c96e8d4 100644
--- a/src/include/catalog/dependency.h
+++ b/src/include/catalog/dependency.h
@@ -147,6 +147,7 @@ typedef enum ObjectClass
OCLASS_DEFACL, /* pg_default_acl */
OCLASS_EXTENSION, /* pg_extension */
OCLASS_EVENT_TRIGGER, /* pg_event_trigger */
+ OCLASS_ROWSECURITY, /* pg_rowsecurity */
MAX_OCLASS /* MUST BE LAST */
} ObjectClass;
diff --git a/src/include/catalog/indexing.h b/src/include/catalog/indexing.h
index 4860e98..67b3315 100644
--- a/src/include/catalog/indexing.h
+++ b/src/include/catalog/indexing.h
@@ -313,6 +313,11 @@ DECLARE_UNIQUE_INDEX(pg_extension_name_index, 3081, on pg_extension using btree(
DECLARE_UNIQUE_INDEX(pg_range_rngtypid_index, 3542, on pg_range using btree(rngtypid oid_ops));
#define RangeTypidIndexId 3542
+DECLARE_UNIQUE_INDEX(pg_rowsecurity_oid_index, 3178, on pg_rowsecurity using btree(oid oid_ops));
+#define RowSecurityOidIndexId 3178
+DECLARE_UNIQUE_INDEX(pg_rowsecurity_relid_index, 3180, on pg_rowsecurity using btree(rsecrelid oid_ops, rseccmd char_ops));
+#define RowSecurityRelidIndexId 3180
+
/* last step of initialization script: build the indexes declared above */
BUILD_INDICES
diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h
index 49c4f6f..6a16819 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -64,6 +64,7 @@ CATALOG(pg_class,1259) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83) BKI_SCHEMA_MACRO
bool relhaspkey; /* has (or has had) PRIMARY KEY index */
bool relhasrules; /* has (or has had) any rules */
bool relhastriggers; /* has (or has had) any TRIGGERs */
+ bool relhasrowsecurity; /* has (or has had) row-security policy */
bool relhassubclass; /* has (or has had) derived classes */
bool relispopulated; /* matview currently holds query results */
TransactionId relfrozenxid; /* all Xids < this are frozen in this rel */
@@ -93,7 +94,7 @@ typedef FormData_pg_class *Form_pg_class;
* ----------------
*/
-#define Natts_pg_class 28
+#define Natts_pg_class 29
#define Anum_pg_class_relname 1
#define Anum_pg_class_relnamespace 2
#define Anum_pg_class_reltype 3
@@ -116,12 +117,13 @@ typedef FormData_pg_class *Form_pg_class;
#define Anum_pg_class_relhaspkey 20
#define Anum_pg_class_relhasrules 21
#define Anum_pg_class_relhastriggers 22
-#define Anum_pg_class_relhassubclass 23
-#define Anum_pg_class_relispopulated 24
-#define Anum_pg_class_relfrozenxid 25
-#define Anum_pg_class_relminmxid 26
-#define Anum_pg_class_relacl 27
-#define Anum_pg_class_reloptions 28
+#define Anum_pg_class_relhasrowsecurity 23
+#define Anum_pg_class_relhassubclass 24
+#define Anum_pg_class_relispopulated 25
+#define Anum_pg_class_relfrozenxid 26
+#define Anum_pg_class_relminmxid 27
+#define Anum_pg_class_relacl 28
+#define Anum_pg_class_reloptions 29
/* ----------------
* initial contents of pg_class
@@ -136,13 +138,13 @@ typedef FormData_pg_class *Form_pg_class;
* Note: "3" in the relfrozenxid column stands for FirstNormalTransactionId;
* similarly, "1" in relminmxid stands for FirstMultiXactId
*/
-DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0 f f p r 30 0 t f f f f t 3 1 _null_ _null_ ));
+DATA(insert OID = 1247 ( pg_type PGNSP 71 0 PGUID 0 0 0 0 0 0 0 f f p r 30 0 t f f f f f t 3 1 _null_ _null_ ));
DESCR("");
-DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 21 0 f f f f f t 3 1 _null_ _null_ ));
+DATA(insert OID = 1249 ( pg_attribute PGNSP 75 0 PGUID 0 0 0 0 0 0 0 f f p r 21 0 f f f f f f t 3 1 _null_ _null_ ));
DESCR("");
-DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0 f f p r 27 0 t f f f f t 3 1 _null_ _null_ ));
+DATA(insert OID = 1255 ( pg_proc PGNSP 81 0 PGUID 0 0 0 0 0 0 0 f f p r 27 0 t f f f f f t 3 1 _null_ _null_ ));
DESCR("");
-DATA(insert OID = 1259 ( pg_class PGNSP 83 0 PGUID 0 0 0 0 0 0 0 f f p r 28 0 t f f f f t 3 1 _null_ _null_ ));
+DATA(insert OID = 1259 ( pg_class PGNSP 83 0 PGUID 0 0 0 0 0 0 0 f f p r 29 0 t f f f f f t 3 1 _null_ _null_ ));
DESCR("");
diff --git a/src/include/catalog/pg_rowsecurity.h b/src/include/catalog/pg_rowsecurity.h
new file mode 100644
index 0000000..6b1c583
--- /dev/null
+++ b/src/include/catalog/pg_rowsecurity.h
@@ -0,0 +1,76 @@
+/*
+ * pg_rowsecurity.h
+ * definition of the system catalog for row-security policy (pg_rowsecurity)
+ *
+ * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ */
+#ifndef PG_ROWSECURITY_H
+#define PG_ROWSECURITY_H
+
+#include "catalog/genbki.h"
+#include "nodes/primnodes.h"
+#include "utils/memutils.h"
+#include "utils/relcache.h"
+
+/* ----------------
+ * pg_rowlevelsec definition. cpp turns this into
+ * typedef struct FormData_pg_rowlevelsec
+ * ----------------
+ */
+#define RowSecurityRelationId 3179
+
+CATALOG(pg_rowsecurity,3179)
+{
+ /* Oid of the relation that has row-security policy */
+ Oid rsecrelid;
+
+ /* One of ROWSECURITY_CMD_* below */
+ char rseccmd;
+#ifdef CATALOG_VARLEN
+ pg_node_tree rsecqual;
+#endif
+} FormData_pg_rowsecurity;
+
+/* ----------------
+ * Form_pg_rowlevelsec corresponds to a pointer to a row with
+ * the format of pg_rowlevelsec relation.
+ * ----------------
+ */
+typedef FormData_pg_rowsecurity *Form_pg_rowsecurity;
+
+/* ----------------
+ * compiler constants for pg_rowlevelsec
+ * ----------------
+ */
+#define Natts_pg_rowsecurity 3
+#define Anum_pg_rowsecurity_rsecrelid 1
+#define Anum_pg_rowsecurity_rseccmd 2
+#define Anum_pg_rowsecurity_rsecqual 3
+
+#define ROWSECURITY_CMD_ALL 'a'
+#define ROWSECURITY_CMD_SELECT 's'
+#define ROWSECURITY_CMD_INSERT 'i'
+#define ROWSECURITY_CMD_UPDATE 'u'
+#define ROWSECURITY_CMD_DELETE 'd'
+
+typedef struct
+{
+ Oid rsecid;
+ Expr *qual;
+ bool hassublinks;
+} RowSecurityEntry;
+
+typedef struct
+{
+ MemoryContext rscxt;
+ RowSecurityEntry rsall; /* row-security policy for ALL */
+} RowSecurityDesc;
+
+extern void RelationBuildRowSecurity(Relation relation);
+extern void ATExecSetRowSecurity(Relation relation,
+ const char *cmdname, Node *clause);
+extern void RemoveRowSecurityById(Oid relationId);
+
+#endif /* PG_ROWSECURITY_H */
diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h
index 0aa540a..dc59cd4 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -281,6 +281,7 @@ extern int trace_recovery(int trace_level);
/* flags to be OR'd to form sec_context */
#define SECURITY_LOCAL_USERID_CHANGE 0x0001
#define SECURITY_RESTRICTED_OPERATION 0x0002
+#define SECURITY_ROW_LEVEL_DISABLED 0x0004
extern char *DatabasePath;
diff --git a/src/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index 3b430e0..5f8b7f7 100644
--- a/src/include/nodes/execnodes.h
+++ b/src/include/nodes/execnodes.h
@@ -308,6 +308,8 @@ typedef struct JunkFilter
* ConstraintExprs array of constraint-checking expr states
* junkFilter for removing junk attributes from tuples
* projectReturning for computing a RETURNING list
+ * rowSecurity for row-security checks
+ * rowSecParams param-list if row-security has SubLink
* ----------------
*/
typedef struct ResultRelInfo
@@ -329,6 +331,8 @@ typedef struct ResultRelInfo
List **ri_ConstraintExprs;
JunkFilter *ri_junkFilter;
ProjectionInfo *ri_projectReturning;
+ Node *ri_rowSecurity;
+ List *ri_rowSecParams;
} ResultRelInfo;
/* ----------------
diff --git a/src/include/nodes/nodeFuncs.h b/src/include/nodes/nodeFuncs.h
index fe7cfd3..23c3553 100644
--- a/src/include/nodes/nodeFuncs.h
+++ b/src/include/nodes/nodeFuncs.h
@@ -24,6 +24,7 @@
#define QTW_IGNORE_RANGE_TABLE 0x08 /* skip rangetable entirely */
#define QTW_EXAMINE_RTES 0x10 /* examine RTEs */
#define QTW_DONT_COPY_QUERY 0x20 /* do not copy top Query */
+#define QTW_IGNORE_RETURNING 0x40 /* skip returning clause */
extern Oid exprType(const Node *expr);
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index e5235cb..6e54d89 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -31,7 +31,8 @@ typedef enum QuerySource
QSRC_PARSER, /* added by parse analysis (now unused) */
QSRC_INSTEAD_RULE, /* added by unconditional INSTEAD rule */
QSRC_QUAL_INSTEAD_RULE, /* added by conditional INSTEAD rule */
- QSRC_NON_INSTEAD_RULE /* added by non-INSTEAD rule */
+ QSRC_NON_INSTEAD_RULE, /* added by non-INSTEAD rule */
+ QSRC_ROW_SECURITY, /* added by row-security */
} QuerySource;
/* Sort ordering options for ORDER BY and CREATE INDEX */
@@ -112,7 +113,9 @@ typedef struct Query
int resultRelation; /* rtable index of target relation for
* INSERT/UPDATE/DELETE; 0 for SELECT */
-
+ int sourceRelation; /* rtable index of source relation for
+ * UPDATE/DELETE, if not identical with
+ * resultRelation; 0 for elsewhere */
bool hasAggs; /* has aggregates in tlist or havingQual */
bool hasWindowFuncs; /* has window functions in tlist */
bool hasSubLinks; /* has subquery SubLink */
@@ -733,6 +736,11 @@ typedef struct RangeTblEntry
*/
Query *subquery; /* the sub-query */
bool security_barrier; /* is from security_barrier view? */
+ Oid rowsec_relid; /* OID of the original relation, if this
+ * sub-query originated from row-security
+ * policy on the relation. Elsewhere, it
+ * should be InvalidOid.
+ */
/*
* Fields valid for a join RTE (else NULL/zero):
@@ -1284,6 +1292,8 @@ typedef enum AlterTableType
AT_DropInherit, /* NO INHERIT parent */
AT_AddOf, /* OF <type_name> */
AT_DropOf, /* NOT OF */
+ AT_SetRowSecurity, /* SET ROW SECURITY (...) */
+ AT_ResetRowSecurity, /* RESET ROW SECURITY */
AT_GenericOptions /* OPTIONS (...) */
} AlterTableType;
diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 44ea0b7..889dc7d 100644
--- a/src/include/nodes/plannodes.h
+++ b/src/include/nodes/plannodes.h
@@ -67,6 +67,8 @@ typedef struct PlannedStmt
List *invalItems; /* other dependencies, as PlanInvalItems */
int nParamExec; /* number of PARAM_EXEC Params used */
+
+ Oid planUserId; /* user-id this plan assumed, or InvalidOid */
} PlannedStmt;
/* macro for fetching the Plan associated with a SubPlan node */
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index a2853fb..e7eb8cf 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -98,6 +98,8 @@ typedef struct PlannerGlobal
Index lastRowMarkId; /* highest PlanRowMark ID assigned */
bool transientPlan; /* redo plan when TransactionXmin changes? */
+
+ Oid planUserId; /* User-Id to be assumed on this plan */
} PlannerGlobal;
/* macro for fetching the Plan associated with a SubPlan node */
@@ -1426,6 +1428,10 @@ typedef struct AppendRelInfo
*/
Index parent_relid; /* RT index of append parent rel */
Index child_relid; /* RT index of append child rel */
+ Index child_result; /* RT index of append child rel's source,
+ * if source of result relation is not
+ * identical. Elsewhere, 0.
+ */
/*
* For an inheritance appendrel, the parent and child are both regular
diff --git a/src/include/optimizer/rowsecurity.h b/src/include/optimizer/rowsecurity.h
new file mode 100644
index 0000000..ff4dd9a
--- /dev/null
+++ b/src/include/optimizer/rowsecurity.h
@@ -0,0 +1,27 @@
+/* -------------------------------------------------------------------------
+ *
+ * rowsecurity.h
+ * prototypes for optimizer/rowsecurity.c
+ *
+ * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * -------------------------------------------------------------------------
+ */
+#ifndef ROWSECURITY_H
+#define ROWSECURITY_H
+
+#include "nodes/execnodes.h"
+#include "nodes/parsenodes.h"
+#include "nodes/relation.h"
+#include "utils/rel.h"
+
+typedef List *(*row_security_policy_hook_type)(CmdType cmdtype,
+ Relation relation);
+extern PGDLLIMPORT row_security_policy_hook_type row_security_policy_hook;
+
+extern bool copy_row_security_policy(CopyStmt *stmt,
+ Relation relation, List *attnums);
+extern void apply_row_security_policy(PlannerInfo *root);
+
+#endif /* ROWSECURITY_H */
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index bea3b07..3910853 100644
--- a/src/include/parser/parse_node.h
+++ b/src/include/parser/parse_node.h
@@ -63,7 +63,8 @@ typedef enum ParseExprKind
EXPR_KIND_INDEX_PREDICATE, /* index predicate */
EXPR_KIND_ALTER_COL_TRANSFORM, /* transform expr in ALTER COLUMN TYPE */
EXPR_KIND_EXECUTE_PARAMETER, /* parameter value in EXECUTE */
- EXPR_KIND_TRIGGER_WHEN /* WHEN condition in CREATE TRIGGER */
+ EXPR_KIND_TRIGGER_WHEN, /* WHEN condition in CREATE TRIGGER */
+ EXPR_KIND_ROW_SECURITY, /* ROW SECURITY policy for a table */
} ParseExprKind;
diff --git a/src/include/rewrite/rewriteHandler.h b/src/include/rewrite/rewriteHandler.h
index c959590..ef10f56 100644
--- a/src/include/rewrite/rewriteHandler.h
+++ b/src/include/rewrite/rewriteHandler.h
@@ -18,6 +18,7 @@
#include "nodes/parsenodes.h"
extern List *QueryRewrite(Query *parsetree);
+extern void QueryRewriteExpr(Node *node, List *activeRIRs);
extern void AcquireRewriteLocks(Query *parsetree, bool forUpdatePushedDown);
extern Node *build_column_default(Relation rel, int attrno);
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index 72f8491..35819fa 100644
--- a/src/include/utils/plancache.h
+++ b/src/include/utils/plancache.h
@@ -128,6 +128,8 @@ typedef struct CachedPlan
bool is_oneshot; /* is it a "oneshot" plan? */
bool is_saved; /* is CachedPlan in a long-lived context? */
bool is_valid; /* is the stmt_list currently valid? */
+ Oid planUserId; /* is user-id that is assumed on this cached
+ plan, or InvalidOid if portable for anybody */
TransactionId saved_xmin; /* if valid, replan when TransactionXmin
* changes from this value */
int generation; /* parent's generation number for this plan */
diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h
index 589c9a8..4e4ac65 100644
--- a/src/include/utils/rel.h
+++ b/src/include/utils/rel.h
@@ -18,6 +18,7 @@
#include "catalog/pg_am.h"
#include "catalog/pg_class.h"
#include "catalog/pg_index.h"
+#include "catalog/pg_rowsecurity.h"
#include "fmgr.h"
#include "nodes/bitmapset.h"
#include "rewrite/prs2lock.h"
@@ -109,6 +110,7 @@ typedef struct RelationData
RuleLock *rd_rules; /* rewrite rules */
MemoryContext rd_rulescxt; /* private memory cxt for rd_rules, if any */
TriggerDesc *trigdesc; /* Trigger info, or NULL if rel has none */
+ RowSecurityDesc *rsdesc; /* Row-security policy, or NULL */
/*
* rd_options is set whenever rd_rel is loaded into the relcache entry.
diff --git a/src/test/regress/.gitignore b/src/test/regress/.gitignore
index 7573add..4c3db8d 100644
--- a/src/test/regress/.gitignore
+++ b/src/test/regress/.gitignore
@@ -5,3 +5,7 @@
/tmp_check/
/results/
/log/
+
+# Output we should not version
+regression.diffs
+regression.out
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
new file mode 100644
index 0000000..971d72b
--- /dev/null
+++ b/src/test/regress/expected/rowsecurity.out
@@ -0,0 +1,1276 @@
+--
+-- Test of Row-level security feature
+--
+-- Clean up in case a prior regression run failed
+-- Suppress NOTICE messages when users/groups don't exist
+SET client_min_messages TO 'warning';
+DROP USER IF EXISTS rls_regress_user0;
+DROP USER IF EXISTS rls_regress_user1;
+DROP USER IF EXISTS rls_regress_user2;
+DROP SCHEMA IF EXISTS rls_regress_schema CASCADE;
+RESET client_min_messages;
+-- initial setup
+CREATE USER rls_regress_user0;
+CREATE USER rls_regress_user1;
+CREATE USER rls_regress_user2;
+CREATE SCHEMA rls_regress_schema;
+GRANT ALL ON SCHEMA rls_regress_schema TO public;
+SET search_path = rls_regress_schema;
+-- setup of malicious function
+CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
+ COST 0.0000001 LANGUAGE plpgsql
+ AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END';
+GRANT EXECUTE ON FUNCTION f_leak(text) TO public;
+-- BASIC Row-Level Security Scenario
+SET SESSION AUTHORIZATION rls_regress_user0;
+CREATE TABLE uaccount (
+ pguser name primary key,
+ seclv int
+);
+INSERT INTO uaccount VALUES
+ ('rls_regress_user0', 99),
+ ('rls_regress_user1', 1),
+ ('rls_regress_user2', 2),
+ ('rls_regress_user3', 3);
+GRANT SELECT ON uaccount TO public;
+CREATE TABLE category (
+ cid int primary key,
+ cname text
+);
+GRANT ALL ON category TO public;
+INSERT INTO category VALUES
+ (11, 'novel'),
+ (22, 'science fiction'),
+ (33, 'technology'),
+ (44, 'manga');
+CREATE TABLE document (
+ did int primary key,
+ cid int references category(cid),
+ dlevel int not null,
+ dauthor name,
+ dtitle text
+);
+GRANT ALL ON document TO public;
+INSERT INTO document VALUES
+ ( 1, 11, 1, 'rls_regress_user1', 'my first novel'),
+ ( 2, 11, 2, 'rls_regress_user1', 'my second novel'),
+ ( 3, 22, 2, 'rls_regress_user1', 'my science fiction'),
+ ( 4, 44, 1, 'rls_regress_user1', 'my first manga'),
+ ( 5, 44, 2, 'rls_regress_user1', 'my second manga'),
+ ( 6, 22, 1, 'rls_regress_user2', 'great science fiction'),
+ ( 7, 33, 2, 'rls_regress_user2', 'great technology book'),
+ ( 8, 44, 1, 'rls_regress_user2', 'great manga');
+-- user's security level must higher than or equal to document's one
+ALTER TABLE document SET ROW SECURITY FOR ALL
+ TO (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
+-- viewpoint from rls_regress_user1
+SET SESSION AUTHORIZATION rls_regress_user1;
+SELECT * FROM document WHERE f_leak(dtitle);
+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
+-----+-----+--------+-------------------+-----------------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 6 | 22 | 1 | rls_regress_user2 | great science fiction
+ 8 | 44 | 1 | rls_regress_user2 | great manga
+(4 rows)
+
+SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+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 | rls_regress_user1 | my first novel | novel
+ 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction
+ 44 | 8 | 1 | rls_regress_user2 | great manga | manga
+ 44 | 4 | 1 | rls_regress_user1 | my first manga | manga
+(4 rows)
+
+-- viewpoint from rls_regress_user2
+SET SESSION AUTHORIZATION rls_regress_user2;
+SELECT * FROM document WHERE f_leak(dtitle);
+NOTICE: f_leak => my first novel
+NOTICE: f_leak => my second novel
+NOTICE: f_leak => my science fiction
+NOTICE: f_leak => my first manga
+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
+-----+-----+--------+-------------------+-----------------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+ 6 | 22 | 1 | rls_regress_user2 | great science fiction
+ 7 | 33 | 2 | rls_regress_user2 | great technology book
+ 8 | 44 | 1 | rls_regress_user2 | great manga
+(8 rows)
+
+SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+NOTICE: f_leak => my first novel
+NOTICE: f_leak => my second novel
+NOTICE: f_leak => my science fiction
+NOTICE: f_leak => my first manga
+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 | 2 | 2 | rls_regress_user1 | my second novel | novel
+ 11 | 1 | 1 | rls_regress_user1 | my first novel | novel
+ 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction
+ 22 | 3 | 2 | rls_regress_user1 | my science fiction | science fiction
+ 33 | 7 | 2 | rls_regress_user2 | great technology book | technology
+ 44 | 8 | 1 | rls_regress_user2 | great manga | manga
+ 44 | 5 | 2 | rls_regress_user1 | my second manga | manga
+ 44 | 4 | 1 | rls_regress_user1 | my first manga | manga
+(8 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: (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: (dlevel <= $0)
+ InitPlan 1 (returns $0)
+ -> Index Scan using uaccount_pkey on uaccount
+ Index Cond: (pguser = "current_user"())
+(11 rows)
+
+-- only owner can change row-level security
+ALTER TABLE document SET ROW SECURITY FOR ALL TO (true); -- fail
+ERROR: must be owner of relation document
+ALTER TABLE document RESET ROW SECURITY FOR ALL; -- fail
+ERROR: must be owner of relation document
+SET SESSION AUTHORIZATION rls_regress_user0;
+ALTER TABLE document SET ROW SECURITY FOR ALL TO (dauthor = current_user);
+-- viewpoint from rls_regress_user1 again
+SET SESSION AUTHORIZATION rls_regress_user1;
+SELECT * FROM document WHERE f_leak(dtitle);
+NOTICE: f_leak => my first novel
+NOTICE: f_leak => my second novel
+NOTICE: f_leak => my science fiction
+NOTICE: f_leak => my first manga
+NOTICE: f_leak => my second manga
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+--------------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+(5 rows)
+
+SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+NOTICE: f_leak => my first novel
+NOTICE: f_leak => my second novel
+NOTICE: f_leak => my science fiction
+NOTICE: f_leak => my first manga
+NOTICE: f_leak => my second manga
+ cid | did | dlevel | dauthor | dtitle | cname
+-----+-----+--------+-------------------+--------------------+-----------------
+ 11 | 1 | 1 | rls_regress_user1 | my first novel | novel
+ 11 | 2 | 2 | rls_regress_user1 | my second novel | novel
+ 22 | 3 | 2 | rls_regress_user1 | my science fiction | science fiction
+ 44 | 4 | 1 | rls_regress_user1 | my first manga | manga
+ 44 | 5 | 2 | rls_regress_user1 | my second manga | manga
+(5 rows)
+
+-- viewpoint from rls_regress_user2 again
+SET SESSION AUTHORIZATION rls_regress_user2;
+SELECT * FROM document WHERE f_leak(dtitle);
+NOTICE: f_leak => great science fiction
+NOTICE: f_leak => great technology book
+NOTICE: f_leak => great manga
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-----------------------
+ 6 | 22 | 1 | rls_regress_user2 | great science fiction
+ 7 | 33 | 2 | rls_regress_user2 | great technology book
+ 8 | 44 | 1 | rls_regress_user2 | great manga
+(3 rows)
+
+SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+NOTICE: f_leak => great science fiction
+NOTICE: f_leak => great technology book
+NOTICE: f_leak => great manga
+ cid | did | dlevel | dauthor | dtitle | cname
+-----+-----+--------+-------------------+-----------------------+-----------------
+ 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction
+ 33 | 7 | 2 | rls_regress_user2 | great technology book | technology
+ 44 | 8 | 1 | rls_regress_user2 | great manga | manga
+(3 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: (dauthor = "current_user"())
+(4 rows)
+
+EXPLAIN (costs off) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+ QUERY PLAN
+----------------------------------------------------
+ Nested Loop
+ -> Subquery Scan on document
+ Filter: f_leak(document.dtitle)
+ -> Seq Scan on document document_1
+ Filter: (dauthor = "current_user"())
+ -> Index Scan using category_pkey on category
+ Index Cond: (cid = document.cid)
+(7 rows)
+
+-- interaction of FK/PK constraints
+SET SESSION AUTHORIZATION rls_regress_user0;
+ALTER TABLE category SET ROW SECURITY FOR ALL
+ TO (CASE WHEN current_user = 'rls_regress_user1' THEN cid IN (11, 33)
+ WHEN current_user = 'rls_regress_user2' THEN cid IN (22, 44)
+ ELSE false END);
+-- cannot delete PK referenced by invisible FK
+SET SESSION AUTHORIZATION rls_regress_user1;
+SELECT * FROM document d full outer join category c on d.cid = c.cid;
+ did | cid | dlevel | dauthor | dtitle | cid | cname
+-----+-----+--------+-------------------+--------------------+-----+------------
+ 2 | 11 | 2 | rls_regress_user1 | my second novel | 11 | novel
+ 1 | 11 | 1 | rls_regress_user1 | my first novel | 11 | novel
+ | | | | | 33 | technology
+ 5 | 44 | 2 | rls_regress_user1 | my second manga | |
+ 4 | 44 | 1 | rls_regress_user1 | my first manga | |
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction | |
+(6 rows)
+
+DELETE FROM category WHERE cid = 33; -- fails with FK violation
+ERROR: update or delete on table "category" violates foreign key constraint "document_cid_fkey" on table "document"
+DETAIL: Key (cid)=(33) is still referenced from table "document".
+-- cannot insert FK referencing invisible PK
+SET SESSION AUTHORIZATION rls_regress_user2;
+SELECT * FROM document d full outer join category c on d.cid = c.cid;
+ did | cid | dlevel | dauthor | dtitle | cid | cname
+-----+-----+--------+-------------------+-----------------------+-----+-----------------
+ 6 | 22 | 1 | rls_regress_user2 | great science fiction | 22 | science fiction
+ 8 | 44 | 1 | rls_regress_user2 | great manga | 44 | manga
+ 7 | 33 | 2 | rls_regress_user2 | great technology book | |
+(3 rows)
+
+INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge'); -- fail with FK violation
+ERROR: insert or update on table "document" violates foreign key constraint "document_cid_fkey"
+DETAIL: Key (cid)=(33) is not present in table "category".
+-- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
+SET SESSION AUTHORIZATION rls_regress_user1;
+INSERT INTO document VALUES ( 8, 44, 1, 'rls_regress_user_1', 'my third manga' ); -- Must fail with unique violation, revealing presence of did we can't see
+ERROR: duplicate key value violates unique constraint "document_pkey"
+DETAIL: Key (did)=(8) already exists.
+SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+---------+--------
+(0 rows)
+
+-- Nonsense results produced when RLS-exempt superuser tries to insert into
+-- table with FK owned by RLS-affected user. Should not be possible for this
+-- to fail except by PK conflict, but fails with FK constraint error. FIXME.
+RESET SESSION AUTHORIZATION;
+INSERT INTO document(did,cid,dlevel,dauthor,dtitle)
+SELECT 1000, cid, dlevel, dauthor, dtitle from document where did = 8;
+INSERT 0 1
+
+-- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
+SET SESSION AUTHORIZATION rls_regress_user1;
+INSERT INTO document VALUES ( 8, 44, 1, 'rls_regress_user_1', 'my third manga' ); -- Must fail with unique violation, revealing presence of did we can't see
+ERROR: duplicate key value violates unique constraint "document_pkey"
+DETAIL: Key (did)=(8) already exists.
+SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+---------+--------
+(0 rows)
+
+-- database superuser can bypass RLS policy
+RESET SESSION AUTHORIZATION;
+SELECT * FROM document;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-----------------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+ 6 | 22 | 1 | rls_regress_user2 | great science fiction
+ 7 | 33 | 2 | rls_regress_user2 | great technology book
+ 8 | 44 | 1 | rls_regress_user2 | great manga
+(8 rows)
+
+SELECT * FROM category;
+ cid | cname
+-----+-----------------
+ 11 | novel
+ 22 | science fiction
+ 33 | technology
+ 44 | manga
+(4 rows)
+
+--
+-- Table inheritance and RLS policy
+--
+SET SESSION AUTHORIZATION rls_regress_user0;
+CREATE TABLE t1 (a int, junk1 text, b text) WITH OIDS;
+ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor
+GRANT ALL ON t1 TO public;
+COPY t1 FROM stdin WITH (oids);
+CREATE TABLE t2 (c float) INHERITS (t1);
+COPY t2 FROM stdin WITH (oids);
+CREATE TABLE t3 (c text, b text, a int) WITH OIDS;
+ALTER TABLE t3 INHERIT t1;
+COPY t3(a,b,c) FROM stdin WITH (oids);
+ALTER TABLE t1 SET ROW SECURITY FOR ALL TO (a % 2 = 0); -- be even number
+ALTER TABLE t2 SET ROW SECURITY FOR ALL TO (a % 2 = 1); -- be odd number
+SELECT * FROM t1;
+ a | b
+---+-----
+ 2 | bbb
+ 4 | ddd
+ 1 | abc
+ 3 | cde
+ 1 | xxx
+ 2 | yyy
+ 3 | zzz
+(7 rows)
+
+EXPLAIN (costs off) SELECT * FROM t1;
+ QUERY PLAN
+-------------------------------------
+ Append
+ -> Subquery Scan on t1
+ -> Seq Scan on t1 t1_1
+ Filter: ((a % 2) = 0)
+ -> Subquery Scan on t2
+ -> Seq Scan on t2 t2_1
+ Filter: ((a % 2) = 1)
+ -> Seq Scan on t3
+(8 rows)
+
+SELECT * FROM t1 WHERE f_leak(b);
+NOTICE: f_leak => bbb
+NOTICE: f_leak => ddd
+NOTICE: f_leak => abc
+NOTICE: f_leak => cde
+NOTICE: f_leak => xxx
+NOTICE: f_leak => yyy
+NOTICE: f_leak => zzz
+ a | b
+---+-----
+ 2 | bbb
+ 4 | ddd
+ 1 | abc
+ 3 | cde
+ 1 | xxx
+ 2 | yyy
+ 3 | zzz
+(7 rows)
+
+EXPLAIN (costs off) SELECT * FROM t1 WHERE f_leak(b);
+ QUERY PLAN
+-------------------------------------
+ Append
+ -> Subquery Scan on t1
+ Filter: f_leak(t1.b)
+ -> Seq Scan on t1 t1_1
+ Filter: ((a % 2) = 0)
+ -> Subquery Scan on t2
+ Filter: f_leak(t2.b)
+ -> Seq Scan on t2 t2_1
+ Filter: ((a % 2) = 1)
+ -> Seq Scan on t3
+ Filter: f_leak(b)
+(11 rows)
+
+-- reference to system column
+SELECT oid, * FROM t1;
+ oid | a | b
+-----+---+-----
+ 102 | 2 | bbb
+ 104 | 4 | ddd
+ 201 | 1 | abc
+ 203 | 3 | cde
+ 301 | 1 | xxx
+ 302 | 2 | yyy
+ 303 | 3 | zzz
+(7 rows)
+
+EXPLAIN (costs off) SELECT * FROM t1;
+ QUERY PLAN
+-------------------------------------
+ Append
+ -> Subquery Scan on t1
+ -> Seq Scan on t1 t1_1
+ Filter: ((a % 2) = 0)
+ -> Subquery Scan on t2
+ -> Seq Scan on t2 t2_1
+ Filter: ((a % 2) = 1)
+ -> Seq Scan on t3
+(8 rows)
+
+-- reference to whole-row reference
+SELECT *,t1 FROM t1;
+ a | b | t1
+---+-----+---------
+ 2 | bbb | (2,bbb)
+ 4 | ddd | (4,ddd)
+ 1 | abc | (1,abc)
+ 3 | cde | (3,cde)
+ 1 | xxx | (1,xxx)
+ 2 | yyy | (2,yyy)
+ 3 | zzz | (3,zzz)
+(7 rows)
+
+EXPLAIN (costs off) SELECT *,t1 FROM t1;
+ QUERY PLAN
+-------------------------------------
+ Append
+ -> Subquery Scan on t1
+ -> Seq Scan on t1 t1_1
+ Filter: ((a % 2) = 0)
+ -> Subquery Scan on t2
+ -> Seq Scan on t2 t2_1
+ Filter: ((a % 2) = 1)
+ -> Seq Scan on t3
+(8 rows)
+
+-- for share/update lock
+SELECT * FROM t1 FOR SHARE;
+ a | b
+---+-----
+ 2 | bbb
+ 4 | ddd
+ 1 | abc
+ 3 | cde
+ 1 | xxx
+ 2 | yyy
+ 3 | zzz
+(7 rows)
+
+EXPLAIN (costs off) SELECT * FROM t1 FOR SHARE;
+ QUERY PLAN
+-------------------------------------------
+ LockRows
+ -> Append
+ -> Subquery Scan on t1
+ -> Seq Scan on t1 t1_1
+ Filter: ((a % 2) = 0)
+ -> Subquery Scan on t2
+ -> Seq Scan on t2 t2_1
+ Filter: ((a % 2) = 1)
+ -> Seq Scan on t3
+(9 rows)
+
+SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
+NOTICE: f_leak => bbb
+NOTICE: f_leak => ddd
+NOTICE: f_leak => abc
+NOTICE: f_leak => cde
+NOTICE: f_leak => xxx
+NOTICE: f_leak => yyy
+NOTICE: f_leak => zzz
+ a | b
+---+-----
+ 2 | bbb
+ 4 | ddd
+ 1 | abc
+ 3 | cde
+ 1 | xxx
+ 2 | yyy
+ 3 | zzz
+(7 rows)
+
+EXPLAIN (costs off) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
+ QUERY PLAN
+-------------------------------------------
+ LockRows
+ -> Append
+ -> Subquery Scan on t1
+ Filter: f_leak(t1.b)
+ -> Seq Scan on t1 t1_1
+ Filter: ((a % 2) = 0)
+ -> Subquery Scan on t2
+ Filter: f_leak(t2.b)
+ -> Seq Scan on t2 t2_1
+ Filter: ((a % 2) = 1)
+ -> Seq Scan on t3
+ Filter: f_leak(b)
+(12 rows)
+
+--
+-- COPY TO statement
+--
+COPY t1 TO stdout;
+2 bbb
+4 ddd
+COPY t1 TO stdout WITH OIDS;
+102 2 bbb
+104 4 ddd
+COPY t2(c,b) TO stdout WITH OIDS;
+201 1.1 abc
+203 3.3 cde
+COPY (SELECT * FROM t1) TO stdout;
+2 bbb
+4 ddd
+1 abc
+3 cde
+1 xxx
+2 yyy
+3 zzz
+COPY document TO stdout WITH OIDS; -- failed (no oid column)
+ERROR: table "document" does not have OIDs
+--
+-- recursive RLS and VIEWs in policy
+--
+CREATE TABLE s1 (a int, b text);
+INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
+CREATE TABLE s2 (x int, y text);
+INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x);
+CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%';
+ALTER TABLE s1 SET ROW SECURITY FOR ALL
+ TO (a in (select x from s2 where y like '%2f%'));
+ALTER TABLE s2 SET ROW SECURITY FOR ALL
+ TO (x in (select a from s1 where b like '%22%'));
+SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion)
+ERROR: infinite recursion detected for relation "s1"
+ALTER TABLE s2 SET ROW SECURITY FOR ALL TO (x % 2 = 0);
+SELECT * FROM s1 WHERE f_leak(b); -- OK
+NOTICE: f_leak => c81e728d9d4c2f636f067f89cc14862c
+NOTICE: f_leak => a87ff679a2f3e71d9181a67b7542122c
+ a | b
+---+----------------------------------
+ 2 | c81e728d9d4c2f636f067f89cc14862c
+ 4 | a87ff679a2f3e71d9181a67b7542122c
+(2 rows)
+
+EXPLAIN SELECT * FROM only s1 WHERE f_leak(b);
+ QUERY PLAN
+---------------------------------------------------------------------------------------
+ Subquery Scan on s1 (cost=28.55..61.67 rows=205 width=36)
+ Filter: f_leak(s1.b)
+ -> Hash Join (cost=28.55..55.52 rows=615 width=36)
+ Hash Cond: (s1_1.a = s2.x)
+ -> Seq Scan on s1 s1_1 (cost=0.00..22.30 rows=1230 width=36)
+ -> Hash (cost=28.54..28.54 rows=1 width=4)
+ -> HashAggregate (cost=28.53..28.54 rows=1 width=4)
+ -> Subquery Scan on s2 (cost=0.00..28.52 rows=1 width=4)
+ Filter: (s2.y ~~ '%2f%'::text)
+ -> Seq Scan on s2 s2_1 (cost=0.00..28.45 rows=6 width=36)
+ Filter: ((x % 2) = 0)
+(11 rows)
+
+ALTER TABLE s1 SET ROW SECURITY FOR ALL
+ TO (a in (select x from v2)); -- using VIEW in RLS policy
+SELECT * FROM s1 WHERE f_leak(b); -- OK
+NOTICE: f_leak => 0267aaf632e87a63288a08331f22c7c3
+NOTICE: f_leak => 1679091c5a880faf6fb5e6087eb1b2dc
+ a | b
+----+----------------------------------
+ -4 | 0267aaf632e87a63288a08331f22c7c3
+ 6 | 1679091c5a880faf6fb5e6087eb1b2dc
+(2 rows)
+
+EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
+ QUERY PLAN
+----------------------------------------------------------
+ Subquery Scan on s1
+ Filter: f_leak(s1.b)
+ -> Hash Join
+ Hash Cond: (s1_1.a = s2.x)
+ -> Seq Scan on s1 s1_1
+ -> Hash
+ -> HashAggregate
+ -> Subquery Scan on s2
+ Filter: (s2.y ~~ '%af%'::text)
+ -> Seq Scan on s2 s2_1
+ Filter: ((x % 2) = 0)
+(11 rows)
+
+SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
+ xx | x | y
+----+----+----------------------------------
+ -6 | -6 | 596a3d04481816330f07e4f97510c28f
+ -4 | -4 | 0267aaf632e87a63288a08331f22c7c3
+ 2 | 2 | c81e728d9d4c2f636f067f89cc14862c
+(3 rows)
+
+EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
+ QUERY PLAN
+--------------------------------------------------------------------
+ Subquery Scan on s2
+ Filter: (s2.y ~~ '%28%'::text)
+ -> Seq Scan on s2 s2_1
+ Filter: ((x % 2) = 0)
+ SubPlan 1
+ -> Limit
+ -> Subquery Scan on s1
+ -> Nested Loop Semi Join
+ Join Filter: (s1_1.a = s2_2.x)
+ -> Seq Scan on s1 s1_1
+ -> Materialize
+ -> Subquery Scan on s2_2
+ Filter: (s2_2.y ~~ '%af%'::text)
+ -> Seq Scan on s2 s2_3
+ Filter: ((x % 2) = 0)
+(15 rows)
+
+ALTER TABLE s2 SET ROW SECURITY FOR ALL
+ TO (x in (select a from s1 where b like '%d2%'));
+SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion via view)
+ERROR: infinite recursion detected for relation "s1"
+-- prepared statement with rls_regress_user0 privilege
+PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1;
+EXECUTE p1(2);
+ a | b
+---+-----
+ 2 | bbb
+ 1 | abc
+ 1 | xxx
+ 2 | yyy
+(4 rows)
+
+EXPLAIN (costs off) EXECUTE p1(2);
+ QUERY PLAN
+----------------------------------------------------
+ Append
+ -> Subquery Scan on t1
+ -> Seq Scan on t1 t1_1
+ Filter: ((a <= 2) AND ((a % 2) = 0))
+ -> Subquery Scan on t2
+ -> Seq Scan on t2 t2_1
+ Filter: ((a <= 2) AND ((a % 2) = 1))
+ -> Seq Scan on t3
+ Filter: (a <= 2)
+(9 rows)
+
+-- superuser is allowed to bypass RLS checks
+RESET SESSION AUTHORIZATION;
+SELECT * FROM t1 WHERE f_leak(b);
+NOTICE: f_leak => aaa
+NOTICE: f_leak => bbb
+NOTICE: f_leak => ccc
+NOTICE: f_leak => ddd
+NOTICE: f_leak => abc
+NOTICE: f_leak => bcd
+NOTICE: f_leak => cde
+NOTICE: f_leak => def
+NOTICE: f_leak => xxx
+NOTICE: f_leak => yyy
+NOTICE: f_leak => zzz
+ a | b
+---+-----
+ 1 | aaa
+ 2 | bbb
+ 3 | ccc
+ 4 | ddd
+ 1 | abc
+ 2 | bcd
+ 3 | cde
+ 4 | def
+ 1 | xxx
+ 2 | yyy
+ 3 | zzz
+(11 rows)
+
+EXPLAIN (costs off) SELECT * FROM t1 WHERE f_leak(b);
+ QUERY PLAN
+---------------------------
+ Append
+ -> Seq Scan on t1
+ Filter: f_leak(b)
+ -> Seq Scan on t2
+ Filter: f_leak(b)
+ -> Seq Scan on t3
+ Filter: f_leak(b)
+(7 rows)
+
+-- plan cache should be invalidated
+EXECUTE p1(2);
+ a | b
+---+-----
+ 1 | aaa
+ 2 | bbb
+ 1 | abc
+ 2 | bcd
+ 1 | xxx
+ 2 | yyy
+(6 rows)
+
+EXPLAIN (costs off) EXECUTE p1(2);
+ QUERY PLAN
+--------------------------
+ Append
+ -> Seq Scan on t1
+ Filter: (a <= 2)
+ -> Seq Scan on t2
+ Filter: (a <= 2)
+ -> Seq Scan on t3
+ Filter: (a <= 2)
+(7 rows)
+
+PREPARE p2(int) AS SELECT * FROM t1 WHERE a = $1;
+EXECUTE p2(2);
+ a | b
+---+-----
+ 2 | bbb
+ 2 | bcd
+ 2 | yyy
+(3 rows)
+
+EXPLAIN (costs off) EXECUTE p2(2);
+ QUERY PLAN
+-------------------------
+ Append
+ -> Seq Scan on t1
+ Filter: (a = 2)
+ -> Seq Scan on t2
+ Filter: (a = 2)
+ -> Seq Scan on t3
+ Filter: (a = 2)
+(7 rows)
+
+-- also, case when privilege switch from superuser
+SET SESSION AUTHORIZATION rls_regress_user0;
+EXECUTE p2(2);
+ a | b
+---+-----
+ 2 | bbb
+ 2 | yyy
+(2 rows)
+
+EXPLAIN (costs off) EXECUTE p2(2);
+ QUERY PLAN
+---------------------------------------------------
+ Append
+ -> Subquery Scan on t1
+ -> Seq Scan on t1 t1_1
+ Filter: ((a = 2) AND ((a % 2) = 0))
+ -> Subquery Scan on t2
+ -> Seq Scan on t2 t2_1
+ Filter: ((a = 2) AND ((a % 2) = 1))
+ -> Seq Scan on t3
+ Filter: (a = 2)
+(9 rows)
+
+--
+-- UPDATE / DELETE and Row-level security
+--
+SET SESSION AUTHORIZATION rls_regress_user0;
+EXPLAIN (costs off) UPDATE t1 SET b = b || b WHERE f_leak(b);
+ QUERY PLAN
+-------------------------------------
+ Update on t1
+ -> Subquery Scan on t1_1
+ Filter: f_leak(t1_1.b)
+ -> Seq Scan on t1 t1_2
+ Filter: ((a % 2) = 0)
+ -> Subquery Scan on t2
+ Filter: f_leak(t2.b)
+ -> Seq Scan on t2 t2_1
+ Filter: ((a % 2) = 1)
+ -> Seq Scan on t3
+ Filter: f_leak(b)
+(11 rows)
+
+UPDATE t1 SET b = b || b WHERE f_leak(b);
+NOTICE: f_leak => bbb
+NOTICE: f_leak => ddd
+NOTICE: f_leak => abc
+NOTICE: f_leak => cde
+NOTICE: f_leak => xxx
+NOTICE: f_leak => yyy
+NOTICE: f_leak => zzz
+EXPLAIN (costs off) UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
+ QUERY PLAN
+-------------------------------------
+ Update on t1
+ -> Subquery Scan on t1_1
+ Filter: f_leak(t1_1.b)
+ -> Seq Scan on t1 t1_2
+ Filter: ((a % 2) = 0)
+(5 rows)
+
+UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
+NOTICE: f_leak => bbbbbb
+NOTICE: f_leak => dddddd
+-- returning clause with system column
+UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
+NOTICE: f_leak => bbbbbb_updt
+NOTICE: f_leak => dddddd_updt
+ oid | a | b | t1
+-----+---+-------------+-----------------
+ 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt)
+ 104 | 4 | dddddd_updt | (4,dddddd_updt)
+(2 rows)
+
+UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *;
+NOTICE: f_leak => bbbbbb_updt
+NOTICE: f_leak => dddddd_updt
+NOTICE: f_leak => abcabc
+NOTICE: f_leak => cdecde
+NOTICE: f_leak => xxxxxx
+NOTICE: f_leak => yyyyyy
+NOTICE: f_leak => zzzzzz
+ a | b
+---+-------------
+ 2 | bbbbbb_updt
+ 4 | dddddd_updt
+ 1 | abcabc
+ 3 | cdecde
+ 1 | xxxxxx
+ 2 | yyyyyy
+ 3 | zzzzzz
+(7 rows)
+
+UPDATE t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
+NOTICE: f_leak => bbbbbb_updt
+NOTICE: f_leak => dddddd_updt
+NOTICE: f_leak => abcabc
+NOTICE: f_leak => cdecde
+NOTICE: f_leak => xxxxxx
+NOTICE: f_leak => yyyyyy
+NOTICE: f_leak => zzzzzz
+ oid | a | b | t1
+-----+---+-------------+-----------------
+ 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt)
+ 104 | 4 | dddddd_updt | (4,dddddd_updt)
+ 201 | 1 | abcabc | (1,abcabc)
+ 203 | 3 | cdecde | (3,cdecde)
+ 301 | 1 | xxxxxx | (1,xxxxxx)
+ 302 | 2 | yyyyyy | (2,yyyyyy)
+ 303 | 3 | zzzzzz | (3,zzzzzz)
+(7 rows)
+
+RESET SESSION AUTHORIZATION;
+SELECT * FROM t1;
+ a | b
+---+-------------
+ 1 | aaa
+ 3 | ccc
+ 2 | bbbbbb_updt
+ 4 | dddddd_updt
+ 2 | bcd
+ 4 | def
+ 1 | abcabc
+ 3 | cdecde
+ 1 | xxxxxx
+ 2 | yyyyyy
+ 3 | zzzzzz
+(11 rows)
+
+SET SESSION AUTHORIZATION rls_regress_user0;
+EXPLAIN (costs off) DELETE FROM only t1 WHERE f_leak(b);
+ QUERY PLAN
+-------------------------------------
+ Delete on t1
+ -> Subquery Scan on t1_1
+ Filter: f_leak(t1_1.b)
+ -> Seq Scan on t1 t1_2
+ Filter: ((a % 2) = 0)
+(5 rows)
+
+EXPLAIN (costs off) DELETE FROM t1 WHERE f_leak(b);
+ QUERY PLAN
+-------------------------------------
+ Delete on t1
+ -> Subquery Scan on t1_1
+ Filter: f_leak(t1_1.b)
+ -> Seq Scan on t1 t1_2
+ Filter: ((a % 2) = 0)
+ -> Subquery Scan on t2
+ Filter: f_leak(t2.b)
+ -> Seq Scan on t2 t2_1
+ Filter: ((a % 2) = 1)
+ -> Seq Scan on t3
+ Filter: f_leak(b)
+(11 rows)
+
+DELETE FROM only t1 WHERE f_leak(b) RETURNING oid, *, t1;
+NOTICE: f_leak => bbbbbb_updt
+NOTICE: f_leak => dddddd_updt
+ oid | a | b | t1
+-----+---+-------------+-----------------
+ 102 | 2 | bbbbbb_updt | (2,bbbbbb_updt)
+ 104 | 4 | dddddd_updt | (4,dddddd_updt)
+(2 rows)
+
+DELETE FROM t1 WHERE f_leak(b) RETURNING oid, *, t1;
+NOTICE: f_leak => abcabc
+NOTICE: f_leak => cdecde
+NOTICE: f_leak => xxxxxx
+NOTICE: f_leak => yyyyyy
+NOTICE: f_leak => zzzzzz
+ oid | a | b | t1
+-----+---+--------+------------
+ 201 | 1 | abcabc | (1,abcabc)
+ 203 | 3 | cdecde | (3,cdecde)
+ 301 | 1 | xxxxxx | (1,xxxxxx)
+ 302 | 2 | yyyyyy | (2,yyyyyy)
+ 303 | 3 | zzzzzz | (3,zzzzzz)
+(5 rows)
+
+----------------------------------------------------------------------
+-- Check refcursors returned from PL/PgSQL SECURITY DEFINER functions
+RESET SESSION AUTHORIZATION;
+CREATE OR REPLACE FUNCTION return_refcursor_assuper() RETURNS refcursor AS $$
+DECLARE
+ curs1 refcursor;
+BEGIN
+ curs1 = 'super_cursor';
+ OPEN curs1 FOR SELECT * FROM document;
+ RETURN curs1;
+END;
+$$
+LANGUAGE plpgsql
+SECURITY DEFINER;
+-- Run the function entirely as rls_regress_user1
+SET SESSION AUTHORIZATION rls_regress_user1;
+BEGIN;
+SELECT return_refcursor_assuper();
+ return_refcursor_assuper
+--------------------------
+ super_cursor
+(1 row)
+
+-- This fetch should return the full results, even though we are now
+-- running as a user with much lower access according to the current
+-- RLS policy.
+FETCH ALL FROM "super_cursor";
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-----------------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+ 6 | 22 | 1 | rls_regress_user2 | great science fiction
+ 7 | 33 | 2 | rls_regress_user2 | great technology book
+ 8 | 44 | 1 | rls_regress_user2 | great manga
+(8 rows)
+
+-- But this should still return the usual result set
+SELECT * FROM document;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+--------------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+(5 rows)
+
+ROLLBACK;
+-- Do the same check where we return a refcursor from one RLS-affected
+-- user to another RLS-affected user.
+SET SESSION AUTHORIZATION rls_regress_user2;
+CREATE OR REPLACE FUNCTION return_refcursor_asuser2() RETURNS refcursor AS $$
+DECLARE
+ curs1 refcursor;
+BEGIN
+ curs1 = 'user2_cursor';
+ OPEN curs1 FOR SELECT * FROM document;
+ RETURN curs1;
+END;
+$$
+LANGUAGE plpgsql
+SECURITY DEFINER;
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user1;
+SELECT return_refcursor_asuser2();
+ return_refcursor_asuser2
+--------------------------
+ user2_cursor
+(1 row)
+
+-- Even though we're user1, we should see only user2's results from this.
+-- This FAILS, returning user1's results.
+FETCH ALL FROM "user2_cursor";
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-----------------------
+ 6 | 22 | 1 | rls_regress_user2 | great science fiction
+ 7 | 33 | 2 | rls_regress_user2 | great technology book
+ 8 | 44 | 1 | rls_regress_user2 | great manga
+(3 rows)
+
+-- but user1's results for this
+SELECT * FROM document;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+--------------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+(5 rows)
+
+ROLLBACK;
+-- Now as the superuser, see if the SECURITY DEFINER on an RLS-affected
+-- user filters the rows the superuser sees. It should, for consistency.
+BEGIN;
+RESET SESSION AUTHORIZATION;
+SELECT return_refcursor_asuser2();
+ return_refcursor_asuser2
+--------------------------
+ user2_cursor
+(1 row)
+
+-- Should see user2's results, but FAILS, instead returning an empty result set (!)
+FETCH ALL FROM "user2_cursor";
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-----------------------
+ 6 | 22 | 1 | rls_regress_user2 | great science fiction
+ 7 | 33 | 2 | rls_regress_user2 | great technology book
+ 8 | 44 | 1 | rls_regress_user2 | great manga
+(3 rows)
+
+-- Should see superuser's results
+SELECT * FROM document;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-----------------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+ 6 | 22 | 1 | rls_regress_user2 | great science fiction
+ 7 | 33 | 2 | rls_regress_user2 | great technology book
+ 8 | 44 | 1 | rls_regress_user2 | great manga
+(8 rows)
+
+ROLLBACK;
+--------------------------------------------------------------------
+-- Tests of DECLARE and FETCH cursors during privilege
+-- transitions.
+--
+-- Declare as user1, switch to user2, fetch all. Returns results as user2;
+-- should be user1. FIXME.
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user1;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document;
+SET SESSION AUTHORIZATION rls_regress_user2;
+FETCH ALL FROM curs;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+--------------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+(5 rows)
+
+ROLLBACK;
+-- If we add an ORDER BY clause on a non-indexed column to force a sort,
+-- still returns rows for user2 because execution didn't start. FIXME.
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user1;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document ORDER BY cid;
+SET SESSION AUTHORIZATION rls_regress_user2;
+FETCH ALL FROM curs;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+--------------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+(5 rows)
+
+ROLLBACK;
+-- If we add a single row FETCH before switching to force materialization
+-- though, suddenly we see rows for user1. This is correct, just inconsistent.
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user1;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document ORDER BY cid;
+FETCH 1 FROM curs;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+----------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+(1 row)
+
+SET SESSION AUTHORIZATION rls_regress_user2;
+FETCH ALL FROM curs;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+--------------------
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+(4 rows)
+
+ROLLBACK;
+-- Remove the ORDER BY, and we get rows for user2 again, because the result set
+-- isn't materialized anymore. FIXME.
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user1;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document;
+FETCH 1 FROM curs;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+----------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+(1 row)
+
+SET SESSION AUTHORIZATION rls_regress_user2;
+FETCH ALL FROM curs;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+--------------------
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+(4 rows)
+
+ROLLBACK;
+-- Perform similar tests with superuser.
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user1;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document;
+RESET SESSION AUTHORIZATION;
+-- Should return user1 rows, returns none instead (FIXME)
+FETCH ALL FROM curs;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+--------------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+(5 rows)
+
+ROLLBACK;
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user2;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document;
+FETCH 2 FROM curs;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-----------------------
+ 6 | 22 | 1 | rls_regress_user2 | great science fiction
+ 7 | 33 | 2 | rls_regress_user2 | great technology book
+(2 rows)
+
+RESET SESSION AUTHORIZATION;
+-- Should see user2's rows; instead sees none due to username check change (FIXME)
+FETCH ALL FROM curs;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-----------------------
+ 8 | 44 | 1 | rls_regress_user2 | great manga
+(1 row)
+
+ROLLBACK;
+BEGIN;
+RESET SESSION AUTHORIZATION;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document;
+SET SESSION AUTHORIZATION rls_regress_user1;
+-- Should return all rows and does so because it's planned as superuser before switch so rls qual not added
+FETCH ALL FROM curs;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-----------------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+ 6 | 22 | 1 | rls_regress_user2 | great science fiction
+ 7 | 33 | 2 | rls_regress_user2 | great technology book
+ 8 | 44 | 1 | rls_regress_user2 | great manga
+(8 rows)
+
+ROLLBACK;
+BEGIN;
+RESET SESSION AUTHORIZATION;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document;
+FETCH 3 FROM curs;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+--------------------
+ 1 | 11 | 1 | rls_regress_user1 | my first novel
+ 2 | 11 | 2 | rls_regress_user1 | my second novel
+ 3 | 22 | 2 | rls_regress_user1 | my science fiction
+(3 rows)
+
+SET SESSION AUTHORIZATION rls_regress_user2;
+-- Still returns all rows because it's planned as superuser before switch, so rls qual not added
+FETCH ALL FROM curs;
+ did | cid | dlevel | dauthor | dtitle
+-----+-----+--------+-------------------+-----------------------
+ 4 | 44 | 1 | rls_regress_user1 | my first manga
+ 5 | 44 | 2 | rls_regress_user1 | my second manga
+ 6 | 22 | 1 | rls_regress_user2 | great science fiction
+ 7 | 33 | 2 | rls_regress_user2 | great technology book
+ 8 | 44 | 1 | rls_regress_user2 | great manga
+(5 rows)
+
+ROLLBACK;
+----------------------------------------------------------------------
+-- Test psql \dt+ command
+--
+ALTER TABLE category RESET ROW SECURITY FOR ALL; -- too long qual
+ERROR: must be owner of relation category
+\dt+
+ List of relations
+ Schema | Name | Type | Owner | Size | Description | Row-security
+--------------------+----------+-------+-------------------+------------+-------------+------------------------------------------------------------------------------------------
+ rls_regress_schema | category | table | rls_regress_user0 | 16 kB | | +
+ | | | | | | CASE +
+ | | | | | | WHEN ("current_user"() = 'rls_regress_user1'::name) THEN (cid = ANY (ARRAY[11, 33]))+
+ | | | | | | WHEN ("current_user"() = 'rls_regress_user2'::name) THEN (cid = ANY (ARRAY[22, 44]))+
+ | | | | | | ELSE false +
+ | | | | | | END
+ rls_regress_schema | document | table | rls_regress_user0 | 16 kB | | (dauthor = "current_user"())
+ rls_regress_schema | s1 | table | rls_regress_user0 | 16 kB | | (a IN ( SELECT v2.x +
+ | | | | | | FROM v2))
+ rls_regress_schema | s2 | table | rls_regress_user0 | 16 kB | | (x IN ( SELECT s1.a +
+ | | | | | | FROM s1 +
+ | | | | | | WHERE (s1.b ~~ '%d2%'::text)))
+ rls_regress_schema | t1 | table | rls_regress_user0 | 16 kB | | ((a % 2) = 0)
+ rls_regress_schema | t2 | table | rls_regress_user0 | 16 kB | | ((a % 2) = 1)
+ rls_regress_schema | t3 | table | rls_regress_user0 | 16 kB | |
+ rls_regress_schema | uaccount | table | rls_regress_user0 | 8192 bytes | |
+(8 rows)
+
+----------------------------------------------------------------------
+-- Clean up objects
+--
+RESET SESSION AUTHORIZATION;
+DROP SCHEMA rls_regress_schema CASCADE;
+NOTICE: drop cascades to 12 other objects
+DETAIL: drop cascades to function f_leak(text)
+drop cascades to table uaccount
+drop cascades to table category
+drop cascades to table document
+drop cascades to table t1
+drop cascades to table t2
+drop cascades to table t3
+drop cascades to table s1
+drop cascades to table s2
+drop cascades to view v2
+drop cascades to function return_refcursor_assuper()
+drop cascades to function return_refcursor_asuser2()
+DROP USER rls_regress_user0;
+DROP USER rls_regress_user1;
+DROP USER rls_regress_user2;
diff --git a/src/test/regress/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out
index a62a3e3..a41344b 100644
--- a/src/test/regress/expected/sanity_check.out
+++ b/src/test/regress/expected/sanity_check.out
@@ -121,6 +121,7 @@ pg_pltemplate|t
pg_proc|t
pg_range|t
pg_rewrite|t
+pg_rowsecurity|t
pg_seclabel|t
pg_shdepend|t
pg_shdescription|t
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 1c1491c..91a4aff 100644
--- a/src/test/regress/parallel_schedule
+++ b/src/test/regress/parallel_schedule
@@ -83,7 +83,7 @@ test: select_into select_distinct select_distinct_on select_implicit select_havi
# ----------
# Another group of parallel tests
# ----------
-test: privileges security_label collate matview lock
+test: privileges rowsecurity security_label collate matview lock
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index c4d451a..6031350 100644
--- a/src/test/regress/serial_schedule
+++ b/src/test/regress/serial_schedule
@@ -94,6 +94,7 @@ test: delete
test: namespace
test: prepared_xacts
test: privileges
+test: rowsecurity
test: security_label
test: collate
test: matview
diff --git a/src/test/regress/sql/rowsecurity.sql b/src/test/regress/sql/rowsecurity.sql
new file mode 100644
index 0000000..15a832c
--- /dev/null
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -0,0 +1,464 @@
+--
+-- Test of Row-level security feature
+--
+
+-- Clean up in case a prior regression run failed
+
+-- Suppress NOTICE messages when users/groups don't exist
+SET client_min_messages TO 'warning';
+
+DROP USER IF EXISTS rls_regress_user0;
+DROP USER IF EXISTS rls_regress_user1;
+DROP USER IF EXISTS rls_regress_user2;
+
+DROP SCHEMA IF EXISTS rls_regress_schema CASCADE;
+
+RESET client_min_messages;
+
+-- initial setup
+CREATE USER rls_regress_user0;
+CREATE USER rls_regress_user1;
+CREATE USER rls_regress_user2;
+
+CREATE SCHEMA rls_regress_schema;
+GRANT ALL ON SCHEMA rls_regress_schema TO public;
+SET search_path = rls_regress_schema;
+
+-- setup of malicious function
+CREATE OR REPLACE FUNCTION f_leak(text) RETURNS bool
+ COST 0.0000001 LANGUAGE plpgsql
+ AS 'BEGIN RAISE NOTICE ''f_leak => %'', $1; RETURN true; END';
+GRANT EXECUTE ON FUNCTION f_leak(text) TO public;
+
+-- BASIC Row-Level Security Scenario
+
+SET SESSION AUTHORIZATION rls_regress_user0;
+CREATE TABLE uaccount (
+ pguser name primary key,
+ seclv int
+);
+INSERT INTO uaccount VALUES
+ ('rls_regress_user0', 99),
+ ('rls_regress_user1', 1),
+ ('rls_regress_user2', 2),
+ ('rls_regress_user3', 3);
+GRANT SELECT ON uaccount TO public;
+
+CREATE TABLE category (
+ cid int primary key,
+ cname text
+);
+GRANT ALL ON category TO public;
+INSERT INTO category VALUES
+ (11, 'novel'),
+ (22, 'science fiction'),
+ (33, 'technology'),
+ (44, 'manga');
+
+CREATE TABLE document (
+ did int primary key,
+ cid int references category(cid),
+ dlevel int not null,
+ dauthor name,
+ dtitle text
+);
+GRANT ALL ON document TO public;
+INSERT INTO document VALUES
+ ( 1, 11, 1, 'rls_regress_user1', 'my first novel'),
+ ( 2, 11, 2, 'rls_regress_user1', 'my second novel'),
+ ( 3, 22, 2, 'rls_regress_user1', 'my science fiction'),
+ ( 4, 44, 1, 'rls_regress_user1', 'my first manga'),
+ ( 5, 44, 2, 'rls_regress_user1', 'my second manga'),
+ ( 6, 22, 1, 'rls_regress_user2', 'great science fiction'),
+ ( 7, 33, 2, 'rls_regress_user2', 'great technology book'),
+ ( 8, 44, 1, 'rls_regress_user2', 'great manga');
+
+-- user's security level must higher than or equal to document's one
+ALTER TABLE document SET ROW SECURITY FOR ALL
+ TO (dlevel <= (SELECT seclv FROM uaccount WHERE pguser = current_user));
+
+-- viewpoint from rls_regress_user1
+SET SESSION AUTHORIZATION rls_regress_user1;
+SELECT * FROM document WHERE f_leak(dtitle);
+SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+
+-- viewpoint from rls_regress_user2
+SET SESSION AUTHORIZATION rls_regress_user2;
+SELECT * FROM document WHERE f_leak(dtitle);
+SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+
+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 row-level security
+ALTER TABLE document SET ROW SECURITY FOR ALL TO (true); -- fail
+ALTER TABLE document RESET ROW SECURITY FOR ALL; -- fail
+
+SET SESSION AUTHORIZATION rls_regress_user0;
+ALTER TABLE document SET ROW SECURITY FOR ALL TO (dauthor = current_user);
+
+-- viewpoint from rls_regress_user1 again
+SET SESSION AUTHORIZATION rls_regress_user1;
+SELECT * FROM document WHERE f_leak(dtitle);
+SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+
+-- viewpoint from rls_regress_user2 again
+SET SESSION AUTHORIZATION rls_regress_user2;
+SELECT * FROM document WHERE f_leak(dtitle);
+SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+
+EXPLAIN (costs off) SELECT * FROM document WHERE f_leak(dtitle);
+EXPLAIN (costs off) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle);
+
+-- interaction of FK/PK constraints
+SET SESSION AUTHORIZATION rls_regress_user0;
+ALTER TABLE category SET ROW SECURITY FOR ALL
+ TO (CASE WHEN current_user = 'rls_regress_user1' THEN cid IN (11, 33)
+ WHEN current_user = 'rls_regress_user2' THEN cid IN (22, 44)
+ ELSE false END);
+
+-- cannot delete PK referenced by invisible FK
+SET SESSION AUTHORIZATION rls_regress_user1;
+SELECT * FROM document d full outer join category c on d.cid = c.cid;
+DELETE FROM category WHERE cid = 33; -- fails with FK violation
+
+-- cannot insert FK referencing invisible PK
+SET SESSION AUTHORIZATION rls_regress_user2;
+SELECT * FROM document d full outer join category c on d.cid = c.cid;
+INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge'); -- fail with FK violation
+
+-- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
+SET SESSION AUTHORIZATION rls_regress_user1;
+INSERT INTO document VALUES ( 8, 44, 1, 'rls_regress_user_1', 'my third manga' ); -- Must fail with unique violation, revealing presence of did we can't see
+SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
+
+-- Nonsense results produced when RLS-exempt superuser tries to insert into
+-- table with FK owned by RLS-affected user. Should not be possible for this
+-- to fail except by PK conflict, but fails with FK constraint error. FIXME.
+RESET SESSION AUTHORIZATION;
+INSERT INTO document(did,cid,dlevel,dauthor,dtitle)
+SELECT 1000, cid, dlevel, dauthor, dtitle from document where did = 8;
+
+-- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row
+SET SESSION AUTHORIZATION rls_regress_user1;
+INSERT INTO document VALUES ( 8, 44, 1, 'rls_regress_user_1', 'my third manga' ); -- Must fail with unique violation, revealing presence of did we can't see
+SELECT * FROM document WHERE did = 8; -- and confirm we can't see it
+
+-- database superuser can bypass RLS policy
+RESET SESSION AUTHORIZATION;
+SELECT * FROM document;
+SELECT * FROM category;
+
+
+--
+-- Table inheritance and RLS policy
+--
+SET SESSION AUTHORIZATION rls_regress_user0;
+
+CREATE TABLE t1 (a int, junk1 text, b text) WITH OIDS;
+ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor
+GRANT ALL ON t1 TO public;
+
+COPY t1 FROM stdin WITH (oids);
+101 1 aaa
+102 2 bbb
+103 3 ccc
+104 4 ddd
+\.
+
+CREATE TABLE t2 (c float) INHERITS (t1);
+COPY t2 FROM stdin WITH (oids);
+201 1 abc 1.1
+202 2 bcd 2.2
+203 3 cde 3.3
+204 4 def 4.4
+\.
+
+CREATE TABLE t3 (c text, b text, a int) WITH OIDS;
+ALTER TABLE t3 INHERIT t1;
+COPY t3(a,b,c) FROM stdin WITH (oids);
+301 1 xxx X
+302 2 yyy Y
+303 3 zzz Z
+\.
+
+ALTER TABLE t1 SET ROW SECURITY FOR ALL TO (a % 2 = 0); -- be even number
+ALTER TABLE t2 SET ROW SECURITY FOR ALL TO (a % 2 = 1); -- be odd number
+
+SELECT * FROM t1;
+EXPLAIN (costs off) SELECT * FROM t1;
+
+SELECT * FROM t1 WHERE f_leak(b);
+EXPLAIN (costs off) SELECT * FROM t1 WHERE f_leak(b);
+
+-- reference to system column
+SELECT oid, * FROM t1;
+EXPLAIN (costs off) SELECT * FROM t1;
+
+-- reference to whole-row reference
+SELECT *,t1 FROM t1;
+EXPLAIN (costs off) SELECT *,t1 FROM t1;
+
+-- for share/update lock
+SELECT * FROM t1 FOR SHARE;
+EXPLAIN (costs off) SELECT * FROM t1 FOR SHARE;
+
+SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
+EXPLAIN (costs off) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE;
+
+--
+-- COPY TO statement
+--
+COPY t1 TO stdout;
+COPY t1 TO stdout WITH OIDS;
+COPY t2(c,b) TO stdout WITH OIDS;
+COPY (SELECT * FROM t1) TO stdout;
+COPY document TO stdout WITH OIDS; -- failed (no oid column)
+
+--
+-- recursive RLS and VIEWs in policy
+--
+CREATE TABLE s1 (a int, b text);
+INSERT INTO s1 (SELECT x, md5(x::text) FROM generate_series(-10,10) x);
+
+CREATE TABLE s2 (x int, y text);
+INSERT INTO s2 (SELECT x, md5(x::text) FROM generate_series(-6,6) x);
+CREATE VIEW v2 AS SELECT * FROM s2 WHERE y like '%af%';
+
+ALTER TABLE s1 SET ROW SECURITY FOR ALL
+ TO (a in (select x from s2 where y like '%2f%'));
+
+ALTER TABLE s2 SET ROW SECURITY FOR ALL
+ TO (x in (select a from s1 where b like '%22%'));
+
+SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion)
+
+ALTER TABLE s2 SET ROW SECURITY FOR ALL TO (x % 2 = 0);
+
+SELECT * FROM s1 WHERE f_leak(b); -- OK
+EXPLAIN SELECT * FROM only s1 WHERE f_leak(b);
+
+ALTER TABLE s1 SET ROW SECURITY FOR ALL
+ TO (a in (select x from v2)); -- using VIEW in RLS policy
+SELECT * FROM s1 WHERE f_leak(b); -- OK
+EXPLAIN (COSTS OFF) SELECT * FROM s1 WHERE f_leak(b);
+
+SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
+EXPLAIN (COSTS OFF) SELECT (SELECT x FROM s1 LIMIT 1) xx, * FROM s2 WHERE y like '%28%';
+
+ALTER TABLE s2 SET ROW SECURITY FOR ALL
+ TO (x in (select a from s1 where b like '%d2%'));
+SELECT * FROM s1 WHERE f_leak(b); -- fail (infinite recursion via view)
+
+-- prepared statement with rls_regress_user0 privilege
+PREPARE p1(int) AS SELECT * FROM t1 WHERE a <= $1;
+EXECUTE p1(2);
+EXPLAIN (costs off) EXECUTE p1(2);
+
+-- superuser is allowed to bypass RLS checks
+RESET SESSION AUTHORIZATION;
+SELECT * FROM t1 WHERE f_leak(b);
+EXPLAIN (costs off) SELECT * FROM t1 WHERE f_leak(b);
+
+-- plan cache should be invalidated
+EXECUTE p1(2);
+EXPLAIN (costs off) EXECUTE p1(2);
+
+PREPARE p2(int) AS SELECT * FROM t1 WHERE a = $1;
+EXECUTE p2(2);
+EXPLAIN (costs off) EXECUTE p2(2);
+
+-- also, case when privilege switch from superuser
+SET SESSION AUTHORIZATION rls_regress_user0;
+EXECUTE p2(2);
+EXPLAIN (costs off) EXECUTE p2(2);
+
+--
+-- UPDATE / DELETE and Row-level security
+--
+SET SESSION AUTHORIZATION rls_regress_user0;
+EXPLAIN (costs off) UPDATE t1 SET b = b || b WHERE f_leak(b);
+UPDATE t1 SET b = b || b WHERE f_leak(b);
+
+EXPLAIN (costs off) UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
+UPDATE only t1 SET b = b || '_updt' WHERE f_leak(b);
+
+-- returning clause with system column
+UPDATE only t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
+UPDATE t1 SET b = b WHERE f_leak(b) RETURNING *;
+UPDATE t1 SET b = b WHERE f_leak(b) RETURNING oid, *, t1;
+
+RESET SESSION AUTHORIZATION;
+SELECT * FROM t1;
+
+SET SESSION AUTHORIZATION rls_regress_user0;
+EXPLAIN (costs off) DELETE FROM only t1 WHERE f_leak(b);
+EXPLAIN (costs off) DELETE FROM t1 WHERE f_leak(b);
+
+DELETE FROM only t1 WHERE f_leak(b) RETURNING oid, *, t1;
+DELETE FROM t1 WHERE f_leak(b) RETURNING oid, *, t1;
+
+
+----------------------------------------------------------------------
+-- Check refcursors returned from PL/PgSQL SECURITY DEFINER functions
+
+RESET SESSION AUTHORIZATION;
+
+CREATE OR REPLACE FUNCTION return_refcursor_assuper() RETURNS refcursor AS $$
+DECLARE
+ curs1 refcursor;
+BEGIN
+ curs1 = 'super_cursor';
+ OPEN curs1 FOR SELECT * FROM document;
+ RETURN curs1;
+END;
+$$
+LANGUAGE plpgsql
+SECURITY DEFINER;
+
+-- Run the function entirely as rls_regress_user1
+SET SESSION AUTHORIZATION rls_regress_user1;
+BEGIN;
+SELECT return_refcursor_assuper();
+-- This fetch should return the full results, even though we are now
+-- running as a user with much lower access according to the current
+-- RLS policy.
+FETCH ALL FROM "super_cursor";
+-- But this should still return the usual result set
+SELECT * FROM document;
+ROLLBACK;
+
+-- Do the same check where we return a refcursor from one RLS-affected
+-- user to another RLS-affected user.
+
+SET SESSION AUTHORIZATION rls_regress_user2;
+
+CREATE OR REPLACE FUNCTION return_refcursor_asuser2() RETURNS refcursor AS $$
+DECLARE
+ curs1 refcursor;
+BEGIN
+ curs1 = 'user2_cursor';
+ OPEN curs1 FOR SELECT * FROM document;
+ RETURN curs1;
+END;
+$$
+LANGUAGE plpgsql
+SECURITY DEFINER;
+
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user1;
+SELECT return_refcursor_asuser2();
+-- Even though we're user1, we should see only user2's results from this.
+-- This FAILS, returning user1's results.
+FETCH ALL FROM "user2_cursor";
+-- but user1's results for this
+SELECT * FROM document;
+ROLLBACK;
+
+-- Now as the superuser, see if the SECURITY DEFINER on an RLS-affected
+-- user filters the rows the superuser sees. It should, for consistency.
+
+BEGIN;
+RESET SESSION AUTHORIZATION;
+SELECT return_refcursor_asuser2();
+-- Should see user2's results, but FAILS, instead returning an empty result set (!)
+FETCH ALL FROM "user2_cursor";
+-- Should see superuser's results
+SELECT * FROM document;
+ROLLBACK;
+
+--------------------------------------------------------------------
+-- Tests of DECLARE and FETCH cursors during privilege
+-- transitions.
+--
+
+-- Declare as user1, switch to user2, fetch all. Returns results as user2;
+-- should be user1. FIXME.
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user1;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document;
+SET SESSION AUTHORIZATION rls_regress_user2;
+FETCH ALL FROM curs;
+ROLLBACK;
+
+-- If we add an ORDER BY clause on a non-indexed column to force a sort,
+-- still returns rows for user2 because execution didn't start. FIXME.
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user1;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document ORDER BY cid;
+SET SESSION AUTHORIZATION rls_regress_user2;
+FETCH ALL FROM curs;
+ROLLBACK;
+
+-- If we add a single row FETCH before switching to force materialization
+-- though, suddenly we see rows for user1. This is correct, just inconsistent.
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user1;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document ORDER BY cid;
+FETCH 1 FROM curs;
+SET SESSION AUTHORIZATION rls_regress_user2;
+FETCH ALL FROM curs;
+ROLLBACK;
+
+-- Remove the ORDER BY, and we get rows for user2 again, because the result set
+-- isn't materialized anymore. FIXME.
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user1;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document;
+FETCH 1 FROM curs;
+SET SESSION AUTHORIZATION rls_regress_user2;
+FETCH ALL FROM curs;
+ROLLBACK;
+
+
+-- Perform similar tests with superuser.
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user1;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document;
+RESET SESSION AUTHORIZATION;
+-- Should return user1 rows, returns none instead (FIXME)
+FETCH ALL FROM curs;
+ROLLBACK;
+
+BEGIN;
+SET SESSION AUTHORIZATION rls_regress_user2;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document;
+FETCH 2 FROM curs;
+RESET SESSION AUTHORIZATION;
+-- Should see user2's rows; instead sees none due to username check change (FIXME)
+FETCH ALL FROM curs;
+ROLLBACK;
+
+BEGIN;
+RESET SESSION AUTHORIZATION;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document;
+SET SESSION AUTHORIZATION rls_regress_user1;
+-- Should return all rows and does so because it's planned as superuser before switch so rls qual not added
+FETCH ALL FROM curs;
+ROLLBACK;
+
+BEGIN;
+RESET SESSION AUTHORIZATION;
+DECLARE curs CURSOR FOR SELECT * FROM rls_regress_schema.document;
+FETCH 3 FROM curs;
+SET SESSION AUTHORIZATION rls_regress_user2;
+-- Still returns all rows because it's planned as superuser before switch, so rls qual not added
+FETCH ALL FROM curs;
+ROLLBACK;
+
+----------------------------------------------------------------------
+-- Test psql \dt+ command
+--
+ALTER TABLE category RESET ROW SECURITY FOR ALL; -- too long qual
+\dt+
+
+----------------------------------------------------------------------
+-- Clean up objects
+--
+RESET SESSION AUTHORIZATION;
+
+DROP SCHEMA rls_regress_schema CASCADE;
+
+DROP USER rls_regress_user0;
+DROP USER rls_regress_user1;
+DROP USER rls_regress_user2;
--
1.8.3.1
Re: Row-security writer-side checks proposal
On Fri, Nov 1, 2013 at 3:52 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
I've been looking some more into write-side checks in row-security and
have a suggestion.Even though write-side checks are actually fairly separate to read
checks, and can be done as another step, I'd like to think about them
before the catalog format and syntax are settled. I think we need fields
for write operations in pg_rowsecurity and the syntax to set them so
that the catalog information can be used by triggers to enforce write
checks. Even if, for the first cut, they're not supported by built-in
auto-created triggers.Here's my proposal, let me know what you think:
SET ROW SECURITY FOR { ALL COMMANDS | {[SELECT,INSERT,UPDATE,DELETE}+}
in other words, you specify either:
SET ROW SECURITY FOR ALL COMMANDS
I continue to think that this syntax is misguided. For SELECT and
DELETE there is only read-side security, and for INSERT there is only
write-side security, so that's OK as far as it goes, but for UPDATE
both read-side security and write-side security are possible, and
there ought to be a way to get one without the other. This syntax
won't support that cleanly.
I wonder whether it's worth thinking about the relationship between
the write-side security contemplated for this feature iand the WITH
CHECK OPTION syntax that we have for auto-updateable views, which
serves more or less the same purpose. I'm not sure that syntax is any
great shakes, but it's existing precedent of some form and could
perhaps at least be looked at as a source of inspiration.
I would generally expect that most people would want either "read side
security for all commands" or "read and write side security for all
commands". I think whatever syntax we come up with this feature ought
to make each of those things straightforward to get.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Row-security writer-side checks proposal
On 11/04/2013 09:55 PM, Robert Haas wrote:
I continue to think that this syntax is misguided. For SELECT and
DELETE there is only read-side security, and for INSERT there is only
write-side security, so that's OK as far as it goes, but for UPDATE
both read-side security and write-side security are possible, and
there ought to be a way to get one without the other. This syntax
won't support that cleanly.
That's what I was thinking earlier too - separate "FOR READ" and "FOR
WRITE" instead.
The reason I came back to insert/update/delete was that it's entirely
reasonable to want to prohibit deletes but permit updates to the same
tuple. Both are writes; both set xmax, it's just that one _replaces_ the
tuple, the other doesn't.
So really, there are four cases:
READ
WRITE INSERT
WRITE UPDATE
WRITE DELETE
I wonder whether it's worth thinking about the relationship between
the write-side security contemplated for this feature iand the WITH
CHECK OPTION syntax that we have for auto-updateable views, which
serves more or less the same purpose. I'm not sure that syntax is any
great shakes, but it's existing precedent of some form and could
perhaps at least be looked at as a source of inspiration.
I've been thinking about the overlap with WITH CHECK OPTION as well.
I would generally expect that most people would want either "read side
security for all commands" or "read and write side security for all
commands". I think whatever syntax we come up with this feature ought
to make each of those things straightforward to get.
but sometimes with different predicates for read and write, i.e. you can
see rows you can't modify or can insert rows / update rows that you
can't see after the change.
Similarly, saying you can update but not delete seems quite reasonable
to me.
On the other hand, we might choose to say "if you want to do things with
that granularity use your own triggers to enforce it" and provide only
READ and WRITE for RLS.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Row-security writer-side checks proposal
On Mon, Nov 4, 2013 at 8:00 PM, Craig Ringer <craig@2ndquadrant.com> wrote:
On 11/04/2013 09:55 PM, Robert Haas wrote:
I continue to think that this syntax is misguided. For SELECT and
DELETE there is only read-side security, and for INSERT there is only
write-side security, so that's OK as far as it goes, but for UPDATE
both read-side security and write-side security are possible, and
there ought to be a way to get one without the other. This syntax
won't support that cleanly.That's what I was thinking earlier too - separate "FOR READ" and "FOR
WRITE" instead.The reason I came back to insert/update/delete was that it's entirely
reasonable to want to prohibit deletes but permit updates to the same
tuple. Both are writes; both set xmax, it's just that one _replaces_ the
tuple, the other doesn't.So really, there are four cases:
READ
WRITE INSERT
WRITE UPDATE
WRITE DELETE
Isn't READ similarly divisible into READ SELECT, READ UPDATE, and READ DELETE?
I would generally expect that most people would want either "read side
security for all commands" or "read and write side security for all
commands". I think whatever syntax we come up with this feature ought
to make each of those things straightforward to get.but sometimes with different predicates for read and write, i.e. you can
see rows you can't modify or can insert rows / update rows that you
can't see after the change.
Yes, that's possible.
Similarly, saying you can update but not delete seems quite reasonable
to me.
If you simply want to allow UPDATE but not DELETE, you can refrain
from granting the table-level privilege. The situation in which you
need things separate is when you want to allow both UPDATE and DELETE
but with different RLS quals for each.
On the other hand, we might choose to say "if you want to do things with
that granularity use your own triggers to enforce it" and provide only
READ and WRITE for RLS.
The funny thing about this whole feature is that it's just syntax
support for doing things that you can already do in other ways. If
you want read-side security, create a security_barrier view and select
from that instead of hitting the table directly. If you want
write-side security, enforce it using triggers. So essentially what
this is, I think, is an attempt to invent nicer syntax around
something that we already have, and provide a one-stop-shopping
experience rather than a roll-your-own experience for people who want
row-level security.
Now maybe that's fine. But given that, I think it's pretty important
that we get the syntax right. Because if you're adding a feature
primarily to add a more convenient syntax, then the syntax had better
actually be convenient.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Row-security writer-side checks proposal
* Robert Haas (robertmhaas@gmail.com) wrote:
Now maybe that's fine. But given that, I think it's pretty important
that we get the syntax right. Because if you're adding a feature
primarily to add a more convenient syntax, then the syntax had better
actually be convenient.
I agree that we want to get the syntax correct, but also very clear as
it's security related and we don't want anyone surprised by what happens
when they use it. The idea, as has been discussed in the past, is to
then allow tying RLS in with SELinux and provide MAC.
Thanks,
Stephen
Re: Row-security writer-side checks proposal
On Tue, Nov 5, 2013 at 9:01 AM, Stephen Frost <sfrost@snowman.net> wrote:
* Robert Haas (robertmhaas@gmail.com) wrote:
Now maybe that's fine. But given that, I think it's pretty important
that we get the syntax right. Because if you're adding a feature
primarily to add a more convenient syntax, then the syntax had better
actually be convenient.I agree that we want to get the syntax correct, but also very clear as
it's security related and we don't want anyone surprised by what happens
when they use it. The idea, as has been discussed in the past, is to
then allow tying RLS in with SELinux and provide MAC.
No argument. I think "convenient" and "unsurprising" are closely-aligned goals.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Row-security writer-side checks proposal
On 11/05/2013 09:30 PM, Robert Haas wrote:
So really, there are four cases:
READ
WRITE INSERT
WRITE UPDATE
WRITE DELETEIsn't READ similarly divisible into READ SELECT, READ UPDATE, and READ DELETE?
Not in my opinion. No matter what the command, the read side is all
about having some way to obtain the contents of the tuple.
Separate "READ DELETE" etc would only be interesting if we wanted to let
someone DELETE rows they cannot SELECT. Since we have DELETE ...
RETURNING, and since users can write a predicate function for DELETE
that leaks the information even if we didn't, in practice if you give
the user any READ right you've given them all of them. So I don't think
we can support that (except maybe by column RLS down the track).
By contrast on the write side it seems routine to need different rules
for different operations. The traditional heriachical mandatory access
model as implemented by Teradata Row Level Security, Oracle Label Based
Security, etc, can have different rules for each operation. Here's a
synopsis of the example described in the Teradata docs as a typical policy:
- SELECT: Current session security label must be >= the row label
- INSERT: Current session security label must be = the new row label
- UPDATE: Session label must be >= row label to update the row.
New row must be = session security label.
- DELETE: Only permitted for rows with the lowest label set, ensuring
row is reviewed and declassified before deletion.
Except for the DELETE, this is actually just two policies, one for reads
(session label => row label) and one for writes (session label = new row
label). So this might be an acceptable constraint if necessary, but it'd
be really good to support per-command rules, and we certainly need
asymmetric read- and write- rules.
I'm looking into use cases and existing examples to put this in a more
concerete context, as much of the RLS discussion has been a hypothetical
one that doesn't look at concrete user problems much.
Similarly, saying you can update but not delete seems quite reasonable
to me.If you simply want to allow UPDATE but not DELETE, you can refrain
from granting the table-level privilege. The situation in which you
need things separate is when you want to allow both UPDATE and DELETE
but with different RLS quals for each.
That's what I was getting at, yes. Like the example above; the set of
rows you can update might be different to the set of rows you can delete.
On the other hand, we might choose to say "if you want to do things with
that granularity use your own triggers to enforce it" and provide only
READ and WRITE for RLS.The funny thing about this whole feature is that it's just syntax
support for doing things that you can already do in other ways. If
you want read-side security, create a security_barrier view and select
from that instead of hitting the table directly. If you want
write-side security, enforce it using triggers.
Right now you can't have both together, though; an UPDATE on the raw
table can observe rows that wouldn't be visible via the view and can
send them to the client via RAISE NOTICE or whatever.
Support for automatically updatable security barrier views would take
care of this issue, at which point I'd agree: RLS becomes mostly
cosmetic syntactical sugar over existing capabilities. FKs would ignore
RLS, much like the would if you use explicit SECURITY BARRIER views and
have FKs between the base tables.
One big difference still remains though: when you add an RLS policy on a
table, all procedures and views referring to that table automatically
use the transparent security barrier view over the table instead. That's
*not* the case when you use views manually; you have to re-create views
that point to the table so they instead point to a security barrier view
over the table. Again it's nothing you can't do with updatable security
barrier views, but it's automatic and transparent with RLS.
Now maybe that's fine. But given that, I think it's pretty important
that we get the syntax right. Because if you're adding a feature
primarily to add a more convenient syntax, then the syntax had better
actually be convenient.
I completely agree with that. I don't have a strong opinion on the
current syntax.
I've looked at how some other vendors do it, and I can't say their
approaches are pretty.
Oracle VPD has you create a PL/SQL procedure to generate the SQL text of
the desired RLS predicate. It then wants you to create a POLICY (via a
PL/SQL call to a built-in package) that associates the predicate
generation function with a table and sets some options controlling what
statement types it applies to, when the predicate is re-generated, etc.
It also has policy groups, which bundle policies together and control
whether or not they're applied for a given session. The predicates of
different policies are ANDed together.
So to create a single RLS policy on a single table you have to write a
PL/SQL stored procedure that generates an SQL predicate and then create
a RLS policy to apply that procedure to the table.
ALTER TABLE ... SET ROW SECURITY is the equivalent of adding the policy;
Pg RLS doesn't have an equivalent of the predicate generating function,
instead only supporting static predicates. (There might be patent issues
around using a predicate-generator function; I haven't looked, but think
it was mentioned in earlier discussion).
Teradata instead eschews general-purpose row level security. So it's not
really within the scope of the current RLS feature, comparing instead to
whatever we build on top of it (using SEPostgreSQL or otherwise). It
implements label based security directly, with two kinds of labels. You
can create a CONSTRAINT with either a single hirachical compartment
(think "secret", "top secret", etc) or a CONSTRAINT with set of discrete
and isolated security compartment labels. The CONSTRAINT has functions
written in C associated with it to do enforcement, one for each
statement type. The C functions must not execute SQL. A CONSTRAINT can
be assigned to tables and to users in an m:n manner. A user can have
multiple constraints, as can a table. They're ANDed. A user with
non-heirachical "country" constraint set to "uk" can only see rows with
country label "uk"; a user with heirachical "classification" constraint
set to "unclassified (default), classified" can see rows labeled
"unclassified" and, if they elevate their session, "classified", but
cannot see higher rows.
Both of these have a concept that Pg RLS doesn't seem to have: multiple
RLS policies. I think that's actually quite important to consider,
because we'll need that anyway to support RLS on a subset of columns.
Both also have the concept of turning particular RLS policies on and off
on a per-user basis or per-session using privileged on-login triggers,
so that application A and application B can apply different RLS rules on
the same data.
I don't think it's important to cover these from the start, but it'd be
a good idea not to foreclose these possibilities in whatever gets into Pg.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Row-security writer-side checks proposal
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 11/05/2013 10:01 PM, Stephen Frost wrote:
* Robert Haas (robertmhaas@gmail.com) wrote:
Now maybe that's fine. But given that, I think it's pretty
important that we get the syntax right. Because if you're adding
a feature primarily to add a more convenient syntax, then the
syntax had better actually be convenient.I agree that we want to get the syntax correct, but also very clear
as it's security related and we don't want anyone surprised by what
happens when they use it. The idea, as has been discussed in the
past, is to then allow tying RLS in with SELinux and provide MAC.
That was my impression also.
To help get closer to that point, since you were involved in the work
on auto-updatable views: any hints on what might be needed to tackle
making security barrier views updatable?
There's a fun little wrinkle with MAC, by the way: functional indexes.
We can't allow the creation of a functional index, even by the table
owner, if it uses any non-LEAKPROOF operators and functions. Otherwise
the user can write a function to leak the rows, then create an index
using that function.
That's not a problem for the current phase of RLS because the table
owner is allowed to remove the RLS constraint directly. They can also
add triggers that might leak rows via CASCADEs, etc. When MAC comes
into the picture we'll need to impose limits on triggers and
functional indexes added to rows.
- --
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.15 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/
iQEcBAEBAgAGBQJSefGAAAoJELBXNkqjr+S2W6EH+wc3fM3GGoYjnietLfGiiFmA
4ea7sIcio9kdDap3dNpgnMW2NfEHu/OLxSptFGBjl3w4RfA1KSQaKcwupjmanPGa
har7MylI4SKDRHB5LWZEgYrK1A3n/PTJUap3DFGhLJxAdCMM3AtQfcyHBoj/LXfZ
9o9KkpXfzFW2e4yuPR714rZMzfAgO+Jyij9WkcayNASw/0jnCuhCdBtg8mKU6mhz
lC4KA0WGxXqCGDdKxPwVRSJTMoT8kBeUBf4lznSEeGspxCHb4GafMCFvhHarQ9WU
+aBY1mw3ELFXqfPurLC5RZVQGYsygWfzrREJ+oHUJ3khgPR2djj0EAemK3lwO6M=
=HYU7
-----END PGP SIGNATURE-------
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Row-security writer-side checks proposal
On Wed, Nov 6, 2013 at 2:27 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
Separate "READ DELETE" etc would only be interesting if we wanted to let
someone DELETE rows they cannot SELECT. Since we have DELETE ...
RETURNING, and since users can write a predicate function for DELETE
that leaks the information even if we didn't, in practice if you give
the user any READ right you've given them all of them. So I don't think
we can support that (except maybe by column RLS down the track).
Well, we could require SELECT privilege when a a RETURNING clause is present...
Except for the DELETE, this is actually just two policies, one for reads
(session label => row label) and one for writes (session label = new row
label). So this might be an acceptable constraint if necessary, but it'd
be really good to support per-command rules, and we certainly need
asymmetric read- and write- rules.
OK.
Support for automatically updatable security barrier views would take
care of this issue, at which point I'd agree: RLS becomes mostly
cosmetic syntactical sugar over existing capabilities. FKs would ignore
RLS, much like the would if you use explicit SECURITY BARRIER views and
have FKs between the base tables.One big difference still remains though: when you add an RLS policy on a
table, all procedures and views referring to that table automatically
use the transparent security barrier view over the table instead. That's
*not* the case when you use views manually; you have to re-create views
that point to the table so they instead point to a security barrier view
over the table. Again it's nothing you can't do with updatable security
barrier views, but it's automatic and transparent with RLS.
That's true, but it's that automatic transparent part that also
introduces a lot of pain, because what do you do when you need to
really get at the real data (e.g. to back it up)? The ad-hoc rule
"superusers are exempt" solves the problem at one level, but it
doesn't do a lot for e.g. database owners.
I've looked at how some other vendors do it, and I can't say their
approaches are pretty.
Did you look at Trusted RUBIX?
Both of these have a concept that Pg RLS doesn't seem to have: multiple
RLS policies. I think that's actually quite important to consider,
because we'll need that anyway to support RLS on a subset of columns.
Both also have the concept of turning particular RLS policies on and off
on a per-user basis or per-session using privileged on-login triggers,
so that application A and application B can apply different RLS rules on
the same data.I don't think it's important to cover these from the start, but it'd be
a good idea not to foreclose these possibilities in whatever gets into Pg.
I agree, and I'm not sure we're there yet. Frankly, switching from a
single security policy per table to multiple policies per table
doesn't sound like a good candidate for a follow-on commit; it's
likely to have fundamental ramifications for the syntax, and I'm not
eager to see us implement one syntax now only to overhaul it in the
next release.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Row-security writer-side checks proposal
On 11/08/2013 11:03 PM, Robert Haas wrote:
Separate "READ DELETE" etc would only be interesting if we wanted to let
someone DELETE rows they cannot SELECT. Since we have DELETE ...
RETURNING, and since users can write a predicate function for DELETE
that leaks the information even if we didn't, in practice if you give
the user any READ right you've given them all of them. So I don't think
we can support that (except maybe by column RLS down the track).Well, we could require SELECT privilege when a a RETURNING clause is present...
Absolutely could. Wouldn't stop them grabbing the data via a predicate
function on the update/delete, though, and we can't sanely (IMO) require
SELECT rights if they want to use non-LEAKPROOF functions/operators either.
I do think this needs looking at further, but I suspect it's an area
where Pg's flexibility will make life harder.
--
Craig Ringer http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: Row-security writer-side checks proposal
On Sat, Nov 9, 2013 at 10:01 AM, Craig Ringer <craig@2ndquadrant.com> wrote:
On 11/08/2013 11:03 PM, Robert Haas wrote:
Separate "READ DELETE" etc would only be interesting if we wanted to let
someone DELETE rows they cannot SELECT. Since we have DELETE ...
RETURNING, and since users can write a predicate function for DELETE
that leaks the information even if we didn't, in practice if you give
the user any READ right you've given them all of them. So I don't think
we can support that (except maybe by column RLS down the track).Well, we could require SELECT privilege when a a RETURNING clause is present...
Absolutely could. Wouldn't stop them grabbing the data via a predicate
function on the update/delete, though, and we can't sanely (IMO) require
SELECT rights if they want to use non-LEAKPROOF functions/operators either.
Hmm, good point.
--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers