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.