Обсуждение: Question about stored procedures
Hi everyone,
I just want to know if there is an equivalent method in PostgreSQL that acts like SQL Server's extended stored procedure. I want to run a stored procedure that can update a file in the file system.. I dont know whether I can do this using PL/pgSQL or do i need to use another method.
Someone please enlighten me.
Best Regards,
Jo
Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
am 13.10.2005, um 3:36:19 -0700 mailte Josephine E. de Castro folgendes: > Hi everyone, > > I just want to know if there is an equivalent method in PostgreSQL > that acts like SQL Server's extended stored procedure. I want to run a > stored procedure that can update a file in the file system.. I dont > know whether I can do this using PL/pgSQL or do i need to use another > method. PL/pgsql i a trusted language with no access to the filesystem. But, we have other languages like plperlu or plsh. This languages can access local files. http://www.postgresql.org/docs/8.0/interactive/plperl.html Regards, Andreas -- Andreas Kretschmer (Kontakt: siehe Header) Heynitz: 035242/47212, D1: 0160/7141639 GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net === Schollglas Unternehmensgruppe ===
On 10/13/05 6:36 AM, "Josephine E. de Castro" <jedecastro23@yahoo.com> wrote: > Hi everyone, > > I just want to know if there is an equivalent method in PostgreSQL that acts > like SQL Server's extended stored procedure. I want to run a stored procedure > that can update a file in the file system.. I dont know whether I can do this > using PL/pgSQL or do i need to use another method. > > Someone please enlighten me. Look at pl/perlu or any of the other "untrusted" flavors of procedure language. Probably all can do what you like as far as file manipulation. http://www.postgresql.org/docs/8.0/static/server-programming.html Sean
Hi Sean! Thanks for your reply. :)
My knowledge of PL/Perl is limited and confined to knowing that such language exist.
Is there no 'trusted' way of doing this? How about creating a trigger using C? Or should i stick with something like PL/pgSQL and look for its 'untrusted' flavors?
Anyway, my questions sound so elementary.. really sorry for that..
Thanks again!
Sean Davis <sdavis2@mail.nih.gov> wrote:
Look at pl/perlu or any of the other "untrusted" flavors of procedure
language. Probably all can do what you like as far as file manipulation.
http://www.postgresql.org/docs/8.0/static/server-programming.html
Sean
Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
Thanks Andreas!
I will try yours and Sean's suggestions and see where it will take me! :)
I've got some learning to do and I'm really grateful for the replies that I got from this group!
Again, my utmost gratitude!
:)
"A. Kretschmer" <andreas.kretschmer@schollglas.com> wrote:
PL/pgsql i a trusted language with no access to the filesystem. But, we
have other languages like plperlu or plsh. This languages can access
local files.
http://www.postgresql.org/docs/8.0/interactive/plperl.html
Regards, Andreas
--
Andreas Kretschmer (Kontakt: siehe Header)
Heynitz: 035242/47212, D1: 0160/7141639
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
=== Schollglas Unternehmensgruppe ===
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
Yahoo! Music Unlimited - Access over 1 million songs. Try it free.
On Thu, Oct 13, 2005 at 04:20:39AM -0700, Josephine E. de Castro <jedecastro23@yahoo.com> wrote a message of 49 lines which said: > Is there no 'trusted' way of doing this? By definition, certainly not. A "trusted" procedure can be installed by an ordinary user so it MUST NOT play outside of the sandbox. > How about creating a trigger using C? Yes, C extensions can do anything. > Or should i stick with something like PL/pgSQL and look for its > 'untrusted' flavors? I am afraid there is no "untrusted" PL/pgSQL. (And no file manipulation primitives in PL/pgSQL.) Note that a common trick, when you want to do X and you cannot do it directly from PostgreSQL (or are unwilling to force the sysadmin to install stuff like plWhatever - for instance, I was never able to make plPython run on my NetBSD machines), is to put data in a table and to have an auxiliary daemon which connects to the database and read in the table what it must do. For a more complete example, let's assume that your application must delete files. You create a table Files_to_delete (name TEXT, deleted BOOLEAN default False), your trigger writes the file names, and you write a program (running with any UID you choose) which SELECT name FROM WHERE deleted = False and then performs the rm (and UPDATE SET deleted = True).
On Thu, Oct 13, 2005 at 03:00:32PM +0200, Stephane Bortzmeyer wrote:
> Note that a common trick, when you want to do X and you cannot do it
> directly from PostgreSQL (or are unwilling to force the sysadmin to
> install stuff like plWhatever - for instance, I was never able to make
> plPython run on my NetBSD machines), is to put data in a table and to
> have an auxiliary daemon which connects to the database and read in
> the table what it must do.
From the point of view of security, that's also a good idea. If you
have a bug in your trusted function, and it can write on the
filesystem, and somebody can manage to get their string to be passed
into your function, then they maybe can inject things like "rm -r
$PGDATA". Which would hurt.
That isn't to say, "Never do this." It's just to say that you have a
real dangerous tool there in your hand, so don't be waving it about
carelessly.
A
--
Andrew Sullivan | ajs@crankycanuck.ca
It is above all style through which power defers to reason.
--J. Robert Oppenheimer