Permission denied when inserting

From: Borek Lupomesky <borek(at)lupomesky(dot)cz>
To: pgsql-general(at)postgresql(dot)org
Subject: Permission denied when inserting
Date: 2011-02-28 14:37:51
Message-ID: 4D6BB33F.9010704@lupomesky.cz
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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').

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Merlin Moncure 2011-02-28 14:42:36 Re: Binary params in libpq
Previous Message Andre Lopes 2011-02-28 13:08:44 Re: Transactions and ID's generated by triggers