diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml new file mode 100644 index c174e67..a26be35 100644 *** a/doc/src/sgml/catalogs.sgml --- b/doc/src/sgml/catalogs.sgml *************** *** 239,244 **** --- 239,249 ---- + pg_rowsecuritylevelsec + row-level security policy of relation + + + pg_seclabel security labels on database objects *************** *** 1924,1929 **** --- 1929,1944 ---- + relhasrowsecurity + bool + + + True if table has row-security policy; see + pg_rowsecurity catalog + + + + relhassubclass bool *************** *** 5324,5329 **** --- 5339,5395 ---- + + <structname>pg_rowsecurity</structname> + + + pg_rowsecurity + + + The catalog pg_rowsecurity stores the expression + tree which is generated and used to enforce row-level security on a particular + relation. + + + <structname>pg_rowsecurity</structname> Columns + + + + Name + Type + References + Description + + + + + rsecrelid + oid + pg_class.oid + The relation that the row-level security expression tree applies to. + + + rseccmd + char + + The command this row-security is for. Currently, only 'a' (meaning 'all') is supported. + + + rsecqual + pg_node_tree + + The expression tree that will be applied to any queries referencing this relation. + + + +
+ + + pg_class.relhasrowsecurity + This will be true if a relation has an entry in this catalog, used to determine if RLS needs to be applied. + + + <structname>pg_seclabel</structname> diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml new file mode 100644 index 0b08f83..9b11183 100644 *** a/doc/src/sgml/ref/alter_table.sgml --- b/doc/src/sgml/ref/alter_table.sgml *************** ALTER TABLE [ IF EXISTS ] new_owner SET TABLESPACE new_tablespace + SET ROW SECURITY FOR rowsec_command TO (condition) + RESET ROW SECURITY FOR rowsec_command REPLICA IDENTITY {DEFAULT | USING INDEX index_name | FULL | NOTHING} and table_constraint_using_index is: *************** ALTER TABLE [ IF EXISTS ] constraint_name ] { UNIQUE | PRIMARY KEY } USING INDEX index_name [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] + + and rowsec_command is: + { ALL | SELECT | INSERT | UPDATE | DELETE } + *************** ALTER TABLE [ IF EXISTS ] + + SET ROW SECURITY FOR rowsec_command TO (condition) + + + This form sets a row-level security policy for the table. + Only rows where condition returns true + will be visible to a given query. + Currently, the only the command type supported is ALL. + See also . + + + + + + RESET ROW SECURITY FOR rowsec_command + + + This form resets (clears) the row-level security policy for the table. + ALL is the only supported command type right now. + + + + REPLICA IDENTITY *************** ALTER TABLE [ IF EXISTS ] + + + condition + + + An expression that returns a value of type boolean. Only rows where + the condition return true will be fetched, updated, or deleted. Roles + marked as 'superuser' will not have the condition applied. + The condition can reference columns of the relation and include sub-queries, + provided those sub-queries do not reference the relation recursively. + + + diff --git a/src/backend/catalog/Makefile b/src/backend/catalog/Makefile new file mode 100644 index a974bd5..beb73df 100644 *** a/src/backend/catalog/Makefile --- b/src/backend/catalog/Makefile *************** OBJS = catalog.o dependency.o heap.o ind *** 15,21 **** 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 BKIFILES = postgres.bki postgres.description postgres.shdescription --- 15,21 ---- 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_rowsecurity.o pg_type.o storage.o toasting.o BKIFILES = postgres.bki postgres.description postgres.shdescription *************** POSTGRES_BKI_SRCS = $(addprefix $(top_sr *** 39,45 **** 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_default_acl.h pg_seclabel.h pg_shseclabel.h pg_collation.h pg_range.h \ toasting.h indexing.h \ ) --- 39,45 ---- 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_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 new file mode 100644 index e511669..8f95b14 100644 *** a/src/backend/catalog/dependency.c --- b/src/backend/catalog/dependency.c *************** *** 45,50 **** --- 45,51 ---- #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" *************** doDeletion(const ObjectAddress *object, *** 1249,1254 **** --- 1250,1259 ---- RemoveEventTriggerById(object->objectId); break; + case OCLASS_ROWSECURITY: + RemoveRowSecurityById(object->objectId); + break; + default: elog(ERROR, "unrecognized object class: %u", object->classId); *************** getObjectClass(const ObjectAddress *obje *** 2316,2321 **** --- 2321,2329 ---- 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 new file mode 100644 index 2cf4bc0..5be195b 100644 *** a/src/backend/catalog/heap.c --- b/src/backend/catalog/heap.c *************** InsertPgClassTuple(Relation pg_class_des *** 799,804 **** --- 799,805 ---- 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 new file mode 100644 index ea81223..083284f 100644 *** a/src/backend/catalog/objectaddress.c --- b/src/backend/catalog/objectaddress.c *************** getObjectDescription(const ObjectAddress *** 2166,2171 **** --- 2166,2221 ---- 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, "cache lookup failed for row-security relation %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, "unrecognized 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 ...6f55654 . *** a/src/backend/catalog/pg_rowsecurity.c --- b/src/backend/catalog/pg_rowsecurity.c *************** *** 0 **** --- 1,351 ---- + /* ------------------------------------------------------------------------- + * + * 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 in + * 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(); + { + /* + * Loop through the row-level security entries for this relation, if + * any. While we currently only support one command type for row-level + * security, eventually we will support multiple types and we will + * need to find the correct one (or possibly merge them?). + */ + while (HeapTupleIsValid(tuple = systable_getnext(sscan))) + { + Datum value; + bool isnull; + char *temp; + + 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; + } + + /* + * Set up the memory context inside our loop to ensure we are only + * building it when we actually need it. + */ + if (!rsdesc) + { + rscxt = AllocSetContextCreate(CacheMemoryContext, + "Row-security descriptor", + ALLOCSET_SMALL_MINSIZE, + ALLOCSET_SMALL_INITSIZE, + ALLOCSET_SMALL_MAXSIZE); + rsdesc = MemoryContextAllocZero(rscxt, sizeof(RowSecurityDesc)); + rsdesc->rscxt = rscxt; + } + + 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 or update the row + * in 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); + + /* We will be adding them back below. */ + deleteDependencyRecordsFor(RowSecurityRelationId, rowsecId, false); + } + else + { + values[Anum_pg_rowsecurity_rsecrelid - 1] + = ObjectIdGetDatum(relationId); + values[Anum_pg_rowsecurity_rseccmd - 1] + = CharGetDatum(rseccmd); + values[Anum_pg_rowsecurity_rsecqual - 1] + = CStringGetTextDatum(nodeToString(qual)); + newtup = heap_form_tuple(RelationGetDescr(catalog), + values, isnull); + rowsecId = simple_heap_insert(catalog, newtup); + } + CatalogUpdateIndexes(catalog, newtup); + + heap_freetuple(newtup); + + /* records dependencies of row-security policy and relation/columns */ + target.classId = RelationRelationId; + target.objectId = relationId; + target.objectSubId = 0; + + myself.classId = RowSecurityRelationId; + myself.objectId = rowsecId; + myself.objectSubId = 0; + + recordDependencyOn(&myself, &target, DEPENDENCY_AUTO); + + recordDependencyOnExpr(&myself, qual, pstate->p_rtable, + DEPENDENCY_NORMAL); + free_parsestate(pstate); + + systable_endscan(sscan); + heap_close(catalog, RowExclusiveLock); + } + + /* + * Remove row-security policy row of pg_rowsecurity + */ + static void + DeletePolicyRow(Relation relation, char rseccmd) + { + Assert(rseccmd == ROWSECURITY_CMD_ALL); + + if (relation->rsdesc) + { + ObjectAddress address; + + address.classId = RowSecurityRelationId; + address.objectId = relation->rsdesc->rsall.rsecid; + address.objectSubId = 0; + + performDeletion(&address, DROP_RESTRICT, 0); + } + else + { + /* Nothing to do here */ + elog(INFO, "relation %s has no row-security policy, skipped", + RelationGetRelationName(relation)); + } + } + + /* + * Guts of row-security policy deletion. + */ + void + RemoveRowSecurityById(Oid rowsecId) + { + Relation catalog; + ScanKeyData skey; + SysScanDesc sscan; + HeapTuple tuple; + Relation rel; + Oid relid; + + catalog = heap_open(RowSecurityRelationId, RowExclusiveLock); + + ScanKeyInit(&skey, + ObjectIdAttributeNumber, + BTEqualStrategyNumber, F_OIDEQ, + ObjectIdGetDatum(rowsecId)); + sscan = systable_beginscan(catalog, RowSecurityOidIndexId, true, + NULL, 1, &skey); + tuple = systable_getnext(sscan); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "could not find tuple for row-security %u", rowsecId); + + /* + * Open and exclusive-lock the relation the row-security belongs to. + */ + relid = ((Form_pg_rowsecurity) GETSTRUCT(tuple))->rsecrelid; + + rel = heap_open(relid, AccessExclusiveLock); + + simple_heap_delete(catalog, &tuple->t_self); + + /* Ensure relcache entries of other session being rebuilt */ + CacheInvalidateRelcache(rel); + + heap_close(rel, NoLock); + + systable_endscan(sscan); + heap_close(catalog, RowExclusiveLock); + } + + /* + * ALTER TABLE SET ROW SECURITY (...) OR + * RESET ROW SECURITY + */ + void + ATExecSetRowSecurity(Relation relation, const char *cmdname, Node *clause) + { + Oid relid = RelationGetRelid(relation); + char rseccmd; + + if (strcmp(cmdname, "all") == 0) + rseccmd = ROWSECURITY_CMD_ALL; + else + ereport(ERROR, + (errcode(ERRCODE_FEATURE_NOT_SUPPORTED), + errmsg("Row-security for \"%s\" is not implemented yet", + cmdname))); + + if (clause != NULL) + { + InsertOrUpdatePolicyRow(relation, rseccmd, clause); + + /* + * Also, turn on relhasrowsecurity, if not. + */ + if (!RelationGetForm(relation)->relhasrowsecurity) + { + Relation class_rel = heap_open(RelationRelationId, + RowExclusiveLock); + HeapTuple tuple; + + tuple = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(relid)); + if (!HeapTupleIsValid(tuple)) + elog(ERROR, "cache lookup failed for relation %u", relid); + + ((Form_pg_class) GETSTRUCT(tuple))->relhasrowsecurity = true; + + simple_heap_update(class_rel, &tuple->t_self, tuple); + CatalogUpdateIndexes(class_rel, tuple); + + heap_freetuple(tuple); + heap_close(class_rel, RowExclusiveLock); + } + } + else + DeletePolicyRow(relation, rseccmd); + + CacheInvalidateRelcache(relation); + } diff --git a/src/backend/commands/copy.c b/src/backend/commands/copy.c new file mode 100644 index 70ee7e5..4568a47 100644 *** a/src/backend/commands/copy.c --- b/src/backend/commands/copy.c *************** *** 34,39 **** --- 34,40 ---- #include "libpq/pqformat.h" #include "mb/pg_wchar.h" #include "miscadmin.h" + #include "rewrite/rowsecurity.h" #include "optimizer/clauses.h" #include "optimizer/planner.h" #include "parser/parse_relation.h" *************** DoCopy(const CopyStmt *stmt, const char *** 809,814 **** --- 810,816 ---- RangeTblEntry *rte; List *attnums; ListCell *cur; + List *rowsecpolicy; Assert(!stmt->query); *************** DoCopy(const CopyStmt *stmt, const char *** 816,823 **** rel = heap_openrv(stmt->relation, (is_from ? RowExclusiveLock : AccessShareLock)); ! relid = RelationGetRelid(rel); rte = makeNode(RangeTblEntry); rte->rtekind = RTE_RELATION; rte->relid = RelationGetRelid(rel); --- 818,831 ---- rel = heap_openrv(stmt->relation, (is_from ? RowExclusiveLock : AccessShareLock)); ! /* Test for row-security policy. If there's any policy for this ! * relation, we don't permit COPY on it. */ ! rowsecpolicy = pull_row_security_policy(CMD_UTILITY, rel, NULL); ! if (rowsecpolicy != NIL) ! ereport(ERROR, (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE), ! errmsg("Cannot COPY a relation with a row-security policy as non-superuser"))); + relid = RelationGetRelid(rel); rte = makeNode(RangeTblEntry); rte->rtekind = RTE_RELATION; rte->relid = RelationGetRelid(rel); diff --git a/src/backend/commands/event_trigger.c b/src/backend/commands/event_trigger.c new file mode 100644 index 024a477..220e731 100644 *** a/src/backend/commands/event_trigger.c --- b/src/backend/commands/event_trigger.c *************** EventTriggerSupportsObjectClass(ObjectCl *** 995,1000 **** --- 995,1001 ---- 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 new file mode 100644 index f5ae98f..9569d64 100644 *** a/src/backend/commands/tablecmds.c --- b/src/backend/commands/tablecmds.c *************** *** 37,42 **** --- 37,43 ---- #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" *************** AlterTableGetLockLevel(List *cmds) *** 2807,2812 **** --- 2808,2822 ---- break; /* + * Changing row security changes the set of quals that a + * select could be built off of. + */ + case AT_SetRowSecurity: + case AT_ResetRowSecurity: + cmd_lockmode = AccessExclusiveLock; + break; + + /* * Subcommands that may be visible to concurrent SELECTs */ case AT_DropColumn: /* change visible to SELECT */ *************** ATPrepCmd(List **wqueue, Relation rel, A *** 3234,3239 **** --- 3244,3251 ---- 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 */ *************** ATExecCmd(List **wqueue, AlteredTableInf *** 3519,3524 **** --- 3531,3542 ---- 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; *************** ATExecAlterColumnType(AlteredTableInfo * *** 7889,7894 **** --- 7907,7928 ---- Assert(defaultexpr); break; + case OCLASS_ROWSECURITY: + /* + * Since a row-level security policy can reference a column + * of the table, we currently just punt on attempts to change + * the column type of a relation which has an RLS policy. + * See above comments regarding a similar situation with + * triggers (the OCLASS_TRIGGER case). FIXME someday. + */ + 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/nodes/copyfuncs.c b/src/backend/nodes/copyfuncs.c new file mode 100644 index 98ad910..8a3a121 100644 *** a/src/backend/nodes/copyfuncs.c --- b/src/backend/nodes/copyfuncs.c *************** _copyQuery(const Query *from) *** 2491,2496 **** --- 2491,2497 ---- COPY_NODE_FIELD(rowMarks); COPY_NODE_FIELD(setOperations); COPY_NODE_FIELD(constraintDeps); + COPY_SCALAR_FIELD(dependsUserId); return newnode; } diff --git a/src/backend/nodes/equalfuncs.c b/src/backend/nodes/equalfuncs.c new file mode 100644 index 9901d23..0c9637f 100644 *** a/src/backend/nodes/equalfuncs.c --- b/src/backend/nodes/equalfuncs.c *************** _equalQuery(const Query *a, const Query *** 872,877 **** --- 872,878 ---- COMPARE_NODE_FIELD(rowMarks); COMPARE_NODE_FIELD(setOperations); COMPARE_NODE_FIELD(constraintDeps); + COMPARE_SCALAR_FIELD(dependsUserId); return true; } diff --git a/src/backend/nodes/outfuncs.c b/src/backend/nodes/outfuncs.c new file mode 100644 index 10e8139..ce57004 100644 *** a/src/backend/nodes/outfuncs.c --- b/src/backend/nodes/outfuncs.c *************** _outQuery(StringInfo str, const Query *n *** 2264,2269 **** --- 2264,2270 ---- WRITE_NODE_FIELD(rowMarks); WRITE_NODE_FIELD(setOperations); WRITE_NODE_FIELD(constraintDeps); + WRITE_OID_FIELD(dependsUserId); } static void diff --git a/src/backend/nodes/readfuncs.c b/src/backend/nodes/readfuncs.c new file mode 100644 index ef1eae9..1161cb4 100644 *** a/src/backend/nodes/readfuncs.c --- b/src/backend/nodes/readfuncs.c *************** _readQuery(void) *** 224,229 **** --- 224,230 ---- READ_NODE_FIELD(rowMarks); READ_NODE_FIELD(setOperations); READ_NODE_FIELD(constraintDeps); + READ_OID_FIELD(dependsUserId); READ_DONE(); } diff --git a/src/backend/optimizer/plan/planner.c b/src/backend/optimizer/plan/planner.c new file mode 100644 index 0508d16..e1ccc31 100644 *** a/src/backend/optimizer/plan/planner.c --- b/src/backend/optimizer/plan/planner.c *************** *** 40,45 **** --- 40,46 ---- #include "rewrite/rewriteManip.h" #include "utils/rel.h" #include "utils/selfuncs.h" + #include "utils/syscache.h" /* GUC parameter */ *************** standard_planner(Query *parse, int curso *** 177,182 **** --- 178,184 ---- 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) *************** standard_planner(Query *parse, int curso *** 254,263 **** --- 256,305 ---- result->relationOids = glob->relationOids; result->invalItems = glob->invalItems; result->nParamExec = glob->nParamExec; + result->planUserId = glob->planUserId; return result; } + static void + create_userdep_planinval(Query* parse, PlannerGlobal *glob) + { + Oid dep = parse->dependsUserId; + Oid globdep = glob->planUserId; + + /* + * If the global dependency is already set and we have a dependency + * for this subtree, make sure it's the same as the one we found on + * this subtree. A plan can't depend on multiple user IDs. + */ + Assert( !OidIsValid(globdep) || !OidIsValid(dep) || (globdep == dep) ); + + /* + * If the global dependency isn't already set, and we have a + * user id dependency, copy the oid and create a plan inval + * item on pg_authid so that if the user id is updated this plan + * gets invalidated (say, due to the user becoming/ceasing to be + * superuser). + */ + if (!OidIsValid(globdep) && OidIsValid(dep)) + { + PlanInvalItem *pi = makeNode(PlanInvalItem); + + glob->planUserId = dep; + + pi->cacheId = AUTHOID; + pi->hashValue = GetSysCacheHashValue1(AUTHOID, + ObjectIdGetDatum(dep)); + glob->invalItems = lappend(glob->invalItems, pi); + } + + /* + * Otherwise we have nothing to do. Either the dependency + * has already been recognised and invalidation items created, + * or there isn't a dependency for this subnode. + */ + } + /*-------------------- * subquery_planner *************** subquery_planner(PlannerGlobal *glob, Qu *** 323,328 **** --- 365,377 ---- root->non_recursive_plan = NULL; /* + * Check to see whether anything in the subquery depends on the current user ID. + * If it does, set the relevant PlannerGlobal flag and create an inval item + * on the catalog. + */ + create_userdep_planinval(parse, glob); + + /* * If there is a WITH list, process each WITH query and build an initplan * SubPlan structure for it. */ diff --git a/src/backend/optimizer/plan/setrefs.c b/src/backend/optimizer/plan/setrefs.c new file mode 100644 index 46affe7..ef29b30 100644 *** a/src/backend/optimizer/plan/setrefs.c --- b/src/backend/optimizer/plan/setrefs.c *************** record_plan_function_dependency(PlannerI *** 2040,2046 **** void extract_query_dependencies(Node *query, List **relationOids, ! List **invalItems) { PlannerGlobal glob; PlannerInfo root; --- 2040,2047 ---- void extract_query_dependencies(Node *query, List **relationOids, ! List **invalItems, ! Oid *planUserId) { PlannerGlobal glob; PlannerInfo root; *************** extract_query_dependencies(Node *query, *** 2050,2055 **** --- 2051,2057 ---- glob.type = T_PlannerGlobal; glob.relationOids = NIL; glob.invalItems = NIL; + glob.planUserId = InvalidOid; MemSet(&root, 0, sizeof(root)); root.type = T_PlannerInfo; *************** extract_query_dependencies(Node *query, *** 2059,2064 **** --- 2061,2067 ---- *relationOids = glob.relationOids; *invalItems = glob.invalItems; + *planUserId = glob.planUserId; } static bool *************** extract_query_dependencies_walker(Node * *** 2074,2079 **** --- 2077,2097 ---- Query *query = (Query *) node; ListCell *lc; + /* + * Check to see if this node has a user id dependency; if so, + * register it in the global state. + */ + if (OidIsValid(query->dependsUserId)) + { + /* + * Different Query nodes may not have the same user id + * dependency. + */ + Assert( !OidIsValid(context->glob->planUserId) + || context->glob->planUserId == query->dependsUserId ); + context->glob->planUserId = query->dependsUserId; + } + if (query->commandType == CMD_UTILITY) { /* diff --git a/src/backend/parser/analyze.c b/src/backend/parser/analyze.c new file mode 100644 index 7225bb6..0550f63 100644 *** a/src/backend/parser/analyze.c --- b/src/backend/parser/analyze.c *************** transformDeleteStmt(ParseState *pstate, *** 346,351 **** --- 346,352 ---- Node *qual; qry->commandType = CMD_DELETE; + qry->dependsUserId = InvalidOid; /* process the WITH clause independently of all else */ if (stmt->withClause) *************** transformInsertStmt(ParseState *pstate, *** 428,433 **** --- 429,435 ---- Assert(pstate->p_ctenamespace == NIL); qry->commandType = CMD_INSERT; + qry->dependsUserId = InvalidOid; pstate->p_is_insert = true; /* process the WITH clause independently of all else */ *************** transformSelectStmt(ParseState *pstate, *** 912,917 **** --- 914,920 ---- ListCell *l; qry->commandType = CMD_SELECT; + qry->dependsUserId = InvalidOid; /* process the WITH clause independently of all else */ if (stmt->withClause) *************** transformValuesClause(ParseState *pstate *** 1051,1056 **** --- 1054,1060 ---- int i; qry->commandType = CMD_SELECT; + qry->dependsUserId = InvalidOid; /* Most SELECT stuff doesn't apply in a VALUES clause */ Assert(stmt->distinctClause == NIL); *************** transformSetOperationStmt(ParseState *ps *** 1287,1292 **** --- 1291,1297 ---- int tllen; qry->commandType = CMD_SELECT; + qry->dependsUserId = InvalidOid; /* * Find leftmost leaf SelectStmt. We currently only need to do this in *************** transformUpdateStmt(ParseState *pstate, *** 1909,1914 **** --- 1914,1920 ---- ListCell *tl; qry->commandType = CMD_UPDATE; + qry->dependsUserId = InvalidOid; pstate->p_is_update = true; /* process the WITH clause independently of all else */ diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y new file mode 100644 index 7b9895d..686750b 100644 *** a/src/backend/parser/gram.y --- b/src/backend/parser/gram.y *************** static Node *makeRecursiveViewSelect(cha *** 258,263 **** --- 258,264 ---- %type alter_table_cmd alter_type_cmd opt_collate_clause replica_identity %type alter_table_cmds alter_type_cmds + %type row_security_cmd %type opt_drop_behavior *************** alter_table_cmd: *** 2190,2195 **** --- 2191,2214 ---- n->def = (Node *)$2; $$ = (Node *)n; } + /* ALTER TABLE SET ROW SECURITY FOR TO () */ + | 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 RESET ROW SECURITY FOR */ + | 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 REPLICA IDENTITY */ | REPLICA IDENTITY_P replica_identity { *************** reloption_elem: *** 2300,2305 **** --- 2319,2330 ---- } ; + 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 new file mode 100644 index 272d27f..091ac68 100644 *** a/src/backend/parser/parse_agg.c --- b/src/backend/parser/parse_agg.c *************** transformAggregateCall(ParseState *pstat *** 333,338 **** --- 333,341 ---- 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 *************** transformWindowFuncCall(ParseState *psta *** 662,667 **** --- 665,673 ---- 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 new file mode 100644 index 81c9338..e289e77 100644 *** a/src/backend/parser/parse_expr.c --- b/src/backend/parser/parse_expr.c *************** transformSubLink(ParseState *pstate, Sub *** 1462,1467 **** --- 1462,1468 ---- case EXPR_KIND_OFFSET: case EXPR_KIND_RETURNING: case EXPR_KIND_VALUES: + case EXPR_KIND_ROW_SECURITY: /* okay */ break; case EXPR_KIND_CHECK_CONSTRAINT: *************** ParseExprKindName(ParseExprKind exprKind *** 2643,2648 **** --- 2644,2651 ---- return "EXECUTE"; case EXPR_KIND_TRIGGER_WHEN: return "WHEN"; + case EXPR_KIND_ROW_SECURITY: + return "ROW SECURITY"; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/rewrite/Makefile b/src/backend/rewrite/Makefile new file mode 100644 index 9ff56c7..25423d3 100644 *** a/src/backend/rewrite/Makefile --- b/src/backend/rewrite/Makefile *************** top_builddir = ../../.. *** 13,18 **** include $(top_builddir)/src/Makefile.global OBJS = rewriteRemove.o rewriteDefine.o \ ! rewriteHandler.o rewriteManip.o rewriteSupport.o include $(top_srcdir)/src/backend/common.mk --- 13,19 ---- include $(top_builddir)/src/Makefile.global OBJS = rewriteRemove.o rewriteDefine.o \ ! rewriteHandler.o rewriteManip.o rewriteSupport.o \ ! rowsecurity.o include $(top_srcdir)/src/backend/common.mk diff --git a/src/backend/rewrite/rewriteHandler.c b/src/backend/rewrite/rewriteHandler.c new file mode 100644 index caed8ca..8d987a6 100644 *** a/src/backend/rewrite/rewriteHandler.c --- b/src/backend/rewrite/rewriteHandler.c *************** *** 25,41 **** #include "rewrite/rewriteDefine.h" #include "rewrite/rewriteHandler.h" #include "rewrite/rewriteManip.h" #include "utils/builtins.h" #include "utils/lsyscache.h" #include "utils/rel.h" - /* We use a list of these to detect recursion in RewriteQuery */ typedef struct rewrite_event { ! Oid relation; /* OID of relation having rules */ ! CmdType event; /* type of rule being fired */ } rewrite_event; typedef struct acquireLocksOnSubLinks_context { --- 25,42 ---- #include "rewrite/rewriteDefine.h" #include "rewrite/rewriteHandler.h" #include "rewrite/rewriteManip.h" + #include "rewrite/rowsecurity.h" #include "utils/builtins.h" #include "utils/lsyscache.h" #include "utils/rel.h" /* We use a list of these to detect recursion in RewriteQuery */ typedef struct rewrite_event { ! Oid relation; /* OID of relation having rules */ ! CmdType event; /* type of rule being fired */ } rewrite_event; + typedef struct acquireLocksOnSubLinks_context { *************** fireRIRrules(Query *parsetree, List *act *** 1670,1715 **** * Collect the RIR rules that we must apply */ rules = rel->rd_rules; ! if (rules == NULL) ! { ! heap_close(rel, NoLock); ! continue; ! } ! locks = NIL; ! for (i = 0; i < rules->numLocks; i++) { ! rule = rules->rules[i]; ! if (rule->event != CMD_SELECT) ! continue; ! locks = lappend(locks, rule); ! } /* ! * If we found any, apply them --- but first check for recursion! */ ! if (locks != NIL) { ! ListCell *l; ! if (list_member_oid(activeRIRs, RelationGetRelid(rel))) ereport(ERROR, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), ! errmsg("infinite recursion detected in rules for relation \"%s\"", RelationGetRelationName(rel)))); activeRIRs = lcons_oid(RelationGetRelid(rel), activeRIRs); ! ! foreach(l, locks) ! { ! rule = lfirst(l); ! ! parsetree = ApplyRetrieveRule(parsetree, ! rule, ! rt_index, ! rel, ! activeRIRs, ! forUpdatePushedDown); ! } activeRIRs = list_delete_first(activeRIRs); } --- 1671,1733 ---- * Collect the RIR rules that we must apply */ rules = rel->rd_rules; ! if (rules != NULL) { ! locks = NIL; ! for (i = 0; i < rules->numLocks; i++) ! { ! rule = rules->rules[i]; ! if (rule->event != CMD_SELECT) ! continue; ! locks = lappend(locks, rule); ! } + /* + * If we found any, apply them --- but first check for recursion! + */ + if (locks != NIL) + { + ListCell *l; + + if (list_member_oid(activeRIRs, RelationGetRelid(rel))) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("infinite recursion detected in rules for relation \"%s\"", + RelationGetRelationName(rel)))); + activeRIRs = lcons_oid(RelationGetRelid(rel), activeRIRs); + + foreach(l, locks) + { + rule = lfirst(l); + + parsetree = ApplyRetrieveRule(parsetree, + rule, + rt_index, + rel, + activeRIRs, + forUpdatePushedDown); + } + + activeRIRs = list_delete_first(activeRIRs); + } + } /* ! * If the RTE has row-security quals, apply them and recurse into the ! * securityQuals. */ ! if (prepend_row_security_quals(parsetree, rte, rt_index)) { ! // We applied security quals, check for infinite recursion and ! // then expand any nested queries. if (list_member_oid(activeRIRs, RelationGetRelid(rel))) ereport(ERROR, (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), ! errmsg("infinite recursion detected in row-security policy for relation \"%s\"", RelationGetRelationName(rel)))); activeRIRs = lcons_oid(RelationGetRelid(rel), activeRIRs); ! ! expression_tree_walker( (Node*) rte->securityQuals, fireRIRonSubLink, (void*)activeRIRs ); activeRIRs = list_delete_first(activeRIRs); } *************** rewriteTargetView(Query *parsetree, Rela *** 2902,2908 **** * rewrite_events is a list of open query-rewrite actions, so we can detect * infinite recursion. */ ! static List * RewriteQuery(Query *parsetree, List *rewrite_events) { CmdType event = parsetree->commandType; --- 2920,2926 ---- * rewrite_events is a list of open query-rewrite actions, so we can detect * infinite recursion. */ ! List * RewriteQuery(Query *parsetree, List *rewrite_events) { CmdType event = parsetree->commandType; *************** RewriteQuery(Query *parsetree, List *rew *** 2975,2980 **** --- 2993,2999 ---- } } + /* * If the statement is an insert, update, or delete, adjust its targetlist * as needed, and then fire INSERT/UPDATE/DELETE rules on it. diff --git a/src/backend/rewrite/rowsecurity.c b/src/backend/rewrite/rowsecurity.c new file mode 100644 index ...2c2f6a3 . *** a/src/backend/rewrite/rowsecurity.c --- b/src/backend/rewrite/rowsecurity.c *************** *** 0 **** --- 1,143 ---- + /* + * rewrite/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 "parser/parsetree.h" + #include "rewrite/rewriteHandler.h" + #include "rewrite/rowsecurity.h" + #include "utils/lsyscache.h" + #include "utils/rel.h" + #include "utils/syscache.h" + #include "tcop/utility.h" + + /* hook to allow extensions to apply their own security policy */ + row_security_policy_hook_type row_security_policy_hook = NULL; + + /* + * Check the given RTE to see whether it's already had row-security quals + * expanded and, if not, prepend any row-security rules from built-in or + * plug-in sources to the securityQuals. The security quals are rewritten (for + * view expansion, etc) before being added to the RTE. + * + * Returns true if any quals were added. Note that quals may have been found + * but not added if user rights make the user exempt from row security. + */ + bool + prepend_row_security_quals(Query* root, RangeTblEntry* rte, int rt_index) + { + List *rowsecquals; + Relation rel; + Oid userid; + int sec_context; + bool qualsAdded = false; + bool depends_on_userid; + + GetUserIdAndSecContext(&userid, &sec_context); + + if (rte->relid >= FirstNormalObjectId + && rte->relkind == 'r' + && !(sec_context & SECURITY_ROW_LEVEL_DISABLED)) + { + /* + * Fetch the row-security qual and add it to the list of quals + * to be expanded by expand_security_quals. + */ + rel = heap_open(rte->relid, NoLock); + rowsecquals = pull_row_security_policy(root->commandType, rel, &depends_on_userid); + if (rowsecquals) + { + /* + * Row security quals always have the target table as varno 1, as no + * joins are permitted in row security expressions. We must walk + * the expression, updating any references to varno 1 to the varno + * the table has in the outer query. + * + * We rewrite the expression in-place. + */ + qualsAdded = true; + ChangeVarNodes(rowsecquals, 1, rt_index, 0); + rte->securityQuals = list_concat(rowsecquals, rte->securityQuals); + } + heap_close(rel, NoLock); + } + if (depends_on_userid) + { + /* + * Record that this plan depends on the current user ID + * and must be replanned if it changes. + */ + Assert( !OidIsValid(root->dependsUserId) || root->dependsUserId == GetUserId() ); + root->dependsUserId = GetUserId(); + } + return qualsAdded; + } + + /* + * pull_row_security_policy + * + * Fetches the configured row-security policy of both built-in catalogs and any + * extensions. If any policy is found then a list of qualifier expressions is + * returned, where each is treated as a securityQual. + * + * Vars must use varno 1 to refer to the table with row security. + * + * The returned expression trees will be modified in-place, so return copies if + * you're not generating the expression tree each time. + */ + List * + pull_row_security_policy(CmdType cmd, Relation relation, bool* depends_on_userid) + { + List *quals = NIL; + Expr *qual = NULL; + + /* + * Pull the row-security policy configured with built-in features, + * if unprivileged users. Note: superuser bypasses RLS. + */ + if (relation->rsdesc) + { + if (depends_on_userid) + *depends_on_userid = true; + /* Should make this dependent on a grantable right instead: */ + if (!superuser()) + { + RowSecurityDesc *rsdesc = relation->rsdesc; + qual = copyObject(rsdesc->rsall.qual); + quals = lcons(qual, quals); + } + } else { + if (depends_on_userid) + *depends_on_userid = false; + } + + /* + * Also, ask extensions whether they want to apply their own + * row-security policy. If both built-in and extension has + * their own policy they're applied as nested qualifiers. + */ + if (row_security_policy_hook) + { + List *temp; + + temp = (*row_security_policy_hook)(cmd, relation); + if (temp != NIL) + lcons(temp, quals); + } + return quals; + } diff --git a/src/backend/utils/adt/ri_triggers.c b/src/backend/utils/adt/ri_triggers.c new file mode 100644 index 1e1e616..5630663 100644 *** a/src/backend/utils/adt/ri_triggers.c --- b/src/backend/utils/adt/ri_triggers.c *************** ri_PlanCheck(const char *querystr, int n *** 2955,2960 **** --- 2955,2961 ---- Relation query_rel; Oid save_userid; int save_sec_context; + int temp_sec_context; /* * Use the query type code to determine whether the query is run against *************** ri_PlanCheck(const char *querystr, int n *** 2967,2974 **** /* Switch to proper UID to perform check as */ GetUserIdAndSecContext(&save_userid, &save_sec_context); SetUserIdAndSecContext(RelationGetForm(query_rel)->relowner, ! save_sec_context | SECURITY_LOCAL_USERID_CHANGE); /* Create the plan */ qplan = SPI_prepare(querystr, nargs, argtypes); --- 2968,2985 ---- /* Switch to proper UID to perform check as */ GetUserIdAndSecContext(&save_userid, &save_sec_context); + + /* + * Row-level security should be disabled in the case where a 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 (qkey->constr_queryno != RI_PLAN_CHECK_LOOKUPPK) + temp_sec_context |= SECURITY_ROW_LEVEL_DISABLED; + SetUserIdAndSecContext(RelationGetForm(query_rel)->relowner, ! temp_sec_context); /* Create the plan */ qplan = SPI_prepare(querystr, nargs, argtypes); *************** ri_PerformCheck(const RI_ConstraintInfo *** 3087,3092 **** --- 3098,3104 ---- /* Switch to proper UID to perform check as */ GetUserIdAndSecContext(&save_userid, &save_sec_context); + SetUserIdAndSecContext(RelationGetForm(query_rel)->relowner, save_sec_context | SECURITY_LOCAL_USERID_CHANGE); diff --git a/src/backend/utils/cache/plancache.c b/src/backend/utils/cache/plancache.c new file mode 100644 index d492cbb..e53a6ed 100644 *** a/src/backend/utils/cache/plancache.c --- b/src/backend/utils/cache/plancache.c *************** *** 53,58 **** --- 53,59 ---- #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" *************** CompleteCachedPlan(CachedPlanSource *pla *** 371,377 **** */ extract_query_dependencies((Node *) querytree_list, &plansource->relationOids, ! &plansource->invalItems); /* * Also save the current search_path in the query_context. (This --- 372,379 ---- */ extract_query_dependencies((Node *) querytree_list, &plansource->relationOids, ! &plansource->invalItems, ! &plansource->planUserId); /* * Also save the current search_path in the query_context. (This *************** RevalidateCachedQuery(CachedPlanSource * *** 583,588 **** --- 585,600 ---- } /* + * If the plan was constructed with assumption of a particular user-id, + * and the current user ID is different, invalidate the plan, then rerun + * rewrite on the original parsetree. + */ + if (plansource->is_valid && + OidIsValid(plansource->planUserId) && + plansource->planUserId != GetUserId()) + plansource->is_valid = false; + + /* * If the query is currently valid, acquire locks on the referenced * objects; then check again. We need to do it this way to cover the race * condition that an invalidation message arrives before we get the locks. *************** RevalidateCachedQuery(CachedPlanSource * *** 723,729 **** */ extract_query_dependencies((Node *) qlist, &plansource->relationOids, ! &plansource->invalItems); /* * Also save the current search_path in the query_context. (This should --- 735,743 ---- */ extract_query_dependencies((Node *) qlist, &plansource->relationOids, ! &plansource->invalItems, ! &plansource->planUserId ! ); /* * Also save the current search_path in the query_context. (This should *************** BuildCachedPlan(CachedPlanSource *planso *** 847,852 **** --- 861,867 ---- { CachedPlan *plan; List *plist; + ListCell *cell; bool snapshot_set; bool spi_pushed; MemoryContext plan_context; diff --git a/src/backend/utils/cache/relcache.c b/src/backend/utils/cache/relcache.c new file mode 100644 index c8cea02..66e3d0c 100644 *** a/src/backend/utils/cache/relcache.c --- b/src/backend/utils/cache/relcache.c *************** *** 50,55 **** --- 50,56 ---- #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" *************** RelationBuildDesc(Oid targetRelId, bool *** 954,959 **** --- 955,965 ---- else relation->trigdesc = NULL; + if (relation->rd_rel->relhasrowsecurity) + RelationBuildRowSecurity(relation); + else + relation->rsdesc = NULL; + /* * if it's an index, initialize index-related information */ *************** RelationDestroyRelation(Relation relatio *** 1914,1919 **** --- 1920,1927 ---- 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); *************** RelationCacheInitializePhase3(void) *** 3302,3308 **** relation->rd_rel->relhastriggers = false; restart = true; } ! /* Release hold on the relation */ RelationDecrementReferenceCount(relation); --- 3310,3322 ---- 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); *************** load_relcache_init_file(bool shared) *** 4614,4619 **** --- 4628,4634 ---- rel->rd_rules = NULL; rel->rd_rulescxt = NULL; rel->trigdesc = NULL; + rel->rsdesc = NULL; rel->rd_indexprs = NIL; rel->rd_indpred = NIL; rel->rd_exclops = NULL; diff --git a/src/bin/pg_dump/common.c b/src/bin/pg_dump/common.c new file mode 100644 index 4d35ae5..fd9e6cd 100644 *** a/src/bin/pg_dump/common.c --- b/src/bin/pg_dump/common.c *************** getSchemaData(Archive *fout, int *numTab *** 244,249 **** --- 244,253 ---- 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 new file mode 100644 index 9464540..fb4d965 100644 *** a/src/bin/pg_dump/pg_backup_archiver.c --- b/src/bin/pg_dump/pg_backup_archiver.c *************** _printTocEntry(ArchiveHandle *AH, TocEnt *** 3223,3228 **** --- 3223,3229 ---- 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 new file mode 100644 index a6c0428..fe181ef 100644 *** a/src/bin/pg_dump/pg_dump.c --- b/src/bin/pg_dump/pg_dump.c *************** static char *myFormatType(const char *ty *** 247,252 **** --- 247,253 ---- 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); *************** dumpBlobs(Archive *fout, void *arg) *** 2723,2728 **** --- 2724,2857 ---- 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, *************** getTables(Archive *fout, int *numTables) *** 4253,4258 **** --- 4382,4388 ---- int i_relhastriggers; int i_relhasindex; int i_relhasrules; + int i_relhasrowsec; int i_relhasoids; int i_relfrozenxid; int i_toastoid; *************** getTables(Archive *fout, int *numTables) *** 4304,4313 **** "(%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, " "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, " --- 4434,4440 ---- "(%s c.relowner) AS rolname, " "c.relchecks, c.relhastriggers, " "c.relhasindex, c.relhasrules, c.relhasoids, " ! "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, " *************** getTables(Archive *fout, int *numTables) *** 4343,4348 **** --- 4470,4476 ---- "(%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, " *************** getTables(Archive *fout, int *numTables) *** 4382,4387 **** --- 4510,4516 ---- "(%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, " *************** getTables(Archive *fout, int *numTables) *** 4419,4424 **** --- 4548,4554 ---- "(%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, " *************** getTables(Archive *fout, int *numTables) *** 4455,4460 **** --- 4585,4591 ---- "(%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, " *************** getTables(Archive *fout, int *numTables) *** 4491,4496 **** --- 4622,4628 ---- "(%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, " *************** getTables(Archive *fout, int *numTables) *** 4527,4532 **** --- 4659,4665 ---- "(%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, " *************** getTables(Archive *fout, int *numTables) *** 4563,4568 **** --- 4696,4702 ---- "(%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, " *************** getTables(Archive *fout, int *numTables) *** 4595,4600 **** --- 4729,4735 ---- "(%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, " *************** getTables(Archive *fout, int *numTables) *** 4622,4627 **** --- 4757,4763 ---- "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, " "'t'::bool AS relhasoids, " + "'f'::bool AS relhasrowsecurity, " "0 AS relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " *************** getTables(Archive *fout, int *numTables) *** 4659,4664 **** --- 4795,4801 ---- "relchecks, (reltriggers <> 0) AS relhastriggers, " "relhasindex, relhasrules, " "'t'::bool AS relhasoids, " + "'f'::bool AS relhasrowsecurity, " "0 as relfrozenxid, " "0 AS toid, " "0 AS tfrozenxid, " *************** getTables(Archive *fout, int *numTables) *** 4706,4711 **** --- 4843,4849 ---- 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"); *************** getTables(Archive *fout, int *numTables) *** 4755,4760 **** --- 4893,4899 ---- 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)); *************** dumpDumpableObject(Archive *fout, Dumpab *** 7878,7883 **** --- 8017,8025 ---- 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 */ *************** addBoundaryDependencies(DumpableObject * *** 15261,15266 **** --- 15403,15409 ---- 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 new file mode 100644 index e01015e..08e0fdd 100644 *** a/src/bin/pg_dump/pg_dump.h --- b/src/bin/pg_dump/pg_dump.h *************** typedef enum *** 111,117 **** DO_PRE_DATA_BOUNDARY, DO_POST_DATA_BOUNDARY, DO_EVENT_TRIGGER, ! DO_REFRESH_MATVIEW } DumpableObjectType; typedef struct _dumpableObject --- 111,118 ---- DO_PRE_DATA_BOUNDARY, DO_POST_DATA_BOUNDARY, DO_EVENT_TRIGGER, ! DO_REFRESH_MATVIEW, ! DO_ROW_SECURITY, } DumpableObjectType; typedef struct _dumpableObject *************** typedef struct _tableInfo *** 245,250 **** --- 246,252 ---- 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 */ *************** typedef struct _blobInfo *** 484,489 **** --- 486,499 ---- 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 */ *************** extern DefaultACLInfo *getDefaultACLs(Ar *** 575,579 **** --- 585,590 ---- 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 new file mode 100644 index 3590932..48602e5 100644 *** a/src/bin/pg_dump/pg_dump_sort.c --- b/src/bin/pg_dump/pg_dump_sort.c *************** describeDumpableObject(DumpableObject *o *** 1398,1403 **** --- 1398,1408 ---- "BLOB DATA (ID %d)", obj->dumpId); return; + case DO_ROW_SECURITY: + snprintf(buf, bufsize, + "ROW-SECURITY POLICY (ID %d OID %u)", + obj->dumpId, obj->catId.oid); + return; case DO_PRE_DATA_BOUNDARY: snprintf(buf, bufsize, "PRE-DATA BOUNDARY (ID %d)", diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c new file mode 100644 index d1447fe..d8225ea 100644 *** a/src/bin/psql/describe.c --- b/src/bin/psql/describe.c *************** listTables(const char *tabtypes, const c *** 2795,2800 **** --- 2795,2804 ---- appendPQExpBuffer(&buf, ",\n pg_catalog.obj_description(c.oid, 'pg_class') as \"%s\"", gettext_noop("Description")); + if (pset.sversion >= 90400) + appendPQExpBuffer(&buf, + ",\n pg_catalog.pg_get_expr(rs.rsecqual, c.oid) as \"%s\"", + gettext_noop("Row-security")); } appendPQExpBufferStr(&buf, *************** listTables(const char *tabtypes, const c *** 2804,2809 **** --- 2808,2816 ---- 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 >= 90400) + appendPQExpBuffer(&buf, + "\n LEFT JOIN pg_rowsecurity rs ON rs.rsecrelid = c.oid"); appendPQExpBufferStr(&buf, "\nWHERE c.relkind IN ("); if (showTables) diff --git a/src/include/catalog/dependency.h b/src/include/catalog/dependency.h new file mode 100644 index 8948589..de9323e 100644 *** a/src/include/catalog/dependency.h --- b/src/include/catalog/dependency.h *************** typedef enum ObjectClass *** 147,152 **** --- 147,153 ---- 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 new file mode 100644 index 0515b75..4f46c5d 100644 *** a/src/include/catalog/indexing.h --- b/src/include/catalog/indexing.h *************** DECLARE_UNIQUE_INDEX(pg_extension_name_i *** 313,318 **** --- 313,323 ---- 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 new file mode 100644 index f2fb317..bbbf62d 100644 *** a/src/include/catalog/pg_class.h --- b/src/include/catalog/pg_class.h *************** CATALOG(pg_class,1259) BKI_BOOTSTRAP BKI *** 65,70 **** --- 65,71 ---- 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 */ *************** typedef FormData_pg_class *Form_pg_class *** 94,100 **** * ---------------- */ ! #define Natts_pg_class 29 #define Anum_pg_class_relname 1 #define Anum_pg_class_relnamespace 2 #define Anum_pg_class_reltype 3 --- 95,101 ---- * ---------------- */ ! #define Natts_pg_class 30 #define Anum_pg_class_relname 1 #define Anum_pg_class_relnamespace 2 #define Anum_pg_class_reltype 3 *************** typedef FormData_pg_class *Form_pg_class *** 118,129 **** #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 /* ---------------- * initial contents of pg_class --- 119,131 ---- #define Anum_pg_class_relhasrules 21 #define Anum_pg_class_relhastriggers 22 #define Anum_pg_class_relhassubclass 23 ! #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 *************** typedef FormData_pg_class *Form_pg_class *** 138,150 **** * 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_ )); 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_ )); 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_ )); 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_ )); DESCR(""); --- 140,153 ---- * 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 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 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 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 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 ...f1158d7 . *** a/src/include/catalog/pg_rowsecurity.h --- b/src/include/catalog/pg_rowsecurity.h *************** *** 0 **** --- 1,76 ---- + /* + * pg_rowsecurity.h + * definition of the system catalog for row-security policy (pg_rowsecurity) + * + * Portions Copyright (c) 1996-2012, PostgreSQL Global Development Group + * Portions Copyright (c) 1994, Regents of the University of California + * + */ + #ifndef PG_ROWSECURITY_H + #define PG_ROWSECURITY_H + + #include "catalog/genbki.h" + #include "nodes/primnodes.h" + #include "utils/memutils.h" + #include "utils/relcache.h" + + /* ---------------- + * pg_rowsecurity definition. cpp turns this into + * typedef struct FormData_pg_rowsecurity + * ---------------- + */ + #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_rowsecurity corresponds to a pointer to a row with + * the format of pg_rowsecurity relation. + * ---------------- + */ + typedef FormData_pg_rowsecurity *Form_pg_rowsecurity; + + /* ---------------- + * compiler constants for pg_rowsecurity + * ---------------- + */ + #define Natts_pg_rowsecurity 3 + #define Anum_pg_rowsecurity_rsecrelid 1 + #define Anum_pg_rowsecurity_rseccmd 2 + #define Anum_pg_rowsecurity_rsecqual 3 + + #define ROWSECURITY_CMD_ALL 'a' + #define ROWSECURITY_CMD_SELECT 's' + #define ROWSECURITY_CMD_INSERT 'i' + #define ROWSECURITY_CMD_UPDATE 'u' + #define ROWSECURITY_CMD_DELETE 'd' + + typedef struct + { + Oid rsecid; + Expr *qual; + bool hassublinks; + } RowSecurityEntry; + + typedef struct + { + MemoryContext rscxt; + RowSecurityEntry rsall; /* row-security policy for ALL */ + } RowSecurityDesc; + + extern void RelationBuildRowSecurity(Relation relation); + extern void ATExecSetRowSecurity(Relation relation, + const char *cmdname, Node *clause); + extern void RemoveRowSecurityById(Oid relationId); + + #endif /* PG_ROWSECURITY_H */ diff --git a/src/include/miscadmin.h b/src/include/miscadmin.h new file mode 100644 index 0d61b82..dae01ad 100644 *** a/src/include/miscadmin.h --- b/src/include/miscadmin.h *************** extern int trace_recovery(int trace_leve *** 272,277 **** --- 272,278 ---- /* 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/parsenodes.h b/src/include/nodes/parsenodes.h new file mode 100644 index 18d4991..ad867fe 100644 *** a/src/include/nodes/parsenodes.h --- b/src/include/nodes/parsenodes.h *************** typedef struct Query *** 152,157 **** --- 152,161 ---- List *constraintDeps; /* a list of pg_constraint OIDs that the query * depends on to be semantically valid */ + + Oid dependsUserId; /* If this rewritten tree is only valid + * for a given user ID, that ID. Otherwise InvalidOid. + * Not used after PlannerGlobal is created. */ } Query; *************** typedef enum AlterTableType *** 1317,1322 **** --- 1321,1328 ---- AT_AddOf, /* OF */ AT_DropOf, /* NOT OF */ AT_ReplicaIdentity, /* REPLICA IDENTITY */ + AT_SetRowSecurity, /* SET ROW SECURITY (...) */ + AT_ResetRowSecurity, /* RESET ROW SECURITY */ AT_GenericOptions /* OPTIONS (...) */ } AlterTableType; diff --git a/src/include/nodes/plannodes.h b/src/include/nodes/plannodes.h new file mode 100644 index 38c039c..35c88cc 100644 *** a/src/include/nodes/plannodes.h --- b/src/include/nodes/plannodes.h *************** typedef struct PlannedStmt *** 67,72 **** --- 67,74 ---- 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 new file mode 100644 index c607b36..c464db0 100644 *** a/src/include/nodes/relation.h --- b/src/include/nodes/relation.h *************** typedef struct PlannerGlobal *** 99,104 **** --- 99,107 ---- Index lastRowMarkId; /* highest PlanRowMark ID assigned */ bool transientPlan; /* redo plan when TransactionXmin changes? */ + + Oid planUserId; /* User-id this plan depends on, or + * InvalidOid if it doesn't depend on a userid. */ } PlannerGlobal; /* macro for fetching the Plan associated with a SubPlan node */ diff --git a/src/include/optimizer/planmain.h b/src/include/optimizer/planmain.h new file mode 100644 index 8bdb7db..193b1de 100644 *** a/src/include/optimizer/planmain.h --- b/src/include/optimizer/planmain.h *************** extern void set_sa_opfuncid(ScalarArrayO *** 133,138 **** extern void record_plan_function_dependency(PlannerInfo *root, Oid funcid); extern void extract_query_dependencies(Node *query, List **relationOids, ! List **invalItems); #endif /* PLANMAIN_H */ --- 133,139 ---- extern void record_plan_function_dependency(PlannerInfo *root, Oid funcid); extern void extract_query_dependencies(Node *query, List **relationOids, ! List **invalItems, ! Oid *planUserId); #endif /* PLANMAIN_H */ diff --git a/src/include/parser/parse_node.h b/src/include/parser/parse_node.h new file mode 100644 index 85598e8..9c7c382 100644 *** a/src/include/parser/parse_node.h --- b/src/include/parser/parse_node.h *************** typedef enum ParseExprKind *** 63,69 **** 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 */ } ParseExprKind; --- 63,70 ---- 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_ROW_SECURITY /* ROW SECURITY policy for a table */ } ParseExprKind; diff --git a/src/include/rewrite/rowsecurity.h b/src/include/rewrite/rowsecurity.h new file mode 100644 index ...5b3f0c3 . *** a/src/include/rewrite/rowsecurity.h --- b/src/include/rewrite/rowsecurity.h *************** *** 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 List *pull_row_security_policy(CmdType cmd, Relation relation, bool *depends_on_userid); + + extern bool prepend_row_security_quals(Query* root, RangeTblEntry* rte, int rt_index); + + #endif /* ROWSECURITY_H */ diff --git a/src/include/utils/plancache.h b/src/include/utils/plancache.h new file mode 100644 index b8ca643..00f6b66 100644 *** a/src/include/utils/plancache.h --- b/src/include/utils/plancache.h *************** typedef struct CachedPlanSource *** 93,98 **** --- 93,101 ---- List *invalItems; /* other dependencies, as PlanInvalItems */ struct OverrideSearchPath *search_path; /* search_path used for * parsing and planning */ + Oid planUserId; /* User-id that the plan depends on, or + * InvalidOid if the plan doesn't depend on any + * particular user ID. */ MemoryContext query_context; /* context holding the above, or NULL */ /* If we have a generic plan, this is a reference-counted link to it: */ struct CachedPlan *gplan; /* generic plan, or NULL if not valid */ diff --git a/src/include/utils/rel.h b/src/include/utils/rel.h new file mode 100644 index c87dadc..3fc3e5e 100644 *** a/src/include/utils/rel.h --- b/src/include/utils/rel.h *************** *** 18,23 **** --- 18,24 ---- #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" *************** typedef struct RelationData *** 110,115 **** --- 111,117 ---- 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/rowsecurity.out b/src/test/regress/expected/rowsecurity.out new file mode 100644 index ...0f456ea . *** a/src/test/regress/expected/rowsecurity.out --- b/src/test/regress/expected/rowsecurity.out *************** *** 0 **** --- 1,991 ---- + -- + -- 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) ORDER BY did; + NOTICE: f_leak => my first novel + NOTICE: f_leak => my first manga + NOTICE: f_leak => great science fiction + NOTICE: f_leak => great manga + 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) ORDER BY did; + NOTICE: f_leak => my first novel + NOTICE: f_leak => my first manga + NOTICE: f_leak => great science fiction + NOTICE: f_leak => great manga + cid | did | dlevel | dauthor | dtitle | cname + -----+-----+--------+-------------------+-----------------------+----------------- + 11 | 1 | 1 | rls_regress_user1 | my first novel | novel + 44 | 4 | 1 | rls_regress_user1 | my first manga | manga + 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction + 44 | 8 | 1 | rls_regress_user2 | great manga | manga + (4 rows) + + -- viewpoint from rls_regress_user2 + SET SESSION AUTHORIZATION rls_regress_user2; + SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; + NOTICE: f_leak => my first novel + NOTICE: f_leak => my second novel + NOTICE: f_leak => my science fiction + NOTICE: f_leak => 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) ORDER BY did; + 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 | 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 + 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 + (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) ORDER BY did; + 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) ORDER BY did; + 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) ORDER BY did; + 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) ORDER BY did; + NOTICE: f_leak => great science fiction + NOTICE: f_leak => great technology book + NOTICE: f_leak => great manga + cid | did | dlevel | dauthor | dtitle | cname + -----+-----+--------+-------------------+-----------------------+----------------- + 22 | 6 | 1 | rls_regress_user2 | great science fiction | science fiction + 33 | 7 | 2 | rls_regress_user2 | great technology book | technology + 44 | 8 | 1 | rls_regress_user2 | great manga | manga + (3 rows) + + EXPLAIN (costs off) SELECT * FROM document WHERE f_leak(dtitle); + QUERY PLAN + ---------------------------------------------- + Subquery Scan on document + Filter: f_leak(document.dtitle) + -> Seq Scan on document document_1 + Filter: (dauthor = "current_user"()) + (4 rows) + + EXPLAIN (costs off) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); + QUERY PLAN + ---------------------------------------------------- + Nested Loop + -> Subquery Scan on document + Filter: f_leak(document.dtitle) + -> Seq Scan on document document_1 + Filter: (dauthor = "current_user"()) + -> Index Scan using category_pkey on category + Index Cond: (cid = document.cid) + (7 rows) + + -- interaction of FK/PK constraints + SET SESSION AUTHORIZATION rls_regress_user0; + ALTER TABLE category SET ROW SECURITY FOR ALL + TO (CASE WHEN current_user = 'rls_regress_user1' THEN cid IN (11, 33) + WHEN current_user = 'rls_regress_user2' THEN cid IN (22, 44) + ELSE false END); + -- cannot delete PK referenced by invisible FK + SET SESSION AUTHORIZATION rls_regress_user1; + SELECT * FROM document d full outer join category c on d.cid = c.cid; + did | cid | dlevel | dauthor | dtitle | cid | cname + -----+-----+--------+-------------------+--------------------+-----+------------ + 2 | 11 | 2 | rls_regress_user1 | my second novel | 11 | novel + 1 | 11 | 1 | rls_regress_user1 | my first novel | 11 | novel + | | | | | 33 | technology + 5 | 44 | 2 | rls_regress_user1 | my second manga | | + 4 | 44 | 1 | rls_regress_user1 | my first manga | | + 3 | 22 | 2 | rls_regress_user1 | my science fiction | | + (6 rows) + + DELETE FROM category WHERE cid = 33; -- fails with FK violation + ERROR: update or delete on table "category" violates foreign key constraint "document_cid_fkey" on table "document" + DETAIL: Key (cid)=(33) is still referenced from table "document". + -- cannot insert FK referencing invisible PK + SET SESSION AUTHORIZATION rls_regress_user2; + SELECT * FROM document d full outer join category c on d.cid = c.cid; + did | cid | dlevel | dauthor | dtitle | cid | cname + -----+-----+--------+-------------------+-----------------------+-----+----------------- + 6 | 22 | 1 | rls_regress_user2 | great science fiction | 22 | science fiction + 8 | 44 | 1 | rls_regress_user2 | great manga | 44 | manga + 7 | 33 | 2 | rls_regress_user2 | great technology book | | + (3 rows) + + INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge'); -- fail with FK violation + ERROR: insert or update on table "document" violates foreign key constraint "document_cid_fkey" + DETAIL: Key (cid)=(33) is not present in table "category". + -- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row + SET SESSION AUTHORIZATION rls_regress_user1; + INSERT INTO document VALUES ( 8, 44, 1, 'rls_regress_user_1', 'my third manga' ); -- Must fail with unique violation, revealing presence of did we can't see + ERROR: duplicate key value violates unique constraint "document_pkey" + DETAIL: Key (did)=(8) already exists. + SELECT * FROM document WHERE did = 8; -- and confirm we can't see it + did | cid | dlevel | dauthor | dtitle + -----+-----+--------+---------+-------- + (0 rows) + + -- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row + SET SESSION AUTHORIZATION rls_regress_user1; + INSERT INTO document VALUES ( 8, 44, 1, 'rls_regress_user_1', 'my third manga' ); -- Must fail with unique violation, revealing presence of did we can't see + ERROR: duplicate key value violates unique constraint "document_pkey" + DETAIL: Key (did)=(8) already exists. + SELECT * FROM document WHERE did = 8; -- and confirm we can't see it + did | cid | dlevel | dauthor | dtitle + -----+-----+--------+---------+-------- + (0 rows) + + -- database superuser can bypass RLS policy + RESET SESSION AUTHORIZATION; + SELECT * FROM document; + did | cid | dlevel | dauthor | dtitle + -----+-----+--------+-------------------+----------------------- + 1 | 11 | 1 | rls_regress_user1 | my first novel + 2 | 11 | 2 | rls_regress_user1 | my second novel + 3 | 22 | 2 | rls_regress_user1 | my science fiction + 4 | 44 | 1 | rls_regress_user1 | my first manga + 5 | 44 | 2 | rls_regress_user1 | my second manga + 6 | 22 | 1 | rls_regress_user2 | great science fiction + 7 | 33 | 2 | rls_regress_user2 | great technology book + 8 | 44 | 1 | rls_regress_user2 | great manga + (8 rows) + + SELECT * FROM category; + cid | cname + -----+----------------- + 11 | novel + 22 | science fiction + 33 | technology + 44 | manga + (4 rows) + + -- + -- Table inheritance and RLS policy + -- + SET SESSION AUTHORIZATION rls_regress_user0; + CREATE TABLE t1 (a int, junk1 text, b text) WITH OIDS; + ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor + GRANT ALL ON t1 TO public; + COPY t1 FROM stdin WITH (oids); + CREATE TABLE t2 (c float) INHERITS (t1); + COPY t2 FROM stdin WITH (oids); + CREATE TABLE t3 (c text, b text, a int) WITH OIDS; + ALTER TABLE t3 INHERIT t1; + COPY t3(a,b,c) FROM stdin WITH (oids); + ALTER TABLE t1 SET ROW SECURITY FOR ALL TO (a % 2 = 0); -- be even number + ALTER TABLE t2 SET ROW SECURITY FOR ALL TO (a % 2 = 1); -- be odd number + SELECT * FROM t1; + a | b + ---+----- + 2 | bbb + 4 | ddd + 2 | bcd + 4 | def + 2 | yyy + (5 rows) + + EXPLAIN (costs off) SELECT * FROM t1; + QUERY PLAN + ------------------------------- + Append + -> Seq Scan on t1 + Filter: ((a % 2) = 0) + -> Seq Scan on t2 + Filter: ((a % 2) = 0) + -> Seq Scan on t3 + Filter: ((a % 2) = 0) + (7 rows) + + SELECT * FROM t1 WHERE f_leak(b); + NOTICE: f_leak => bbb + NOTICE: f_leak => ddd + NOTICE: f_leak => bcd + NOTICE: f_leak => def + NOTICE: f_leak => yyy + a | b + ---+----- + 2 | bbb + 4 | ddd + 2 | bcd + 4 | def + 2 | yyy + (5 rows) + + EXPLAIN (costs off) SELECT * FROM t1 WHERE f_leak(b); + QUERY PLAN + ------------------------------------- + Subquery Scan on t1 + Filter: f_leak(t1.b) + -> Append + -> Seq Scan on t1 t1_1 + Filter: ((a % 2) = 0) + -> Seq Scan on t2 + Filter: ((a % 2) = 0) + -> Seq Scan on t3 + Filter: ((a % 2) = 0) + (9 rows) + + -- reference to system column + SELECT oid, * FROM t1; + oid | a | b + -----+---+----- + 102 | 2 | bbb + 104 | 4 | ddd + 202 | 2 | bcd + 204 | 4 | def + 302 | 2 | yyy + (5 rows) + + EXPLAIN (costs off) SELECT * FROM t1; + QUERY PLAN + ------------------------------- + Append + -> Seq Scan on t1 + Filter: ((a % 2) = 0) + -> Seq Scan on t2 + Filter: ((a % 2) = 0) + -> Seq Scan on t3 + Filter: ((a % 2) = 0) + (7 rows) + + -- reference to whole-row reference + SELECT *,t1 FROM t1; + a | b | t1 + ---+-----+--------- + 2 | bbb | (2,bbb) + 4 | ddd | (4,ddd) + 2 | bcd | (2,bcd) + 4 | def | (4,def) + 2 | yyy | (2,yyy) + (5 rows) + + EXPLAIN (costs off) SELECT *,t1 FROM t1; + QUERY PLAN + ------------------------------- + Append + -> Seq Scan on t1 + Filter: ((a % 2) = 0) + -> Seq Scan on t2 + Filter: ((a % 2) = 0) + -> Seq Scan on t3 + Filter: ((a % 2) = 0) + (7 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) + + ----- Dependencies ----- + CREATE TABLE dependee (x integer, y integer); + CREATE TABLE dependent(x integer, y integer); + ALTER TABLE dependent SET ROW SECURITY FOR ALL TO (x = (SELECT d.x FROM dependee d WHERE d.y = y)); + DROP TABLE dependee; -- Should fail without CASCADE due to dependency on row-security qual? + ERROR: cannot drop table dependee because other objects depend on it + DETAIL: row-security of table dependent FOR ALL depends on table dependee + HINT: Use DROP ... CASCADE to drop the dependent objects too. + EXPLAIN SELECT * FROM dependent; -- After drop, should be unqualified + QUERY PLAN + ------------------------------------------------------------- + Seq Scan on dependent (cost=0.00..31.40 rows=2140 width=8) + Planning time: 0.078 ms + (2 rows) + + ----- RECURSION ---- + -- + -- Simple recursion + -- + CREATE TABLE rec1 (x integer, y integer); + ALTER TABLE rec1 + SET ROW SECURITY FOR ALL TO ( + x = (SELECT r.x FROM rec1 r WHERE y = r.y) + ); + SELECT * FROM rec1; -- fail, direct recursion + ERROR: infinite recursion detected in row-security policy for relation "rec1" + -- + -- Mutual recursion + -- + CREATE TABLE rec2 (a integer, b integer); + ALTER TABLE rec1 SET ROW SECURITY FOR ALL TO (x = (SELECT a FROM rec2 WHERE b = y)); + ALTER TABLE rec2 SET ROW SECURITY FOR ALL TO (a = (SELECT x FROM rec1 WHERE y = b)); + SELECT * FROM rec1; -- fail, mutual recursion + ERROR: infinite recursion detected in row-security policy for relation "rec1" + -- + -- Mutual recursion via views + -- + CREATE VIEW rec1v AS SELECT * FROM rec1; + CREATE VIEW rec2v AS SELECT * FROM rec2; + ALTER TABLE rec1 SET ROW SECURITY FOR ALL TO (x = (SELECT a FROM rec2v WHERE b = y)); + ALTER TABLE rec2 SET ROW SECURITY FOR ALL TO (a = (SELECT x FROM rec1v WHERE y = b)); + SELECT * FROM rec1; -- fail, mutual recursion via views + ERROR: infinite recursion detected in row-security policy for relation "rec1" + -- + -- Mutual recursion via .s.b views + -- + DROP VIEW rec1v, rec2v CASCADE; + NOTICE: drop cascades to 2 other objects + DETAIL: drop cascades to row-security of table rec1 FOR ALL + drop cascades to row-security of table rec2 FOR ALL + CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1; + CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2; + ALTER TABLE rec1 SET ROW SECURITY FOR ALL TO (x = (SELECT a FROM rec2v WHERE b = y)); + ALTER TABLE rec2 SET ROW SECURITY FOR ALL TO (a = (SELECT x FROM rec1v WHERE y = b)); + SELECT * FROM rec1; -- fail, mutual recursion via s.b. views + ERROR: infinite recursion detected in row-security policy for relation "rec1" + -- + -- 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/expected/sanity_check.out b/src/test/regress/expected/sanity_check.out new file mode 100644 index 111d24c..2c8ec11 100644 *** a/src/test/regress/expected/sanity_check.out --- b/src/test/regress/expected/sanity_check.out *************** pg_pltemplate|t *** 121,126 **** --- 121,127 ---- pg_proc|t pg_range|t pg_rewrite|t + pg_rowsecurity|t pg_seclabel|t pg_shdepend|t pg_shdescription|t diff --git a/src/test/regress/parallel_schedule b/src/test/regress/parallel_schedule new file mode 100644 index c0416f4..8d4cd05 100644 *** a/src/test/regress/parallel_schedule --- b/src/test/regress/parallel_schedule *************** test: select_into select_distinct select *** 83,89 **** # ---------- # Another group of parallel tests # ---------- ! test: privileges security_label collate matview lock replica_identity # ---------- # Another group of parallel tests --- 83,89 ---- # ---------- # Another group of parallel tests # ---------- ! 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 new file mode 100644 index 16a1905..9c45b8b 100644 *** a/src/test/regress/serial_schedule --- b/src/test/regress/serial_schedule *************** test: delete *** 96,101 **** --- 96,102 ---- 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 ...61e4ca8 . *** a/src/test/regress/sql/rowsecurity.sql --- b/src/test/regress/sql/rowsecurity.sql *************** *** 0 **** --- 1,355 ---- + -- + -- 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) ORDER BY did; + SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did; + + -- viewpoint from rls_regress_user2 + SET SESSION AUTHORIZATION rls_regress_user2; + SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; + SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did; + + EXPLAIN (costs off) SELECT * FROM document WHERE f_leak(dtitle); + EXPLAIN (costs off) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); + + -- only owner can change 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) ORDER BY did; + SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did; + + -- viewpoint from rls_regress_user2 again + SET SESSION AUTHORIZATION rls_regress_user2; + SELECT * FROM document WHERE f_leak(dtitle) ORDER BY did; + SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle) ORDER BY did; + + EXPLAIN (costs off) SELECT * FROM document WHERE f_leak(dtitle); + EXPLAIN (costs off) SELECT * FROM document NATURAL JOIN category WHERE f_leak(dtitle); + + -- interaction of FK/PK constraints + SET SESSION AUTHORIZATION rls_regress_user0; + ALTER TABLE category SET ROW SECURITY FOR ALL + TO (CASE WHEN current_user = 'rls_regress_user1' THEN cid IN (11, 33) + WHEN current_user = 'rls_regress_user2' THEN cid IN (22, 44) + ELSE false END); + + -- cannot delete PK referenced by invisible FK + SET SESSION AUTHORIZATION rls_regress_user1; + SELECT * FROM document d full outer join category c on d.cid = c.cid; + DELETE FROM category WHERE cid = 33; -- fails with FK violation + + -- cannot insert FK referencing invisible PK + SET SESSION AUTHORIZATION rls_regress_user2; + SELECT * FROM document d full outer join category c on d.cid = c.cid; + INSERT INTO document VALUES (10, 33, 1, current_user, 'hoge'); -- fail with FK violation + + -- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row + SET SESSION AUTHORIZATION rls_regress_user1; + INSERT INTO document VALUES ( 8, 44, 1, 'rls_regress_user_1', 'my third manga' ); -- Must fail with unique violation, revealing presence of did we can't see + SELECT * FROM document WHERE did = 8; -- and confirm we can't see it + + -- UNIQUE or PRIMARY KEY constraint violation DOES reveal presence of row + SET SESSION AUTHORIZATION rls_regress_user1; + INSERT INTO document VALUES ( 8, 44, 1, 'rls_regress_user_1', 'my third manga' ); -- Must fail with unique violation, revealing presence of did we can't see + SELECT * FROM document WHERE did = 8; -- and confirm we can't see it + + -- database superuser can bypass RLS policy + RESET SESSION AUTHORIZATION; + SELECT * FROM document; + SELECT * FROM category; + + -- + -- Table inheritance and RLS policy + -- + SET SESSION AUTHORIZATION rls_regress_user0; + + CREATE TABLE t1 (a int, junk1 text, b text) WITH OIDS; + ALTER TABLE t1 DROP COLUMN junk1; -- just a disturbing factor + GRANT ALL ON t1 TO public; + + COPY t1 FROM stdin WITH (oids); + 101 1 aaa + 102 2 bbb + 103 3 ccc + 104 4 ddd + \. + + CREATE TABLE t2 (c float) INHERITS (t1); + COPY t2 FROM stdin WITH (oids); + 201 1 abc 1.1 + 202 2 bcd 2.2 + 203 3 cde 3.3 + 204 4 def 4.4 + \. + + CREATE TABLE t3 (c text, b text, a int) WITH OIDS; + ALTER TABLE t3 INHERIT t1; + COPY t3(a,b,c) FROM stdin WITH (oids); + 301 1 xxx X + 302 2 yyy Y + 303 3 zzz Z + \. + + ALTER TABLE t1 SET ROW SECURITY FOR ALL TO (a % 2 = 0); -- be even number + ALTER TABLE t2 SET ROW SECURITY FOR ALL TO (a % 2 = 1); -- be odd number + + SELECT * FROM t1; + EXPLAIN (costs off) SELECT * FROM t1; + + SELECT * FROM t1 WHERE f_leak(b); + EXPLAIN (costs off) SELECT * FROM t1 WHERE f_leak(b); + + -- reference to system column + SELECT oid, * FROM t1; + EXPLAIN (costs off) SELECT * FROM t1; + + -- reference to whole-row reference + SELECT *,t1 FROM t1; + EXPLAIN (costs off) SELECT *,t1 FROM t1; + + -- for share/update lock + SELECT * FROM t1 FOR SHARE; + EXPLAIN (costs off) SELECT * FROM t1 FOR SHARE; + + SELECT * FROM t1 WHERE f_leak(b) FOR SHARE; + EXPLAIN (costs off) SELECT * FROM t1 WHERE f_leak(b) FOR SHARE; + + ----- Dependencies ----- + + CREATE TABLE dependee (x integer, y integer); + + CREATE TABLE dependent(x integer, y integer); + ALTER TABLE dependent SET ROW SECURITY FOR ALL TO (x = (SELECT d.x FROM dependee d WHERE d.y = y)); + + DROP TABLE dependee; -- Should fail without CASCADE due to dependency on row-security qual? + + EXPLAIN SELECT * FROM dependent; -- After drop, should be unqualified + + ----- RECURSION ---- + + -- + -- Simple recursion + -- + + CREATE TABLE rec1 (x integer, y integer); + ALTER TABLE rec1 + SET ROW SECURITY FOR ALL TO ( + x = (SELECT r.x FROM rec1 r WHERE y = r.y) + ); + + SELECT * FROM rec1; -- fail, direct recursion + + -- + -- Mutual recursion + -- + CREATE TABLE rec2 (a integer, b integer); + ALTER TABLE rec1 SET ROW SECURITY FOR ALL TO (x = (SELECT a FROM rec2 WHERE b = y)); + ALTER TABLE rec2 SET ROW SECURITY FOR ALL TO (a = (SELECT x FROM rec1 WHERE y = b)); + + SELECT * FROM rec1; -- fail, mutual recursion + + -- + -- Mutual recursion via views + -- + CREATE VIEW rec1v AS SELECT * FROM rec1; + CREATE VIEW rec2v AS SELECT * FROM rec2; + ALTER TABLE rec1 SET ROW SECURITY FOR ALL TO (x = (SELECT a FROM rec2v WHERE b = y)); + ALTER TABLE rec2 SET ROW SECURITY FOR ALL TO (a = (SELECT x FROM rec1v WHERE y = b)); + + SELECT * FROM rec1; -- fail, mutual recursion via views + + -- + -- Mutual recursion via .s.b views + -- + + DROP VIEW rec1v, rec2v CASCADE; + CREATE VIEW rec1v WITH (security_barrier) AS SELECT * FROM rec1; + CREATE VIEW rec2v WITH (security_barrier) AS SELECT * FROM rec2; + ALTER TABLE rec1 SET ROW SECURITY FOR ALL TO (x = (SELECT a FROM rec2v WHERE b = y)); + ALTER TABLE rec2 SET ROW SECURITY FOR ALL TO (a = (SELECT x FROM rec1v WHERE y = b)); + + SELECT * FROM rec1; -- fail, mutual recursion via s.b. views + + -- + -- 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;