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 -- Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin