column-level security with policies in PostgreSQL 14 / PostgREST?

Question

I'm using PostgREST as an API for a project and now implement the security functions.

One thing I'd like to have but that is covered neither by row- nor by column-level security as I've read them so far is this use case:

• Table USERS is public SELECT (everyone can read it)
• Users can only edit (UPDATE) their own data (easy, row-level security)
• Admins can edit/delete all user's data (easy, role-based permission and row-level security)
• A few fields within the table should only be visible to admins and users themselves. Specifically, the phone number of a user should not be public information, but everyone should be able to see and edit their own phone number.

From everything I've read so far, neither row-level nor column-level security cover this, but I'm sure Postgres can handle it. How?

(Note: I know how to understand who the current user is and which row belongs to him, that is not my question)

Clarification: My users are not database users, but users in a user table. I have DB user roles for users, editors, admin, etc. but I don't create a DB role for every user signing up to the service. I can distinguish them for row-level security via JWT and

current_setting('request.jwt.claims', true)::json->>'user_id'

That's why I'm not trying to solve this with the obvious solution of using views.

Answer 1

The solution for that kind of problem is a view that shows the data only to certain people. Don't forget to set security_barrier = on on the view.

Answer 2

-- Normal user
CREATE TABLE users (
    user_name text not null,
    user_id text primary key,
    phone text,
    address text,
    users_field1 text,
    users_field2 text,
    users_field3 text,
    users_field4 text,
    created_by text DEFAULT CURRENT_USER,
    created_at timestamptz DEFAULT now()
);


INSERT INTO users (user_name, phone, address, user_id)
VALUES ('bob', 'misc', 'misc_add','unique1'),
('alice', 'misc1', 'misc_add1','unique2'),
('alice1', 'misc1', 'misc_add1','unique3'),
('alice2', 'misc1', 'misc_add1','unique4');

CREATE ROLE common_users;
CREATE OR REPLACE FUNCTION regress_rls_schema.current_userid()
 RETURNS text
 LANGUAGE plpgsql
 STABLE
AS $function$
BEGIN
  RETURN current_setting('regress_rls_schema.current_userid');
EXCEPTION
  WHEN undefined_object THEN
    RETURN NULL;
END;
$function$;


--two interface. one common interface, everyone can see it.
CREATE VIEW common_view WITH ( security_barrier = TRUE
) AS
SELECT
    user_name,
    users_field1,
    users_field2,
    users_field3
FROM
    users;

CREATE OR REPLACE VIEW special_view WITH ( security_barrier = TRUE
) AS
SELECT
    user_name,
    user_id,
    phone,
    users_field3,
    users_field1,
    users_field2
FROM
    users
WHERE
    user_id = current_userid();

GRANT SELECT ON common_view TO public;
GRANT SELECT, INSERT, UPDATE, DELETE ON users TO admin_user;
GRANT SELECT, DELETE, UPDATE ON special_view TO common_users;

set role  common_users;
set session  regress_rls_schema.current_userid = 'unique1';
select current_userid();

TABLE common_view; --ok
TABLE special_view; --ok
table users; --permission denied. 


 -- permission denied for table users
UPDATE
    users
SET
    created_by = 'dummy'
RETURNING
    *;

UPDATE
    special_view
SET
    users_field3 = 'hi there'
RETURNING
    *; --ok

UPDATE
    special_view
SET
    phone = '1234'
RETURNING
    *;--ok.

set session  regress_rls_schema.current_userid = 'unique2';
TABLE special_view;--ok

UPDATE
    special_view
SET
    phone = '911246',
    users_field2 = 'test view',
    users_field1 = ' this is a test'
RETURNING
    *;

-- permission denied for table users
UPDATE
    users
SET
    phone = '9012890'
RETURNING
    *;