Re: [pgAdmin III] #88: Function's access control list ambiguity

#88: Function's access control list ambiguity
------------------------------+---------------------------------------------
  Reporter:  Dmitry Samokhin  |       Owner:  dpage   
      Type:  bug              |      Status:  reopened
  Priority:  minor            |   Milestone:  1.10.1  
 Component:  pgadmin          |     Version:  1.14    
Resolution:                   |    Keywords:  function
  Platform:  all              |  
------------------------------+---------------------------------------------
Changes (by brsa):

 * cc: brsa@… (added)
  * status:  closed => reopened
  * version:  1.10 => 1.14
  * resolution:  fixed =>


Comment:

 Turns out to be a problem we had before. Reopening this ticket.

 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 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.

--
Ticket URL: <http://code.pgadmin.org/trac/ticket/88#comment:2>
pgAdmin III <http://code.pgadmin.org/trac/>
pgAdmin III