Thread: Private or publice function
Hi, How can I create a publice or private function? Nirmalya Lahiri __________________________________ Do you Yahoo!? Yahoo! Mail - 250MB free storage. Do more. Manage less. http://info.mail.yahoo.com/mail_250
Nirmalya Lahiri wrote: > Hi, > How can I create a publice or private function? I'm not sure the concept makes sense in PostgreSQL. Can you explain what you're trying to achieve? -- Richard Huxton Archonet Ltd
Nirmalya Lahiri wrote: > Thanks Richard, > for your reply. Now I am explaining you what I want. > Let I have 4 > functions......function_1(),function_2(),function_3(),function_4(). [snip] > Now after creating these functions, I can easily call every function > from psql prompt. But I want to encapsulate function_2() and > function_3() withen function_1(), so that no one can call > function_2() and function_3() from psql prompt and from function_4(), > only from withen the function_1() I can call function_2() and > function_3(). > > Is it possible in PostgreSQL? Not elegantly - plpgsql only has PostgreSQL's naming and permissions structure to use. Perhaps the simplest is to create a schema "private" only accessible to user "U1". Place function2(),function(3) in schema "private". Place function1() in the public schema, making sure you create it as user "U1" and mark it "SECURITY DEFINER". So - everyone can execute function1(), but only user U1 can run function2()/3(). Since function1() runs as user U1 everything should just work. -- Richard Huxton Archonet Ltd
Thanks Richard, for your reply. Now I am explaining you what I want. Let I have 4 functions......function_1(),function_2(),function_3(),function_4(). create or replace function function_1() returns numeric as ' begin create or replace function function_2() returns numeric as '' begin -------- -------- end; '' language plpgsql ----------- ----------- ----------- create or replace function function_3() returns numeric as '' begin --------- --------- end; '' language plpgsql end ; ' language plpgsql create or replace function function_4() returns numeric as ' begin ----------- ----------- end; ' language plpgsql Now after creating these functions, I can easily call every function from psql prompt. But I want to encapsulate function_2() and function_3() withen function_1(), so that no one can call function_2() and function_3() from psql prompt and from function_4(), only from withen the function_1() I can call function_2() and function_3(). Is it possible in PostgreSQL? Nirmalya Lahiri --- Richard Huxton <dev@archonet.com> wrote: > Nirmalya Lahiri wrote: > > Hi, > > How can I create a publice or private function? > > I'm not sure the concept makes sense in PostgreSQL. Can you explain > what > you're trying to achieve? > > -- > Richard Huxton > Archonet Ltd > > ---------------------------(end of > broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister > command > (send "unregister YourEmailAddressHere" to > majordomo@postgresql.org) > __________________________________ Do you Yahoo!? Read only the mail you want - Yahoo! Mail SpamGuard. http://promotions.yahoo.com/new_mail
On Tue, Jan 11, 2005 at 18:36:17 +0000, Richard Huxton <dev@archonet.com> wrote: > Nirmalya Lahiri wrote: > >Thanks Richard, > > for your reply. Now I am explaining you what I want. > >Let I have 4 > >functions......function_1(),function_2(),function_3(),function_4(). > [snip] > > Now after creating these functions, I can easily call every function > >from psql prompt. But I want to encapsulate function_2() and > >function_3() withen function_1(), so that no one can call > >function_2() and function_3() from psql prompt and from function_4(), > >only from withen the function_1() I can call function_2() and > >function_3(). > > > > Is it possible in PostgreSQL? > > Not elegantly - plpgsql only has PostgreSQL's naming and permissions > structure to use. Perhaps the simplest is to create a schema "private" > only accessible to user "U1". Place function2(),function(3) in schema > "private". > Place function1() in the public schema, making sure you create it as > user "U1" and mark it "SECURITY DEFINER". > > So - everyone can execute function1(), but only user U1 can run > function2()/3(). Since function1() runs as user U1 everything should > just work. This is the right idea, but you can do it more elegantly using the EXECUTE privilege for functions. Postgres grants EXECUTE access to PUBLIC by default for functions, as they are usually meant to be public. But you can revoke that access and use the scheme above without needing to futz with schemas.