From: Luis Sousa <llsousa@ualg.pt>
To: Robert Treat <xzilla@users.sourceforge.net>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Re: Permission on insert rules
Date: Wed, 13 Nov 2002 10:44:19 +0000
Message-ID: <3DD22D03.4090505@ualg.pt> (raw)
References: <web-1822655@davinci.ethosmedia.com>
<1037137215.27220.64.camel@camel>
Hi again,
I already know what's the problem. Actually, everything works fine in
the example posted by Robert. Part of my rule is as simple as that
example, but I'm also calling functions inside the rule.
I have a table, whose primary key is a serial, that is connected to a
few tables. In this view, I want to insert data, in the main table, and
also in the "child" tables. My idea was to create a rule, that first
inserts in the parent table, and some functions, that will select the
parent table returning the id created, and will insert some data on
child table (I'm open for sugestions to do this !!!). I don't know
exactly how this works if more than one user at the same time !!!!
When inserting, using the rule, the insert that's defined on the rule
works fine, but the insert defined inside the function, doesn't (that's
the one that gives permssion denied).
Suppose these definitions:
-- Tables definition
CREATE TABLE "pessoal" (
"idPessoal" serial,
"titulo" text default '',
"nome" text NOT NULL,
PRIMARY KEY ("idPessoal")
);
CREATE TABLE "pessoalGabinete" (
"idPessoal" int4,
edificio text,
sala text,
PRIMARY KEY ("idPessoal",edificio,sala),
FOREIGN KEY("idPessoal") REFERENCES pessoal
ON UPDATE CASCADE
);
-- View definition
CREATE VIEW "pessoalInfo_v" AS
SELECT p.titulo, p.nome, pg.edificio, pg.sala
FROM pessoal p LEFT OUTER JOIN "pessoalGabinete" pg USING ("idPessoal");
-- Function definition
CREATE FUNCTION "pessoalInfoGab_f_insert"(text,text)
RETURNS boolean AS '
DECLARE
f_edificio ALIAS FOR $1;
f_sala ALIAS FOR $2;
pessoal RECORD;
BEGIN
SELECT MAX("idPessoal") AS max INTO pessoal
FROM pessoal;
INSERT INTO "pessoalGabinete" ("idPessoal",edificio,sala)
VALUES (pessoal.max,f_edificio,f_sala);
RETURN 1;
END; '
LANGUAGE 'plpgsql';
-- Rule definition
CREATE RULE "pessoalInfo_r_insert" AS ON INSERT TO "pessoalInfo_v"
DO INSTEAD (
INSERT INTO pessoal (titulo,nome)
VALUES (NEW.titulo,NEW.nome);
SELECT "pessoalInfoGab_f_insert"(NEW.edificio,NEW.sala) AS ok;
);
GRANT SELECT,INSERT,UPDATE on "pessoalInfo_v" to nobody;
GRANT INSERT,UPDATE on "pessoal_idPessoal_seq" to nobody;
INSERT INTO "pessoalInfo_v" (titulo,nome,edificio,sala) VALUES
('Dr.','Robert','A',5);
And I got this message:
NOTICE: Error occurred while executing PL/pgSQL function
pessoalInfoGab_f_insert
NOTICE: line 10 at SQL statement
ERROR: pessoalGabinete: Permission denied.
But, suppose that I use this rule instead and that already exists in
table pessoal "idPessoal"=1:
-- Rule definition
CREATE RULE "pessoalInfo_r_insert" AS ON INSERT TO "pessoalInfo_v"
DO INSTEAD (
INSERT INTO pessoal (titulo,nome)
VALUES (NEW.titulo,NEW.nome);
INSERT INTO "pessoalGabinete" ("idPessoal",edificio,sala)
VALUES (1,NEW.edificio,NEW.sala);
);
In this case everything works fine, but this doesn't solve my problem,
because I need to know whats the number created by the sequence in pessoal.
Any ideas ??
Thanks in advance.
Luis Sousa
Robert Treat wrote:
>This should be a test case for what Luis wants, although it works in
>7.2.1 so maybe not. Luis, if this isn't what your trying to do, you'll
>need to post some code:
>
>create table parent (id int, name text, misc text);
>
>create view child as select id,name from parent;
>
>create rule jammasterjay as on insert to child do instead insert into
>parent values (new.id,new.name);
>
>insert into parent values (1,'one','wahad');
>insert into parent values (2,'two','ithnain');
>insert into parent values (3,'three','thalata');
>
>select * from parent;
>select * from child;
>
>insert into child (4,'four');
>
>select * from parent;
>
>create user mellymel;
>grant select on child to mellymel;
>grant insert on child to mellymel;
>
>** reconnect as mellymel **
>
>select * from parent; (generates error)
>select * from child;
>
>insert into child values (5,'five');
>
>select * from child; (has all 5 rows)
>
>
>Robert Treat
>
>On Tue, 2002-11-12 at 12:29, Josh Berkus wrote:
>
>
>>Luis,
>>
>>
>>
>>>That's what I already made. The problem is when I do the update, I
>>>permission denied in all the tables for update and insert. The user
>>>that's making this operation only have select privilege.
>>>Any way, I'm using version 7.2.1-2 for debian.
>>>
>>>
>>I can't reproduce the problem, and permissions did not get fixed
>>between 7.2.1 and 7.2.3. So I'm pretty sure that you're missing
>>something, somewhere.
>>
>>Please post:
>>
>>1) The table definitions for the tables being updated.
>>2) The view definition and permissions
>>3) The Rules statements defined on the view
>>4) A copy of your database session where your update is denied,
>>including the exact error message received.
>>
>>Without that information, no futher help is available.
>>
>>-Josh Berkus
>>
>>
>>
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>>
>>
>
>
>
>
>
>
>
Attachments:
smime.p7s (application/x-pkcs7-signature, 3.2K) download
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: llsousa@ualg.pt, xzilla@users.sourceforge.net, josh@agliodbs.com
Subject: Re: Permission on insert rules
In-Reply-To: <3DD22D03.4090505@ualg.pt>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox