Thread: Unable to delete row on 7.3.2 with schemas

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

Fernando Schapachnik <fernando@mecon.gov.ar> 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

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