Date: Fri, 18 Jun 2010 23:09:13 +0000
From: John Rouillard <rouilj@renesys.com>
To: pgsql-admin@postgresql.org
Subject: Ideas for a read only user access on partitioned tables.
Message-ID: <20100618230913.GH703@renesys.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
User-Agent: Mutt/1.5.20 (2009-06-14)

Hello folks:

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.

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.

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.

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

Thanks for your help.

-- 
				-- rouilj

John Rouillard       System Administrator
Renesys Corporation  603-244-9084 (cell)  603-643-9300 x 111