Menu
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?
965
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.
Give SELECT permissions the new test_mv materialized view to the my_user role. CREATE ROLE my_user WITH NOLOGIN; GRANT SELECT ON test_mv TO my_user; To test the SELECT permission use SET ROLE to change the security context to the my_user role and verify with another check of current_user .
For the existing view, you can go to the Properties of the view in SSMS, add users in the Permissions, and then grant select permission in the permissions list. Or use the following statement to grant user permissions: GRANT SELECT ON OBJECT::[schema]. [yourview] TO User1,User2.
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.
77
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
