Обсуждение: Function script generator lacks revoke from public for ACL {postgres=X/postgres} -functions

<div style="font-family: 'Times New Roman'; font-size: 16px;"><span  _moz_dirty="">It is dangerous when working with
securitydefiner 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
youwill get public execute granted to that function.<br  _moz_dirty="" />pg_dump adds a revoke from public in this
case.Is this missing revoke in pgAdmin3 intentional or was it forgotten?<br  _moz_dirty="" /><br  _moz_dirty="" />KP
Lehre<br _moz_dirty="" /><br  _moz_dirty="" /></span></div> 

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.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com

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

Le 08/04/2011 23:09, Knut P. Lehre a écrit :
> 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.

When you explicitly revoke it first, yes, y'oure right. This wasn't
obvious in your previous mail.

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

This is a bug in pgAdmin. We'll have to fix it.


--
Guillaume
 http://www.postgresql.fr
 http://dalibo.com