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;