Postgres 7.3.2:

table2 has a foreign key on table1 (id).

Trying to delete a value from table1, not actually referenced from table2.

psql -U usr db

db=> DELETE FROM table1 WHERE id=7;
LOG: query: DELETE FROM table1 WHERE id=7;
LOG: query: SELECT 1 FROM ONLY "public"."table1" x WHERE "id" = $1 FOR UPDATE
OF x
LOG: query: SELECT 1 FROM ONLY "public"."table2" x WHERE "id" = $1 FOR UPDATE
OF x
ERROR: usr: permission denied

db=> \dp table2

Access privileges for database "db"
Schema | Table | Access privileges
--------+------------+-------------------------------------------------------------------
public | table2 | {=,...,usr=arwd,...}
(1 row)

db=> \db table1
Access privileges for database "db"
Schema | Table | Access privileges
--------+------------+-------------------------------------------------------------------
public | table1 | {=,...,usr=arwd,...}
(1 row)

Same happens if I do this as db super-user. "usr" is also an schema name.

Can't see why. Any ideas? Some obvious overlook?

Thanks in advance!

Fernando Schapachnik
Proyecto de Informática
Ministerio de Economía

Search Discussions

  • Tom Lane at Mar 5, 2003 at 12:19 pm

    Fernando Schapachnik writes:
    ERROR: usr: permission denied
    AFAIR, all our "permission denied" messages cite the object in question,
    not the referencing user. So this is complaining about access rights to
    the "usr" schema. You have not shown us enough information to guess why
    this query would need to look into the "usr" schema --- but presumably
    someone needs the USAGE right on "usr", and hasn't got it.

    regards, tom lane
  • Fernando Schapachnik at Mar 5, 2003 at 1:12 pm

    En un mensaje anterior, Tom Lane escribió:
    AFAIR, all our "permission denied" messages cite the object in question,
    not the referencing user. So this is complaining about access rights to
    the "usr" schema. You have not shown us enough information to guess why
    this query would need to look into the "usr" schema --- but presumably
    someone needs the USAGE right on "usr", and hasn't got it.
    Wouldn't pgsql user has all the needed rights? The same happens if I connect as
    pgsql.

    I will fall back to the actual names:

    maquinas.maquinas has a foreign key on public.ubicaciones_fisicas_propias.

    psql -U maquinas db

    db=> \d maquinas.maquinas
    Table "maquinas.maquinas"
    Column | Type | Modifiers
    --------------+---------+--------------------------------------------------------------------
    id_maquina | integer | not null default nextval('maquinas.maquinas_id_maquina_seq'::text)
    id_ubicacion | integer |
    Indexes: maquinas_pkey primary key btree (id_maquina)
    Foreign Key constraints: $1 FOREIGN KEY (id_ubicacion) REFERENCES
    ubicaciones_fisicas_propias(id_ubicacion) ON UPDATE NO ACTION ON DELETE NO
    ACTION DEFERRABLE INITIALLY DEFERRED

    No tuple in maquinas.maquinas is referecing public.
    ubicaciones_fisicas_propias(7).

    db=> SELECT * from maquinas.maquinas where id_ubicacion=7;
    id_maquina | id_ubicacion
    ------------+--------------
    (0 rows)

    mecon=> DELETE from ubicaciones_fisicas_propias WHERE id_ubicacion =7;
    ERROR: maquinas: permission denied


    On the log:
    LOG: query: SELECT 1 FROM ONLY "maquinas"."maquinas" x WHERE "id_ubicacion" =
    $1 FOR UPDATE OF x
    ERROR: maquinas: permission denied

    db=> \dp maquinas.maquinas
    Access privileges for database "mecon"
    Schema | Table | Access privileges
    ----------+----------+-----------------------------------------------------
    maquinas | maquinas | {=,maqadm=arwdRxt,maquinas=arwd,"group maquinas=r"}
    (1 row)

    db=> SELECT * from pg_namespace where nspname='maquinas';
    nspname | nspowner | nspacl
    ----------+----------+---------------------------------------------
    maquinas | 103 | {=,maqadm=UC,maquinas=U,"group maquinas=U"}
    (1 row)


    If more info is needed, I'd gladly provide it (even a dump, if needed).

    Thanks and regards.


    Lic. Fernando Schapachnik
    Proyecto de Informática
    Ministerio de Economía

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedFeb 27, '03 at 2:32p
activeMar 5, '03 at 1:12p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Fernando Schapachnik: 2 posts Tom Lane: 1 post

People

Translate

site design / logo © 2022 Grokbase