Re: permission prob: granted, but still denied

On Wed, Oct 30, 2002 at 01:17:23PM -0800, Stephan Szabo wrote:
> On Wed, 30 Oct 2002 s-psql(at)rhythm(dot)cx wrote:
>
> > On Wed, Oct 30, 2002 at 02:06:11PM -0500, Tom Lane wrote:
> > > s-psql(at)rhythm(dot)cx writes:
> > > > Hello, I'm having some sort of permission problem on my database, running
> > > > version 7.2.3.
> > >
> > > Curious. What exactly is the connection between the two tables?
> > > Standard foreign-key reference, or something else? Could we see the
> > > full schemas for both tables (ideally from pg_dump -s -t)?
> > >
> >
> > The relationship is a standard foreign key, with websess referencing
> > cscuser. I am enclosing the schema for both tables below.
> >
> > Stephan Szabo requested I turn on query logging (is that synonymous with
> > increasing postmaster's debugging level?). I increased the debug level all
> > the way to 5, however I didn't see anything telling in the resulting log.
> > I'm enclosing that as well if it helps anyone.
>
> Well, it seems to say that it is the foreign key select.
>
> > If it increases readability for anyone, I put the schema & log output on my
> > website: http://rhythm.cx/~steve/pg/
> >
> > Richard Huxton also suggested I check the permissions on related sequences -
> > they are ok. Here is a listing of permissions relevant to this problem
> > (webauth is the user with the Permission Denied problem):
> >
> > cscuser | {=,webauth=arwdRxt}
>
> Who is the owner of cscuser? If you log in as that user, does a
> select 1 from cscuser where userid=<some userid> for update of cscuser
> succeed?
>
>

You lead me to the solution.

The owner of table cscuser is 'csclub', who did not have permissions to
cscuser (accidentally revoked them). Earlier I tried "select 1 from cscuser
where userid=<some userid> for update of cscuser" as webauth and it
succeeded, and at that point I was lost.

I just now put one and one together thanks to your post about fkey triggers
being executed as the table owner. I granted all to the table owner, and it
all works now.

Thanks for your help.

-Steve