From: | Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Creation of a read-only role. |
Date: | 2007-03-17 13:47:47 |
Message-ID: | 20070317134747.GC4252@merkur.hilbert.loc |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Sat, Mar 17, 2007 at 01:47:11AM +0300, Dmitry Koterov wrote:
> 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.
It may be possible to set the session to read-only
"set session characteristics as transaction readonly"
and make that the default (along the lines of "alter
database set ...") for the readonly role. There may be a way
to disallow that role to change that characteristic.
> 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.
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
From | Date | Subject | |
---|---|---|---|
Next Message | hubert depesz lubaczewski | 2007-03-17 15:22:24 | Re: Creation of a read-only role. |
Previous Message | Christian Schröder | 2007-03-17 09:21:16 | Re: How to enforce uniqueness when NULL values are present? |