Thread: thread by Jessica Richards on read only permissions

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

Mija Lee <mija@scharp.org> writes:
> 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

You do realize that #2 is completely insecure?  It's only establishing a
session default, which the user can override with a simple SET.

            regards, tom lane

Actually I didn't. I thought I had tried doing that as my testuser and
wasn't able to, but I must not have.

Okay, so nix that idea, because why assign security if it isn't secure.
Which brings me back to the question of if there is a way to do this
without listing every table in every schema. There must be...

Thanks Tom!

Mija



Tom Lane wrote:
> Mija Lee <mija@scharp.org> writes:
>> 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
>
> You do realize that #2 is completely insecure?  It's only establishing a
> session default, which the user can override with a simple SET.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq