From PostgreSQL wiki
Revision as of 04:14, 11 November 2013 by Ringerc (talk | contribs) (Progressively updating RS docs)
Jump to: navigation, search


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. With RS, 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.
        • mark*****

Row-security policy is set by table owner, using the following syntax. 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.

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. Listing the policies in a table form would look like:

ALTER TABLE mytable SET ROW SECURITY FOR ALL TO (where user = 1);

table command expression
mytable select user = 1
mytable insert user = 1
mytable update user = 1
mytable delete user = 1

If it was followed by:

ALTER TABLE mytable SET ROW SECURITY FOR SELECT TO (where user = 2); the result would be:

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.

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.

Issues & discussion

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

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

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

  • 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


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

Issue: A leaky VIEWs for RLS

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

In this message, KaiGai pointed out we have two different causes of the problem, but both of them can cause same information leaks.

  • [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 order_qual_clauses().
  • [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 distribute_qual_to_rels().


  • 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.
  • It needs a way to provide a hint whether the view is defined for security purpose, or not.
    • Tom Lane suggested CREATE SECURITY VIEW AS ... 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 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 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.

Discussions of RLS in PG

Articles/Documentation of existing RLS implementations

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)