Re: Permission denied when inserting

In response to Rob Sargent <robjsargent(at)gmail(dot)com>:

> On 02/28/2011 07:37 AM, Borek Lupomesky wrote:
> > Hello,
> >
> > I have a database app that worked fine until we reinstalled the
> > server with the related DB dump and restore. Most of the stuff works
> > fine after the reinstall, but one particular insert gives very cryptic
> > (for me) message:
> >
> > spam=> INSERT INTO permout ( site, cp, owner, descr, creat_who ) VALUES
> > ( 'vin', '3035.1', 'borelupo', 'test', 'borelupo' );
> > ERROR: permission denied for relation out2cp
> > CONTEXT: SQL statement "SELECT 1 FROM ONLY "public"."out2cp" x WHERE
> > "site"::pg_catalog.text OPERATOR(pg_catalog.=) $1::pg_catalog.text AND
> > "cp"::pg_catalog.text OPERATOR(pg_catalog.=) $2::pg_catalog.text FOR
> > SHARE OF x"
> >
> > Note, that I am inserting into table "permout", but the message is
> > about permission for "out2cp". Any idea what went wrong? When I was
> > doing the dump I forgot to dump all the permissions as well so I had to
> > restore them manually and possibly something is not right somewhere, but
> > the error message given is of no help to me. What does the "CONTEXT"
> > message actually mean?
> > More information is below (I'm logged in as user 'borelupo').

As you show below, permout has a foreign key constraint to out2cp. I'm
guessing that the error message is the result of PostgreSQL validating that
the key exists, but you haven't given sufficient permissions on out2cp
to view rows in that table, thus the foreign key check is unable to
execute, thus the row can not be inserted.

> >
> > Thanks in advance to anyone who can direct me in the right direction.
> > Borek
> >
> >
> > spam=> \d permout
> > Table "public.permout"
> > Column | Type | Modifiers
> > ------------+-----------------------------+------------------------
> > site | character varying(3) | not null
> > cp | character varying(10) | not null
> > valfrom | timestamp without time zone | not null default now()
> > valuntil | timestamp without time zone |
> > owner | character varying(32) | not null
> > descr | character varying(64) |
> > creat_who | character varying(8) |
> > creat_when | timestamp without time zone | default now()
> > chg_who | character varying(8) |
> > chg_when | timestamp without time zone |
> > Indexes:
> > "permout_pkey" PRIMARY KEY, btree (site, cp)
> > Foreign-key constraints:
> > "permout_site_fkey" FOREIGN KEY (site, cp) REFERENCES out2cp(site,
> > cp) ON DELETE CASCADE
> >
> > spam=> \d out2cp
> > Table "public.out2cp"
> > Column | Type | Modifiers
> > ----------+-----------------------+---------------
> > site | character varying(3) | not null
> > cp | character varying(10) | not null
> > outlet | character varying(10) | not null
> > location | character varying(32) |
> > dont_age | boolean | default false
> > fault | boolean | default false
> > coords | character varying(4) |
> > Indexes:
> > "out2cp_pkey" PRIMARY KEY, btree (site, cp)
> > "myo2c" UNIQUE, btree (site, cp, outlet)
> > "o2c_outlet" UNIQUE, btree (site, outlet)
> > "o2c_cp" btree (cp)
> > "o2c_site" btree (site)
> >
> > spam=> \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)
> >
> > spam=> select version();
> > version
> > -----------------------------------------------------------------------------------------------
> >
> > PostgreSQL 8.3.14 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real
> > (Debian 4.3.2-1.1) 4.3.2
> > (1 row)
> >
> >
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/