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
------------------------------------------------------------------------

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

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/s...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/s...L-SCHEMAS-PATH

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend