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?
Assuming you don't have any specific schemas defined, all your tables will be in the 'public' schema, so you can say:
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
Default privileges came with version 9.0, it's not available in older versions.
You can create a stored procedure that loops through all tables and sets the privileges.
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.