Thread: Permission denied when inserting

Permission denied when inserting

From

Borek Lupomesky

Date:

28 February 2011, 14:16:13

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)

Re: Permission denied when inserting

From

Rob Sargent

Date:

28 February 2011, 14:22:51

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

Re: Permission denied when inserting

From

Vick Khera

Date:

28 February 2011, 14:26:08

On Mon, Feb 28, 2011 at 9:37 AM, Borek Lupomesky <borek@lupomesky.cz> wrote:
>    "permout_site_fkey" FOREIGN KEY (site, cp) REFERENCES out2cp(site, cp) ON
> DELETE CASCADE
>

you don't have permissions to verify the FK constraint. the query you
see in the error is exactly this test.

Re: Permission denied when inserting

From

Bill Moran

Date:

28 February 2011, 14:29:29

In response to Rob Sargent <robjsargent@gmail.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@postgresql.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/

Re: Permission denied when inserting

From

Vibhor Kumar

Date:

28 February 2011, 14:30:35

On Feb 28, 2011, at 8:07 PM, Borek Lupomesky wrote:

> 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.textAND "cp"::pg_catalog.text OPERATOR(pg_catalog.=) $2::pg_catalog.text FOR SHARE OF x" 



Try to give SELECT privileges to user on table "public"."out2cp"

Thanks & Regards,
Vibhor Kumar
EnterpriseDB Corporation
vibhor.kumar@enterprisedb.com
Blog:http://vibhork.blogspot.com

Re: Permission denied when inserting

From

Adrian Klaver

Date:

28 February 2011, 14:39:08

On Monday, February 28, 2011 6:37:51 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').

Just to be clear where you borelupo when you ran the above statement?
Also what do:
SELECT current_user;
SELECT session_user;

show when you are logged in?



--
Adrian Klaver
adrian.klaver@gmail.com

Re: Permission denied when inserting

From

Tom Lane

Date:

28 February 2011, 15:03:06

Vick Khera <vivek@khera.org> writes:
> On Mon, Feb 28, 2011 at 9:37 AM, Borek Lupomesky <borek@lupomesky.cz> wrote:
>> � �"permout_site_fkey" FOREIGN KEY (site, cp) REFERENCES out2cp(site, cp) ON
>> DELETE CASCADE

> you don't have permissions to verify the FK constraint. the query you
> see in the error is exactly this test.

Right.  But actually, that query will be run with the permissions of the
owner of the table, so it's that user (not necessarily the one doing the
INSERT) who lacks permissions.

It sounds like the dump-and-restore process was pretty incomplete.
How was it done exactly?  Not with pg_dumpall, I'm guessing.

            regards, tom lane

Re: Permission denied when inserting

From

Adrian Klaver

Date:

28 February 2011, 15:10:11

On Monday, February 28, 2011 8:02:53 am Tom Lane wrote:
> Vick Khera <vivek@khera.org> writes:
> > On Mon, Feb 28, 2011 at 9:37 AM, Borek Lupomesky <borek@lupomesky.cz> wrote:
> >>    "permout_site_fkey" FOREIGN KEY (site, cp) REFERENCES out2cp(site,
> >> cp) ON DELETE CASCADE
> >
> > you don't have permissions to verify the FK constraint. the query you
> > see in the error is exactly this test.
>
> Right.  But actually, that query will be run with the permissions of the
> owner of the table, so it's that user (not necessarily the one doing the
> INSERT) who lacks permissions.
>
> It sounds like the dump-and-restore process was pretty incomplete.
> How was it done exactly?  Not with pg_dumpall, I'm guessing.
>
>             regards, tom lane

The OP listed the permissions for the tables:

pam=> \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)

Looks like borelupo is owner of permout and has permissions on out2cp.

--
Adrian Klaver
adrian.klaver@gmail.com

Re: Permission denied when inserting

From

Tom Lane

Date:

28 February 2011, 15:17:19

Adrian Klaver <adrian.klaver@gmail.com> writes:
> On Monday, February 28, 2011 8:02:53 am Tom Lane wrote:
>> Right.  But actually, that query will be run with the permissions of the
>> owner of the table, so it's that user (not necessarily the one doing the
>> INSERT) who lacks permissions.

