Row-security

From PostgreSQL wiki
Jump to navigationJump to search

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
Retrieved from "https://wiki.postgresql.org/index.php?title=Row-security&oldid=27955"