Hi All, This is something that bugs me for some time now. I have: (as user postgres I do) CREATE TABLE debi (id int, name text); REVOKE ALL ON debi FROM public; CREATE FUNCTION piti() RETURNS "trigger" AS $$ DECLARE me RECORD; BEGIN select * into me FROM pg_authid; new.id := me.oid; new.name := me.rolname; return new; END $$ LANGUAGE plpgsql; INSERT INTO debi (id,name) VALUES (22, 'jklsdf'); INSERT 0 1 INSERT INTO debi (id,name) VALUES (22, 'jklsdf'); INSERT 0 1 CREATE VIEW mdebi as SELECT * from debi; GRANT SELECT, insert on mdebi to public; (now I become common user) SELECT * from debi; ERROR: permission denied for relation debi SELECT * from mdebi; id | name ----+---------- 10 | postgres 10 | postgres (2 rows) So far so good. But the insert fails: INSERT INTO mdebi (id,name) VALUES (22, 'jklsdf'); ERROR: permission denied for relation pg_authid CONTEXT: SQL statement "SELECT * from pg_authid" PL/pgSQL function "piti" line 1 at select into variables So it looks like the VIEW have elevated my security level thus allowing me to access the DEBI table (SELECT statement), but inside of the TRIGGER within DEBI I'm back with my original security level. This is weird. I thought trigger functions execute at root/postgres security level? But definitely I though, once I've passed VIEW access control, I'm prity mutch root/postgres superuser. Apparently not so. Why I can "SELECT * FROM pg_authid" within SELECT, and I cannot do that within INSERT (to the same table/view) is a real mistery to me. But, is there a way around it? (meaning: to have a trigger function do it's security related job on a table *not* publically accesable, but available for public access only through a specially designed VIEW). One thing though. I *realy* *really* *need* to do the job using trigger functions. Functions called from within the RULE-set are not an option here - although I wouldn't like to elaborate. Thenx -- -R
