Combining row level security with column grants

Question

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?

Answer 1

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;