Why Isn't Row Level Security Enabled for Postgres Views

Why isn't row level security enabled for Postgres views?

Basically because it wasn't possible to retroactively change how views work. I'd like to be able to support SECURITY INVOKER (or equivalent) for views but as far as I know no such feature presently exists.

You can filter access to the view its self with row security normally.

The tables accessed by the view will also have their row security rules applied. However, they'll see the current_user as the view creator because views access tables (and other views) with the rights of the user who created/owns the view.

Maybe it'd be worth raising this on pgsql-hackers if you're willing to step in and help with development of the feature you need, or pgsql-general otherwise?

That said, while views access tables as the creating user and change current_user accordingly, they don't prevent you from using custom GUCs, the session_user, or other contextual information in row security policies. You can use row security with views, just not (usefully) to filter based on current_user.

Applying a row-level-security policy on a view

No, at present, there is no feature in PostgreSQL that would allow you to do that. The obvious idea would be to define the policy on the underlying tables, but then the policy would not be checked for the calling user, but for the view owner.

There is currently a patch in the pipeline that would allow you to define the policy on the underlying tables and make it work as you want to; if you are interested, you can reply to the thread and/or review the patch.

Row level policy doesn't work on my table

update

https://stackoverflow.com/a/33863371/5315974

RLS won't work with views like that. You can use RLS for views though it is limited.

what you can do is

alter view api.mytable owner to anonymous ;

Is possible to create row level security policy for a postgresql view?

CREATE VIEW
  view_own_log
AS SELECT
  these,
  fields,
  only
FROM
  restricted_log_table
WHERE
  username=user;

user is the logged on user.

You can create more sophisticated WHERE clauses if needed. Have the table access limited to minimum and use GRANT to give access to the view.

Row level security does not work for table owner

You forgot to enable row level security for the table.

ALTER TABLE customer enable ROW LEVEL SECURITY;

force only makes sure that RLS is applied if enabled, it does not enable RLS on the table.

Online example: https://rextester.com/TCLZ82421

Reviewing defined row level security policies in PostgreSQL-9.5

Ok, figured it out. (Geez, is nobody using 9.5 yet???)

Question 1: Which tables have row level security?

The pg_class relation has a new column relrowsecurity boolean which is as straightforward as it looks:

SELECT oid, relname FROM pg_class WHERE relrowsecurity = 'true';

Question 2: Which policies are defined on RLS-enabled tables?

The system catalog has a new relation pg_policy which stores all the information on the policy, specifically the name of the policy, the oid of the table, the command it applies to, the roles (oid[]) to which the policy applies and the USING and WITH CHECK clauses.

Interestingly, the latter two are stored as a pg_node_tree which is merged with the execution plan of the query subject to the policy so the condition(s) of the policy are not re-evaluated on every call. That makes this approach potentially faster than using views as elaborated in the question, because fewer clauses have to be parsed and evaluated for each call.

Why is Row Level Security (RLS) not using indexes?

The reason why you are not seeing the same plan as the seemingly equivalent query without the RLS policy is that subquery pullup is happening before RLS policies are taken into account. This is a planner quirk.

To summarize, RLS policies in combination with subqueries are unfortunately not each other friends performance-wise.

For your information, a similar manifestation can be seen when comparing the following two queries:

SELECT ... FROM my_table WHERE                     EXISTS(SELECT ...);
SELECT ... FROM my_table WHERE CASE WHEN true THEN EXISTS(SELECT ...) END;

Here, while both queries are equivalent, the second query results in a (hashed) subplan for the subquery, because the folding of the unnecessary CASE WHEN true is done after subquery pullup.

Disclaimer: I got this information from RhodiumToad on IRC #postgresql, but explained/simplified it in my own words.

Row level security for groups or Making rows accebile to groups

This seems to work:

CREATE TABLE workers
(
    worid   int,
    worname text,
    pgrole text[]
);

INSERT INTO workers 
VALUES 
  (1,'Jason','{group1}'),
  (2,'Roy','{group1,group2}'),
  (3,'Johny','{group1}');

CREATE POLICY policy_employee_user ON workers FOR ALL
TO PUBLIC 
   USING ( (select count(*) 
            from unnest(pgrole) r 
            where pg_has_role(current_user, r, 'MEMBER')) > 0 );

ALTER TABLE workers ENABLE ROW LEVEL SECURITY;

Row Level Security, poor performance

I recommend you to upgrade to latest Postgres version 10.3.
Since version 9.5 significant improvements regarding performance of the Row-Level Security features have been made.
For example check out this improvement that is only available since Postgres 10.0: https://github.com/postgres/postgres/commit/215b43cdc8d6b4a1700886a39df1ee735cb0274d

I don't think it makes sense to try to optimize RLS queries in Postgres 9.5 since it was a very new feature back then and wasn't really optimized for performance yet back then. Just upgrade.

---

Related Topics