Skip site navigation (1) Skip section navigation (2)

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 (view raw or flat)
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

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group