Re: Permission on insert rules

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(at)postgresql(dot)org
>>
>>
>
>
>
>
>
>
>