thread by Jessica Richards on read only permissions - Mailing list pgsql-novice

From Mija Lee
Subject thread by Jessica Richards on read only permissions
Date
Msg-id 4722465B.30000@scharp.org
Whole thread Raw
Responses Re: thread by Jessica Richards on read only permissions  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-novice
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





pgsql-novice by date:

Previous
From: Tom Lane
Date:
Subject: Re: plpgsql question: inserting records
Next
From: Tom Lane
Date:
Subject: Re: thread by Jessica Richards on read only permissions