Grant access to views in postgresql

Question

I have a view called testview in postgresql.

I created a new user called testuser.

I would like testuser to have all privileges on all tables and views in the database.

To do this I ran the following commands:

GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;  GRANT USAGE ON SCHEMA public TO testuser;  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO testuser; 

testuser now has access to all tables in the database, but if I try to run SELECT * FROM testview I get the following error: permission denied for relation testview.

What is wrong? How do testuser get access to testview?

Answer 1

I agree it should work. With permissions GRANT ... ON ALL TABLES should include views too.

Did you create the view after granting the privileges to testuser? If so then it doesn't have the same privileges as the other tables. That's because GRANT ... ON ALL TABLES means "on all tables that currently exist". To include tables/views you create in the future, you can say:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO testuser; 

Or if you want to give more than SELECT, you can say ALL PRIVILEGES instead.

I think this behavior of ON ALL TABLES is one of the most misunderstood bits about Postgres permissions, and it isn't really called out in the standard documentation, so I tried to emphasize it in my own Postgres permissions overview.