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