When we start using of any replication system (e.g. Slony) we need to create a "read-only" role for access the database. This role must be able to read anything, but should NOT be able to INSERT, UPDATE or DELETE for all database objects.
Overall, we need 3 roles:
1. Administrator: can do anything with a database (by default this user is already exists - "postgres"). 2. Read-only: can only read. Runs on all slave nodes. 3. Read-write: can write, but cannot change the database schema. Runs on master node only.
Is any way to easily create and maintain these standard roles?
Now I have written a stored procedure which iterates over the pg_catalog and runs a lot of REVOKE & GRANT commands, but it seems to be not an universal solution, because:
1. I have to re-run this procedure after I change the database schema. (Very bad item! Can we avoid it?) 2. It looks like a "broot-force" method, and nothing said about it in the Slony documentation (strange). 3. In MySQL (e.g.) there is a one-command way to create these three roles.
Again, these 3 roles seems to be a de-facto standard for replication systems, but I found nothing about this question in the Google.
