From: | Erwin Brandstetter <brandstetter(at)falter(dot)at> |
---|---|
To: | "pgadmin-hackers(at)postgresql(dot)org" <pgadmin-hackers(at)postgresql(dot)org> |
Subject: | Missing REVOKE in SQL for functions with SECURITY DEFINER (or any function, really) |
Date: | 2012-02-27 22:38:44 |
Message-ID: | 4F4C05F4.3000301@falter.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgadmin-hackers |
Hi developers!
Congratulations on the many bug fixes in the latest release!
I think I found another serious problem.
Testing with pgAdmin 1.14.2 on Windows XP. Server is PostgreSQL 9.1 on
Devian Squeeze.
There is a security hazard lingering in the reverse engineered SQL of
the latest version 1.14.2 (and versions before it).
As summed up here
http://www.postgresql.org/docs/current/interactive/sql-createfunction.html#SQL-CREATEFUNCTION-SECURITY
the execute privilege is granted to PUBLIC by default. It needs to be
revoked for security critical functions.
I quote the manual:
> Another point to keep in mind is that by default, execute privilege is
> granted to PUBLIC for newly created functions (see GRANT
> <http://www.postgresql.org/docs/current/interactive/sql-grant.html>
> for more information). Frequently you will wish to restrict use of a
> security definer function to only some users. To do that, you must
> revoke the default PUBLIC privileges and then grant execute privilege
> selectively.
This goes wrong with pgAdmin 1.14.2. Consider this test case, executed
as superuser postgres:
CREATE OR REPLACE FUNCTION foo ()
RETURNS void AS
$BODY$
BEGIN
PERFORM 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
ALTER FUNCTION foo() SET search_path=public, pg_temp;
*REVOKE ALL ON FUNCTION foo() FROM PUBLIC;*
GRANT EXECUTE ON FUNCTION foo() TO ief;
The reverse engineered SQL looks like this
-- Function: foo()
-- DROP FUNCTION foo();
CREATE OR REPLACE FUNCTION foo()
RETURNS void AS
$BODY$
BEGIN
PERFORM 1;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION foo() SET search_path=public, pg_temp;
ALTER FUNCTION foo()
OWNER TO postgres;
GRANT EXECUTE ON FUNCTION foo() TO postgres;
GRANT EXECUTE ON FUNCTION foo() TO ief;
The REVOKE statement is missing, which is a serious security hazard. A
recreated function will be open to the the public.
Regards
Erwin
From | Date | Subject | |
---|---|---|---|
Next Message | pgAdmin Trac | 2012-02-27 22:50:17 | Re: [pgAdmin III] #88: Function's access control list ambiguity |
Previous Message | Guillaume Lelarge | 2012-02-27 22:36:53 | pgAdmin website commit: Update the website's french translation |