Обсуждение: [pgAdmin III] #88: Function's access control list ambiguity
#88: Function's access control list ambiguity -----------------------------+---------------------------------------------- Reporter: Dmitry Samokhin | Owner: dpage Type: bug | Status: new Priority: minor | Milestone: Component: pgadmin | Version: 1.10 Keywords: function | Platform: -----------------------------+---------------------------------------------- When a new function is created, the initial default EXECUTE privilege is granted to PUBLIC (this is described is the documentation), and pgAdmin generates the followng script: CREATE OR REPLACE FUNCTION ... ; ALTER FUNCTION ... OWNER TO ...; The function's ACL is dispayed in the 'ACL' field in the object properties upper-right pane and in this case it is null. (As far as I can guess the contents are just read from the 'proacl' field of the 'pg_proc' system catalog table.) If then to revoke all privileges both from PUBLIC and the owner, this function would be accessible only to a superuser, and its ACL property is '{}' (empty). Empty and null values are different, and this behavior is by design. But the script generated in the SQL pane remains the same. In any case there should be no problem for a pgAdmin user to determine the effective ACL of a function by examining its ACL in the property list. But let's observe the problem from the top point of view. Deciding to take advantage of using a DDL script carefully prepared by pgAdmin, one might expect that creating an object in another schema/database from the scratch executing this script he gets the exact copy of the object including the correct ACL. To implement this in case of empty, but not null, ACL of functions (and other database objects granting default access to PUBLIC), the reverse-engineering procedure of pgAdmin might be modified for the script to look like: CREATE OR REPLACE FUNCTION ... ; ALTER FUNCTION ... OWNER TO <owner>; REVOKE ALL ON FUNCTION ... FROM public; REVOKE ALL ON FUNCTION ... FROM <owner>; -- Ticket URL: <http://code.pgadmin.org/trac/ticket/88> pgAdmin III <http://code.pgadmin.org/trac/> pgAdmin III
#88: Function's access control list ambiguity ------------------------------+--------------------------------------------- Reporter: Dmitry Samokhin | Owner: dpage Type: bug | Status: closed Priority: minor | Milestone: 1.10.1 Component: pgadmin | Version: 1.10 Resolution: fixed | Keywords: function Platform: all | ------------------------------+--------------------------------------------- Changes (by gleu): * status: new => closed * platform: => all * resolution: => fixed * milestone: => 1.10.1 Comment: Fixed in r8069 and r8070. -- Ticket URL: <http://code.pgadmin.org/trac/ticket/88#comment:1> pgAdmin III <http://code.pgadmin.org/trac/> pgAdmin III
#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
#88: Function's access control list ambiguity ------------------------------+--------------------------------------------- Reporter: Dmitry Samokhin | Owner: dpage Type: bug | Status: reopened Priority: major | Milestone: Component: pgadmin | Version: 1.14 Resolution: | Keywords: function Platform: all | ------------------------------+--------------------------------------------- Changes (by brsa): * priority: minor => major * milestone: 1.10.1 => Comment: Raised priority because of security implications. -- Ticket URL: <http://code.pgadmin.org/trac/ticket/88#comment:3> pgAdmin III <http://code.pgadmin.org/trac/> pgAdmin III
#88: Function's access control list ambiguity ------------------------------+--------------------------------------------- Reporter: Dmitry Samokhin | Owner: dpage Type: bug | Status: closed Priority: major | Milestone: Component: pgadmin | Version: 1.14 Resolution: fixed | Keywords: function Platform: all | ------------------------------+--------------------------------------------- Changes (by dpage): * status: reopened => closed * resolution: => fixed Comment: commit abc44d28db6da2a335760cb5451fefc0dd035f5b Author: Dhiraj Chawla <dhiraj.chawla@enterprisedb.com> Date: Mon Apr 22 11:28:16 2013 +0100 Handle default privileges to PUBLIC correctly. Teach pgObject::GetGrant() that databases, functions and languages have implicit privileges granted to PUBLIC, and that REVOKE statements may need to be generated for them. Ensure that affected object properties dialogues display the default privileges so the user can revoke them if desired. -- Ticket URL: <http://code.pgadmin.org/trac/ticket/88#comment:4> pgAdmin III <http://code.pgadmin.org/trac/> pgAdmin III