updable view can update the base table, but cannot update view column

Question

BEGIN;
SET search_path TO test;
CREATE USER regress_view_user1;
CREATE USER regress_view_user2;
GRANT CREATE, usage ON SCHEMA test TO regress_view_user1;
GRANT CREATE, usage ON SCHEMA test TO regress_view_user2;
SET session AUTHORIZATION regress_view_user1;
CREATE TABLE test.base_tbl (
    a int UNIQUE,
    b text,
    c float
);
INSERT INTO test.base_tbl
    VALUES (1, 'xxx', 1.0);
GRANT SELECT (a, b) ON test.base_tbl TO regress_view_user2;
GRANT INSERT (a, b) ON test.base_tbl TO regress_view_user2;
GRANT UPDATE (a, b) ON test.base_tbl TO regress_view_user2;
COMMIT;

SET session AUTHORIZATION regress_view_user2;

CREATE VIEW rw_view2 AS
SELECT
    b AS bb,
    c AS cc,
    a AS aa
FROM
    base_tbl;

INSERT INTO rw_view2 (aa, bb)
    VALUES (1, 'xxx')
ON CONFLICT (aa)
    DO UPDATE SET
        bb = excluded.bb;-- Not allowed

ERROR: 42501: permission denied for table base_tbl I am not sure why this would be permission denied.

select aa, bb from rw_view2;--permission denied
 select a,b from base_tbl; --permission ok.

Answer 1

Edit: Rewrote this with more detail and a more helpful solution

Views are implemented in Postgres as Select rewrite rules on an empty table which run the SELECT statement that you use to define the view. These rewrite rules operate with the same permissions as the owner of the newly created table.

When you execute

select aa, bb from rw_view2

you are likely expecting that the query planner is going to recognize that column cc from rw_view2 isn't needed and therefore a lack of read permissions on column c is irrelevant -and indeed the query planner will actually execute a table scan on base_tbl; however, Postgres going to check permissions BEFORE the query planner does optimization. The rewrite rule is going generate a parse tree equivalent to:

SELECT aa,bb
FROM (
  SELECT
      b AS bb,
      c AS cc,
      a AS aa
  FROM
      test.base_tbl
) as rw_view2

which regress_user2 does not have the permissions to execute, resulting in the error you experience.

Similary

UPDATE test.rw_view2 SET bb = 'xxx' WHERE aa = 1;

fails because what the query planner sees, for purposes of permissions, is something like:

UPDATE test.base_tbl SET b = 'xxx'
FROM (
 SELECT
     b AS bb,
     c AS cc,
     a AS aa
 FROM
     test.base_tbl
) as rw_view2

WHERE base_tbl.a = rw_view2.aa;

You wouldn't want the authorization of operations to succeed or fail based on optimizations the query planner uses -as this would lead to inconsistent behavior that produces difficult-to-debug errors when executing statements.

So fundamentally a user needs SELECT permissions on all the columns that they define in their view for any non trivial INSERT/UPDATE operations.

However, you can work around this restriction by creating the view under regress_user1 and then granting SELECT,UPDATE,INSERT permissions to regress_view_user2. This setup allows you to directly define the permissions you want regress_view_user2 to have on rw_view2 instead of base_tbl:

DROP SCHEMA IF EXISTS test;
CREATE SCHEMA test;
CREATE USER regress_view_user1;
CREATE USER regress_view_user2;
GRANT CREATE, usage ON SCHEMA test TO regress_view_user1;
GRANT CREATE, usage ON SCHEMA test TO regress_view_user2;
SET session AUTHORIZATION regress_view_user1;
CREATE TABLE test.base_tbl (
   a int UNIQUE,
   b text,
   c float
);
INSERT INTO test.base_tbl
VALUES (1, 'xxx', 1.0);

CREATE VIEW test.rw_view2 AS
SELECT
    b AS bb,
    c AS cc,
    a AS aa
FROM
    test.base_tbl;

GRANT SELECT (aa,bb) ON test.rw_view2 TO regress_view_user2;
GRANT INSERT (aa,bb) ON test.rw_view2 TO regress_view_user2;
GRANT UPDATE (aa,bb) ON test.rw_view2 TO regress_view_user2;

SET session authorization regress_view_user2;

INSERT INTO test.rw_view2 (aa, bb)
VALUES (1, 'xxx')
ON CONFLICT (aa)
    DO UPDATE SET
    bb = excluded.bb; --succeeds

In this case regress_view_user1's SELECT permissions on base_tbl allow the view to be computed, and since regress_user2 is only using columns from the view they have been specifically granted access to, the INSERT ON CONFLICT operation is allowed to proceed.

If your view has a WHERE clause, there are some additional security considerations you might want to take into account when defining views such as using security_barriers and also setting the appropriate CHECK OPTION on the view for INSERTS/UPDATES. More info on using rules here