11

Let's say I have a users table with three columns, public_data, private_data, and system_data, and I have three roles named postgres, authenticated_user, and visitor.

postgres is superuser and can access all data (including system_data of course)

authenticated_user should be able to access every user's public_data, their own private_data, but not system_data

visitor can only access every users' public_data

How do I set up my tables, roles, grants, and policies to accomplish the above?

Julien
  • 5,243
  • 4
  • 34
  • 35

1 Answers1

4

This isn't really a declarative row security question, as it sounds like you want to return all rows, but display different column values depending on the current user

As per your comment, you're okay with different queries per user type. I don't see a way around using a case statement for private_data

create table users (
  id int primary key,
  owner name not null,
  public_data text,
  private_data text,
  system_data text
);

--these should probably be groups, not users:
create user visitor;
create user authenticated_user;
grant visitor to authenticated_user; -- will inherit visitors permissions

grant select(id, owner, public_data) on users to visitor;

grant select(private_data) on users to authenticated_user;

insert into users (id, owner, public_data, private_data, system_data) values (1, 'visitor', 'public', 'private to visitor', 'system');

insert into users (id, owner, public_data, private_data, system_data) values (2, 'authenticated_user', 'public', 'private to authenticated_user', 'system');

set role visitor;
select id, owner, public_data from users;

set role authenticated_user;
select id, owner, public_data, case when owner=current_user then private_data else null end as private_data from users;

set role postgres;
select * from users;
Neil McGuigan
  • 46,580
  • 12
  • 123
  • 152
  • 3
    The tooling I am using (PostGraphile) has limited support for views, so I can't go that route. – Julien Mar 14 '18 at 15:03
  • having `select *` return an error is acceptable, as only specific columns will be queried – Julien Mar 14 '18 at 15:04
  • As per your update, it looks like you're avoiding using RLS. Is there a specific reason? Would there be a way to make this work with RLS policies instead of that case statement? – Julien Mar 14 '18 at 22:51
  • @Julien I'm not using RLS because you want to see all rows – Neil McGuigan Mar 14 '18 at 22:54
  • Would it be possible to enable RLS for `authenticated_user` and disable it for `visitor`, while simultaneously limiting column access of `visitor` to just `public_data`? – Julien Mar 14 '18 at 23:13
  • After testing I see that with RLS enabled, it forces only one row returned, which is what you hinted at. It seems like I might just have to break out all the `data` columns into their own table, and have RLS on that table instead. – Julien Mar 15 '18 at 00:12
  • @Julien I have the exact same problem. Was that the solution you ended up doing? Or have things changed with Postgraphile 4? – pir Oct 11 '18 at 18:16
  • 5
    Yes, I broke the table into two tables. For what it's worth, this is also what the creator of Postgraphile suggests as well. – Julien Oct 12 '18 at 01:19