Postgres Pro
Downloads
Home   >   mailing lists

Thread: question about stored procedure / function

question about stored procedure / function

From
"Alain Roger"
Date:
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 ?

--
Alain
------------------------------------
Windows XP SP2
PostgreSQL 8.1.4
Apache 2.0.58
PHP 5

Re: question about stored procedure / function

From
"Anton Melser"
Date:
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

Re: question about stored procedure / function

From
Bill Moran
Date:
"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