Granting privileges on ALL tables in Postgresql

If you have a database and you would like to grant select on all tables to a particular user or role, in Postgresql one must follow an unorthodox procedure. This is described in the following article.

Most users mistakenly believe that to give a privilege on all tables to a user/role, they have to grant the privelege at a database level like so:

grant all on database my_db to developers

This will not work because it will not cascade at a table level. Therefore the correct procedure to grant a privilege on all tables is in following these two steps:

1) Generate grant statements as follows:

select 'grant all on '||schemaname||'.'||tablename||' to developers;' from pg_tables where schemaname in ('my_db', 'public') order by schemaname, tablename;

2) Copy and paste the generated output and run the resulting scripts.

This will grant all privileges on all tables to a particular user or role.