Re: about the RULE system - Mailing list pgsql-general
From | Albe Laurenz |
---|---|
Subject | Re: about the RULE system |
Date | |
Msg-id | 52EF20B2E3209443BC37736D00C3C1380BE31DA0@EXADV1.host.magwien.gv.at Whole thread Raw |
In response to | about the RULE system (Rafal Pietrak <rafal@zorro.isa-geek.com>) |
Responses |
Re: about the RULE system
(Rafal Pietrak <rafal@zorro.isa-geek.com>)
|
List | pgsql-general |
>>> "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
pgsql-general by date: