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

Re: Missing REVOKE in SQL for functions with SECURITY DEFINER (or any function, really)

From: Erwin Brandstetter <brsaweda(at)gmail(dot)com>
To: "pgadmin-hackers(at)postgresql(dot)org" <pgadmin-hackers(at)postgresql(dot)org>
Subject: Re: Missing REVOKE in SQL for functions with SECURITY DEFINER (or any function, really)
Date: 2012-02-27 22:53:11
Message-ID: 4F4C0957.5040208@gmail.com (view raw or flat)
Thread:
Lists: pgadmin-hackers
On 27.02.2012 23:38, Erwin Brandstetter wrote:
> 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

I reopened ticket #88 for that
     http://code.pgadmin.org/trac/ticket/88#comment:2
because it seemed closely related.

Regards
Erwin

In response to

Responses

pgadmin-hackers by date

Next:From: Guillaume LelargeDate: 2012-02-27 22:58:39
Subject: pgAdmin website commit: Add russian translation for the website
Previous:From: pgAdmin TracDate: 2012-02-27 22:50:17
Subject: Re: [pgAdmin III] #88: Function's access control list ambiguity

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