PostgreSQL SELECT-RULES , inheritance, row-level permissions

Question

Here's what I've been reading:

 http://www.postgresql.org/docs/9.2/static/rules-views.html
 http://www.postgresql.org/docs/9.2/static/rules-privileges.html

My goal is to allow a login to see only those rows that it "owns", so to speak.

Let's say every table in the database inherits from this table:

   create table WHOAMI
   ( 
      tenant varchar(25) not null default current_user
   );

for example:

   create table FOO
   (
     id int primary key,
     invoicedate date
   ) inherits (WHOAMI);

    insert into FOO(id, invoicedate) values(1,now()::date);

    select * from FOO;

    --abclogin|1|2013-02-01

Is there such a thing in PostgreSQL as a schema-level select rule, affecting all tables and views in the schema, that appends to every select, insert, update, or delete statement a condition that says, in effect, ..AND WHERE TENANT = current_user? If there isn't such a global rule, can it be done on a table-by-table basis? I am not having any success with my attempts, and am probably misunderstanding a few things about how rules are created. Here is what I have tried to do:

I try to create a select-rule:

  CREATE RULE "_RETURN" AS ON SELECT TO FOO DO INSTEAD
  SELECT * FROM FOO where tenant = current_user;

but get this error: ERROR: could not convert table "foo" to a view because it has indexes

I try to create a view with a security-barrier:

       CREATE VIEW TENANTFOO WITH (security_barrier) AS
       SELECT * FROM FOO WHERE tenant=current_user;

and then attempt an insert:

        insert into TENANTFOO(id,invoicedate)
        values(2,(now()::date);

but get this error:

        `ERROR:  cannot insert into view "tenantfoo"
         HINT:  You need an unconditional ON INSERT DO INSTEAD rule
         or an INSTEAD OF INSERT trigger.`

What steps are required to implement row-level security barriers on tables?

score 2 · Answer 1 · answered Feb 09 '13 at 22:50

2

In your last example, you'd need to run the INSERT against the table or create another RULE: ON INSERT TO TENANTFOO DO INSTEAD.

answered Feb 09 '13 at 22:50

Erwin Brandstetter

605,456
145
1,078
1,228

score 1 · Answer 2 · answered Feb 09 '13 at 23:15

What you're looking for is a Row-Level Security, it is not yet available, although some work had been done on this thing. I hope this patch will make it into the upcoming 9.3 release.

Meanwhile, I've been working with the following design a while ago.

Requirements were similar, views should have been delivering only those rows intended for the CURRENT_USER. In our case access had been done quite simple: a table that specified whether given user had access for the given relation and given key, smth like:

CREATE TABLE user_grants (
    user_id     integer,
    entity_name text, -- should exist in pg_class
    entity_id   integer
);

Then, say for the tasks, the following view had been created:

CREATE VIEW tasks_v AS
SELECT t.*
  FROM tasks t
  JOIN user_grants ug ON t.user_id = ug.user_id
   AND ug.entity_name='TASKS' AND ug.entity_id = t.task_id;

Of course, the setup is not complete without a number of helper functions, triggers and rules. Also it was necessary to make sure some reasonable default privileges are always granted.

PostgreSQL SELECT-RULES , inheritance, row-level permissions

2 Answers2