3

I have a problem I can't work out. I am using the postgres account on my instance, and I have some failures in selecting from a view, where can select against the base table fine.

Let me run through a log of what I did, regranting explicitly anything that may be missing.

I can select from the table, but not from the view, with ERROR: 42501


deals2=> select count(*) from schema1.a1a_table1 where 1 = 0;
ERROR:  42501: permission denied for table table1
LOCATION:  aclcheck_error, aclchk.c:3554
deals2=> select count(*) from schema1.table1 where 1 = 0;
 count
-------
     0
(1 row)

deals2=> grant connect on database deals2 to postgres;
GRANT
deals2=> grant usage on schema schema1 to postgres;
GRANT
deals2=> grant select on schema1.table1 to postgres;
GRANT
deals2=> grant select on schema1.a1a_table1 to postgres;
GRANT
deals2=> select * from schema1.a1a_table1 where 1 = 0;
ERROR:  42501

The view is defined as:

-- View definition of schema1.a1a_table1

 SELECT schema1.book,
    schema1.deal_number,
    schema1.parent,
    schema1.child,
    schema1.deal_date,
    -- etc
FROM schema1.table1;

I think I know my databases quite well and can't really figure this one out.

1 Answer 1

4

Check who the owner of the table and the view are, and what permissions are set on each.

Unless you defined the view with security_invoker = on (from v15 on), the permissions on table1 are checked with the owner of a1a_table. So you'd have to grant that owner the SELECT privilege on table1 for the view to work.

If the view has security_invoker = on, the user who uses the view must have the SELECT privilege.

The documentation describes that in detail.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.