Grant question

[Barbara Lindsey]

What kind of grant do you need to give a user so that they can have
permission to do a "SELECT nextval(ID)" on a sequence?
I granted the user SELECT,UPDATE,INSERT,DELETE on all the tables,
including the one that has the sequence, but the sequence query is
failing on permissions.

--
Barbara E. Lindsey,
COG RDC
Phone: [phone] ext. 314 Fax: [phone]

----
CONFIDENTIALITY NOTICE: The information contained in this electronic
message is legally privileged and confidential and intended only for the
use of the individual(s) or entity(ies) named above. If the reader of
this message is not the intended recipient, you are hereby notified that
any dissemination, distribution, or copying of this email or any of it's
components is strictly prohibited. If you have received this email in
error, please contact the sender.
----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Martín Marqués]

Mensaje citado por Barbara Lindsey <bl******@cog.ufl.edu>:

What kind of grant do you need to give a user so that they can have
permission to do a "SELECT nextval(ID)" on a sequence?
I granted the user SELECT,UPDATE,INSERT,DELETE on all the tables,
including the one that has the sequence, but the sequence query is
failing on permissions.

You have to give him grant permissons on the sequence, not only the table, as when
you do a nextval(´sequece_name´) you are updating the value of the sequence.

P.D.: Could there be some extra docs about this in the GRANT command manual:

http://www.postgresql.org/docs/curre...sql-grant.html

It speeks about granting all kind of relations, but not sequences. Just a tip.

--
select 'mmarques' || '@' || 'unl.edu.ar' AS email;
-------------------------------------------------------
Martín Marqués | Programador, DBA
Centro de Telemática | Administrador
Universidad Nacional
del Litoral
-------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Bruno Wolff III]

On Thu, Jan 29, 2004 at 09:52:56 -0500,
Barbara Lindsey <bl******@cog.ufl.edu> wrote:

What kind of grant do you need to give a user so that they can have
permission to do a "SELECT nextval(ID)" on a sequence?
I granted the user SELECT,UPDATE,INSERT,DELETE on all the tables,
including the one that has the sequence, but the sequence query is
failing on permissions.

Sequences have permissions separate from tables. I believe you need
update access to call nextval or setval on a sequence. This should
be described in the documention on the GRANT command.

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Barbara Lindsey]

Yay! It works.
Thank you.

Bruno Wolff III wrote:

On Thu, Jan 29, 2004 at 09:52:56 -0500,
Barbara Lindsey <bl******@cog.ufl.edu> wrote:

What kind of grant do you need to give a user so that they can have
permission to do a "SELECT nextval(ID)" on a sequence?
I granted the user SELECT,UPDATE,INSERT,DELETE on all the tables,
including the one that has the sequence, but the sequence query is
failing on permissions.

Sequences have permissions separate from tables. I believe you need
update access to call nextval or setval on a sequence. This should
be described in the documention on the GRANT command.

--
Barbara E. Lindsey,
COG RDC
Phone: [phone] ext. 314 Fax: [phone]

----
CONFIDENTIALITY NOTICE: The information contained in this electronic
message is legally privileged and confidential and intended only for the
use of the individual(s) or entity(ies) named above. If the reader of
this message is not the intended recipient, you are hereby notified that
any dissemination, distribution, or copying of this email or any of it's
components is strictly prohibited. If you have received this email in
error, please contact the sender.
----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[John Sidney-Woollett]

I think you should provide an explicit grant (all) on the sequence as
well. The Sequence is an object that is distinct from the table that uses
it.

John Sidney-Woollett

Barbara Lindsey said:

What kind of grant do you need to give a user so that they can have
permission to do a "SELECT nextval(ID)" on a sequence?
I granted the user SELECT,UPDATE,INSERT,DELETE on all the tables,
including the one that has the sequence, but the sequence query is
failing on permissions.

--
Barbara E. Lindsey,
COG RDC
Phone: [phone] ext. 314 Fax: [phone]

----
CONFIDENTIALITY NOTICE: The information contained in this electronic
message is legally privileged and confidential and intended only for the
use of the individual(s) or entity(ies) named above. If the reader of
this message is not the intended recipient, you are hereby notified that
any dissemination, distribution, or copying of this email or any of it's
components is strictly prohibited. If you have received this email in
error, please contact the sender.
----
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

[Barbara Lindsey]

What permission does a user need to have to query the pg_ tables
(pg_attribute, pg_class, etc) ? Do they require special permissions?
arbara Lindsey wrote:

What kind of grant do you need to give a user so that they can have
permission to do a "SELECT nextval(ID)" on a sequence?
I granted the user SELECT,UPDATE,INSERT,DELETE on all the tables,
including the one that has the sequence, but the sequence query is
failing on permissions.

--
Barbara E. Lindsey,
COG RDC
Phone: [phone] ext. 314 Fax: [phone]

----
CONFIDENTIALITY NOTICE: The information contained in this electronic
message is legally privileged and confidential and intended only for the
use of the individual(s) or entity(ies) named above. If the reader of
this message is not the intended recipient, you are hereby notified that
any dissemination, distribution, or copying of this email or any of it's
components is strictly prohibited. If you have received this email in
error, please contact the sender.
----
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Martin Marques]

El Jue 29 Ene 2004 16:26, Barbara Lindsey escribió:

What permission does a user need to have to query the pg_ tables
(pg_attribute, pg_class, etc) ? Do they require special permissions?

What kind of queries? Read or Write?

Users have permission to read from most catalog tables. One they can't read
for security resons is pg_shadow:

prueba=> select * from pg_shadow;
ERROR: pg_shadow: permission denied

Saludos... :-)

--
16:44:01 up 64 days, 23:00, 3 users, load average: 0.46, 0.38, 0.30
-----------------------------------------------------------------
Martín Marqués | select 'mmarques' || '@' || 'unl.edu.ar'
Centro de Telematica | DBA, Programador, Administrador
Universidad Nacional
del Litoral
-----------------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[btober]

On Thu, Jan 29, 2004 at 09:52:56 -0500,
Barbara Lindsey <bl******@cog.ufl.edu> wrote:

What kind of grant do you need to give a user so that they can have
permission to do a "SELECT nextval(ID)" on a sequence?
I granted the user SELECT,UPDATE,INSERT,DELETE on all the tables,
including the one that has the sequence, but the sequence query is
failing on permissions.

Sequences have permissions separate from tables. I believe you need
update access to call nextval or setval on a sequence. This should be
described in the documention on the GRANT command.

CREATE SEQUENCE paid.person_person_pk_seq
INCREMENT 1
MINVALUE 1
MAXVALUE 9223372036854775807
START 232
CACHE 1;
REVOKE ALL ON TABLE paid.person_person_pk_seq FROM public;
GRANT ALL ON TABLE paid.person_person_pk_seq TO blindsey;
GRANT SELECT, UPDATE ON TABLE paid.person_person_pk_seq TO GROUP app_user;

~Berend Tober


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster