Use Cases:
← Older revision
Revision as of 07:50, 11 November 2013
(12 intermediate revisions by one user not shown)
Line 1:
Line 1:
+
'''This is a ''development page'' discussing work on a feature that is not yet included in PostgreSQL. It is not a guide on using an existing feature.'''
+
= Row-Security =
= Row-Security =
Line 15:
Line 17:
For example, regular <tt>GRANT/REVOKE</tt> 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.
For example, regular <tt>GRANT/REVOKE</tt> 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
==
+
== Use
Cases
==
+
+
* PCI Compliant implementations
+
* Classified Environments
+
* Shared hosting / multi-tenant applications
−
== Design
Target
==
+
== 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 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.
Line 23:
Line 29:
The simplest way to achieve this is to express these access constraints as SQL predicates like any other SQL <tt>WHERE</tt> clause.
The simplest way to achieve this is to express these access constraints as SQL predicates like any other SQL <tt>WHERE</tt> 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 <tt>SELECT</tt>, <tt>INSERT</tt>, <tt>UPDATE</tt> or <tt>DELETE</tt>. Discussion on per-command vs flat access control can be found in the mailing list archives; no firm conclusion has yet been reached.
+
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 <tt>SELECT</tt>, <tt>INSERT</tt>, <tt>UPDATE</tt> or <tt>DELETE</tt>. 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
RLS
policy affects individual SQL commands ==
+
== How
RS
policy affects individual SQL commands ==
PostgreSQL has several SQL commands that allow users to directly access database rows:
PostgreSQL has several SQL commands that allow users to directly access database rows:
Line 72:
Line 80:
=== 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)''
Line 87:
Line 95:
== Issues & discussion ==
== Issues & discussion ==
+
+
=== Implementation detail ===
No dependencies are defined from <tt>pg_rowsecurity</tt> constraints to the constrained tables, or to types/functions/operators referenced in those constraints. This can cause inconsistencies.
No dependencies are defined from <tt>pg_rowsecurity</tt> constraints to the constrained tables, or to types/functions/operators referenced in those constraints. This can cause inconsistencies.
+
+
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.
=== Dump and reload ===
=== Dump and reload ===
−
Row-security policies must be dumped and reloaded without concern for the <tt>search_path<tt> setting at the time. <tt>pg_dump</tt> will need to be enhanced to use a new variant of <tt>pg_get_expr</tt> that fully schema-qualifies expressions when extracting row-security expressions from <tt>pg_catalog.pg_rowsecurity</tt>. '''TODO'''.
+
Row-security policies must be dumped and reloaded without concern for the <tt>search_path<
/
tt> setting at the time. <tt>pg_dump</tt> will need to be enhanced to use a new variant of <tt>pg_get_expr</tt> that fully schema-qualifies expressions when extracting row-security expressions from <tt>pg_catalog.pg_rowsecurity</tt>. '''TODO'''.
It must be possible to take a consistent backup that ignores row-security policy using <tt>pg_dump</tt> while still constraining <tt>COPY</tt> otherwise.
It must be possible to take a consistent backup that ignores row-security policy using <tt>pg_dump</tt> while still constraining <tt>COPY</tt> otherwise.
Line 114:
Line 138:
For that reason the plan is to automatically create <tt>BEFORE INSERT OR UPDATE OR DELETE ... FOR EACH ROW</tt> 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'''
For that reason the plan is to automatically create <tt>BEFORE INSERT OR UPDATE OR DELETE ... FOR EACH ROW</tt> 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
=
==
+
== 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.
* 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.
Line 159:
Line 183:
* [http://www.info.teradata.com/edownload.cfm?itemid=112860002 Teradata 14 Security Administration] (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 ==
−
−
* PCI Compliant implementations
−
* Classified Environments
−
* Other?
== Components of an implementation ==
== Components of an implementation ==
Line 202:
Line 220:
** What policy should be applied on the parent and child relations.
** What policy should be applied on the parent and child relations.
** idea: Also copy row-level policies from the parent tables.
** idea: Also copy row-level policies from the parent tables.
+
+
== Roadmap ==
+
+
Current roadmap is as follows:
+
+
* <strike>[http://www.postgresql.org/message-id/CADyhKSWGtZqpsXtF7_q2FvKRvX6RqW+xv7VmxNmj4gubSBoo-g@mail.gmail.com RLS implemented directly in 9.3]. Deferred due to the need to fix the leaky view problem.</strike>
+
+
* <tt>security_barrier</tt> views - '''DONE''', see [https://github.com/postgres/postgres/commit/0e4611c0234d89e288a53351f775c59522baed7c commit 0e4611c0234d89e288a53351f775c59522baed7c]
+
+
* <tt>LEAKPROOF</tt> - '''DONE''', see [https://github.com/postgres/postgres/commit/cd30728fb2ed7c367d545fc14ab850b5fa2a4850 commit cd30728fb2ed7c367d545fc14ab850b5fa2a4850]
+
+
* <strike>[http://www.postgresql.org/message-id/CADyhKSXFzQBXQoNwiKdN9xxoVw7Zqz9gmF64Sry57QKduSFwSQ@mail.gmail.com RLS implementation directly against 9.4]</strike>. 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|automatically updatable <tt>security_barrier</tt> views]] (target: 9.4). Might involve adding support for <tt>UPDATE</tt>, <tt>DELETE</tt> 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
+
+
* Implement secure session variables, document their use for row security
+
+
* Implement mandatory access control and SELinux integration
[[Category:SELinux]]
[[Category:SELinux]]