Difference between revisions of "Row-security"

From PostgreSQL wiki
Jump to: navigation, search
(Design Target)
 
(26 intermediate revisions by 2 users not shown)
Line 1: Line 1:
= Row-Level Security =
+
'''This is a ''development page'' discussing work on a feature that is not yet included in PostgreSQL or work-in-progress discussion for a feature included in PostgreSQL in a later from. It is not a guide on using an existing feature.'''
  
This page is for discussing the implementation of Row-Level Security (RLS) in PostgreSQL.
+
'''Row Security was [https://www.postgresql.org/docs/current/static/ddl-rowsecurity.html committed to PostgreSQL 9.5]'''
  
RLS is a security feature which allows users to give access to a sub-set of data in their table to others.  Traditional RDBMS permission systems don't distinguish the individual rows in a table- GRANT SELECT on a table will allow a user to access all rows of that table.  With RLS, requirements such as "a manager can only view sensetive employee information for those employees who report to them." can be specified and enforced by the database.
+
= Row-Security =
  
== Jobs of access control feature ==
+
This page is for discussing the implementation of Row-Security (RS) in PostgreSQL. Row security was previously known as Row-level secuity (RLS). "Row-security" is known by names like "Virtual Private Database", "Fine-grained security", etc. It is the foundation upon which [[Label-based security]] and [[Mandatory Access Control]] (MAC) can be built.
  
According to the definition of ISO/IEC27001 (information security management), design target of information security feature is to ensure confidentiality, integrity and availability of information asset. In short, these are often called C.I.A.
+
Row-security is a security feature which allows users to give access to a sub-set of data in their table to others. Traditional RDBMS permission systems don't distinguish between individual rows in a table, so access is all-or-nothing. <tt>GRANT SELECT</tt> on a table will allow a user to access all rows of that table. Row-security ''supplements'' these with an additional layer of access controls on a per-row basis, so requirements such as "a manager can only view sensitive employee information for those employees who report to them" can be specified and enforced by the database.
Access control contributes towards both confidentiality and integrity. Access controls prevent unprivileged users from reading or writing information asset base on the rules which are configured in the access control system. Information or data itself does not have a particularly tangible form and therefore it must be stored in an object. Usually, access control features allow or prohibit users access to the object that contains the information or data. The intent of this feature (RLS) is to allow a more fine-grained control over the information inside of the objects.
 
For example, regular GRANT/REVOKE mechanisms control access on the specified database object according to the access control list, but they do not allow anything more granular.
 
  
== Design Target ==
+
== Commitfest, patches, and status ==
  
