CODEWITHSUNDEEP
databasepostgresqlpermissions
HyderA
HyderA

Reputation: 21371

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

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?

Upvotes: 5

Views: 9828

Answers (3)

mattlaw
mattlaw

Reputation: 99

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

Upvotes: 8

Scott Marlowe
Scott Marlowe

Reputation: 8870

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;

Upvotes: 3

Frank Heikens
Frank Heikens

Reputation: 127086

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.

Upvotes: 2

Related Questions