Re: How to grant a user read-only access to a database?

On 3 March 2010 14:51, Kevin Kempter <kevink(at)consistentstate(dot)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(at)gmail(dot)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