Postgres Pro
Downloads
Home   >   mailing lists

Thread: Grant Permissions for View Only

Grant Permissions for View Only

From
Chris Campbell
Date:
<div class="WordSection1"><p class="MsoNormal">Hello,<p class="MsoNormal"> <p class="MsoNormal">Using pgAdmin III
version1.14.3, PostgreSQL 9.1.5,  Windows 7/64 bit<p class="MsoNormal"> <p class="MsoNormal">I’ve created a Role in a
newdatabase called [appuser].  I’d like this user to be able to run queries and view data in tables, but not be able to
alteranything in the given schema.  So I issued the following command:<p class="MsoNormal"> <p class="MsoNormal"
style="text-autospace:none"><spanstyle="font-size:9.5pt;font-family:Consolas;color:#A31515">GRANT SELECT ON ALL TABLES
INSCHEMA schema1</span><span style="font-size:9.5pt;font-family:Consolas"> <span style="color:#A31515">TO
appuser;</span></span><pclass="MsoNormal" style="text-autospace:none"><span
style="font-size:9.5pt;font-family:Consolas;color:#A31515"> </span><pclass="MsoNormal"
style="text-autospace:none"><spanstyle="color:black">I then created a new server called viewonly for the [appuser]. 
WhenI drill down to the tables and attempt to “view” the records, I get a permissions error.</span><p class="MsoNormal"
style="text-autospace:none"><spanstyle="color:black"> </span><p class="MsoNormal" style="text-autospace:none"><span
style="color:black">ERROR:permission denied for schema schema1</span><p class="MsoNormal"
style="text-autospace:none"><spanstyle="color:black">Line 1: Select count(*) AS rows FROM ONLY schema1.mytable</span><p
class="MsoNormal"style="text-autospace:none"><span style="color:black"> </span><p class="MsoNormal"
style="text-autospace:none"><spanstyle="color:black">So I went back and added the following permission:</span><p
class="MsoNormal"style="text-autospace:none"><span style="color:black"> </span><p class="MsoNormal"
style="text-autospace:none"><spanstyle="font-size:9.5pt;font-family:Consolas;color:#A31515">GRANT EXECUTE ON ALL
FUNCTIONSIN SCHEMA schema1 TO appuser;</span><p class="MsoNormal" style="text-autospace:none"><span
style="color:black"> </span><pclass="MsoNormal" style="text-autospace:none"><span style="color:black">Didn’t work.  So
Ithen added:</span><p class="MsoNormal" style="text-autospace:none"><span style="color:black"> </span><p
class="MsoNormal"style="text-autospace:none"><span style="font-size:9.5pt;font-family:Consolas;color:#A31515">GRANT
EXECUTEON ALL FUNCTIONS IN SCHEMA public TO appuser;</span><p class="MsoNormal" style="text-autospace:none"><span
style="color:black"> </span><pclass="MsoNormal" style="text-autospace:none"><span style="color:black">Still doesn’t
work. </span><p class="MsoNormal" style="text-autospace:none"><span style="color:black"> </span><p class="MsoNormal"
style="text-autospace:none"><spanstyle="color:black">What am I missing and how do I fix this so a user can “view” but
notchange data using pgAdmin III?</span><p class="MsoNormal" style="text-autospace:none"><span
style="color:black"> </span><pclass="MsoNormal" style="text-autospace:none"><span style="color:black">Thanks,</span><p
class="MsoNormal"style="text-autospace:none"><span style="color:black"> </span><p class="MsoNormal"
style="text-autospace:none"><spanstyle="color:black">Chris</span><p class="MsoNormal"> <p class="MsoNormal"> <p
class="MsoNormal"> </div>

Re: Grant Permissions for View Only

From
Chris Campbell
Date:

 

>From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Chris Campbell
>Sent: Tuesday, November 27, 2012 8:55 PM
>To: pgadmin-support@postgresql.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? 

Re: Grant Permissions for View Only

From
Guillaume Lelarge
Date:
On Thu, 2012-11-29 at 10:26 -0800, Chris Campbell wrote:
> >From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Chris
Campbell
> >Sent: Tuesday, November 27, 2012 8:55 PM
> >To: pgadmin-support@postgresql.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
intables, 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.


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

Re: Grant Permissions for View Only

From
Chris Campbell
Date:
> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-
> owner@postgresql.org] On Behalf Of Guillaume Lelarge
> Sent: Thursday, November 29, 2012 1:23 PM
> To: Chris Campbell
> Cc: pgadmin-support@postgresql.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@postgresql.org
> > >[mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Chris
> > >Campbell
> > >Sent: Tuesday, November 27, 2012 8:55 PM
> > >To: pgadmin-support@postgresql.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.
> 
> 
> --
> Guillaume
> http://blog.guillaume.lelarge.info
> http://www.dalibo.com
> 
> 
> 
> --
> Sent via pgadmin-support mailing list (pgadmin-support@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgadmin-support


Indeed it does.  Thank you.

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

Thanks again.

Chris

Re: Grant Permissions for View Only

From
Guillaume Lelarge
Date:
On Thu, 2012-11-29 at 13:51 -0800, Chris Campbell wrote:
> > -----Original Message-----
> > From: pgadmin-support-owner@postgresql.org [mailto:pgadmin-support-
> > owner@postgresql.org] On Behalf Of Guillaume Lelarge
> > Sent: Thursday, November 29, 2012 1:23 PM
> > To: Chris Campbell
> > Cc: pgadmin-support@postgresql.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@postgresql.org
> > > >[mailto:pgadmin-support-owner@postgresql.org] On Behalf Of Chris
> > > >Campbell
> > > >Sent: Tuesday, November 27, 2012 8:55 PM
> > > >To: pgadmin-support@postgresql.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