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

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.

CodePudding user response：

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.

CodePudding user response：

CREATE ROLE admin_user;

-- Administrator
CREATE ROLE bob;

-- Normal user
CREATE ROLE alice;

-- Normal user
CREATE TABLE users (
    user_name text DEFAULT CURRENT_USER,
    user_unique_identifier text,
    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)
    VALUES ('bob', 'misc', 'misc_add');
INSERT INTO users (user_name, phone, address)
    VALUES ('alice', 'misc1', 'misc_add1');

--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,
    phone,
    users_field3,
    users_field1,
    users_field2
FROM
    users
WHERE
    user_name = CURRENT_USER;

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 public;
SET session AUTHORIZATION alice;
TABLE common_view; --ok
TABLE special_view; --ok

 -- 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 AUTHORIZATION bob;

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
    *;

use view as interface to communicate outside the database! So the user don't even have to access to table user!.
turn security_barrier = true to prevent function exploit the situation.

the remaining issue. user is not unqiue, people name can be same, so there is an problem in here.