PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to get column-level protection of the original tables in security_invoker views?

I have a simple table "users", for which I want to block access to the password for some role "alice".

-- create table and some example data
create table users (
  username text,
  password text
);

insert into users(username, password) values
    ('foo', 'foopass'),
    ('bar', 'barpass');

-- create a role and limit its access
create role "alice";
grant select("username") on users to "alice";

So far so good. Using the "alice" role will successfully run select "username" from users but not select "password" from users.

However, problems arise when I introduce a view:

-- create a simple view
create view users_view with (security_invoker) as (
  select * from users
);
grant select on "users_view" to public;

Now if I run (as "alice") select "username" from users_view, it fails because "permission denied for table users".

Full db-fiddle

This is not what I expect from security_invoker. Is there a way this could still work, without completely overhauling the definition of the view?

Tags:

2

Answers


  1. 0

    The only ways to avoid the problem are

    1. define the view with the default security_invoker = off

    2. create a view that only selects the "allowed" columns

    Login or Signup to reply.
  2. 0

    Is there a way this could still work, without completely overhauling the definition of the view?

    That’s kind of asking if this could work without changing things that make this not work. No.

    This is not what I expect from security_invoker.

    1. You restricted Alice’s access to just one column username in table user.

    2. You defined a view that relies on her access privileges.

    3. You told Alice to select username from (select * from users) where * includes password column she doesn’t have sufficient access privileges for. Unsurprisingly:

      the user of a security invoker view must have the relevant permissions on the view and its underlying base relations

      and she doesn’t, so that fails.

    The only scenario where this could be expected to work is if the query rewrite system merged the view definition into the statement and reduced the query to select username first, then only verified privileges on its final form, after that. Here’s another illustration: demo at db<>fiddle

    create role "Eve";
create table "tree_of_knowledge"(forbidden_fruit money default 666);
revoke all privileges on "tree_of_knowledge" from "Eve";
create view sinful_view with (security_invoker) as (
with original_sin as (
  select--no columns            --"I'm not touching anything"
  from "tree_of_knowledge"
  where false--nothing qualifies--"I'm really touching nothing"
  limit 0)--zero of those       --"Even if I was, I'm not"
  select 'Forgive me father for I have sinned.' repent --constant, unrelated to anything
  from original_sin --"I'm touching nothing, nowhere and there's nothing in there."
  limit 0           --"Not even, at all."
);
grant select on sinful_view to public;
set role "Eve";
select repent from sinful_view limit 0;

    ERROR:  permission denied for table tree_of_knowledge

    select/*nothing*/ from sinful_view limit 0;

    ERROR:  permission denied for table tree_of_knowledge

    The way PostgreSQL works, Eve isn’t allowed to even smell the forbidden tree, no matter how much she promises to behave. We’re not taking any more risks.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search