Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Does somebody now an easy and relatively fast way to achieve the following:

I have a PostgreSQL 8.2.9 DBMS with several (10 or so) different databases, which all serve for respective web applications. Those databases are separated into different schemes which are separated into different tables, of course.

I was using the postgres superuser to connect to the databases in my web applications. Now I want to rethink my security concept and therefore create a less-privileged user which can only do the following: INSERT, DELETE, UPDATE and SELECT on all tables of all databases. It doesn't matter if this user can access all databases, I don't want to create separate users for all web applications.

Anyway, there should be some way to grant those privileges to the user after creating it, something like

GRANT SELECT, INSERT, UPDATE, DELETE ON . TO new_user;

I only know how to grant those privileges to single tables but that would be a odyssey with over 10000 tables distributed over 10 databases. I've read something about reading out the pg_tables metatable to get a list of all tables but it somehow didn't work on my version. Maybe you could also answer if there is actually a PostgreSQL database which stands above all databases and includes metainformation about all databases?! I only noticed that in each database there is a schema called information_schema which hosts information about the current database?!

Thank you very much.

share|improve this question

1 Answer 1

Mass GRANT/REVOKE is only available on PostgreSQL 9.0+.

Instead, you can create the functions pg_grant() and pg_revoke() found at http://blog.bhushangahire.net/2009/06/17/grant-privileges-to-all-tables-in-a-database-for-postgresql/

The parameters to both functions are:

  1. User Role
  2. Permissions
  3. Object name, may be a pattern. "%" affects all objects
  4. Schema name

You will need to run the command on each database and each schema. The syntax looks like this:

SELECT pg_grant('web_user','SELECT,INSERT,UPDATE,DELETE','%','public');

If you like, you may be able to change this line in the function definition:

nspname = $4 AND

to this:

nspname like $4 AND

in order to be able to specify schema as a wildcard, but I haven't tested it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.