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.

Categories: BlogSQL

1 Comment

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

Leave a Reply

Your email address will not be published. Required fields are marked *