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)

Search Discussions

  • Rob Sargent at Feb 28, 2011 at 3:22 pm

    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)
  • Bill Moran at Feb 28, 2011 at 3:29 pm

    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/
  • Vivek Khera at Feb 28, 2011 at 3:26 pm

    On Mon, Feb 28, 2011 at 9:37 AM, Borek Lupomesky 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.
  • Tom Lane at Feb 28, 2011 at 4:03 pm

    Vick Khera writes:
    On Mon, Feb 28, 2011 at 9:37 AM, Borek Lupomesky 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
  • Adrian Klaver at Feb 28, 2011 at 4:10 pm

    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 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
  • Tom Lane at Feb 28, 2011 at 4:17 pm

    Adrian Klaver 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
  • Adrian Klaver at Feb 28, 2011 at 5:06 pm

    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
  • Tom Lane at Feb 28, 2011 at 5:15 pm

    Adrian Klaver 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
  • Adrian Klaver at Feb 28, 2011 at 5:21 pm

    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
  • Borek Lupomesky at Feb 28, 2011 at 4:25 pm

    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
  • Vibhor Kumar at Feb 28, 2011 at 3:30 pm

    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
  • Adrian Klaver at Feb 28, 2011 at 3:39 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedFeb 28, '11 at 3:16p
activeFeb 28, '11 at 5:21p
posts13
users7
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase