Postgres GRANT not applied on parent

Question

I'm in trouble with grant in postgresql (version 9.3).

I'm trying to restrict a ROLE 'client_1'. I want it to be able to do only select for one table. But there is inheritance between tables.

Here is my table structure:

CREATE TABLE public.table_a (...);
CREATE TABLE table_a_partitions.child_1 (...) INHERITS (public.table_a);
CREATE TABLE table_a_partitions.child_2 (...) INHERITS (public.table_a);

GRANT SELECT ON table_a_child_1 TO client_1;

It's okay when I do a select on child_2, there is an error, but if I do a SELECT * FROM table_a; for example it also reads the forbidden table child_2. I would my client access only child_1 (and some other in the future) results when he does SELECT * FROM table_a;.

Is there a simple way to solve this problem ?

Thank you

Answer 1

You would need to use a VIEW in PostgreSQL 9.3 to solve this problem. If you upgrade to 9.5, however, you could use row-level security.

As a note as to why, the grant check only occurs on the level of the initial relation queried. This means if you query a view, you need access to the view's contents, but the view owner (NOT YOU) needs access to the underlying relations. This allows a view to be useful for information hiding. Similarly with inheritance, this structure allows you to forbid rows to be inserted or queried directly from partitions of a table, but to allow different queries via the parent table. So this is a consequence of design priorities, not a bug.

Before row-level security, you would basically create a view and fold in user privilege criteria into the view (with partitioning/inheritance this is also a good idea for other reasons since your insert/update/delete triggers can return exactly what the db would do even though it cannot on a table).

As for row-level security, PostgreSQL 9.5 does allow you to specify row-level policies (conditions that get appended to insert/select/update/delete queries) and that provides something a little more manageable in some cases than the view approach.