Re: Permission denied when inserting

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