Delete row despite missing select right on a column-CodePudding

In this example, the second column should not be visible for a member (role) of the group 'user_group', because this column is only required internally to regulate the row level security. however, records can only be deleted if this column is also visible. How can you get around that?

Options that come to mind would be:

just make the second column visible (i.e. selectable), which would actually be completely superfluous and I want to hide internally as much as possible
write a function that is called with elevated rights (security definer), which I want even less.

Are there any other options? (and especially with deletions I want to use nice things like 'ON DELETE SET NULL' for foreign keys in other tables, instead of having to unnecessarily program triggers for them)

create table test (
    internal_id serial primary key,
    user_id int not null default session_user_id(),
    info text default null
);


grant
    select(internal_id, info),
    insert(info),
    update(info),
    delete
on test to user_group;

create policy test_policy on policy for all to public using (
            user_id = session_user_id());

CodePudding user response：

RLS just implicitly adds unavoidable WHERE clauses to all queries, it doesn't mess with the roles under which code is evaluated. From the docs:

"Since policy expressions are added to the user's query directly, they will be run with the rights of the user running the overall query. Therefore, users who are using a given policy must be able to access any tables or functions referenced in the expression or they will simply receive a permission denied error when attempting to query the table that has row-level security enabled."

This feature is orthogonal to the granted column permissions. So the public role must be able to view the user_id column, otherwise evaluating user_id = session_user_id() leads to an error. There's really no way around making the column visible.

completely superfluous and I want to hide internally as much as possible

The solution for that would be a VIEW that doesn't include the column. It will even be updatable!