Re: Function script generator lacks revoke from public for ACL {postgres=X/postgres} -functions

On 2011-04-08 22:18, Guillaume Lelarge wrote:
> Le 08/04/2011 20:07, Knut P. Lehre a écrit :
>> It is dangerous when working with security definer functions that the pgAdmin3
>> script creator does not include a "revoke from public" for functions with e.g.
>> ACL postgres=X/postgres (at least in version 1.10.1). If you use this script to
>> copy a function definition, then you will get public execute granted to that
>> function.
>
> Sure. That's the usual behaviour of PostgreSQL. So I don't get why
> pgAdmin should do otherwise. We can of course allow the user to
> automatically revoke public permissions on this kind of functions, if a
> user clicks a checkbox for example (just like we do to automatically add
> an index for foreign keys).
>
>> pg_dump adds a revoke from public in this case. Is this missing revoke in
>> pgAdmin3 intentional or was it forgotten?
>
> Neither intentional nor forgotten. I don't think anyone ever thought
> about it.
>
> BTW, I don't know where you saw/heard/read that pg_dump adds a revoke
> from public in this particular case, but it doesn't, AFAICT.
>
>

pg_dump does add a revoke on public.
Please try f.ex. this in pgAdmin3:

CREATE OR REPLACE FUNCTION test9(text) RETURNS text AS
$BODY$
DECLARE
BEGIN
RETURN $1;
END;
$BODY$
  LANGUAGE 'plpgsql';
ALTER FUNCTION test9(text) OWNER TO postgres;
REVOKE ALL ON FUNCTION test9(text) FROM public;

Then, in pgAdmin3, you will see that the ACL and function script are:

{postgres=X/postgres}

CREATE OR REPLACE FUNCTION test9(text)
  RETURNS text AS
$BODY$
DECLARE
BEGIN
RETURN $1;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION test9(text) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION test9(text) TO postgres;


Now, pg_dump the database, and you will see:

CREATE FUNCTION test9(text) RETURNS text
    LANGUAGE plpgsql
    AS $_$
DECLARE
BEGIN
RETURN $1;
END;
$_$;

ALTER FUNCTION public.test9(text) OWNER TO postgres;

REVOKE ALL ON FUNCTION test9(text) FROM PUBLIC;
REVOKE ALL ON FUNCTION test9(text) FROM postgres;
GRANT ALL ON FUNCTION test9(text) TO postgres;


In pgAdmin3, if you right click the function name to get a window with
the script generated by pgAdmin, uncomment the drop stmt at the top, and
run the script.
The new ACL and script look like this:

{=X/postgres,postgres=X/postgres}

CREATE OR REPLACE FUNCTION test9(text)
  RETURNS text AS
$BODY$
DECLARE
BEGIN
RETURN $1;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION test9(text) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION test9(text) TO public;
GRANT EXECUTE ON FUNCTION test9(text) TO postgres;


As you can see, the script does not regenerate the original ACL.
It adds execute to public. This is dangerous is you are copying a
security definer function!

Now, use pg_dump on the database, and you will see:

CREATE FUNCTION test9(text) RETURNS text
    LANGUAGE plpgsql
    AS $_$
DECLARE
BEGIN
RETURN $1;
END;
$_$;

ALTER FUNCTION public.test9(text) OWNER TO postgres;

REVOKE ALL ON FUNCTION test9(text) FROM PUBLIC;
REVOKE ALL ON FUNCTION test9(text) FROM postgres;
GRANT ALL ON FUNCTION test9(text) TO postgres;
GRANT ALL ON FUNCTION test9(text) TO PUBLIC;


The reason why we need the revoke from public for FUNCTIONS (and not
tables) is that in pg, by default, execute privilege is granted to
PUBLIC for newly created functions.

KP Lehre