> The OP listed the permissions for the tables:

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

> Looks like borelupo is owner of permout and has permissions on out2cp.

But what matters is the permissions of the owner of out2cp, which looks
from this to be petrcech.  And I don't see that he's granted himself
any permissions.

            regards, tom lane

Re: Permission denied when inserting (SOLVED)

From

Borek Lupomesky

Date:

28 February 2011, 15:25:09

On 28.2.2011 17:02, Tom Lane wrote:
>
>> you don't have permissions to verify the FK constraint. the query you
>> see in the error is exactly this test.
> Right.  But actually, that query will be run with the permissions of the
> owner of the table, so it's that user (not necessarily the one doing the
> INSERT) who lacks permissions.

    Ah! That's the piece information I didn't know. Yes, after doing
'ALTER TABLE out2cp OWNER TO "borelupo"' all is fine and dandy.
    Thank a lot, Tom.

> It sounds like the dump-and-restore process was pretty incomplete.
> How was it done exactly?  Not with pg_dumpall, I'm guessing.
>

    I used pg_restore and didn't use the right set of switches... I
know, silly me.

Borek

Re: Permission denied when inserting

From

Adrian Klaver

Date:

28 February 2011, 16:06:39

On Monday, February 28, 2011 8:17:07 am Tom Lane wrote:
> Adrian Klaver <adrian.klaver@gmail.com> writes:
> > On Monday, February 28, 2011 8:02:53 am Tom Lane wrote:
> >> Right.  But actually, that query will be run with the permissions of the
> >> owner of the table, so it's that user (not necessarily the one doing the
> >> INSERT) who lacks permissions.
> >
> > The OP listed the permissions for the tables:
> >
> > 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/petrce
> > ch,borelupo=arwdxt/petrcech} (1 row)
> >
> > Looks like borelupo is owner of permout and has permissions on out2cp.
>
> But what matters is the permissions of the owner of out2cp, which looks
> from this to be petrcech.  And I don't see that he's granted himself
> any permissions.
>
>             regards, tom lane

I understand, yet I don't:) Seems I have a misconception of the the FOREIGN KEY
process. The error was on the query below, which I am taking is the query you
refer to above. To me it looks like a look up from the referencing(permout)
table to the referenced(out2cp) one. The part I am not clear on is why that runs
with the permissions of the referenced table not the referencing table?


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"
--
Adrian Klaver
adrian.klaver@gmail.com

Re: Permission denied when inserting

From

Tom Lane

Date:

28 February 2011, 16:15:40

Adrian Klaver <adrian.klaver@gmail.com> writes:
> I understand, yet I don't:) Seems I have a misconception of the the
> FOREIGN KEY process. The error was on the query below, which I am
> taking is the query you refer to above. To me it looks like a look up
> from the referencing(permout) table to the referenced(out2cp) one. The
> part I am not clear on is why that runs with the permissions of the
> referenced table not the referencing table?

Because we force it that way so that you don't have to splatter
permissions all around to support a foreign key check.  Generally
the internal FK check queries will only fail if the owner of a table
revokes his own permissions, which isn't too usual.

            regards, tom lane

Re: Permission denied when inserting

From

Adrian Klaver

Date:

28 February 2011, 16:21:40

On Monday, February 28, 2011 9:15:27 am Tom Lane wrote:
> Adrian Klaver <adrian.klaver@gmail.com> writes:
> > I understand, yet I don't:) Seems I have a misconception of the the
> > FOREIGN KEY process. The error was on the query below, which I am
> > taking is the query you refer to above. To me it looks like a look up
> > from the referencing(permout) table to the referenced(out2cp) one. The
> > part I am not clear on is why that runs with the permissions of the
> > referenced table not the referencing table?
>
> Because we force it that way so that you don't have to splatter
> permissions all around to support a foreign key check.  Generally
> the internal FK check queries will only fail if the owner of a table
> revokes his own permissions, which isn't too usual.
>
>             regards, tom lane

Had not thought about it from that angle. Thanks.

--
Adrian Klaver
adrian.klaver@gmail.com