GRANT ALL ON ALL TABLES TO user; in Postgres < 9

Asked Dec 06 '13 at 14:00

Active Dec 06 '13 at 14:00

Viewed 152 times

PostgreSQL 9 brought the new GRANT Option "...ON ALL TABLES...".

How can I do the same in PostgreSQL 8.2?

I have a Database 'myDatabase' In myDatabse are 2 Schemas 'public' and 'myschema' with each around 20 Tables. Now I created a new user for testing which needs to have all privileges on 'myschema'.

I already tried:

GRANT ALL ON SCHEMA myschema TO testuser;
GRANT ALL ON DATABASE myDatabse TO testuser;
GRANT ALL ON ALL TABLES IN SCHEMA myschema; (Which doesn't work in postgres 8)

What would work is the following. But I'm sure there must be a better way:

GRANT ALL ON myschema.table1 TO testuser;
GRANT ALL ON myschema.table2 TO testuser;
...
GRANT ALL ON myschema.tableN TO testuser;

asked Dec 06 '13 at 14:00

0xAffe

1,156
1
13
29

8.2 is no longer supported and you should not use it. You really have to upgrade as soon as possible. – Dec 06 '13 at 14:02
Would they have introduced the `ALL TABLES` way if there was already a "better way"? – Damien_The_Unbeliever Dec 06 '13 at 14:02
I need to use 8.2 in my company. There's no way to upgrade it on my own. – 0xAffe Dec 06 '13 at 14:04
Create a role and give it access to all tables. Grant permission to the role every time you add a table. – Dan Bracuk Dec 06 '13 at 14:10

GRANT ALL ON ALL TABLES TO user; in Postgres < 9

0 Answers0