question about stored procedure / function

On 11/03/07, Alain Roger <raf.news@gmail.com> wrote:

Hi,

i created the following function :
-- Function: immense.sp_a_001(username "varchar", pwd "varchar")
-- DROP FUNCTION immense.sp_a_001(username "varchar", pwd "varchar");

CREATE OR REPLACE FUNCTION immense.sp_a_001(username "varchar", pwd
"varchar")
RETURNS int4 AS
$BODY$

DECLARE
myrec immense.accounts%ROWTYPE;
count INTEGER := 0;
/**************************************/

BEGIN

FOR myrec IN
SELECT * FROM immense.accounts WHERE account_login=$1 and account_pwd=$2
LOOP
count := count + 1;
END LOOP;
RETURN count;

END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION immense.sp_a_001(username "varchar", pwd "varchar") OWNER TO
immensesk;
GRANT EXECUTE ON FUNCTION immense.sp_a_001(username "varchar", pwd
"varchar") TO immensesk;

However, postgreSQL add automatically the following line to each procedure
and i do not know why ?
GRANT EXECUTE ON FUNCTION immense.sp_a_001(username "varchar", pwd
"varchar") TO public;

normally, in such case (i mean without granted execution right to public on
this procedure), only immensesk user should be able to run it... so why such
thing ?
it is not secured...

or is there something i missed ?

Where exactly does postgresql add this line? In pgadmin? Well then
it's not postgres, but pgadmin. If you tell postgres that the execute
rights go to X, then it is X that has those rights...
Cheers
Anton

"Alain Roger" <raf.news@gmail.com> wrote:

Hi,

i created the following function :
-- Function: immense.sp_a_001(username "varchar", pwd "varchar")
-- DROP FUNCTION immense.sp_a_001(username "varchar", pwd "varchar");

CREATE OR REPLACE FUNCTION immense.sp_a_001(username "varchar", pwd
"varchar")
RETURNS int4 AS
$BODY$

DECLARE
myrec immense.accounts%ROWTYPE;
count INTEGER := 0;
/**************************************/

BEGIN

FOR myrec IN
SELECT * FROM immense.accounts WHERE account_login=$1 and account_pwd=$2
LOOP
count := count + 1;
END LOOP;
RETURN count;

END;

$BODY$
LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION immense.sp_a_001(username "varchar", pwd "varchar") OWNER TO
immensesk;
GRANT EXECUTE ON FUNCTION immense.sp_a_001(username "varchar", pwd
"varchar") TO immensesk;

However, postgreSQL add automatically the following line to each procedure
and i do not know why ?
GRANT EXECUTE ON FUNCTION immense.sp_a_001(username "varchar", pwd
"varchar") TO public;

normally, in such case (i mean without granted execution right to public on
this procedure), only immensesk user should be able to run it... so why such
thing ?
it is not secured...

Default rights for newly created functions allow execution by public.

To remove this, use REVOKE.

--
Bill Moran
http://www.potentialtech.com