Looks like this thread have died away.
But since this permission check looks like a security issue to me too,
I'd really apreciate someones explanation on why it is not ... if it is
not.
But if it is a security leak I'd like to pass it over as bug report - so
it does not disapear from sight.
-R
On Thu, 2006-12-14 at 12:51 +0100, Albe Laurenz wrote:
> >>> "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
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
> choose an index scan if your joining column's datatypes do not
> match
