I am using postgres and have a new read-only user that has read permissions on all tables in a base schema. I was trying to SELECT some data from a particular view from base schema and was able to see the values from the view. To test the data count I had to make some changes in the view definition and in Postgres there isnt a way to alter the View so I had to drop and recreate the view with new definition using a older user with higher privilege. Now I run the same SELECT query on the View and am getting a permissions error.
ERROR: permission denied for view SQL state: 42501
I tried Grant SELECT on ALL TABLES on Schema base to user again and was able to see the values from the view. What I dont get is that I havent changed any syntax since the first GRANT, why am I having to run the GRANT SELECT statement again for the new user to be able to access the view ? shouldnt the user already be able to access all the tables and views by default
Advertisement
Answer
You can use CREATE OR REPLACE VIEW to change a view definition if you don’t change its columns.
In PostgreSQL, permissions are stored on the object. If you drop an object, all its permissions are gone. If you later create an object with the same name, that is still a different object and will have the default permissions.
You can use ALTER DEFAULT PRIVILEGES to grant permissions for objects that are created in the future.