pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedRe: unable to GRANT on sequence
3+ messages / 3 participants
[nested] [flat]
* Re: unable to GRANT on sequence
@ 2005-06-03 16:26 woody <woody.1q2c4a@mail.droptable.com>
2005-06-06 02:41 ` Re: unable to GRANT on sequence Tom Lane <tgl@sss.pgh.pa.us>
2005-06-06 03:49 ` Re: unable to GRANT on sequence Michael Fuhr <mike@fuhr.org>
0 siblings, 2 replies; 3+ messages in thread
From: woody @ 2005-06-03 16:26 UTC (permalink / raw)
To: pgsql-admin
I'm having trouble GRANTing access on sequences. I created a database
called 'asterisk' using the 'postgres' user. I then created 2 users
called 'asterisk' and 'asterisk_owner'. I created a schema called
'asterisk' and GRANTed CREATE, DROP, INSERT, UPDATE, DELETE on this
schema to 'asterisk_owner'. I signed in as 'asterisk_owner' and created
some tables with primary keys based on sequences, then GRANTed SELECT,
UPDATE, INSERT, DELETE on these tables to 'asterisk'. When I log in as
'asterisk', I can insert rows but I can't use the implied nextval(). It
says the relation <seq_name> does not exist. When I do a \dp, I can see
the sequence objects listed, but no permissions are listed. The tables
have all the expected permissions. I couldn't see anything in the
reference for the GRANT command for sequences.
--
woody
------------------------------------------------------------------------
Posted via http://www.codecomments.com
------------------------------------------------------------------------
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: unable to GRANT on sequence
2005-06-03 16:26 Re: unable to GRANT on sequence woody <woody.1q2c4a@mail.droptable.com>
@ 2005-06-06 02:41 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 3+ messages in thread
From: Tom Lane @ 2005-06-06 02:41 UTC (permalink / raw)
To: woody <woody.1q2c4a@mail.droptable.com>; +Cc: pgsql-admin
woody <woody.1q2c4a@mail.droptable.com> writes:
> ... When I log in as
> 'asterisk', I can insert rows but I can't use the implied nextval(). It
> says the relation <seq_name> does not exist.
I'd bet this isn't a permissions problem at all, but a schema search
path problem. Do you have different search paths for asterisk and
asterisk_owner? (Hint: by default, a schema that has the same name as a
user is part of that user's search path, and only that user's search
path.)
regards, tom lane
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: unable to GRANT on sequence
2005-06-03 16:26 Re: unable to GRANT on sequence woody <woody.1q2c4a@mail.droptable.com>
@ 2005-06-06 03:49 ` Michael Fuhr <mike@fuhr.org>
1 sibling, 0 replies; 3+ messages in thread
From: Michael Fuhr @ 2005-06-06 03:49 UTC (permalink / raw)
To: woody <woody.1q2c4a@mail.droptable.com>; +Cc: pgsql-admin
On Fri, Jun 03, 2005 at 11:26:29AM -0500, woody wrote:
>
> I'm having trouble GRANTing access on sequences. I created a database
> called 'asterisk' using the 'postgres' user. I then created 2 users
> called 'asterisk' and 'asterisk_owner'. I created a schema called
> 'asterisk' and GRANTed CREATE, DROP, INSERT, UPDATE, DELETE on this
> schema to 'asterisk_owner'.
Attempting to grant the above privileges on a schema should fail
with a syntax error. What did you really do? Please copy and paste
the actual commands instead of typing them from memory -- sometimes
little differences matter, so it's important that we see exactly
what you're doing and exactly what error messages you get.
Did you grant schema privileges only to asterisk_owner or also to
asterisk? What version of PostgreSQL are you using?
> I signed in as 'asterisk_owner' and created some tables with primary
> keys based on sequences, then GRANTed SELECT, UPDATE, INSERT, DELETE
> on these tables to 'asterisk'.
Again, please show the commands you executed; the \d output for one
of the tables might also be useful. You don't say which schema you
created the tables in -- public? asterisk? Did you use a SERIAL
type or did you create the sequences manually? If manually, what
schema did you create the sequences in? What do the DEFAULT
expressions look like?
> When I log in as 'asterisk', I can insert rows but I can't use the
> implied nextval(). It says the relation <seq_name> does not exist.
Please show the commands you executed and the exact text of the
error message. Are you sure the error is "relation does not exist"
instead of "permission denied for sequence"? What permissions does
the user asterisk have for the schema the sequence is in?
> When I do a \dp, I can see the sequence objects listed, but no
> permissions are listed. The tables have all the expected permissions.
Please post the \dp output.
> I couldn't see anything in the reference for the GRANT command for
> sequences.
What documentation are you looking at? Searching for the word
"sequence" in the GRANT documentation does indeed yield information
about privileges and sequences. Here's a link to the documentation
for the latest version of PostgreSQL:
http://www.postgresql.org/docs/8.0/static/sql-grant.html
In short: make sure the user asterisk has appropriate permissions
on all needed schemas, tables, and sequences. For example, it
probably needs USAGE on the schema asterisk; INSERT, SELECT, UPDATE,
and DELETE on the tables; and SELECT and UPDATE on the sequences.
Read the GRANT documentation closely for more information. Also,
make sure search_path isn't causing a problem -- see the "Schemas"
documentation for more info:
http://www.postgresql.org/docs/8.0/static/ddl-schemas.html#DDL-SCHEMAS-PATH
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2005-06-06 03:49 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-03 16:26 Re: unable to GRANT on sequence woody <woody.1q2c4a@mail.droptable.com>
2005-06-06 02:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-06-06 03:49 ` Michael Fuhr <mike@fuhr.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox