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