pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Michael Fuhr <mike@fuhr.org>
To: woody <woody.1q2c4a@mail.droptable.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: unable to GRANT on sequence
Date: Sun, 5 Jun 2005 21:49:40 -0600
Message-ID: <20050606034940.GA92561@winnie.fuhr.org> (raw)
In-Reply-To: <woody.1q2c4a@mail.droptable.com>
References: <woody.1q2c4a@mail.droptable.com>

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/



view thread (3+ messages)

Message-ID: <20050606034940.GA92561@winnie.fuhr.org>
Permalink:  ../20050606034940.GA92561@winnie.fuhr.org/
Also on:    postgresql.org/message-id/20050606034940.GA92561@winnie.fuhr.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-admin@postgresql.org
  Cc: mike@fuhr.org, woody.1q2c4a@mail.droptable.com
  Subject: Re: unable to GRANT on sequence
  In-Reply-To: <20050606034940.GA92561@winnie.fuhr.org>
  References: <woody.1q2c4a@mail.droptable.com> <20050606034940.GA92561@winnie.fuhr.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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