How do I grant certain privileges to all tables in

2019-04-09 23:49发布

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?

3条回答
forever°为你锁心
2楼-- · 2019-04-10 00:05

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

查看更多
放荡不羁爱自由
3楼-- · 2019-04-10 00:11

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.

查看更多
做自己的国王
4楼-- · 2019-04-10 00:15

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;
查看更多
登录 后发表回答