Re: Grant Permissions for View Only

On Thu, 2012-11-29 at 13:51 -0800, Chris Campbell wrote:
> > -----Original Message-----
> > From: pgadmin-support-owner(at)postgresql(dot)org [mailto:pgadmin-support-
> > owner(at)postgresql(dot)org] On Behalf Of Guillaume Lelarge
> > Sent: Thursday, November 29, 2012 1:23 PM
> > To: Chris Campbell
> > Cc: pgadmin-support(at)postgresql(dot)org
> > Subject: Re: [pgadmin-support] Grant Permissions for View Only
> >
> > On Thu, 2012-11-29 at 10:26 -0800, Chris Campbell wrote:
> > > >From: pgadmin-support-owner(at)postgresql(dot)org
> > > >[mailto:pgadmin-support-owner(at)postgresql(dot)org] On Behalf Of Chris
> > > >Campbell
> > > >Sent: Tuesday, November 27, 2012 8:55 PM
> > > >To: pgadmin-support(at)postgresql(dot)org
> > > >Subject: [pgadmin-support] Grant Permissions for View Only
> > >
> > > >Hello,
> > >
> > > >Using pgAdmin III version 1.14.3, PostgreSQL 9.1.5, Windows 7/64
> > bit
> > >
> > > >I've created a Role in a new database called [appuser]. I'd like
> > this user to be able to run queries and view data in tables, but not be
> > able to alter anything in the >given schema. So I issued the following
> > command:
> > >
> > > >GRANT SELECT ON ALL TABLES IN SCHEMA schema1 TO appuser; I then
> > > >created a new server called viewonly for the [appuser]. When I
> > drill down to the tables and attempt to "view" the records, I get a
> > permissions error.
> > >
> > > >ERROR: permission denied for schema schema1 Line 1: Select count(*)
> > > >AS rows FROM ONLY schema1.mytable
> > >
> > > >So I went back and added the following permission:
> > > >GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA schema1 TO appuser;
> > >
> > > >Didn't work. So I then added:
> > > >GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO appuser;
> > >
> > > >Still doesn't work.
> > > >What am I missing and how do I fix this so a user can "view" but not
> > change data using pgAdmin III?
> > > >Thanks,
> > >
> > > >Chris
> > >
> > >
> > > Can I take it from the lack of response that I've perhaps posted this
> > pgAdmin question to the wrong list?
> >
> > In a sense, yes. And also from a lack of time, at least for me :)
> >
> > Anyway, now that I have some more time, I think you forgot to give the
> > USAGE permission on the schema to the user. Try:
> >
> > GRANT USAGE ON SCHEMA schema1 TO appuser;
> >
> > and it should work.
> >
>
>
> Indeed it does. Thank you.
>

You're welcome.

> Which list would be more appropriate for these types of questions?
>

I guess pgsql-admin or pgsql-general.

--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com