From PostgreSQL wiki

(Difference between revisions)
Jump to: navigation, search
(SQL commands for setting row security)
Line 74: Line 74:
=== SQL commands for setting row security ===
=== 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:
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)''
''(This segment of the design is still in flux)''

Revision as of 07:22, 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 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.

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

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:


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.

Dump and reload

Row-security policies must be dumped and reloaded without concern for the search_path<tt> setting at the time. <tt>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 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


SQL Server



Use Cases

  • PCI Compliant implementations
  • Classified Environments
  • Other?

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


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


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.
  • Implement automatically updatable security_barrier views (target: 9.4). Might involve adding support for UPDATE, DELETE 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
Personal tools