Re: Unable to delete row on 7.3.2 with schemas

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