Yet another request came to me today for granting SELECT permissions. This time, the request was to give the user the correct privileges to use a view. Basically, you can use the same format that is used for tables as is used for views:
SELECT 'GRANT SELECT ON ' || quote_ident(schemaname) || '.' || quote_ident(viewname) || ' TO john_doe;'
FROM pg_views
WHERE schemaname = 'public';
After running a query like this one, you will be given all of the GRANT statements needed to give SELECT permissions to all of the views in the public schema.
2 Comments
akim · May 5, 2017 at 6:18 PM
Chris , if i don’t want other user or row to view that the views even exist on a cluster what do i do? because with this u won’t have select permission but you can see the structure of the views owned by other user if you are using pg_admin
Amin A · January 30, 2020 at 9:01 AM
Thank you. it worked for me !