Message-ID: <4C1C748E.2090009@lelarge.info>
Date: Sat, 19 Jun 2010 09:41:02 +0200
From: Guillaume Lelarge <guillaume@lelarge.info>
User-Agent: Mozilla/5.0 (X11; U; Linux x86_64; en-US;
	rv:1.9.1.9) Gecko/20100423 Thunderbird/3.0.4
MIME-Version: 1.0
To: John Rouillard <rouilj@renesys.com>
CC: pgsql-admin@postgresql.org
Subject: Re: Ideas for a read only user access on partitioned tables.
References: <20100618230913.GH703@renesys.com>
In-Reply-To: <20100618230913.GH703@renesys.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit

Hi,

Le 19/06/2010 01:09, John Rouillard a écrit :
> [...]
> We are using postgres 8.4.4 as distributed in centos 5.5.
> 
> We have a database that has a number of partitioned tables 
> (http://www.postgresql.org/docs/8.4/interactive/ddl-partitioning.html).
> 
> We would like to add a read only user who is allowed to access all the
> tables in the database. I had hoped that granting select on the master
> table would also allow selects on the child tables. But that doesn't
> seem to work.
> 
> I also tried:
> 
>   grant select on table database.% to readonly_user;
> 
> I also tried with database.*, but those generated a syntax error at
> the wildcard. Also my guess is that it would have allowed it for all
> existing tables and not for the new ones as they are created.
> 

This syntax is not supported. See
http://www.postgresql.org/docs/8.4/interactive/sql-grant.html for details.

> In postgres 9.0 it looks like this use case is better supported with
> the:
> 
>  grant select on all tables in schema public to ro_user;
> 
> but using 9.0 isn't an option at the moment. Also can anybody confirm
> that will do what I want and won't just set the rights on the tables
> that exist in the schema at that time.
> 

This query will give SELECT permission to user ro_user for existing
tables. If you want to set default permissions for not-already-existing
tables, you need to use ALTER DEFAULT PRIVILEGES. Only in 9.0 though.
See
http://www.postgresql.org/docs/9.0/static/sql-alterdefaultprivileges.html for
more details on this statement.

> One other thing I came across is setting the roleconfig
> 
>   {default_transaction_read_only=true}
> 
> so I am wondering if I can duplicate the database owner's roles and
> use this setting to make it readonly? Also it concerns me that it's
> named default_transaction_read_only, which implies that it could be
> overridden as it's only the default.
> 

It could be orverridden. The first user that will issue a "SET
default_transaction_read_only TO false;" will be able to make changes
(if permission allow).

> Does anybody have any other ideas on how to crack this problem from
> the administration side rather than by changing the application.
> 

Yes, use GRANT each time you create a table. You can also use a stored
procedure that will create the table and adds your default permissions.


-- 
Guillaume
 http://www.postgresql.fr
 http://dalibo.com