PostgreSQL: a Login Role to only access Views [duplicate]

Question

I need to write a SQL file which must do the following:

• create a role called 'selection'

• grant connect/usage to the current database

• grant select/execute to all views of all schemas (or at least on the current)

I tried a massive amount of GRANT...ON...TO combinations but I can't figure it out.

Additional info: These scripts are executed by ANT with the database and schema name (search_path) already set. So I can not burn in names like 'production_db' or 'qa_db'.

The goal is to create a single PostgreSQL Role, so that clients can go around Tomcat (Hibernate) and run selections/searches directly on the Postgres server. The role must be able to select from Views but nothing else.

UPDATE: the accepted answer led me to a solution where I extracted all the views to a separate schema and granted access to that schema only.

Answer 1

Unfortunately that's not that easy because there is no "select any view" privilege (as e.g. Oracle offers).

If you can put all views into a single schema, then this could be achieved by granting the select privilege on that schema. Something like this:

First you need to create a role that can be used for this

create role view_reader; 

The role only needs to be created once.

Then create a schema that contains all the views:

create schema view_holder;

Then allow view_reader to read everything in that schema:

grant select on all tables in schema report to view_reader;
alter default privileges in schema view_holder grant select on tables to view_reader;

The all tables is misleading because it also includes views - but also tables. That's the reason why you need to the views into a different schema than the tables. Because of the alter default privileges any new view created in that schema will automatically be accessible to the view_reader role.

You can now create a user that can select anything:

create user client password 'foobar';
alter user client set search_path = 'view_holder';
grant view_reader to client;

If client logs in, the default schema will be view_holder and thus a select * from some_view will default to the views in that schema. But as the user has only privileges on the views in that schema he cannot select from the underlying tables.

You can skip the intermediate role (view_reader) if you want and assign the privileges directly to client. But I think it's better better to give each client a separate user.

This should also work if the views in that schema simple select from the "real" views if you can't move the views into a different schema.

Note that this will increase the maintenance complexity for the views because unfortunately Postgres won't let you change the list of columns of a view if other views depend on it.