Skip site navigation (1) Skip section navigation (2)

thread by Jessica Richards on read only permissions

From: Mija Lee <mija(at)scharp(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: thread by Jessica Richards on read only permissions
Date: 2007-10-26 19:56:11
Message-ID: (view raw, whole thread or download thread mbox)
Lists: 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 

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!



pgsql-novice by date

Next:From: Tom LaneDate: 2007-10-26 20:10:58
Subject: Re: thread by Jessica Richards on read only permissions
Previous:From: Tom LaneDate: 2007-10-26 19:16:29
Subject: Re: plpgsql question: inserting records

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group