I wanted to follow up on the thread opened by Jessica Richards on
granting read only permissions. Basically it sounded like there were two
1. granting select on each table
2. alter user set default_transaction_read_only to true
I have many different databases with many schemas and many tables under
those schemas. I am trying to move to a position where I have a
read-only nologin role (or group) to which I assign specific
individuals. Seems like something you would want to do without listing
every table in every schema.
The alter user set default_transaction_read_only to true works
beautifully for the individual user, but not for a nologin/group role. I
don't want to make the individual's default transaction as read only for
all databases, just certain ones.
1. database is owned by db_owner
2. create group db_ro for the new read only group.
3. make db_ro a member of db_owner
4. alter role/user db_ro set default_transaction_read_only to true
5. make newuser member of db_ro, with inherit
6. try to insert as newuser.
This seemed like a solution (although a bit convoluted, and possible a
little confusing for folks who see that db_ro is part of db_owner), but
it didn't work. I was still able to insert as the newuser.
Is there something I am misunderstanding?
Is there some better way to do this? I could write a script that
generates a list of all tables in all schemas and assign permissions
that way, but it seems like you should be able to do without that script.
Oh, I'm running 8.2.4, solaris 10.
Thanks for the help!
pgsql-novice by date
|Next:||From: Tom Lane||Date: 2007-10-26 20:10:58|
|Subject: Re: thread by Jessica Richards on read only permissions |
|Previous:||From: Tom Lane||Date: 2007-10-26 19:16:29|
|Subject: Re: plpgsql question: inserting records |