How do I grant certain privileges to all tables in a database?

Question

I need something similar to mysql's GRANT SELECT ON db.* TO user in postgresql 8.4. The solutions I find online use a for loop to select tables on at a time and grant privileges on them. But that would mean I would have to rerun the command every time a new table is added to the database.

Is there a more straightforward solution to this?

Answer 1

Assuming you don't have any specific schemas defined, all your tables will be in the 'public' schema, so you can say:

GRANT <permissions> ON ALL TABLES IN SCHEMA public TO <roles>;

This only works on 9.0 or above though, so you're out of luck in 8.4.

See also: http://www.postgresql.org/docs/8.4/static/ddl-schemas.html

Answer 2

Note that often granting a certain user's rights to another user makes as much or more sense and is easier to keep track of.

create user dbuser9;
create database test9 with owner dbuser9;
\c test9 dbuser9
create a bunch of stuff...
\c postgres postgres
grant dbuser9 to stan;