Thread: Function script generator lacks revoke from public for ACL {postgres=X/postgres} -functions
Function script generator lacks revoke from public for ACL {postgres=X/postgres} -functions
From
"Knut P. Lehre"
Date:
<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>
Re: Function script generator lacks revoke from public for ACL {postgres=X/postgres} -functions
From
Guillaume Lelarge
Date:
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
Re: Function script generator lacks revoke from public for ACL {postgres=X/postgres} -functions
From
"Knut P. Lehre"
Date:
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
Re: Function script generator lacks revoke from public for ACL {postgres=X/postgres} -functions
From
Guillaume Lelarge
Date:
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