Обсуждение: Grant Permissions for View Only
<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>
>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?
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
> -----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
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