Emulation security lines in PostgreSQL 9.4

In PostgreSQL 9.5 will appear declarative security lines. You can set rules for tables and make them run automatically, for example, allowing the user joe to see only the rows in which the field owner is set to joe.

This is a great tool and it has long been supposed to appear. This was not done in PostgreSQL 9.4, but it was made with the help of automatically updatable views security_barrier. They the function LEAKPROOF form the Foundation on which is built the security lines. You can use these components without the support of declarative policy to achieve the security lines in 9.4.

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

To achieve a similar effect in relation to the security policy of the rows in the table, you must cancel the access to the table to all but the privileged (but not super users) which role you would like to grant access to their own perceptions. Then need to create a security_barrier view that belongs to this privileged role, with the WHERE condition that limits the ability of other users to see lines based on the chosen predicate, you can use current_user, a call to current_setting, etc.

For example::

the

CREATE ROLE secret_manager;
CREATE ROLE bob;
CREATE ROLE of 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 OWNER TO filtered_user_secrets secret_manager;

GRANT ALL ON filtered_user_secrets TO public;

RESET ROLE;

Now let's see how it works:

the

test=# SET ROLE bob;
SET
test= > select * from filtered_user_secrets ;
secret_id | owner | secret 
-----------+-------+----------
1 | bob | pancakes
(1 row)

test= > SET ROLE of 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).

The behavior is very similar to the security policy lines, but with some reservations:

the

the
• Using ALTER to the underlying table will not make visible changes to the view. To do this you must delete and recreate the view.
the
• Is not transparent to applications. They need to use the view itself, not the underlying table.

The last item may be to some extent solved by the use of schemas and search_path, for example:

the

CREATE SCHEMA filtered_tables;

ALTER TABLE SET SCHEMA user_secrets filtered_tables;

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

Customers can now interact with the view, not caring about the fact that this is just the shell of the original table.

Instead use the current_user, you can use current_setting('myapp.active_user'). If you do so, you must set a blank default value at the database level, in order current_setting did not issue errors if the setting is not defined (in version 9.5, you can use current_setting('myapp.active_user', 't') to ignore the missing records). For example:
the

ALTER DATABASE mydatabase SET myapp_active_user = ";

IMPORTANT: keep in mind that if you use current_setting as a predicate representation, the security policy does not apply to user-defined configuration, so any user who has the ability to perform arbitrary SQL query can change the setting. It still remains a useful tool, when all requests go through the app with full control over the running queries, but is not suitable for limiting user actions that have direct access to the database. The same applies to the use SET ROLE to switch the active user when the application uses the combined connectivity, since any user can just do the RESET ROLE.

Much easier to use the facilities of protection at the row level of version 9.5, but if similar functionality is necessary right now, it is already possible to achieve.

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

Security lines and approaches based on the submissions, will greatly benefit from the ability to define safe variables that can be simultaneously installed and cannot be reset, or can only be installed a certain role (possibly with SECURITY DEFINER function, which makes a test of sanity). This functionality does not appear in 9.5, but it could be possible using some extension that I hope to explore later.

Article based on information from habrahabr.ru