The purpose of row-level security feature is to prevent users (not means database roles, just users) from accessing unprivileged rows. Please note that "access" means two different direction of information (1) data read (rows => users) for confidentiality, and (2) data write (users => rows) for data integrity.
+
* [https://commitfest.postgresql.org/action/patch_view?id=1402 2014-01 commitfest entry]
Due to the nature of database access, it is the most straight-forwards way to describe the rule with regular qualifier style of WHERE clause; that is an expression returning a boolean value.
+
* [https://commitfest.postgresql.org/action/patch_view?id=1208 2013-11 commitfest entry (closed)]
 +
* [https://commitfest.postgresql.org/action/patch_view?id=874 2013-06 commitfest entry (closed)]
  
Overall, RLS prevents users from reading and writing rows that do not satisfy the rule (also know as a row-security policy). If we support per-command configuration, the row-security policy to be checked depends on the command. RLS design accepts individual row-security policy to be applied on SELECT, INSERT, UPDATE or DELETE. Relevant discussions are below.
+
There is [https://trello.com/b/VPGBlTYi/row-security a Trello board] tracking open items for row security, too.
  
We have some SQL commands that allow users to access database rows; SELECT, INSERT, UPDATE or DELETE. COPY TO/FROM is synonym of SELECT and INSERT from security perspective.
+
== Rationale ==
In case of SELECT (data read), what we should do is quite simple: any rows that don't match with the configured row-security policy shall be filtered out for unprivileged users.
 
In case of INSERT or UPDATE (data write), RLS prevents unprivileged rows from being written to the table, as if CHECK constraint performing.
 
In case of UPDATE or DELETE (data write), RLS prevents unprivileged rows from appearing as candidates of modification; that means row-security policy should be applied on the table scan stage. We also need to pay attention on potential information leaks using the leaky-view scenario below. So, UPDATE and DELETE shall also take row-security checks of SELECT command on table scanning stage.
 
  
TRUNCATE command performs as if DELETE, but much faster. It has its own permission separated from DELETE. So, we re-define meaning of TRUNCATE permission; that also implies to ignore row-security policy of DELETE.
+
According to the definition of ISO/IEC27001 (information security management) the design target of information security feature is to ensure confidentiality, integrity and availability of information asset. In short, these are often called C.I.A.
  
Row-security policy is set by table owner, using the following syntax.
+
Access control contributes towards both confidentiality and integrity. Access controls prevent unprivileged users from reading or writing information assets based on the rules which are configured in the access control system. Information or data itself does not have a particularly tangible form and therefore it must be stored in an object. Usually, access control features allow or prohibit users access to the object that contains the information or data. The intent of RS is to allow a more fine-grained control over the information inside of the objects.
If no "ON <command>" given, a unique security policy shall be applied on all the supported privileges (SELECT, INSERT, UPDATE and DELETE).
 
  
For example, an UPDATE command requires SELECT privilege to read a row, yet UPDATE privilege to change a row.
+
For example, regular <tt>GRANT/REVOKE</tt> mechanisms control access on the specified database object according to the access control list, but they do not allow anything more granular. This coarse access control can be a problem for multi-tenanted, hosting, and highly security sensitive environments.
 +
 
 +
== Use Cases ==
 +
 
 +
* PCI Compliant implementations
 +
* Classified Environments
 +
* Shared hosting / multi-tenant applications
 +
 
 +
== Design goals ==
 +
 
 +
The purpose of row-level security feature is to prevent users (not database roles, just users) from accessing unprivileged rows. Access refers to two different direction of information flow - (1) data read (rows => users) for confidentiality, and (2) data write (users => rows) for data integrity.
 +
 
 +
The simplest way to achieve this is to express these access constraints as SQL predicates like any other SQL <tt>WHERE</tt> clause. Such a predicate may be a subquery or even a C function to allow maximum flexibility.
 +
 
 +
Overall, RS prevents users from reading and writing rows that do not satisfy the row access predicate (also know as a row-security policy). If per-command configuration is to be supported then the row-security policy to be checked will depend on the command; on-list discussion has come to no firm conclusion as to whether per-command policy is appropriate.
 +
 
 +
RS seeks to make it as simple and convenient as possible to configure fine grained row security. It needs to be consistent, clear, and as easy as possible to get right for the user.
 +
 
 +
== How RS policy affects individual SQL commands ==
 +
 
 +
PostgreSQL has several SQL commands that allow users to directly access database rows:
 +
 
 +
* <tt>SELECT</tt>, <tt>COPY TO</tt>, DML with <tt>RETURNING</tt> clause
 +
* DML - <tt>INSERT</tt>, <tt>UPDATE</tt> and <tt>DELETE</tt>, <tt>COPY FROM</tt>
 +
 
 +
For <tt>SELECT</tt> (data read, no write) any rows that don't match with the configured row-security policy are be filtered out for non-RS-exempt users by applying the configured row-security predicate.
 +
 
 +
For <tt>UPDATE</tt> or <tt>DELETE</tt> (data write), RLS prevents unprivileged rows from appearing as candidates of modification; that means row-security policy should be applied on the table scan stage for these commands as well. DML may also require checks for write permissions.
 +
 
 +
* For <tt>UPDATE</tt> the read predicate is applied to limit which rows <tt>UPDATE</tt> can see to affect. The write predicate for <tt>UPDATE</tt> is applied for each tuple replaced by the <tt>UPDATE</tt>.
 +
 
 +
* For <tt>INSERT</tt> no read predicate need be applied as there is no read of the target table. The write predicate for <tt>INSERT</tt> is applied for each tuple to be inserted.
 +
 
 +
* For <tt>DELETE</tt> the read predicate is applied to limit which rows <tt>DELETE</tt> can see. The write predicate for <tt>DELETE</tt> is applied for each tuple to be deleted.
 +
 
 +
== Other row accesses ==
 +
 
 +
There are also a variety of indirect ways to see rows or parts of rows that must be protected against or explicitly documented:
 +
 
 +
* Predicate functions that leak information when they test a row. '''RS protects against this''' by enforcing the RS predicate before running any user predicates.
 +
 
 +
* <tt>CREATE INDEX</tt> on an expression/function or user-defined type - this can leak rows via malicious operators/functions. RS does not currently protect against this, but only the table owner or superuser can <tt>CREATE INDEX</tt> on a table. This is only relevant for MAC, so it's out of scope for first-generation RS but requires '''documentation'''.
 +
 
 +
* <tt>CREATE TRIGGER</tt> with a user-defined trigger function. This can leak information when the trigger is invoked via referential integrity constraints. Except for under MAC this only matters for non-table-owners granted <tt>TRIGGER</tt> rights on a table. Needs '''documentation''' that <tt>TRIGGER</tt> right allows RS bypass.
 +
 
 +
* <tt>TRUNCATE</tt>. Under MAC or for non-owners with <tt>TRUNCATE</tt> rights, the <tt>TRUNCATE</tt> statement allows a user to bypass <tt>DELETE</tt> access rules, though it provides no read access. Requires '''documentation'''.
 +
 
 +
* The <tt>FOREIGN KEY</tt> side channel. A user can probe for the existence of keys by testing to see whether or not DML operations succeed on tables with foreign keys to/from protected tables. Needs '''documentation''' - recommend that synthetic keys or no foreign keys at all be used for FK relationships between RS tables.
 +
 
 +
* <tt>FOREIGN KEY</tt> cascades - <tt>ON UPDATE CASCADE</tt>, <tt>ON DELETE CASCADE</tt> and <tt>ON DELETE SET NULL</tt>. These allow users to modify rows in RS-protected tables that they should not be able to. Needs '''documentation''' - don't set cascades on FKs between RS tables unless this is the intended effect.
 +
 
 +
* <tt>pg_catalog.pg_statistic</tt> holds histograms containing most-frequent values from columns. It is only accessible to the superuser or when access is explicitly <tt>GRANT</tt>ed, so it is not a concern unless MAC is in use. Needs '''documentation'''.
 +
 
 +
* <tt>ON UPDATE CASCADE</tt>, <tt>ON DELETE CASCADE</tt> and <tt>ON DELETE SET NULL</tt> will run triggers and rules defined on the target table. These may be user-defined and <tt>must</tt> be affected by row-security policy, even though the foreign key check its self must ''not''. '''TODO'''.
 +
 
 +
== Row security user interface ==
 +
 
 +
=== Privileges for row security ===
 +
 
 +
To protect the superuser from being exploited by malicious user-defined predicate functions, row-security policy is not enforced on users with the <tt>OVERRIDE ROW SECURITY</tt> right (by default, the superuser as well as all foreign key, unique, and exclusion constraint checks). Row-security extensions that inject policy at the C level may choose to ignore <tt>OVERRIDE ROW SECURITY</tt>; they're considered to be trusted. (There is not currently a database privilege named <tt>OVERRIDE ROW SECURITY</tt>, it's implicit, but such a <tt>GRANT</tt>able right should be added in future).
 +
 
 +
Only users with <tt>SET ROW SECURITY</tt> rights may apply row security rights to a table. The table owner or the superuser may set row security on a table. Like <tt>OVERRIDE ROW SECURITY</tt> this is currently an implicit right.
 +
 
 +
Row-level security does not override existing <tt>GRANT</tt> permissions, it adds a finer grained level of control. For example, setting <tt>ROW SECURITY FOR SELECT</tt> to allow a given user to give rows would only give that user access if the user ''also'' has <tt>SELECT</tt> privileges on the column or table in question.
 +
 
 +
=== SQL commands for setting row security ===
 +
 
 +
Row-security policy is set by a user with <tt>ROW SECURITY</tt> rights (by default, the table owner and superuser), using the following syntax:
 +
 
 +
''(This segment of the design is still in flux)''
  
 
  ALTER TABLE <relname> SET ROW SECURITY FOR <privilege> TO (<expression>);
 
  ALTER TABLE <relname> SET ROW SECURITY FOR <privilege> TO (<expression>);
Line 36: Line 100:
 
<privilege> can be one of: ALL, SELECT, INSERT, UPDATE, DELETE.  The initial implementation may only support 'ALL'.
 
<privilege> can be one of: ALL, SELECT, INSERT, UPDATE, DELETE.  The initial implementation may only support 'ALL'.
  
'ALL' would simply define the same policy for all the commands.  Later commands which update only a single command would replace the policy for that command only, leaving the policy for the other commands to what was set from the initial 'ALL'.  The same goes for a RESET. Listing the policies in a table form would look like:
+
'ALL' would simply define the same policy for all the commands.  Later commands which update only a single command would replace the policy for that command only, leaving the policy for the other commands to what was set from the initial 'ALL'.  The same goes for a RESET.
 +
 
 +
If no row security policy has been set for a given command type (or the security policy has been reset for that command type), no row-level security will apply.
 +
 
 +
== Issues & discussion ==
 +
 
 +
=== Implementation detail ===
 +
 
 +
A secure way to run foreign key checks and cascades as RLS-exempt without running triggers and rules with user-defined code as RLS exempt is needed.
 +
 
 +
 
 +
=== Table inheritance ===
 +
 
 +
We need to decide how RS constraints apply for inheritance. Most constraints and security settings affect only the target table directly, not parent/children.
  
ALTER TABLE mytable SET ROW SECURITY FOR ALL TO (where user = 1);
+
For implementation reasons we might need to apply RS predicates on the parent to child tables too. This could result in different rows seen when querying parent vs child tables. Proposal:
  
{| class="wikitable" style="text-align:center; width:200px; height:200px;" border="1"
+
* Child predicates are not applied to queries on parent. The child predicate might refer to rows only present in the child and would not make sense on all rows.
|+ Example
 
|-
 
! table
 
! command
 
! expression
 
|-
 
| mytable || select || user = 1
 
|-
 
| mytable || insert || user = 1
 
|-
 
| mytable || update || user = 1
 
|-
 
| mytable || delete || user = 1
 
|}
 
  
If it was followed by:
+
* Parent predicate *is* applied to child tables when queried via parent. It is *not* applied when child tables are queried directly; the child predicate is used instead.
  
ALTER TABLE mytable SET ROW SECURITY FOR SELECT TO (where user = 2);
+
This ensures that two predicates are not added in any situation.
the result would be:
 
  
 +
A very detailed discussion on this topic took place on the mailing list; see [http://www.postgresql.org/message-id/52EA01C3.70804@2ndquadrant.com the thread "Prohibit row security + inheritance in 9.4"].
  
{| class="wikitable" style="text-align:center; width:200px; height:200px;" border="1"
+
=== Dump and reload ===
|+ Example
 
|-
 
! table
 
! command
 
! expression
 
|-
 
| mytable || select || user = 2
 
|-
 
| mytable || insert || user = 1
 
|-
 
| mytable || update || user = 1
 
|-
 
| mytable || delete || user = 1
 
|}
 
  
and so on.  If no row security policy has been set for a given command type (or the security policy has been reset for that command type), no row-level security would apply and regular GRANT permissions for the operation would be used.  Note that row-level security does not override existing GRANT permissions but provide a more fine-grained control. For example, setting 'ROW SECURITY FOR SELECT' to allow a given user to give rows would only give that user access if the user ALSO has 'SELECT' privileges on the column or table in question.
+
Row-security policies must be dumped and reloaded without concern for the <tt>search_path</tt> setting at the time. <tt>pg_dump</tt> will need to be enhanced to use a new variant of <tt>pg_get_expr</tt> that fully schema-qualifies expressions when extracting row-security expressions from <tt>pg_catalog.pg_rowsecurity</tt>. '''TODO'''.
  
Superuser bypass row-security policy (1) to avoid Trojan-horse attack by table owner (2) to ensure consistent view for database backup, however, row-security policy injected by extension (such as sepgsql) is exception.
+
It must be possible to take a consistent backup that ignores row-security policy using <tt>pg_dump</tt> while still constraining <tt>COPY</tt> otherwise.
  
== Issues & discussion ==
+
<tt>pg_dump</tt> must not run untrusted row-security policies defined by normal users. However, it is also desirable to allow dumps to be run without superuser rights (we already have too much that is superuser only). So we need a row-security exempt right.
  
 
=== Per-command security policy ===
 
=== Per-command security policy ===
Asymmetric row-security policy may cause unexpected information leaks using UPDATE or DELETE command with RETURNING clause or leaky functions in WHERE clause.
+
 
It is an idea to append row-security policy of SELECT when executor scan the result relation. It ensures the rows to be modified are also visible to the user executing the UPDATE.
+
A challenge with per-command policy is that RETURNING, e.g. UPDATE ... RETURNING is also effectively a SELECT. So we really need one policy to control what may be ''read'' and another to control what may be ''written''. It is desirable to have write policies separately for each command; e.g. you may want to permit insertion of a different set of rows than you permit update or delete on.
One other idea was to enforce a unique policy for all commands, however, it has a problematic scenario when user wants to define INSERT-only relation.
+
 
 +
Asymmetric row-security policy may cause unexpected information leaks using <tt>UPDATE</tt> or <tt>DELETE</tt> commands with <tt>RETURNING</tt> clauses or leaky functions in <tt>WHERE</tt> clauses. For this reason, read policy must be enforced on table scans done by DML unless the DML (a) has no <tt>RETURNING</tt> clause and (b) invokes only <tt>LEAKPROOF</tt> functions. <tt>UPDATE</tt> and <tt>DELETE</tt> should not affect a different set of rows based on attributes of functions in the <tt>WHERE</tt> clause or the presence/absence of a <tt>RETURNING</tt> clause, so at this time the row security read policy must always be applied. If you want to allow deletion of / updating of rows that aren't visible in the read policy, you need to use <tt>SECURITY DEFINER</tt> functions or other bypass methods.
 +
 
 +
Note that a read policy applied to <tt>UPDATE</tt> doesn't constrain what the ''new'' values of are, only which ''old'' values are affected. See [[#Writer-side checks]] below for that.
 +
 
 +
Having a single policy for all commands (as implemented in the current row security patch) prevents the implementation of the classic "classification" security model, where you can only read rows of lesser or equal secrecy to the current session, but can only write rows with the ''same'' classification and can only delete rows with ''unclassified'' classification.
  
 
=== Writer-side checks ===
 
=== Writer-side checks ===
Now we can implement writer-side checks on INSERT or UPDATE command using before-row triggers.
 
On the other hand, it makes users to synchronize the configuration of RLS with triggers of this checks. One model case to solve this concern is implementation of FK constraints; that automatically defines before-row triggers that checks newer version of tuples to be inserted or updated.
 
  
=== Table statistics ===
+
Users can implement writer-side checks to constrain the ''new'' tuples on <tt>INSERT</tt>, <tt>UPDATE</tt> or <tt>DELETE</tt> commands using before-row triggers with no additional core code. However, this makes it harder to keep the policy in sync between the write-check and read-check policies.  
pg_statistic hold some example of values on the table being analyzed.
 
Right now, we have no way to prevent users to see collected values on statistical board.
 
An idea is to mask the field if the relation has RLS policy.
 
  
=== Minimal core feature set ===
+
For that reason the plan is to automatically create <tt>BEFORE INSERT OR UPDATE OR DELETE ... FOR EACH ROW</tt> triggers on RS-constrained tables in much the same way as is currently done for foreign keys. This will require additional dependency management too. '''TODO'''
* Checks are only applied on table scanning. If writer-side checks are required, users '''can''' do that using triggers, even though it takes complex setting.
 
* A unique security policy can be configurable on a table. Even though RLS design allows per-command policy, we need to investigate whether asymmetric policy is harmless.
 
  
= Previous discussion in 2010 =
+
== Minimal core feature set ==
  
== Prerequisites ==
+
* Checks are only applied on table scanning. If writer-side checks are required, users '''can''' do that using triggers, even though it takes complex settings and is hard to maintain.
* Before we can try to tackle row-level security generally, using labels or not, we need to fix the issues with data leaks in views.
+
* A unique security policy can be configurable on a table. Even though RLS design allows per-command policy, we need to investigate whether asymmetric policy is harmless.
 +
* Foreign key exemption for checks
 +
* Superuser exemption
 +
* Proper dependency tracking
 +
* Friendly toward future enhancements like policy groups
  
* Related topics in -hackers
+
== Previous RLS discussions ==
** [http://archives.postgresql.org/pgsql-hackers/2010-06/msg00014.php Thread to discuss the way to tackle this problem]
 
** [http://archives.postgresql.org/pgsql-hackers/2009-10/msg01346.php Thread on information leak due to views]
 
** [http://archives.postgresql.org/pgsql-hackers/2009-02/msg00861.php Earlier discussion of view leak]
 
  
=== Issue: A leaky VIEWs for RLS ===
+
[[Row security history]]
  
* This issue is summarized as: an untrusted user can define a function which stores all information it is presented, then query a view using that function in a way which will convince the planner to send every row of the underlying table to the function, thus leaking data in the table which the view was intended to prevent.
+
== Articles/Documentation of existing RLS implementations ==
  
In [http://archives.postgresql.org/pgsql-hackers/2010-06/msg00014.php this message], KaiGai pointed out we have two different causes of the problem, but both of them can cause same information leaks.
+
Other vendors support:
* [1] Unexpected order to evaluate qualifiers on a certain scan plan
 
** When a scan-plan has multiple qualifiers to filter scanned tuples, the optimizer sort the qualifiers based on their estimated cost to execute. If an exogenetic function has smaller cost than other qualifiers come from view definition, the exogenetic function shall be evaluated earlier than others, then contents of invisible tuples may be provided to malicious user-defined functions.
 
** It is reordered at <tt>order_qual_clauses()</tt>.
 
* [2] Unexpected qualifier distributions into inside of join loop
 
** When planner makes a scan plan, it tries to distribute qualifiers of scans into smaller unit as possible as it can. For example, if a function takes arguments only come from a certain table, it will be distributed into scan plan of the table, not outside of the join.
 
** When a view contains JOIN clause between A and B, user can reference the view with his own WHERE clause. If a clause takes arguments depending on only A, the planner distribute the clause into the scan plan of A. In the reault, tuples to be filtered out may become visible to user defined functions.
 
** It is distributed at <tt>distribute_qual_to_rels()</tt>.
 
  
=== Discussion ===
+
* multiple row-security policies on a table that are turned on and off; and
 +
* row-security policy groups to turn sets of policies on and off, associate users and policies;
 +
* label-based security, auto columns;
 +
* Delegateable right for exemption to row security policy
 +
* Delegateable right for setting row security policy
 +
* Row security policy on table indexes
 +
* Secure session variables for fast row security checks
  
* At the point [2], if we prevent all exogenetic functions to push down into join loops, it will make unignorable performance degradation, although the view might not be  intended to security purpose.
+
=== Oracle ===
* It needs a way to provide a hint whether the view is defined for security purpose, or not.
 
** Tom Lane [http://archives.postgresql.org/pgsql-hackers/2010-06/msg00033.php suggested] <tt>CREATE SECURITY VIEW AS ...</tt> statement.
 
** It was not concluded which is the default. Security view? Regular view?
 
** How about a GUC option to specify the default? NACKed.
 
* In addition, Robert Haas [http://archives.postgresql.org/pgsql-hackers/2010-06/msg00053.php suggested] to test privileges of users whether they have privileges to reference underlaying tables without vires, or not. If available, it is eventually harmless even if user defined functions are evaluated within join loop.
 
** Here was one opposition because this check will be applied on planner stage, not execution stage.
 
* KaiGai submitted a [http://archives.postgresql.org/pgsql-hackers/2010-06/msg00150.php proof of concept patch] that prevents to push down exogenetic functions into security views.
 
  
* At the point [1], we don't have any active discussions yet.
+
* [http://docs.oracle.com/cd/B28359_01/network.111/b28531/vpd.htm Oracle VPD documentation] (also known as "fine-grained security")
 +
* [http://docs.oracle.com/cd/E11882_01/network.112/e10745.pdf Oracle Label-security documentation]
  
== Discussions of RLS in PG ==
 
* [http://archives.postgresql.org/pgsql-hackers/2009-12/msg01095.php Current thread on -hackers]
 
* [http://it.toolbox.com/blogs/database-soup/thinking-about-row-level-security-part-1-30732 Josh Berkus on RLS in PG, Part 1]
 
* [http://it.toolbox.com/blogs/database-soup/thinking-about-row-level-security-part-2-30757 Josh Berkus on RLS in PG, Part 2]
 
* [[SEPostgreSQL_Specifications Specifications for SEPostgreSQL, includes RLS]]
 
 
== Articles/Documentation of existing RLS implementations ==
 
 
* [http://www.securityfocus.com/infocus/1743 Oracle RLS Article, Part 1]
 
* [http://www.securityfocus.com/infocus/1743 Oracle RLS Article, Part 1]
 
* [http://www.securityfocus.com/infocus/1744 Oracle RLS Article, Part 2]
 
* [http://www.securityfocus.com/infocus/1744 Oracle RLS Article, Part 2]
 
* [http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Database/ Oracle RLS and VPD Article]
 
* [http://www.devshed.com/c/a/Oracle/RowLevel-Security-with-Virtual-Private-Database/ Oracle RLS and VPD Article]
 +
 +
=== SQL Server ===
 +
 
* [http://msdn.microsoft.com/en-us/library/cc966395.aspx SQL Server RLS with Classified Systems]
 
* [http://msdn.microsoft.com/en-us/library/cc966395.aspx SQL Server RLS with Classified Systems]
 +
 +
=== DB2 ===
 +
 
* [http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.admin/db2z_implementmls4row.htm IBM/DB2 RLS Documentation]
 
* [http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/com.ibm.db29.doc.admin/db2z_implementmls4row.htm IBM/DB2 RLS Documentation]
* Other?
 
  
== Use Cases ==
+
=== Teradata ===
* PCI Compliant implementations
+
 
* Classified Environments
+
* [http://www.info.teradata.com/edownload.cfm?itemid=112860002 Teradata 14 Security Administration] (for Teradata row-level security, a label based implementation)
* Other?
 
  
 
== Components of an implementation ==
 
== Components of an implementation ==
* Overview
+
 
** Allow the query to be modified, prior to being passed to the planner, in such a way that the rows returned will be those the user is authorized for
+
* Record predicates against relations (catalog changes for pg_rowsecurity)
** This depends on being able to tell the planner that this filter must be done, in some way, prior to user-defined functions being called
+
* Grammar and utility function changes to control predicate settings
** This issue is related to the VIEW leak described above.  Once that issue has been resolved, this should be pretty straight-forward to implement
+
* Apply predicates during query planning
* Grammar for catalog updates/changes; user-interface for specifying how RLS is to be done
+
* Ensure predicates are run *before* any untrusted user code to protect against the malicious-predicate leak
* Catalog changes for storing RLS information
+
* Protect against RS predicates being run as another user ID to escalate privileges
* Storage - Could this just be a regular column in the table?  It would be good to avoid changing the header or creating a system column for this.
+
* Set dependencies on columns, functions, etc in pg_depend
** We would need to track, in some fashion, the "security" column in the catalog, perhaps as a flag on pg_attribute, or a 'security_attnum' in pg_class, etc.
+
* Solve the portal uid change problem, replan problem
* Planner updates to enforce the filter based on RLS- this can't be done till after we deal with the issue with VIEWs
+
 
* Executor changes may not be required..  but how to deal with stored plans?  Use invalidation if anything changes with regard to RLS?
+
For label based security:
* Other?
+
 
 +
* Enforce predicates on superuser
 +
* Provide facilities for complete logical dumps
 +
* Auto-create hidden columns for row labels
 +
* Track which col(s) are the security columns in the catalog
  
 
== Issues ==
 
== Issues ==
 +
 
* Covert Channel
 
* Covert Channel
 
** If we try to insert a value which violates a PK constraint, we can assume existence of invisible PK from the error.
 
** If we try to insert a value which violates a PK constraint, we can assume existence of invisible PK from the error.
 
** The same issue exists in a Foreign Key relationship situation
 
** The same issue exists in a Foreign Key relationship situation
** Other databases with row-level security don't address this issue, so it's unclear if we really need to (reference?)
+
** Other databases with row-level security don't address this issue, so it's unclear if we really need to. See Oracle VPD docs.
** In any case, this isn't something we need to address in our initial RLS implementation
+
** There's no sane and consistent model to make FKs make sense between tables with different labels / row security policies.
 
 
* Order to evaluate row level policy
 
** Addressed above with regard to views, once we solve that, this will be handled
 
  
 
* TRUNCATE statement
 
* TRUNCATE statement
 
** TRUNCATE is expected to be fast.
 
** TRUNCATE is expected to be fast.
 +
** TRUNCATE must not have side effects.
 
** If the user does not have rights to remove all rows from the table regardless of row-level policy, then any TRUNCATE must be denied.
 
** If the user does not have rights to remove all rows from the table regardless of row-level policy, then any TRUNCATE must be denied.
** Turning TRUNCATE (a PostgreSQL extension which is not in the SQL spec anyway) into a DELETE FROM doesn't make sense.
+
** Turning TRUNCATE (a PostgreSQL extension which is not in the SQL spec anyway) into a DELETE FROM doesn't make sense, and multi-table TRUNCATE has no equivalent in DELETE.
 
+
** So TRUNCATE is just honoured, treating the right to TRUNCATE as ability to bypass RLS. TRUNCATE doesn't leak data so this works.
* COPY TO statement
 
** COPY can just be reformed into a COPY statement with a query being used instead, eg:
 
** <nowiki>COPY tblname TO xxx;</nowiki> can be replaced by <nowiki>COPY (SELECT * FROM tblname) TO xxx;</nowiki>.
 
  
 
* Table inheritance
 
* Table inheritance
Line 194: Line 232:
 
** idea: Also copy row-level policies from the parent tables.
 
** idea: Also copy row-level policies from the parent tables.
  
* Foreign Key constraint
+
== Roadmap ==
** Adding multiple modes for FK should be a separate, follow-on patch, it doesn't need to be in the initial version.  
+
 
** idea: We can provide two modes: The first is filter-mode, the second is abort-mode.
+
Current roadmap is as follows:
*** filter-mode: A normal mode. Row-level policy is evaluated earlier than user given condition, and returns false, if violated.
+
 
*** abort-mode: A special internal mode. Row-level policy is evaluated after all the condition, and raises an error, if violated. The condition shall be evaluated earlier than row-level policy, the query has to be trusted. Such as queries in FK constraint.
+
* <strike>[http://www.postgresql.org/message-id/CADyhKSWGtZqpsXtF7_q2FvKRvX6RqW+xv7VmxNmj4gubSBoo-g@mail.gmail.com RLS implemented directly in 9.3]. Deferred due to the need to fix the leaky view problem.</strike>
 +
 
 +
* <tt>security_barrier</tt> views - '''DONE''', see [https://github.com/postgres/postgres/commit/0e4611c0234d89e288a53351f775c59522baed7c commit 0e4611c0234d89e288a53351f775c59522baed7c]
 +
 
 +
* <tt>LEAKPROOF</tt> - '''DONE''', see [https://github.com/postgres/postgres/commit/cd30728fb2ed7c367d545fc14ab850b5fa2a4850 commit cd30728fb2ed7c367d545fc14ab850b5fa2a4850]
 +
 
 +
* <strike>[http://www.postgresql.org/message-id/CADyhKSXFzQBXQoNwiKdN9xxoVw7Zqz9gmF64Sry57QKduSFwSQ@mail.gmail.com RLS implementation directly against 9.4]</strike>. Deferred due to need to reduce overlap with updatable views, deal with the portal user id change problem, fix foreign key enforcement, and over some design / abstraction level issues.
 +
 
 +
* Implement [[Automatically updatable security barrier views|automatically updatable <tt>security_barrier</tt> views]] (target: 9.4). Might involve adding support for <tt>UPDATE</tt>, <tt>DELETE</tt> on a subquery without flattening. See [[UPDATE or DELETE on subquery]].
 +
 
 +
* Patch portal implementation (cursors, refcursors, etc) so that user id and security context is carried with portal, saved/restored when portal is entered/left during access.
 +
 
 +
* Support re-running query rewriter during re-planning of RS views after security context change to handle changes to/from RS-exempt user. Invalidate cached plans involving RS tables after user id change or RS predicate change.
 +
 
 +
* Add catalog tables for row security, SQL syntax for configuring row security
 +
 
 +
* Implement secure session variables, document their use for row security
  
== Considerations ==
+
* Implement mandatory access control and SELinux integration
* Performance
 
** With RLS
 
** Without RLS
 
** The page.16 of [http://sepgsql.googlecode.com/files/JLS2009-KaiGai-LAPP_SELinux.pdf LAPP/SELinux slides] shows a pgbench comparison between pgsql-8.4.1 vs SE-PostgreSQL with RLS.
 
*** It has 2-4% of performance tradeoff depending on database size.
 
*** Note that SE-PostgreSQL implemented RLS in a different way than what is being proposed here; our goal is to get RLS support into core PG
 
* Integration with external security manager (eg: SELinux, SMACK)
 
** This will not be included in the initial support of RLS (unless we happen to get support for external security managers implemented first in PG)
 
  
 
[[Category:SELinux]]
 
[[Category:SELinux]]

Latest revision as of 07:53, 4 August 2016

This is a development page discussing work on a feature that is not yet included in PostgreSQL or work-in-progress discussion for a feature included in PostgreSQL in a later from. It is not a guide on using an existing feature.

Row Security was committed to PostgreSQL 9.5

Row-Security

This page is for discussing the implementation of Row-Security (RS) in PostgreSQL. Row security was previously known as Row-level secuity (RLS). "Row-security" is known by names like "Virtual Private Database", "Fine-grained security", etc. It is the foundation upon which Label-based security and Mandatory Access Control (MAC) can be built.

Row-security is a security feature which allows users to give access to a sub-set of data in their table to others. Traditional RDBMS permission systems don't distinguish between individual rows in a table, so access is all-or-nothing. GRANT SELECT on a table will allow a user to access all rows of that table. Row-security supplements these with an additional layer of access controls on a per-row basis, so requirements such as "a manager can only view sensitive employee information for those employees who report to them" can be specified and enforced by the database.

Commitfest, patches, and status

There is a Trello board tracking open items for row security, too.

Rationale

According to the definition of ISO/IEC27001 (information security management) the design target of information security feature is to ensure confidentiality, integrity and availability of information asset. In short, these are often called C.I.A.

Access control contributes towards both confidentiality and integrity. Access controls prevent unprivileged users from reading or writing information assets based on the rules which are configured in the access control system. Information or data itself does not have a particularly tangible form and therefore it must be stored in an object. Usually, access control features allow or prohibit users access to the object that contains the information or data. The intent of RS is to allow a more fine-grained control over the information inside of the objects.

For example, regular GRANT/REVOKE mechanisms control access on the specified database object according to the access control list, but they do not allow anything more granular. This coarse access control can be a problem for multi-tenanted, hosting, and highly security sensitive environments.

Use Cases

  • PCI Compliant implementations
  • Classified Environments
  • Shared hosting / multi-tenant applications

Design goals

The purpose of row-level security feature is to prevent users (not database roles, just users) from accessing unprivileged rows. Access refers to two different direction of information flow - (1) data read (rows => users) for confidentiality, and (2) data write (users => rows) for data integrity.

The simplest way to achieve this is to express these access constraints as SQL predicates like any other SQL WHERE clause. Such a predicate may be a subquery or even a C function to allow maximum flexibility.

Overall, RS prevents users from reading and writing rows that do not satisfy the row access predicate (also know as a row-security policy). If per-command configuration is to be supported then the row-security policy to be checked will depend on the command; on-list discussion has come to no firm conclusion as to whether per-command policy is appropriate.

RS seeks to make it as simple and convenient as possible to configure fine grained row security. It needs to be consistent, clear, and as easy as possible to get right for the user.

How RS policy affects individual SQL commands

PostgreSQL has several SQL commands that allow users to directly access database rows:

  • SELECT, COPY TO, DML with RETURNING clause
  • DML - INSERT, UPDATE and DELETE, COPY FROM

For SELECT (data read, no write) any rows that don't match with the configured row-security policy are be filtered out for non-RS-exempt users by applying the configured row-security predicate.

For UPDATE or DELETE (data write), RLS prevents unprivileged rows from appearing as candidates of modification; that means row-security policy should be applied on the table scan stage for these commands as well. DML may also require checks for write permissions.

  • For UPDATE the read predicate is applied to limit which rows UPDATE can see to affect. The write predicate for UPDATE is applied for each tuple replaced by the UPDATE.
  • For INSERT no read predicate need be applied as there is no read of the target table. The write predicate for INSERT is applied for each tuple to be inserted.
  • For DELETE the read predicate is applied to limit which rows DELETE can see. The write predicate for DELETE is applied for each tuple to be deleted.

Other row accesses

There are also a variety of indirect ways to see rows or parts of rows that must be protected against or explicitly documented:

  • Predicate functions that leak information when they test a row. RS protects against this by enforcing the RS predicate before running any user predicates.
  • CREATE INDEX on an expression/function or user-defined type - this can leak rows via malicious operators/functions. RS does not currently protect against this, but only the table owner or superuser can CREATE INDEX on a table. This is only relevant for MAC, so it's out of scope for first-generation RS but requires documentation.
  • CREATE TRIGGER with a user-defined trigger function. This can leak information when the trigger is invoked via referential integrity constraints. Except for under MAC this only matters for non-table-owners granted TRIGGER rights on a table. Needs documentation that TRIGGER right allows RS bypass.
  • TRUNCATE. Under MAC or for non-owners with TRUNCATE rights, the TRUNCATE statement allows a user to bypass DELETE access rules, though it provides no read access. Requires documentation.
  • The FOREIGN KEY side channel. A user can probe for the existence of keys by testing to see whether or not DML operations succeed on tables with foreign keys to/from protected tables. Needs documentation - recommend that synthetic keys or no foreign keys at all be used for FK relationships between RS tables.
  • FOREIGN KEY cascades - ON UPDATE CASCADE, ON DELETE CASCADE and ON DELETE SET NULL. These allow users to modify rows in RS-protected tables that they should not be able to. Needs documentation - don't set cascades on FKs between RS tables unless this is the intended effect.
  • pg_catalog.pg_statistic holds histograms containing most-frequent values from columns. It is only accessible to the superuser or when access is explicitly GRANTed, so it is not a concern unless MAC is in use. Needs documentation.
  • ON UPDATE CASCADE, ON DELETE CASCADE and ON DELETE SET NULL will run triggers and rules defined on the target table. These may be user-defined and must be affected by row-security policy, even though the foreign key check its self must not. TODO.

Row security user interface

Privileges for row security

To protect the superuser from being exploited by malicious user-defined predicate functions, row-security policy is not enforced on users with the OVERRIDE ROW SECURITY right (by default, the superuser as well as all foreign key, unique, and exclusion constraint checks). Row-security extensions that inject policy at the C level may choose to ignore OVERRIDE ROW SECURITY; they're considered to be trusted. (There is not currently a database privilege named OVERRIDE ROW SECURITY, it's implicit, but such a GRANTable right should be added in future).

Only users with SET ROW SECURITY rights may apply row security rights to a table. The table owner or the superuser may set row security on a table. Like OVERRIDE ROW SECURITY this is currently an implicit right.

Row-level security does not override existing GRANT permissions, it adds a finer grained level of control. For example, setting ROW SECURITY FOR SELECT to allow a given user to give rows would only give that user access if the user also has SELECT privileges on the column or table in question.

SQL commands for setting row security

Row-security policy is set by a user with ROW SECURITY rights (by default, the table owner and superuser), using the following syntax:

(This segment of the design is still in flux)

ALTER TABLE <relname> SET ROW SECURITY FOR <privilege> TO (<expression>);

ALTER TABLE <relname> RESET ROW SECURITY FOR <privilege>;

<privilege> can be one of: ALL, SELECT, INSERT, UPDATE, DELETE. The initial implementation may only support 'ALL'.

'ALL' would simply define the same policy for all the commands. Later commands which update only a single command would replace the policy for that command only, leaving the policy for the other commands to what was set from the initial 'ALL'. The same goes for a RESET.

If no row security policy has been set for a given command type (or the security policy has been reset for that command type), no row-level security will apply.

Issues & discussion

Implementation detail

A secure way to run foreign key checks and cascades as RLS-exempt without running triggers and rules with user-defined code as RLS exempt is needed.


Table inheritance

We need to decide how RS constraints apply for inheritance. Most constraints and security settings affect only the target table directly, not parent/children.

For implementation reasons we might need to apply RS predicates on the parent to child tables too. This could result in different rows seen when querying parent vs child tables. Proposal:

  • Child predicates are not applied to queries on parent. The child predicate might refer to rows only present in the child and would not make sense on all rows.
  • Parent predicate *is* applied to child tables when queried via parent. It is *not* applied when child tables are queried directly; the child predicate is used instead.

This ensures that two predicates are not added in any situation.

A very detailed discussion on this topic took place on the mailing list; see the thread "Prohibit row security + inheritance in 9.4".

Dump and reload

Row-security policies must be dumped and reloaded without concern for the search_path setting at the time. pg_dump will need to be enhanced to use a new variant of pg_get_expr that fully schema-qualifies expressions when extracting row-security expressions from pg_catalog.pg_rowsecurity. TODO.

It must be possible to take a consistent backup that ignores row-security policy using pg_dump while still constraining COPY otherwise.

pg_dump must not run untrusted row-security policies defined by normal users. However, it is also desirable to allow dumps to be run without superuser rights (we already have too much that is superuser only). So we need a row-security exempt right.

Per-command security policy

A challenge with per-command policy is that RETURNING, e.g. UPDATE ... RETURNING is also effectively a SELECT. So we really need one policy to control what may be read and another to control what may be written. It is desirable to have write policies separately for each command; e.g. you may want to permit insertion of a different set of rows than you permit update or delete on.

Asymmetric row-security policy may cause unexpected information leaks using UPDATE or DELETE commands with RETURNING clauses or leaky functions in WHERE clauses. For this reason, read policy must be enforced on table scans done by DML unless the DML (a) has no RETURNING clause and (b) invokes only LEAKPROOF functions. UPDATE and DELETE should not affect a different set of rows based on attributes of functions in the WHERE clause or the presence/absence of a RETURNING clause, so at this time the row security read policy must always be applied. If you want to allow deletion of / updating of rows that aren't visible in the read policy, you need to use SECURITY DEFINER functions or other bypass methods.

Note that a read policy applied to UPDATE doesn't constrain what the new values of are, only which old values are affected. See #Writer-side checks below for that.

Having a single policy for all commands (as implemented in the current row security patch) prevents the implementation of the classic "classification" security model, where you can only read rows of lesser or equal secrecy to the current session, but can only write rows with the same classification and can only delete rows with unclassified classification.

Writer-side checks

Users can implement writer-side checks to constrain the new tuples on INSERT, UPDATE or DELETE commands using before-row triggers with no additional core code. However, this makes it harder to keep the policy in sync between the write-check and read-check policies.

For that reason the plan is to automatically create BEFORE INSERT OR UPDATE OR DELETE ... FOR EACH ROW triggers on RS-constrained tables in much the same way as is currently done for foreign keys. This will require additional dependency management too. TODO

Minimal core feature set

  • Checks are only applied on table scanning. If writer-side checks are required, users can do that using triggers, even though it takes complex settings and is hard to maintain.
  • A unique security policy can be configurable on a table. Even though RLS design allows per-command policy, we need to investigate whether asymmetric policy is harmless.
  • Foreign key exemption for checks
  • Superuser exemption
  • Proper dependency tracking
  • Friendly toward future enhancements like policy groups

Previous RLS discussions

Row security history

Articles/Documentation of existing RLS implementations

Other vendors support:

  • multiple row-security policies on a table that are turned on and off; and
  • row-security policy groups to turn sets of policies on and off, associate users and policies;
  • label-based security, auto columns;
  • Delegateable right for exemption to row security policy
  • Delegateable right for setting row security policy
  • Row security policy on table indexes
  • Secure session variables for fast row security checks

Oracle

SQL Server

DB2

Teradata

Components of an implementation

  • Record predicates against relations (catalog changes for pg_rowsecurity)
  • Grammar and utility function changes to control predicate settings
  • Apply predicates during query planning
  • Ensure predicates are run *before* any untrusted user code to protect against the malicious-predicate leak
  • Protect against RS predicates being run as another user ID to escalate privileges
  • Set dependencies on columns, functions, etc in pg_depend
  • Solve the portal uid change problem, replan problem

For label based security:

  • Enforce predicates on superuser
  • Provide facilities for complete logical dumps
  • Auto-create hidden columns for row labels
  • Track which col(s) are the security columns in the catalog

Issues

  • Covert Channel
    • If we try to insert a value which violates a PK constraint, we can assume existence of invisible PK from the error.
    • The same issue exists in a Foreign Key relationship situation
    • Other databases with row-level security don't address this issue, so it's unclear if we really need to. See Oracle VPD docs.
    • There's no sane and consistent model to make FKs make sense between tables with different labels / row security policies.
  • TRUNCATE statement
    • TRUNCATE is expected to be fast.
    • TRUNCATE must not have side effects.
    • If the user does not have rights to remove all rows from the table regardless of row-level policy, then any TRUNCATE must be denied.
    • Turning TRUNCATE (a PostgreSQL extension which is not in the SQL spec anyway) into a DELETE FROM doesn't make sense, and multi-table TRUNCATE has no equivalent in DELETE.
    • So TRUNCATE is just honoured, treating the right to TRUNCATE as ability to bypass RLS. TRUNCATE doesn't leak data so this works.
  • Table inheritance
    • Not something we really need to deal with in the initial version, so long as it doesn't completely break (or we make sure it does for inheritance)
    • What policy should be applied on the parent and child relations.
    • idea: Also copy row-level policies from the parent tables.

Roadmap

Current roadmap is as follows:

  • RLS implementation directly against 9.4. Deferred due to need to reduce overlap with updatable views, deal with the portal user id change problem, fix foreign key enforcement, and over some design / abstraction level issues.
  • Patch portal implementation (cursors, refcursors, etc) so that user id and security context is carried with portal, saved/restored when portal is entered/left during access.
  • Support re-running query rewriter during re-planning of RS views after security context change to handle changes to/from RS-exempt user. Invalidate cached plans involving RS tables after user id change or RS predicate change.
  • Add catalog tables for row security, SQL syntax for configuring row security
  • Implement secure session variables, document their use for row security
  • Implement mandatory access control and SELinux integration