Re: Ideas for a read only user access on partitioned tables.

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