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

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