Обсуждение: Ideas for a read only user access on partitioned tables.

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

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