>>> "REVOKE ALL ON FUNCTION piti() FROM PUBLIC" >>> >>> Doe not seam to have any effect on functions installed as a trigger. >> >> Does your "common user" have the permission to create users? > > No (although the one I've initially tested this scenario on, was in a > group that did have that permission). [...] > I hope you can copy the results. I can indeed recreate something similar here on PostgreSQL 8.1.4. Permissions on a trigger function seem to not be checked, and I can execute a function for which I have no privileges. I consider this a security leak - or am I missing something? Here is a _complete_ example: As superuser, create a trigger function that selects from pg_authid with SECURITY INVOKER, and REVOKE EXECUTE FROM public: test=# \c test postgres You are now connected to database "test" as user "postgres". test=# CREATE OR REPLACE FUNCTION insert_oid() RETURNS trigger AS test-# $$BEGIN SELECT oid INTO NEW.useroid FROM pg_catalog.pg_authid WHERE rolname = user; RETURN NEW; END;$$ test-# LANGUAGE plpgsql STABLE STRICT SECURITY DEFINER; CREATE FUNCTION test=# REVOKE EXECUTE ON FUNCTION insert_oid() FROM public; REVOKE test=# SELECT proacl FROM pg_catalog.pg_proc WHERE proname = 'insert_oid'; proacl ----------------------- {postgres=X/postgres} (1 row) As normal user, try to execute the function or select from pg_catalog.pg_authid directly, both fail as expected. test=# \c test laurenz You are now connected to database "test" as user "laurenz". test=> SELECT insert_oid(); ERROR: permission denied for function insert_oid test=> SELECT oid FROM pg_catalog.pg_authid WHERE rolname = user; ERROR: permission denied for relation pg_authid Create a temporary table, define a trigger BEFORE INSERT using the function that we cannot execute: test=> CREATE TEMP TABLE lautest (id integer PRIMARY KEY, useroid oid NOT NULL); NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "lautest_pkey" for table "lautest" CREATE TABLE test=> CREATE TRIGGER insert_oid BEFORE INSERT ON lautest FOR EACH ROW EXECUTE PROCEDURE insert_oid(); CREATE TRIGGER Insert a row into the table. The trigger function is executed, and I have selected a value from pg_authid! test=> INSERT INTO lautest (id) VALUES (1); INSERT 0 1 test=> SELECT * FROM lautest; id | useroid ----+--------- 1 | 10 (1 row) Yours, Laurenz Albe