Developers Club geek daily blog

2 years, 10 months ago
In PostgreSQL 9.5 declarative safety of lines will appear. You can set rules for tables and make their execution automatic, for example, allowing the user of joe to see only lines which in the field of owner have a joe value.

It is the excellent tool and it had to appear for a long time. It it was not made in PostgreSQL 9.4, but made by means of automatically updated representations of security_barrier. They and the LEAKPROOF functions create the base on which safety of lines is constructed. You can use these components without support of declarative policy for achievement of safety of lines in 9.4.

Earlier I already discussed representation security_barrier. This post comprises examples of how there can be an information leakage from representation and as security_barrier of representation prevent such leaks. I will assume that you are familiar with the principles stated in the rest of article and I will repeatedly not stage demonstration of how there are information leakages from representations, etc.

For achievement of similar effect in relation to a security policy of lines in the table, it is necessary to cancel access to the table to everything, except exclusive (but not superusers) roles to which you would like to provide access to their representations of. Then it is necessary to create security_barrier representation which belongs to this exclusive role, with a condition of WHERE which limits a possibility of other users to see lines, relying on the predicate selected by you — it is possible to use current_user, current_setting challenge, etc.

For example::

CREATE ROLE secret_manager;

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)
FROM user_secrets
WHERE owner = current_user

ALTER VIEW filtered_user_secrets OWNER TO secret_manager;

GRANT ALL ON filtered_user_secrets TO public;


Now let's look as it works:

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

test=> SET ROLE sid;
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');
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 a security policy of lines, but with some clauses:

  • Use of ALTER to the cornerstone table will not make visible changes to representation. For this purpose it is necessary to delete and recreate representation.
  • It is not transparent for applications. They have to use representation, but not the cornerstone table.

The last point can be to some extent solved by means of schemes 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 clients can interact with representation, without caring that it is only a cover of the original table.

Instead of using current_user, it is possible to use current_setting ('myapp.active_user'). If to do quite so, it is necessary to set empty value by default at the level of base that current_setting did not give an error message if setup is not defined (in version 9.5 it is possible to use current_setting ('myapp.active_user', 't') for ignoring of missing records). For example:

ALTER DATABASE mydatabase SET myapp_active_user = '';

IMPORTANT: keep in mind that if you use current_setting as a representation predicate, the security policy does not extend to the settings of a configuration determined by the user so any user who has an opportunity to execute any SQL request — can change setup. It still remains the useful tool when all requests pass through the application with complete control of the executed requests, but is not suitable for restriction of actions of users which have direct access to base. The same is applicable to use of SET ROLE for switching of the active user when in the application the joint connections as any user can just make RESET ROLE are used.

It is much simpler to use conveniences of protection at the level of lines from version 9.5, but if the similar functionality is necessary right now, then it can already be reached.

For older PostgreSQL versions it is possible to use function SECURITY DEFINER plpgsql which return request of the filtered idea of a base table. Performance will be awful because all lines which will be seen by the user have to be selected at first, and then are filtered so the majority of indexes cannot be used. Besides, you can use normal representations if you do not give to users an opportunity to define own functions so leaks are less probable.

The safety of lines and approaches based on representations substantially will be the winner from capability to define safe variables which can be once set and cannot be reset, or can be set only by a certain role (perhaps by means of SECURITY DEFINER function which makes check on judiciousness). Such functionality not to appear in 9.5, but it can become possible by means of some expansion which I hope to study later.

This article is a translation of the original post at
If you have any questions regarding the material covered in the article above, please, contact the original author of the post.
If you have any complaints about this article or you want this article to be deleted, please, drop an email here:

We believe that the knowledge, which is available at the most popular Russian IT blog, should be accessed by everyone, even though it is poorly translated.
Shared knowledge makes the world better.
Best wishes.

comments powered by Disqus