Re: Permission denied when inserting - Mailing list pgsql-general

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').
>
>    Thanks in advance to anyone who can direct me in the right direction.
>    Borek
>

I wonder if the file permissions survived the re-install?


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