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/
