Re: Permission denied when inserting

On Monday, February 28, 2011 8:02:53 am Tom Lane wrote:
> Vick Khera <vivek(at)khera(dot)org> writes:
> > On Mon, Feb 28, 2011 at 9:37 AM, Borek Lupomesky <borek(at)lupomesky(dot)cz> wrote:
> >> "permout_site_fkey" FOREIGN KEY (site, cp) REFERENCES out2cp(site,
> >> cp) ON DELETE CASCADE
> >
> > you don't have permissions to verify the FK constraint. the query you
> > see in the error is exactly this test.
>
> Right. But actually, that query will be run with the permissions of the
> owner of the table, so it's that user (not necessarily the one doing the
> INSERT) who lacks permissions.
>
> It sounds like the dump-and-restore process was pretty incomplete.
> How was it done exactly? Not with pg_dumpall, I'm guessing.
>
> regards, tom lane

The OP listed the permissions for the tables:

pam=> \z permout
Access privileges for
database "spam"
Schema | Name | Type |
Access privileges
--------+---------+-------+-----------------------------------------------------------------------------------------------------------
public | permout | table |
{borelupo=arwdxt/borelupo,swcoll=r/borelupo,swcgi=arwdx/borelupo,spamdump=r/borelupo,facility=r/borelupo}
(1 row)

spam=> \z out2cp
Access privileges for
database "spam"
Schema | Name | Type |
Access privileges
--------+--------+-------+-------------------------------------------------------------------------------------------------------
public | out2cp | table |
{swcoll=r/petrcech,swcgi=r/petrcech,spamdump=r/petrcech,facility=r/petrcech,borelupo=arwdxt/petrcech}
(1 row)

Looks like borelupo is owner of permout and has permissions on out2cp.

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com