thread by Jessica Richards on read only permissions

[Mija Lee <mija(at)scharp(dot)org>]

Hi -

I wanted to follow up on the thread opened by Jessica Richards on 
granting read only permissions. Basically it sounded like there were two 
options:

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.

I tried:

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!

Mija