pg.ddx.io pgsql-hackers@postgresql.org mailing list archive help / color / mirror / Atom feed From: Craig Ringer <craig@2ndquadrant.com>
To: Gregory Smith <gregsmithpgsql@gmail.com>
To: Greg Stark <stark@mit.edu>
To: Kohei KaiGai <kaigai@kaigai.gr.jp>
To: PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Subject: Re: [v9.4] row level security
Date: Fri, 24 Jan 2014 10:12:20 +0800
Message-ID: <52E1CC04.8040907@2ndquadrant.com> (raw )
In-Reply-To: <52DCBEF1.3010004@2ndquadrant.com >
References: <CADyhKSWkHrPqbRsFK0LjPMOOTsbKGWOuN75GqYJrxCDO3CKWtA@mail.gmail.com >
<5220D832.7090104@agliodbs.com >
<20130830192729.GO2706@tamriel.snowman.net >
<16798.1377891794@sss.pgh.pa.us >
<CA+TgmoZYv_me1iB6iDRA_oO-o9xkxXYqr-0UtwK5itpZbDsqYA@mail.gmail.com >
<1398.1378305956@sss.pgh.pa.us >
<CA+TgmoYTf+4RUOW8stVz1RE+3TD+QR5TNKzYuFDCehSkieL5OQ@mail.gmail.com >
<1543.1378306249@sss.pgh.pa.us >
<2620.1378308159@sss.pgh.pa.us >
<52775604.9090306@2ndquadrant.com >
<11101.1383575839@sss.pgh.pa.us >
<CA+TgmoZS46fGTHCMo1FpNiBYDU7Wrx7R8PL+48Xc7UcVCsuaUA@mail.gmail.com >
<52784DF9.9040205@2ndquadrant.com >
<CA+TgmobvM9t8y6+BoG0DXMVA5jcNc9V6fzNt2tNSaVqsRmGpHA@mail.gmail.com >
<5279E3CA.7050400@2ndquadrant.com >
<CAM-w4HOJaWt2naKakW67KRZE9mBcEF-BQ3UA=7SHKcFv3=FmVQ@mail.gmail.com >
<527C478D.4070901@2ndquadrant.com >
<52ABCF6C.4010409@gmail.com >
<52ABE150.6050201@2ndquadrant.com >
<52D98436.2030508@gmail.com >
<52DC82E2.5010801@2ndquadrant.com >
<52DCBEF1.3010004@2ndquadrant.com >
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-hackers>
(Re-sending; I forgot to cc the list)
On 01/20/2014 02:15 PM, Craig Ringer wrote:
> On 01/20/2014 09:58 AM, Craig Ringer wrote:
>> As it is I'm spending today reworking the RLS patch on top of the new
>> approach to updatable security barrier views.
>
> To get that rolling I've split the RLS patch up into chunks, so we can
> argue about the catalogs, ALTER syntax, and the actual row-filtering
> implementation separately ;-)
>
> It's currently on git@github.com:ringerc/postgres.git in the branch
> rls-9.4-split, which is subject to rebasing. I'm still going through it
> making sure each chunk at least compiles and preferably passes "make
> check".
That branch is now pretty stable, and passes checks at every stage up to
the new RLS regression tests. I've pushed a new version to branch
rls-9.4-split. Further updates will rebase this branch.
The tag rls-9.4-split-v5 identifies this particular push, and won't get
rebased away.
I found a number of merge errors and problems that I've corrected and
incorporated into the history, including a bad pg_class.h edit, a
missing break; in tablecmds.c, a missing Node* assignment in the parser
grammar for RESET ROW SECURITY, etc.
A number of concerns remain outstanding, including those Greg listed
except for the regression tests, which I've fixed, and rebasing on top
of current master.
-Continue to expand the functional tests
-Is there enough information about row security available in psql
output? For example, there's nothing in "\d" output that suggests it
might exist. pg_rowsecurity is a monster to try and read.
-Documentation needs plenty of editing, which I can take care of.
Additionally, the issue I found earlier remains outstanding:
- Fails to deal with user-id change during portal execution in (eg)
SECURITY DEFINER funcs returning refcursor, or SQL cursors open
across SET SESSION AUTHORIZATION. `current_user` returns value
after change, not original value, causing issues with RLS predicates.
Now that the patch is split up into stages I'm going to work on
replacing patches 7 and 8 (query plan transformation, COPY support) with
an approach based on Dean's updaable security barriers code.
If anyone else picks up work on this, **please** try to keep this patch
series intact, or even better, move the regression tests back into the
relevant patches as you go. If you send an updated version of the RLS
patch please send a git ref too; that'll make it much easier to pull
your changes and integrate them into the relevant patch in the series
with appropriate "git rebase --interactive" (ab)use.
--
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
Attachments:
[text/x-patch] 0001-RLS-Make-plan-caching-dependent-on-user-ID.patch (5.3K, ../52E1CC04.8040907@2ndquadrant.com/2-0001-RLS-Make-plan-caching-dependent-on-user-ID.patch)
download | inline diff:
From 5d8145dd0c47fb891139568f82ac87bc2403cfec Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@2ndquadrant.com>
Date: Mon, 20 Jan 2014 13:44:45 +0800
Subject: [PATCH 01/10] RLS: Make plan caching dependent on user ID.
---
src/backend/optimizer/plan/planner.c | 2 ++
src/backend/utils/cache/plancache.c | 32 ++++++++++++++++++++++++++++++++
src/include/nodes/plannodes.h | 2 ++
src/include/nodes/relation.h | 2 ++
src/include/utils/plancache.h | 2 ++
5 files changed, 40 insertions(+)
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 35bda67..8c12eb9 100644
--- a/src/backend/optimizer/plan/planner.c
+++ b/src/backend/optimizer/plan/planner.c
@@ -177,6 +177,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 +255,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;
}
diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c
index d492cbb..77de2df 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/include/nodes/plannodes.h b/src/include/nodes/plannodes.h
index 38c039c..35c88cc 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 8aa40d0..74d0e12 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -99,6 +99,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 */
diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h
index b8ca643..ff89700 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 */
--
1.8.3.1
[text/x-patch] 0002-RLS-add-pg_rowsecurity-catalog.patch (26.3K, ../52E1CC04.8040907@2ndquadrant.com/3-0002-RLS-add-pg_rowsecurity-catalog.patch)
download | inline diff:
From ad60419074deef9f3d057e395ff08375afd9eeaa Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@2ndquadrant.com>
Date: Mon, 20 Jan 2014 13:07:50 +0800
Subject: [PATCH 02/10] RLS: add pg_rowsecurity catalog
---
doc/src/sgml/catalogs.sgml | 65 +++++++
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 | 287 +++++++++++++++++++++++++++++
src/backend/utils/cache/relcache.c | 17 +-
src/include/catalog/dependency.h | 1 +
src/include/catalog/indexing.h | 5 +
src/include/catalog/pg_class.h | 25 +--
src/include/catalog/pg_rowsecurity.h | 74 ++++++++
src/include/parser/parse_node.h | 3 +-
src/include/utils/rel.h | 2 +
src/test/regress/expected/sanity_check.out | 1 +
14 files changed, 528 insertions(+), 15 deletions(-)
create mode 100644 src/backend/catalog/pg_rowsecurity.c
create mode 100644 src/include/catalog/pg_rowsecurity.h
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 2230c93..1ca088a 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_rowsecuritylevelsec</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>
@@ -1876,6 +1881,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>
@@ -5157,6 +5172,56 @@
</sect1>
+ <sect1 id="catalog-pg-rowsecurity">
+ <title><structname>pg_rowsecurity</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 table this row-security is for</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 performed as row-security policy</entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ <note>
+ <para>
+ <literal>pg_class.relhasrowsecurity</literal>
+ must be true if a table has row-security policy in this catalog.
+ </para>
+ </note>
+ </sect1>
<sect1 id="catalog-pg-seclabel">
<title><structname>pg_seclabel</structname></title>
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 e511669..8f95b14 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);
@@ -2316,6 +2321,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 2cf4bc0..5be195b 100644
--- a/src/backend/catalog/heap.c
+++ b/src/backend/catalog/heap.c
@@ -799,6 +799,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_relreplident - 1] = CharGetDatum(rd_rel->relreplident);
diff --git a/src/backend/catalog/objectaddress.c b/src/backend/catalog/objectaddress.c
index 991c02a..870e477 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..7ee4e88
--- /dev/null
+++ b/src/backend/catalog/pg_rowsecurity.c
@@ -0,0 +1,287 @@
+/* -------------------------------------------------------------------------
+ *
+ * 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);
+}
diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c
index 2a46cfc..8ee5353 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
*/
@@ -1840,6 +1846,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);
@@ -3166,7 +3174,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);
@@ -4478,6 +4492,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/include/catalog/dependency.h b/src/include/catalog/dependency.h
index 8948589..de9323e 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 0515b75..4f46c5d 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, 5001, on pg_rowsecurity using btree(oid oid_ops));
+#define RowSecurityOidIndexId 5001
+DECLARE_UNIQUE_INDEX(pg_rowsecurity_relid_index, 5002, on pg_rowsecurity using btree(rsecrelid oid_ops, rseccmd char_ops));
+#define RowSecurityRelidIndexId 5002
+
/* 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 f2fb317..bbbf62d 100644
--- a/src/include/catalog/pg_class.h
+++ b/src/include/catalog/pg_class.h
@@ -65,6 +65,7 @@ CATALOG(pg_class,1259) BKI_BOOTSTRAP BKI_ROWTYPE_OID(83) BKI_SCHEMA_MACRO
bool relhasrules; /* has (or has had) any rules */
bool relhastriggers; /* has (or has had) any TRIGGERs */
bool relhassubclass; /* has (or has had) derived classes */
+ bool relhasrowsecurity; /* has (or has had) row-security policy */
bool relispopulated; /* matview currently holds query results */
char relreplident; /* see REPLICA_IDENTITY_xxx constants */
TransactionId relfrozenxid; /* all Xids < this are frozen in this rel */
@@ -94,7 +95,7 @@ typedef FormData_pg_class *Form_pg_class;
* ----------------
*/
-#define Natts_pg_class 29
+#define Natts_pg_class 30
#define Anum_pg_class_relname 1
#define Anum_pg_class_relnamespace 2
#define Anum_pg_class_reltype 3
@@ -118,12 +119,13 @@ typedef FormData_pg_class *Form_pg_class;
#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_relreplident 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
+#define Anum_pg_class_relhasrowsecurity 24
+#define Anum_pg_class_relispopulated 25
+#define Anum_pg_class_relreplident 26
+#define Anum_pg_class_relfrozenxid 27
+#define Anum_pg_class_relminmxid 28
+#define Anum_pg_class_relacl 29
+#define Anum_pg_class_reloptions 30
/* ----------------
* initial contents of pg_class
@@ -138,13 +140,14 @@ 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 n 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 n 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 n 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 n 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 n 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 n 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 29 0 t f f f f t n 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 30 0 t f f f f f t n 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..10aed9a
--- /dev/null
+++ b/src/include/catalog/pg_rowsecurity.h
@@ -0,0 +1,74 @@
+/*
+ * 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 5000
+
+CATALOG(pg_rowsecurity,5000)
+{
+ /* 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 RemoveRowSecurityById(Oid relationId);
+
+#endif /* PG_ROWSECURITY_H */
diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h
index 85598e8..9c7c382 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/utils/rel.h b/src/include/utils/rel.h
index 9b8a4c9..83a8d03 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"
@@ -110,6 +111,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 */
/*
* The index chosen as the relation's replication identity or
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
--
1.8.3.1
[text/x-patch] 0003-RLS-Add-rowsec_relid-to-ResultRelInfo.patch (4.4K, ../52E1CC04.8040907@2ndquadrant.com/4-0003-RLS-Add-rowsec_relid-to-ResultRelInfo.patch)
download | inline diff:
From 76847f695ce425117783e722dc49d22b1464ef64 Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@2ndquadrant.com>
Date: Mon, 20 Jan 2014 13:28:12 +0800
Subject: [PATCH 03/10] RLS: Add rowsec_relid to ResultRelInfo
This hunk may still be needed in the new approach to filter out rows that've
changed to no longer be visible due to user action r action of triggers;
we'd need to filter the result relation.
---
src/backend/nodes/copyfuncs.c | 1 +
src/backend/nodes/equalfuncs.c | 1 +
src/backend/nodes/outfuncs.c | 1 +
src/backend/nodes/readfuncs.c | 1 +
src/include/nodes/execnodes.h | 4 ++++
src/include/nodes/parsenodes.h | 8 +++++++-
6 files changed, 15 insertions(+), 1 deletion(-)
diff --git a/src/backend/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c
index bb356d0..52bd8f5 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1977,6 +1977,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(functions);
diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c
index 5908d9a..b9b0de2 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -2269,6 +2269,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(functions);
diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c
index 568c3b8..cfa4cb3 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -2376,6 +2376,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 c22acd5..335579f 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -1218,6 +1218,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/include/nodes/execnodes.h b/src/include/nodes/execnodes.h
index a301a08..83637eb 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/parsenodes.h b/src/include/nodes/parsenodes.h
index 846c31a..48c6d0f 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 */
@@ -741,6 +742,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):
--
1.8.3.1
[text/x-patch] 0004-Add-ALTER-TABLE-commands-for-row-security.patch (12.7K, ../52E1CC04.8040907@2ndquadrant.com/5-0004-Add-ALTER-TABLE-commands-for-row-security.patch)
download | inline diff:
From 8a2a621c72464f06f0633b019f62d5dd13f8a10b Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@2ndquadrant.com>
Date: Mon, 20 Jan 2014 13:37:47 +0800
Subject: [PATCH 04/10] Add ALTER TABLE commands for row security
---
doc/src/sgml/ref/alter_table.sgml | 38 +++++++++++++++++++++++++++
src/backend/catalog/pg_rowsecurity.c | 50 ++++++++++++++++++++++++++++++++++++
src/backend/commands/event_trigger.c | 1 +
src/backend/commands/tablecmds.c | 27 +++++++++++++++++++
src/backend/parser/gram.y | 25 ++++++++++++++++++
src/backend/parser/parse_agg.c | 6 +++++
src/backend/parser/parse_expr.c | 3 +++
src/include/catalog/pg_rowsecurity.h | 2 ++
src/include/nodes/parsenodes.h | 2 ++
9 files changed, 154 insertions(+)
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 89649a2..79f37d8 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -69,6 +69,8 @@ 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>
REPLICA IDENTITY {DEFAULT | USING INDEX <replaceable class="PARAMETER">index_name</replaceable> | FULL | NOTHING}
<phrase>and <replaceable class="PARAMETER">table_constraint_using_index</replaceable> is:</phrase>
@@ -76,6 +78,8 @@ ALTER TABLE [ IF EXISTS ] <replaceable class="PARAMETER">name</replaceable>
[ 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>
@@ -581,6 +585,26 @@ 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.
<term><literal>REPLICA IDENTITY</literal></term>
<listitem>
<para>
@@ -840,6 +864,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/src/backend/catalog/pg_rowsecurity.c b/src/backend/catalog/pg_rowsecurity.c
index 7ee4e88..34d33e8 100644
--- a/src/backend/catalog/pg_rowsecurity.c
+++ b/src/backend/catalog/pg_rowsecurity.c
@@ -285,3 +285,53 @@ RemoveRowSecurityById(Oid rowsecId)
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/event_trigger.c b/src/backend/commands/event_trigger.c
index 59f0842..51a1c0f 100644
--- a/src/backend/commands/event_trigger.c
+++ b/src/backend/commands/event_trigger.c
@@ -995,6 +995,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 26a4613..f02ce72 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"
@@ -2789,6 +2790,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;
@@ -3164,6 +3167,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 */
@@ -3449,6 +3454,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_ReplicaIdentity:
ATExecReplicaIdentity(rel, (ReplicaIdentityStmt *) cmd->def, lockmode);
break;
@@ -7814,6 +7825,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/parser/gram.y b/src/backend/parser/gram.y
index 12a6beb..ba1ac81 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -260,6 +260,7 @@ static Node *makeRecursiveViewSelect(char *relname, List *aliases, Node *query);
%type <node> alter_table_cmd alter_type_cmd opt_collate_clause
replica_identity
%type <list> alter_table_cmds alter_type_cmds
+%type <str> row_security_cmd
%type <dbehavior> opt_drop_behavior
@@ -2191,6 +2192,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 TABLE <name> REPLICA IDENTITY */
| REPLICA IDENTITY_P replica_identity
{
@@ -2301,6 +2320,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 9613e2a..333b557 100644
--- a/src/backend/parser/parse_agg.c
+++ b/src/backend/parser/parse_agg.c
@@ -333,6 +333,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
@@ -662,6 +665,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 81c9338..e289e77 100644
--- a/src/backend/parser/parse_expr.c
+++ b/src/backend/parser/parse_expr.c
@@ -1462,6 +1462,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:
@@ -2643,6 +2644,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/include/catalog/pg_rowsecurity.h b/src/include/catalog/pg_rowsecurity.h
index 10aed9a..798d556 100644
--- a/src/include/catalog/pg_rowsecurity.h
+++ b/src/include/catalog/pg_rowsecurity.h
@@ -69,6 +69,8 @@ typedef struct
} 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/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 48c6d0f..3a918bb 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -1322,6 +1322,8 @@ typedef enum AlterTableType
AT_AddOf, /* OF <type_name> */
AT_DropOf, /* NOT OF */
AT_ReplicaIdentity, /* REPLICA IDENTITY */
+ AT_SetRowSecurity, /* SET ROW SECURITY (...) */
+ AT_ResetRowSecurity, /* RESET ROW SECURITY */
AT_GenericOptions /* OPTIONS (...) */
} AlterTableType;
--
1.8.3.1
[text/x-patch] 0005-RLS-pg_dump-support-for-dumping-rowsecurity-catalogs.patch (13.9K, ../52E1CC04.8040907@2ndquadrant.com/6-0005-RLS-pg_dump-support-for-dumping-rowsecurity-catalogs.patch)
download | inline diff:
From bb8455bcf9dbaf5ef9cdf2898efcfde6eb6edecf Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@2ndquadrant.com>
Date: Mon, 20 Jan 2014 13:45:56 +0800
Subject: [PATCH 05/10] RLS: pg_dump support for dumping rowsecurity catalogs
---
src/bin/pg_dump/common.c | 4 +
src/bin/pg_dump/pg_backup_archiver.c | 1 +
src/bin/pg_dump/pg_dump.c | 151 ++++++++++++++++++++++++++++++++++-
src/bin/pg_dump/pg_dump.h | 13 ++-
src/bin/pg_dump/pg_dump_sort.c | 5 ++
5 files changed, 169 insertions(+), 5 deletions(-)
diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c
index 4d35ae5..fd9e6cd 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 7fc0288..6502d93 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -3137,6 +3137,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 3862f05..6939852 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);
@@ -2714,6 +2715,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,
@@ -4242,6 +4371,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;
@@ -4293,10 +4423,7 @@ getTables(Archive *fout, int *numTables)
"(%s c.relowner) AS rolname, "
"c.relchecks, c.relhastriggers, "
"c.relhasindex, c.relhasrules, c.relhasoids, "
- "c.relfrozenxid, tc.oid AS toid, "
- "tc.relfrozenxid AS tfrozenxid, "
- "c.relpersistence, c.relispopulated, "
- "c.relreplident, c.relpages, "
+ "c.relhasrowsecurity, "
"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, "
@@ -4332,6 +4459,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, "
@@ -4371,6 +4499,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, "
@@ -4408,6 +4537,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, "
@@ -4444,6 +4574,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, "
@@ -4480,6 +4611,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, "
@@ -4516,6 +4648,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, "
@@ -4552,6 +4685,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, "
@@ -4584,6 +4718,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, "
@@ -4611,6 +4746,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, "
@@ -4648,6 +4784,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, "
@@ -4695,6 +4832,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");
@@ -4744,6 +4882,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].relreplident = *(PQgetvalue(res, i, i_relreplident));
@@ -7861,6 +8000,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 */
@@ -15147,6 +15289,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 c8dac35..de10c95 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
@@ -245,6 +246,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 */
@@ -484,6 +486,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 */
@@ -575,5 +585,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 bda3535..718007d 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)",
--
1.8.3.1
[text/x-patch] 0006-RLS-psql-support-for-reporting-row-security-constrai.patch (1.4K, ../52E1CC04.8040907@2ndquadrant.com/7-0006-RLS-psql-support-for-reporting-row-security-constrai.patch)
download | inline diff:
From db96cb2be9c1d71d7b5f7f3fe21fd636b0e7a9fb Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@2ndquadrant.com>
Date: Mon, 20 Jan 2014 13:45:24 +0800
Subject: [PATCH 06/10] RLS: psql support for reporting row security
constraints
---
src/bin/psql/describe.c | 7 +++++++
1 file changed, 7 insertions(+)
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index 0d4b151..e99b72a 100644
--- a/src/bin/psql/describe.c
+++ b/src/bin/psql/describe.c
@@ -2764,6 +2764,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"));
}
appendPQExpBufferStr(&buf,
@@ -2773,6 +2777,9 @@ listTables(const char *tabtypes, const char *pattern, bool verbose, bool showSys
appendPQExpBufferStr(&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");
appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN (");
if (showTables)
--
1.8.3.1
[text/x-patch] 0007-RLS-Enforce-row-security-by-transforming-query-plans.patch (45.6K, ../52E1CC04.8040907@2ndquadrant.com/8-0007-RLS-Enforce-row-security-by-transforming-query-plans.patch)
download | inline diff:
From a88e62608515b82eccc78d005e4f838ceb8b51e3 Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@2ndquadrant.com>
Date: Mon, 20 Jan 2014 13:49:56 +0800
Subject: [PATCH 07/10] RLS: Enforce row-security by transforming query plans
---
src/backend/executor/execMain.c | 27 +-
src/backend/nodes/copyfuncs.c | 2 +
src/backend/nodes/equalfuncs.c | 2 +
src/backend/nodes/nodeFuncs.c | 12 +-
src/backend/nodes/outfuncs.c | 2 +
src/backend/nodes/readfuncs.c | 1 +
src/backend/optimizer/plan/planner.c | 21 +-
src/backend/optimizer/prep/preptlist.c | 68 ++-
src/backend/optimizer/prep/prepunion.c | 95 +++-
src/backend/optimizer/util/Makefile | 3 +-
src/backend/optimizer/util/rowsecurity.c | 744 +++++++++++++++++++++++++++++++
src/backend/rewrite/rewriteHandler.c | 16 +
src/backend/utils/adt/ri_triggers.c | 13 +-
src/include/miscadmin.h | 1 +
src/include/nodes/nodeFuncs.h | 1 +
src/include/nodes/parsenodes.h | 4 +-
src/include/nodes/relation.h | 4 +
src/include/optimizer/rowsecurity.h | 27 ++
src/include/rewrite/rewriteHandler.h | 1 +
19 files changed, 1016 insertions(+), 28 deletions(-)
create mode 100644 src/backend/optimizer/util/rowsecurity.c
create mode 100644 src/include/optimizer/rowsecurity.h
diff --git a/src/backend/executor/execMain.c b/src/backend/executor/execMain.c
index 6b5f198..0041c23 100644
--- a/src/backend/executor/execMain.c
+++ b/src/backend/executor/execMain.c
@@ -789,8 +789,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 */
@@ -803,27 +804,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 52bd8f5..3a8b60c 100644
--- a/src/backend/nodes/copyfuncs.c
+++ b/src/backend/nodes/copyfuncs.c
@@ -1936,6 +1936,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);
@@ -2467,6 +2468,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 b9b0de2..cb2abf6 100644
--- a/src/backend/nodes/equalfuncs.c
+++ b/src/backend/nodes/equalfuncs.c
@@ -814,6 +814,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);
@@ -849,6 +850,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);
diff --git a/src/backend/nodes/nodeFuncs.c b/src/backend/nodes/nodeFuncs.c
index 123f2a6..fa8f040 100644
--- a/src/backend/nodes/nodeFuncs.c
+++ b/src/backend/nodes/nodeFuncs.c
@@ -1948,8 +1948,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))
@@ -2686,7 +2689,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 cfa4cb3..500faeb 100644
--- a/src/backend/nodes/outfuncs.c
+++ b/src/backend/nodes/outfuncs.c
@@ -1925,6 +1925,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);
@@ -2241,6 +2242,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);
diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c
index 335579f..884f45c 100644
--- a/src/backend/nodes/readfuncs.c
+++ b/src/backend/nodes/readfuncs.c
@@ -201,6 +201,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);
diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c
index 8c12eb9..63f0ec2 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"
@@ -406,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.
@@ -898,6 +912,8 @@ inheritance_planner(PlannerInfo *root)
ChangeVarNodes((Node *) subroot.parse, rti, newrti, 0);
ChangeVarNodes((Node *) subroot.rowMarks, rti, newrti, 0);
ChangeVarNodes((Node *) subroot.append_rel_list, rti, newrti, 0);
+ if (subroot.parse->sourceRelation == rti)
+ subroot.parse->sourceRelation = newrti;
rte = copyObject(rte);
subroot.parse->rtable = lappend(subroot.parse->rtable,
rte);
@@ -960,7 +976,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 ee773b8..5e4a010 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 52dcc72..9bd97e7 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 c54d0a6..d42a285 100644
--- a/src/backend/optimizer/util/Makefile
+++ b/src/backend/optimizer/util/Makefile
@@ -13,6 +13,7 @@ top_builddir = ../../../..
include $(top_builddir)/src/Makefile.global
OBJS = clauses.o joininfo.o orclauses.o pathnode.o placeholder.o \
- plancat.o predtest.o relnode.o restrictinfo.o tlist.o var.o
+ plancat.o predtest.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/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c
index 0b13645..2f56211 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 1e1e616..68be040 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/include/miscadmin.h b/src/include/miscadmin.h
index f133e5f..1c97bd9 100644
--- a/src/include/miscadmin.h
+++ b/src/include/miscadmin.h
@@ -272,6 +272,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/nodeFuncs.h b/src/include/nodes/nodeFuncs.h
index f9afdb6..9ed3796 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 3a918bb..c8ce32a 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -113,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 */
diff --git a/src/include/nodes/relation.h b/src/include/nodes/relation.h
index 74d0e12..252e45f 100644
--- a/src/include/nodes/relation.h
+++ b/src/include/nodes/relation.h
@@ -1438,6 +1438,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/rewrite/rewriteHandler.h b/src/include/rewrite/rewriteHandler.h
index e4027bd..7bd334c 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);
--
1.8.3.1
[text/x-patch] 0008-RLS-Enforce-row-security-on-COPY.patch (4.8K, ../52E1CC04.8040907@2ndquadrant.com/9-0008-RLS-Enforce-row-security-on-COPY.patch)
download | inline diff:
From a599392b76b77aae5610b5654120f08ff19a6a92 Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@2ndquadrant.com>
Date: Mon, 20 Jan 2014 13:50:23 +0800
Subject: [PATCH 08/10] RLS: Enforce row-security on COPY
---
src/backend/commands/copy.c | 90 ++++++++++++++++++++++++++++++++++++++++++++-
1 file changed, 88 insertions(+), 2 deletions(-)
diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c
index f91f164..8e98fd2 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 &&
--
1.8.3.1
[text/x-patch] 0009-RLS-Regression-tests.patch (43.2K, ../52E1CC04.8040907@2ndquadrant.com/10-0009-RLS-Regression-tests.patch)
download | inline diff:
From 3afd241dd3a81b64c42a86fbc8a87f997e080397 Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@2ndquadrant.com>
Date: Mon, 20 Jan 2014 12:58:29 +0800
Subject: [PATCH 09/10] RLS: Regression tests
---
src/test/regress/expected/rowsecurity.out | 952 ++++++++++++++++++++++++++++++
src/test/regress/parallel_schedule | 2 +-
src/test/regress/serial_schedule | 1 +
src/test/regress/sql/rowsecurity.sql | 298 ++++++++++
4 files changed, 1252 insertions(+), 1 deletion(-)
create mode 100644 src/test/regress/expected/rowsecurity.out
create mode 100644 src/test/regress/sql/rowsecurity.sql
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
new file mode 100644
index 0000000..44b2888
--- /dev/null
+++ b/src/test/regress/expected/rowsecurity.out
@@ -0,0 +1,952 @@
+--
+-- 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; -- failed
+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'); -- failed
+ERROR: insert or update on table "document" violates foreign key constraint "document_cid_fkey"
+DETAIL: Key (cid)=(33) is not present in table "category".
+-- 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)
+ Group Key: s2.x
+ -> 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)
+(12 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
+ Group Key: s2.x
+ -> Subquery Scan on s2
+ Filter: (s2.y ~~ '%af%'::text)
+ -> Seq Scan on s2 s2_1
+ Filter: ((x % 2) = 0)
+(12 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)
+
+--
+-- Test psql \dt+ command
+--
+ALTER TABLE category RESET ROW SECURITY FOR ALL; -- too long qual
+\dt+
+ List of relations
+ Schema | Name | Type | Owner | Size | Description | Row-security
+--------------------+----------+-------+-------------------+------------+-------------+----------------------------------
+ rls_regress_schema | category | table | rls_regress_user0 | 16 kB | |
+ 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 10 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 USER rls_regress_user0;
+DROP USER rls_regress_user1;
+DROP USER rls_regress_user2;
diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule
index 5758b07..f7963fc 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 replica_identity
+test: privileges rowsecurity security_label collate matview lock replica_identity
# ----------
# Another group of parallel tests
diff --git a/src/test/regress/serial_schedule b/src/test/regress/serial_schedule
index 78348f5..51c4c12 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..55d4aad
--- /dev/null
+++ b/src/test/regress/sql/rowsecurity.sql
@@ -0,0 +1,298 @@
+--
+-- 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; -- failed
+
+-- 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'); -- failed
+
+-- 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;
+
+--
+-- 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
[text/x-patch] 0010-RLS-Supplimental-docs.patch (7.0K, ../52E1CC04.8040907@2ndquadrant.com/11-0010-RLS-Supplimental-docs.patch)
download | inline diff:
From bc5a54a13efff6b8deaa433f51578410681fed9d Mon Sep 17 00:00:00 2001
From: Craig Ringer <craig@2ndquadrant.com>
Date: Mon, 20 Jan 2014 13:50:37 +0800
Subject: [PATCH 10/10] RLS: Supplimental docs
---
doc/src/sgml/user-manag.sgml | 145 +++++++++++++++++++++++++++++++++++++++++++
1 file changed, 145 insertions(+)
diff --git a/doc/src/sgml/user-manag.sgml b/doc/src/sgml/user-manag.sgml
index 177ac7a..c764189 100644
--- a/doc/src/sgml/user-manag.sgml
+++ b/doc/src/sgml/user-manag.sgml
@@ -439,4 +439,149 @@ DROP ROLE <replaceable>name</replaceable>;
</para>
</sect1>
+ <sect1 id="row-security">
+ <title>Row-security</title>
+ <para>
+ <productname>PostgreSQL</productname> v9.4 or later provides
+ row-security feature, like several commercial database
+ management system. It allows table owner to assign a particular
+ condition that performs as a security policy of the table; only
+ rows that satisfies the condition should be visible, except for
+ a case when superuser runs queries.
+ </para>
+ <para>
+ Row-security policy can be set using <literal>SET ROW SECURITY</literal>
+ command of <xref linkend="SQL-ALTERTABLE"> statement, as an expression
+ form that returns a value of type boolean. This expression can contain
+ references to columns of the relation, so it enables to construct
+ arbitrary rule to make access control decision based on contents of
+ each rows.
+ </para>
+ <para>
+ For example, the following <literal>customer</literal> table has
+ <literal>uname</literal> field to store user name, and it assume
+ we don't want to expose any properties of other customers.
+ The following command set <literal>current_user = uname</literal>
+ as 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>
+ <xref linkend="SQL-EXPLAIN"> command shows how row-security policy
+ works on the supplied query.
+<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 means the preconfigured row-security policy is
+ implicitly added, and scan plan on the target relation being wrapped up
+ with a sub-query.
+ It ensures user given qualifiers, including functions with side effects,
+ are never executed earlier than the row-security policy regardless of
+ its cost, except for the cases when these were fully leakproof.
+ This design helps to tackle the scenario described in
+ <xref linkend="RULES-PRIVILEGES">; that introduces the order to evaluate
+ qualifiers is significant to keep confidentiality of invisible rows.
+ </para>
+
+ <para>
+ On the other hand, this design allows superusers to bypass checks with
+ row-security.
+ It ensures <application>pg_dump</application> can obtain a complete set
+ of database backup, and avoid to execute Trojan horse trap, being injected
+ as a row-security policy of user-defined table, with privileges of
+ superuser.
+ </para>
+
+ <para>
+ In case of queries on inherited tables, row-security policy of the parent
+ relation is not applied to child relations. Scope of the row-security
+ policy is limited to the relation on which it is set.
+<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 table <literal>t2</literal> and <literal>t3</literal>,
+ and row-security policy is set on only <literal>t1</literal>,
+ and <literal>t3</literal>, not <literal>t2</literal>.
+
+ The row-security policy of <literal>t1</literal>, <literal>x</literal>
+ must be even-number, is appended only <literal>t1</literal>,
+ neither <literal>t2</literal> nor <literal>t3</literal>.
+ On the contrary, <literal>t3</literal> has different row-security policy;
+ <literal>x</literal> must be odd-number.
+ </para>
+
+ <para>
+ Row-security feature also works to queries for writer-operations; such as
+ <xref linkend="SQL-INSERT">, <xref linkend="SQL-UPDATE"> or
+ <xref linkend="SQL-DELETE"> commands.
+ It ensures all the modified rows satisfies configured row-security policy.
+ The below query tries to update e-mail address of the
+ <literal>customer</> table, and configured row-security makes sure all the
+ modified rows's <literal>uname</> field has to match with
+ <literal>current_user</>.
+ </para>
+<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>
+ <para>
+ Modification of a certain table is consist of two different stuffs; one
+ is fetch rows to be modified from the result relation (except for
+ <literal>INSERT</> command), second is insertion of rows to the
+ result relation. Usually, before-row trigger or check constraints are
+ run in the second phase. In addition, row-security policy is also checked
+ on this stage, to prevent to insert or update rows with unprivileged
+ values.
+ </para>
+
+ <para>
+ Unlike other commercial database systems, we don't have any plan to allow
+ individual row-security policy for each command type. Even if we want to
+ perform with difference policy between <xref linkend="SQL-SELECT"> and
+ <xref linkend="SQL-DELETE">, <literal>RETURNING</> clause can leak the rows
+ to be invisible using <xref linkend="SQL-DELETE"> command.
+ </para>
+
+ <para>
+ Even though it is not a specific matter in row-security, please be careful
+ if you plan to use <literal>current_user</> in row-level security policy.
+ <xref linkend="SQL-DECLARE"> and <xref linkend="SQL-FETCH"> allows to
+ switch current user identifier during execution of the query.
+ Thus, it can fetch the first 100 rows with privilege of <literal>alice</>,
+ then remaining rows with privilege of <literal>bob</>. If and when query
+ execution plan contains some kind of materialization and row-security
+ policy contains <literal>current_user</>, the fetched tuples in
+ <literal>bob</>'s screen might be evaluated according to the privilege of
+ <literal>alice</>.
+ </para>
+ </sect1>
</chapter>
--
1.8.3.1
view thread (165+ messages) latest in thread
Message-ID: <52E1CC04.8040907@2ndquadrant.com>
Permalink: ../52E1CC04.8040907@2ndquadrant.com/
Also on: postgresql.org/message-id/52E1CC04.8040907@2ndquadrant.com
copy link · copy postgr.es · copy git am
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: craig@2ndquadrant.com, gregsmithpgsql@gmail.com, stark@mit.edu, kaigai@kaigai.gr.jp
Subject: Re: [v9.4] row level security
In-Reply-To: <52E1CC04.8040907@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox