String Security Emulation in PostgreSQL 9.4

In PostgreSQL 9.5, a declarative row security will appear. You can set rules for tables and make their execution automatic, for example, allowing the user joe to see only rows that have the value joe in the owner field.

This is a great tool and it should have appeared long ago. This was not done in PostgreSQL 9.4, but it was done using automatically updated security_barrier views. They and LEAKPROOF functions form the foundation on which string security is built. You can use these parts without the support of a declarative policy to achieve string security in 9.4.

I have previously discussed the security_barrier view. This post contains examples of how information can leak from a view and how security_barrier views prevent such leaks. I will assume that you are familiar with the principles outlined in the remainder of this article and will not re-arrange a demonstration of how information leaks from submissions, etc.

To achieve a similar effect with respect to the security policy of the rows in the table, you must deny access to the table to everyone except the privileged (but not superuser) roles that you would like to grant access to their own views. After that, you need to create a security_barrier view that belongs to this privileged role, with the WHERE clause, which limits the ability of other users to see strings based on the predicate you selected - you can use current_user, call current_setting, etc.
')
For example::

CREATE ROLE secret_manager; CREATE ROLE bob; CREATE ROLE sid; CREATE TABLE user_secrets( secret_id integer primary key, owner text not null, secret text not null ); ALTER TABLE user_secrets OWNER TO secret_manager; INSERT INTO user_secrets (secret_id, owner, secret) VALUES (1, 'bob', 'pancakes'), (2, 'fred', 'waffles'), (3, 'anne', 'cake'), (4, 'sid', 'fraud'); REVOKE ALL ON user_secrets FROM public; CREATE VIEW filtered_user_secrets WITH (security_barrier) AS SELECT * FROM user_secrets WHERE owner = current_user WITH CHECK OPTION; ALTER VIEW filtered_user_secrets OWNER TO secret_manager; GRANT ALL ON filtered_user_secrets TO public; RESET ROLE; 

Now let's see how it works:

 test=# SET ROLE bob; SET test=> select * from filtered_user_secrets ; secret_id | owner | secret -----------+-------+---------- 1 | bob | pancakes (1 row) test=> SET ROLE sid; SET test=> select * from filtered_user_secrets ; secret_id | owner | secret -----------+-------+-------- 4 | sid | fraud (1 row) test=> SELECT * FROM filtered_user_secrets WHERE owner = 'bob'; secret_id | owner | secret -----------+-------+-------- (0 rows) test=> INSERT INTO filtered_user_secrets (secret_id, owner, secret) VALUES (5, 'sid', 'larceny'); INSERT 0 1 test=> select * from filtered_user_secrets ; secret_id | owner | secret -----------+-------+--------- 4 | sid | fraud 5 | sid | larceny (2 rows) test=> INSERT INTO filtered_user_secrets (secret_id, owner, secret) VALUES (6, 'joe', 'impersonation'); ERROR: new row violates WITH CHECK OPTION for view "filtered_user_secrets" DETAIL: Failing row contains (secret_id, owner, secret) = (6, joe, larceny). 

Behavior is very similar to string security policy, but with some reservations:

• Using ALTER on the underlying table will not make visible changes to the view. To do this, you must delete and re-create the view.
• It is not transparent to applications. They should use the view itself, not the underlying table.

The last item can be solved to some extent with the help of charts and search_path, for example:

 CREATE SCHEMA filtered_tables; ALTER TABLE user_secrets SET SCHEMA filtered_tables; -- Leave the view in the public schema and just rename it ALTER TABLE filtered_user_secrets RENAME TO user_secrets; 

Now, customers can interact with the view without worrying that this is just a wrapper for the original table.

Instead of using current_user, you can use current_setting ('myapp.active_user'). If you do it this way, you should set an empty default value at the base level so that current_setting does not give an error if the setting is not defined (in version 9.5 you can use current_setting ('myapp.active_user', 't') to ignore the missing entries). For example:

 ALTER DATABASE mydatabase SET myapp_active_user = ''; 

IMPORTANT: keep in mind that if you use current_setting as a presentation predicate, the security policy does not apply to user-defined configuration settings, so any user who has the ability to execute an arbitrary SQL query can change the setting. It still remains a useful tool when all requests go through the application with full control of the requests being executed, but not suitable for restricting the actions of users who have direct access to the database. The same applies to using SET ROLE to switch the active user when an application uses combined connections, since any user can simply make a RESET ROLE.

It is much easier to use the convenience of protection at the line level from version 9.5, but if similar functionality is needed right now, then it can already be achieved.

For even older versions of PostgreSQL, you can use the SECURITY DEFINER plpgsql functions that return a query of the filtered view of the base table. The performance will be terrible, because all the rows that the user sees must first be selected and then filtered, so that most of the indices cannot be used. In addition, you can use regular views if you do not provide users with the ability to define their own functions, so that leaks are less likely.

String security and view-based approaches will greatly benefit from the ability to define safe variables that can be set at a time and cannot be reset, or can only be set by a specific role (possibly using the SECURITY DEFINER function that performs the check on sanity). This functionality does not appear in 9.5, but it can be possible with the help of some extension, which I hope to explore later.