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

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