Row Security Considerations

The following are considerations and suggestions on issues raised in a pgsql-hackers thread regarding Row Level Security.

Summary of Issues and Concerns

• Risk of running arbitrary code as part of security qual.
• Security quals are not executed as table owner.
• pg_dump without error should copy all requested tables in their entirety, not a subset of rows in the tables.
• Adding more superuser-specific capabilities.
• Lack of finer grained access controls.
• Cannot apply multiple policies to a table.
• Cannot apply command specific policies to table, currently only supports ALL.
• Need filtering clauses to be supported by index scan not just sequential scan.
• Why can't Updateable Security Barrier Views be used instead?

Considerations and Suggestions

Adding one of the following (or combination of the two) GUC settings would allow for Row Security to be enable or disabled.

Row Security Enabled GUC

Overview

SET [ SESSION | LOCAL ] ROW SECURITY { ON | OFF | ERROR } -- Objective. Functions the same as setting TIMEZONE?

SET [ SESSION | LOCAL ] row_security { TO | = } { 'on' | 'off' | 'error' } -- Threshold. Functions the same as setting any other GUC.

• Global enable/disable of RLS. Does not apply directly to a specific role or table.
• ON - Enable row security for all users, excepting superuser.
• OFF - Disable row security for all users. Default for superuser.
• ERROR - Cause error if RLS is encountered.

RESET ROW SECURITY

• Reset row_security GUC to default value.

ALTER ROLE <role> SET ROW SECURITY { ON | OFF | ERROR } -- Objective.

ALTER ROLE <role> SET row_security = { 'on' | 'off' | 'error' } -- Threshold. Get for free through implementation of setting GUC.

• Allows for overriding global setting on a per role basis.

Questions

• Can this value be set via postgresql.conf?
  • Yes, requires restart, however, not required to be set in postgresql.conf.
• If not set in postgresql.conf, then what is the default value?
  • Default value is 'on'.
• If not set in postgresql.conf, then where should this value be initialized?
  • Value is initialized by GUC at startup.
• Which configuration group would this GUC be a member?
  • CONN_AUTH_SECURITY - Assuming this is the group for connection authentication and security.
• Which GUC context?
  • PGC_SUSET - Only superuser should be able to enable/disable row security.
• How does this facilitate plancache invalidation?
  • Need ability to check setting for specific user. However, unless that setting has been set for the user, then it will default to the current system/session setting. This doesn't seem like a very good approach or design.

Direct Table Access GUC

direct_table_access

options: <true|false|error>

• Not global, can ONLY be applied directly to a specific role and/or table.
• true - Allow direct table access.
• false - Disallow direct table access to all users, except superuser.
• error - Cause error is RLS is encountered.

By Passing RLS Per Role Only

Option #1

ALTER ROLE <role> SET direct_table_access = [ true | false | error ]

ALTER ROLE <role> SET DIRECT TABLE ACCESS TO [ true | false | error ] -- seems cumbersome?

• Add direct access privilege to <role>
• Allows <role> to by-pass RLS on all tables if set to 'true'.
• Does not require that <role> be superuser in order to by-pass RLS.

ALTER ROLE <role> RESET DIRECT TABLE ACCESS

• Reset direct_table_access to 'false' for <role>

Comments

• No different than Row Security Enabled GUC.

Option #2

If 'error' isn't necessary for this option, then perhaps simply adding a boolean attribute to toggle row security on and off for the role.

ALTER ROLE <role> [ ROWSECURITY | NOROWSECURITY ]

• Allow <role> to by-pass RLS checks if set to NOROWSECURITY.
• Default value ROWSECURITY.

Comments

• Minimal impact to ALTER ROLE syntax.
• Relatively simple to implement.

Questions

• When is the appropriate time to check this privilege? Rewrite? Planning?

Dependencies

• Need to add column to pg_authid to support.
• Need to add appropriate checks to in code to support.

By Passing RLS Per Role and Table

Option #1

GRANT DIRECT ON <table> TO <role>

• Add direct access for all commands on <table> for <role>
• Default behavior is that RLS is require for all normal sessions (non-pg_dump) unless specified otherwise by GRANT.

REVOKE DIRECT ON <table> FROM <role>

• Remove direct access for all command on <table> for <role>

DEPENDENCIES:

• Requires one bit from AclMode.

Option #2

GRANT DIRECT <command> ON <table> TO <role>

