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
