Thread: Creation of a read-only role.
Hello.
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.
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.
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
On 3/16/07, Dmitry Koterov <dmitry@koterov.ru> wrote: > 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. actually - you dont need the read-only role, if this is only for slave nodes. slony takes care about the issue and doesn't allow any writes on slaves. depesz
> actually - you dont need the read-only role, if this is only for slave
> nodes. slony takes care about the issue and doesn't allow any writes
> on slaves.
Great!
But what about a role which can modify the data, but cannot modify the database schema?
> nodes. slony takes care about the issue and doesn't allow any writes
> on slaves.
Great!
But what about a role which can modify the data, but cannot modify the database schema?
On 3/17/07, hubert depesz lubaczewski <depesz@gmail.com> wrote:
On 3/16/07, Dmitry Koterov <dmitry@koterov.ru> wrote:
> 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.
actually - you dont need the read-only role, if this is only for slave
nodes. slony takes care about the issue and doesn't allow any writes
on slaves.
depesz
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
Oh, sorry for the previous question - I can create a scheme-changes-disallowed role by revoking the "CREATE" permission from all the database schemas.
The issue seems to be closed now, thanks.
The issue seems to be closed now, thanks.
On 3/18/07, Dmitry Koterov <dmitry@koterov.ru> wrote:
> actually - you dont need the read-only role, if this is only for slave
> nodes. slony takes care about the issue and doesn't allow any writes
> on slaves.
Great!
But what about a role which can modify the data, but cannot modify the database schema?On 3/17/07, hubert depesz lubaczewski < depesz@gmail.com> wrote:On 3/16/07, Dmitry Koterov <dmitry@koterov.ru> wrote:
> 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.
actually - you dont need the read-only role, if this is only for slave
nodes. slony takes care about the issue and doesn't allow any writes
on slaves.
depesz
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster