Grant access to views in postgresql

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.


postgres=# GRANT ALL PRIVILEGES ON DATABASE testdb TO testuser;
postgres=# GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO testuser;

GRANT USAGE on schema:          GRANT USAGE ON SCHEMA schema_name TO username;
  Grant SELECT for a specific table: GRANT SELECT ON tbl_loans_new TO oloffm;
  Grant SELECT for multiple tables:  GRANT SELECT ON ALL TABLES IN SCHEMA schema_name TO username;

Tags:

Postgresql