Permission denied when inserting

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.

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?

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/

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.text AND "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

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

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

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

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

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

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

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

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