Difference between revisions of "Row-security"

From PostgreSQL wiki
Jump to: navigation, search
(Articles/Documentation of existing RLS implementations)
(Articles/Documentation of existing RLS implementations)
Line 158: Line 158:
=== Teradata ===
=== Teradata ===
* Teradata 14 Security Administration design (for Teradata row-level security, a label based implementation)
* [http://www.info.teradata.com/edownload.cfm?itemid=112860002 Teradata 14 Security Administration] (for Teradata row-level security, a label based implementation)
== Use Cases ==
== Use Cases ==

Revision as of 05:36, 11 November 2013


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.

You can find the current progress of this proposal at the commitfest page.

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.


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

Design Target

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.

Overall, RLS prevents users from reading and writing rows that do not satisfy the row access predicate (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. Discussion on per-command vs flat access control can be found in the mailing list archives; no firm conclusion has yet been reached.

How RLS policy affects individual SQL commands

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


For SELECT (data read, no write) any rows that don't match with the configured row-security policy are be filtered out for users subject to 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 controlling 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.

Only users with SET ROW SECURITY rights may apply row security rights to a table. By default only the superuser has these rights because functions in row-security predicates run as the invoking user, allowing a user with RS policy setting rights to run code as another user if they can trick that user into accessing objects they control.

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>);


<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 and regular GRANT permissions for the operation would be used.

Issues & discussion

No dependencies are defined from pg_rowsecurity constraints to the constrained tables, or to types/functions/operators referenced in those constraints. This can cause inconsistencies.

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

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.

Per-command security policy

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 is always applied.

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.

It is not yet settled whether we need to support UPDATE or DELETE that can see rows in a scan that are not visible to SELECT. TODO.

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 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.
  • 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


SQL Server



Use Cases

  • PCI Compliant implementations
  • Classified Environments
  • Other?

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
    • 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
    • This issue is related to the VIEW leak described above. Once that issue has been resolved, this should be pretty straight-forward to implement
  • Grammar for catalog updates/changes; user-interface for specifying how RLS is to be done
  • Catalog changes for storing RLS information
  • 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.
    • 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.
  • 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?
  • Other?


  • 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 (reference?)
    • In any case, this isn't something we need to address in our initial RLS implementation
  • Order to evaluate row level policy
    • Addressed above with regard to views, once we solve that, this will be handled
  • TRUNCATE statement
    • TRUNCATE is expected to be fast.
    • 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.
  • COPY TO statement
    • COPY can just be reformed into a COPY statement with a query being used instead, eg:
    • COPY tblname TO xxx; can be replaced by COPY (SELECT * FROM tblname) TO xxx;.
  • 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.
  • Foreign Key constraint
    • 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.
      • 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.


  • Performance
    • With RLS
    • Without RLS
    • The page.16 of 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)