Combining row level security with column grants

Question

Let's say I have a table userswith three columns, public_data, private_dataand system_data, and I have three roles with the name postgres, authenticated_userand visitor.

postgresis superuser and can access all data (including system_data, of course)

authenticated_usermust have access to each user public_data, his own private_data, but notsystem_data

visitor can apply only to each user public_data

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

Answer 1

, , ,

. case 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;