• Add DIRECT or equivalent keyword to GRANT for setting SELECT|INSERT|UPDATE|DELETE|ALL access privileges.
• Set ACL privilege to 'true' for <role> on <table> for specified command.
• Allow direct access for specified <command> on <table> for <role>.
• Default behavior is that RLS is required for all normal sessions (non-pg_dump) unless specified otherwise by GRANT.
• Command 'ALL' enables all command privileges on <table> for <role>.

REVOKE DIRECT <command> ON <table> FROM <role>

• Add DIRECT or equivalent keyword to REVOKE for setting SELECT|INSERT|UPDATE|DELETE|ALL access privileges.
• Set ACL privilege to 'false' for <role> on <table> for specified command.
• Disallow direct access for specified <command> on <table> for <role>.
• Command 'ALL' disables all command privileges on <table> for <role>.

QUESTIONS:

• Do enough bits exist to support privileges for all commands?
  • Currently, there is a limit of sixteen distinct privileges allowed. Twelve are currently utilized, leaving four open for use.
  • In order to implement DIRECT on all commands, four bits would be necessary.
  • Using all remaining privilege bits is not desired.
  • Recommended expanding from 32-bit to 64-bit per this thread.
• Does support need to be added for all commands, or will one (ALL) suffice in the meantime?
• Does granting DIRECT access override previous grant permissions? For instance, if SELECT is not granted to a role, but DIRECT SELECT is later granted, does that in turn update the SELECT permission for the role?
  • Permission setting should behave as following:

Pre-State	Action	SELECT	INSERT	UPDATE	DELETE
None	GRANT	Set	Set	Set	Set
	REVOKE	None	None	None	None
	GRANT DIRECT	Set & Direct Set	Set & Direct Set	Set & Direct Set	Set & Direct Set
	REVOKE DIRECT	None	None	None	None
Set	GRANT	Set	Set	Set	Set
	REVOKE	None	None	None	None
	GRANT DIRECT	Set & Direct Set	Set & Direct Set	Set & Direct Set	Set & Direct Set
	REVOKE DIRECT	Set	Set	Set	Set
Set & Direct Set	GRANT	Set & Direct Set	Set & Direct Set	Set & Direct Set	Set & Direct Set
	REVOKE	None	None	None	None
	GRANT DIRECT	Set & Direct Set	Set & Direct Set	Set & Direct Set	Set & Direct Set
	REVOKE DIRECT	None	None	None	None

None - No privilege bits are set for command.

Set - Privilege bit is set for command.

Direct Set - Privilege bit is set for direct command.

DEPENDENCIES:

• Privileges added to AclMode:

Privilege	Bit Index
ACL_DIRECT_INSERT	12
ACL_DIRECT_SELECT	13
ACL_DIRECT_UPDATE	14
ACL_DIRECT_DELETE	15

pg_dump Considerations

• pg_dump would run by default in 'error' mode causing it to error if RLS is encountered.
• pg_dump would need an --allow-row-level-security flag. (need to clarify functionality)

Running RLS Qual as Table Owner

QUESTIONS:

• Currently during rewrite, when RLS quals are pulled from their respective tables, they are appended to the "securityQuals" list. After that, they are processed in the same way that security barrier quals are processed. Should security barrier quals be required to be run by the table owner? If so, is this an RLS issue or a SB issue?
• As documented, expand_security_quals creates a set of nested subqueries to isolate each security barrier from the others, this provides protection against malicious user-defined security barriers. Since RLS quals are treated as "securityQuals", does this handle the concern for executing malicious user-defined code?
• Is it possible to wrap the RLS qual in a SECURITY DEFINER function at rewrite?

Multiple Policies On Table

• Modify RLS syntax to:
  • ALTER TABLE <table> SET ROW SECURITY <policy_name> FOR <command> TO <qual>
  • ALTER TABLE <table> RESET ROW SECURITY <policy_name> FOR <command>
• Provide access privilege for policy:
  • GRANT <command> (<policy_name>) ON <table> TO <role>
  • REVOKE <command> (<policy_name>) ON <table> TO <role>

QUESTIONS:

• The above could get quite messy and be painfully difficult to implement. Instead of ALTER syntax and catalog representation, is this better handled by the user programmatically using a language that is better suited such as pl/pgsql, perl, C, etc?
• How should multiple policies be joined together? AND or OR?
• Can the processing of multiple queries be short circuited?
• Can multiple policies be ordered by the user?

Roadmap

TBD