Re: How to grant a user read-only access to a database? - Mailing list pgsql-general

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;