On 3 March 2010 14:51, Kevin Kempter <kevink@consistentstate.com> wrote:
> On Wednesday 03 March 2010 07:29:21 am Merlin Moncure wrote:
>> On Tue, Mar 2, 2010 at 9:56 AM, Thom Brown <thombrown@gmail.com> wrote:
>> > As far as I'm aware. It's only in the upcoming version 9.0 that you
>> > can do things like:
>> >
>> > GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
>> >
>> > Other folk on here may have some alternative suggestions though.
>>
>> 9.0 will also have the hot standby feature. setting up a standby is
>> pretty much always a good idea and access to the standby is
>> automatically read only. this would be a cheap way to get what you
>> want without dealing with privileges which is nice. you are also
>> relatively insulated from problematic queries the user might make like
>> accidental unconstrained joins, full table sorts etc..
>>
>> merlin
>
>
> I believe all you have to do is this to create a read only user:
>
> create user ro_user with password 'passwd';
>
> alter user ro_user set default_transaction_read_only = true;
>
I believe that will only affect the *default* setting of the
transaction. The user could still run the following before a query to
write again:
SET SESSION CHARACTERISTICS AS TRANSACTION READ WRITE
Thom
