Permission denied as superuser?

0

Hi, I came across an issue that's somewhat concerning.
Recently on our QA DB, I have been unable to query one of the views, even as a superuser.

Querying the view

qa=# select count(*) from warehouse.v_quarterly_cohorts;
ERROR:  42501: permission denied for relation account_age
LOCATION:  aclcheck_error, /home/ec2-user/padb/src/pg/src/backend/catalog/aclchk.c:2182

Querying the table that threw the error

qa=# select count(*) from warehouse.account_age;
 count
-------
  7022
(1 row)

Proof that I'm a superuser

qa=# select usename, usecreatedb, usesuper from pg_user where usename = current_user;
 usename | usecreatedb | usesuper
---------+-------------+----------
 admin   | t           | t
(1 row)

The only thing I can think of is that I recently changed the owner of the table. The context there is that I wanted to demote an external user (data pipeline user) from being a superuser, but wanted to preserve its ability to truncate certain tables, so I set it to be the owner of the table. But since my current user is a superuser, I don't see why this should be an issue.

Any ideas?

Thanks!

asked 4 years ago1086 views
3 Answers
0

HI mmasseth,

Could there be a conflict in view owner and table owner, or in the view owners privilege to select from the table that was introduced when you changed the table ownership?

-Kurt

klarson
answered 4 years ago
0

@klarson, interesting. Changing the view owner to be the same as the the table ended up working.
I guess when querying a view, permissions are a function of the creator, not the invoker ?

answered 4 years ago
0

Hi mmasseth,

I believe the privileges of the creator apply to what the view needs to execute the defining query and privileges of the invoker determine if the view can be used.

Glad it worked out for you.

-Kurt

klarson
answered 4 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions