From PostgreSQL wiki
Revision as of 02:50, 15 December 2009 by Sfrost (talk | contribs)
Jump to: navigation, search

Row-Level Security

This page is for discussion of implementing RLS in PostgreSQL.


  • 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.
  • 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.
  • Thread on information leak due to views
  • Earlier discussion of view leak

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
    • We also need to apply row-level policy on COPY TO statement to avoid information leaks.
    • idea: COPY tblname TO xxx; can be replaced by COPY (SELECT * FROM tblname) TO xxx;.
  • Table 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
    • FK constraint is
    • 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.
  • Integration with external security manager (eg: SELinux, SMACK)
    • The way to manage security label of tuples