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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin


[Index of Archives]     [KVM ARM]     [KVM ia64]     [KVM ppc]     [Virtualization Tools]     [Spice Development]     [Libvirt]     [Libvirt Users]     [Linux USB Devel]     [Linux Audio Users]     [Yosemite Questions]     [Linux Kernel]     [Linux SCSI]     [XFree86]

  Powered by Linux