Thread: Creating functions and triggers
Is there a good, hold your hand type of tutorial or howto on creating functions and triggers and using them together? I'm learning PostgreSQL after spending years with MySQL and the information at techdocs.postgresql.org - while good, is a little deep for me right now. Specificly I would like to be able to read something that will tell me how to create a function that will can run a shell script when certain words are entered into a record. Thanks for your time and guidance. ***PRIVILEGED & CONFIDENTIAL*** Unless expressly stated otherwise, this message (and any attachment(s) thereto) is confidential and may be privileged. It is intended for the addressee(s) only. If you are not an addressee, any disclosure or copying of the contents of this e-mail or any action taken (or not taken) in reliance on it is strictly prohibited. If you are not an addressee, please inform sender immediately and delete this message from your system.
On Wednesday 07 May 2003 5:32 pm, Fontenot, Paul wrote: > Is there a good, hold your hand type of tutorial or howto on creating > functions and triggers and using them together? I'm learning PostgreSQL > after spending years with MySQL and the information at > techdocs.postgresql.org - while good, is a little deep for me right now. > Specificly I would like to be able to read something that will tell me > how to create a function that will can run a shell script when certain > words are entered into a record. Thanks for your time and guidance. [blowing own trumpet] Try the "Postgresql Notes" (Automating Processes section) http://techdocs.postgresql.org/ (Quick Reference Material) or "A Brief Real-World Trigger Example" http://techdocs.postgresql.org/guides/ Both the above cover creating a plpgsql function and a trigger to call it. The first is older, but simpler. If you really want to call a shell script, check the mailing list archives and search for "shell" or "bash": http://archives.postgresql.org/ You'll also want to read up on pl/sh (or whatever it's called) - procedural shell stuff. Also perhaps look into pl/perl. I'd probably recommend having a long-lasting process LISTEN for NOTIFY messages rather than directly running shell scripts. -- Richard Huxton
HOLY S**T! <rant> You are basically setting yourself up for a MICROSOFT sized security hole. Can you say, "Seeqwell Server?" You'd be MUCH better off using a PHP, PERL, ASP, JAVA, COLD FUSION, etc. script for doing that. THOSE places are the focus for much work in preventing the misuse of system resources from an end user perspective. DATABASES are for holding data, and their relationships. </rant> "Fontenot, Paul" wrote: > > Is there a good, hold your hand type of tutorial or howto on creating > functions and triggers and using them together? I'm learning PostgreSQL > after spending years with MySQL and the information at > techdocs.postgresql.org - while good, is a little deep for me right now. > Specificly I would like to be able to read something that will tell me > how to create a function that will can run a shell script when certain > words are entered into a record. Thanks for your time and guidance. > > ***PRIVILEGED & CONFIDENTIAL*** > Unless expressly stated otherwise, this message (and any attachment(s) > thereto) is confidential and may be privileged. It is intended for the > addressee(s) only. If you are not an addressee, any disclosure or > copying of the contents of this e-mail or any action taken (or not > taken) in reliance on it is strictly prohibited. If you are not an > addressee, please inform sender immediately and delete this message from > your system. > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html
Bzzzzzzzzt. WRONG. But thanks for playing. Generally speaking, createing triggers and functions to go with them is a safer way of setting up access to your data than allowing Joe Q Programmer full update/insert/delete access. Paul, Bruce Momjian's postgresql book has a nice little section on writing triggers / functions in plpgsql and a few other languages, and there are some examples throughout the docs that show you how to, although they aren't all collected in one place (one example might be in the trigger section, the next in the plpgsql section.) So, Dennis, how do I write a PHP script that does the equivalent of firing an after trigger? On Wed, 7 May 2003, Dennis Gearon wrote: > HOLY S**T! > > <rant> > You are basically setting yourself up for a MICROSOFT sized security > hole. Can you say, "Seeqwell Server?" > > You'd be MUCH better off using a PHP, PERL, ASP, JAVA, COLD FUSION, etc. > script for doing that. THOSE places are the focus for much work in > preventing the misuse of system resources from an end user perspective. > > DATABASES are for holding data, and their relationships. > > </rant> > > "Fontenot, Paul" wrote: > > > > Is there a good, hold your hand type of tutorial or howto on creating > > functions and triggers and using them together? I'm learning PostgreSQL > > after spending years with MySQL and the information at > > techdocs.postgresql.org - while good, is a little deep for me right now. > > Specificly I would like to be able to read something that will tell me > > how to create a function that will can run a shell script when certain > > words are entered into a record. Thanks for your time and guidance. > > > > ***PRIVILEGED & CONFIDENTIAL*** > > Unless expressly stated otherwise, this message (and any attachment(s) > > thereto) is confidential and may be privileged. It is intended for the > > addressee(s) only. If you are not an addressee, any disclosure or > > copying of the contents of this e-mail or any action taken (or not > > taken) in reliance on it is strictly prohibited. If you are not an > > addressee, please inform sender immediately and delete this message from > > your system. > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faqs/FAQ.html > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
My sincere aplogies,
My email client must have scrolled one email before I hit reply. I was trying to answer a person who wanted a
certainword in database query to trigger a system call, executing programs external to the database. Somehow, I ended
upanswering your post instead. I'm terribly sorry.
scott.marlowe wrote:
> Bzzzzzzzzt. WRONG. But thanks for playing.
>
> Generally speaking, createing triggers and functions to go with
> them is a safer way of setting up access to your data than allowing Joe Q
> Programmer full update/insert/delete access.
>
> Paul, Bruce Momjian's postgresql book has a nice little section on writing
> triggers / functions in plpgsql and a few other languages, and there are
> some examples throughout the docs that show you how to, although they
> aren't all collected in one place (one example might be in the trigger
> section, the next in the plpgsql section.)
>
> So, Dennis, how do I write a PHP script that does the equivalent of firing
> an after trigger?
>
> On Wed, 7 May 2003, Dennis Gearon wrote:
>
>
>>HOLY S**T!
>>
>><rant>
>>You are basically setting yourself up for a MICROSOFT sized security
>>hole. Can you say, "Seeqwell Server?"
>>
>>You'd be MUCH better off using a PHP, PERL, ASP, JAVA, COLD FUSION, etc.
>>script for doing that. THOSE places are the focus for much work in
>>preventing the misuse of system resources from an end user perspective.
>>
>>DATABASES are for holding data, and their relationships.
>>
>></rant>
>>
>>"Fontenot, Paul" wrote:
>>
>>>Is there a good, hold your hand type of tutorial or howto on creating
>>>functions and triggers and using them together? I'm learning PostgreSQL
>>>after spending years with MySQL and the information at
>>>techdocs.postgresql.org - while good, is a little deep for me right now.
>>>Specificly I would like to be able to read something that will tell me
>>>how to create a function that will can run a shell script when certain
>>>words are entered into a record. Thanks for your time and guidance.
>>>
>>> ***PRIVILEGED & CONFIDENTIAL***
>>>Unless expressly stated otherwise, this message (and any attachment(s)
>>>thereto) is confidential and may be privileged. It is intended for the
>>>addressee(s) only. If you are not an addressee, any disclosure or
>>>copying of the contents of this e-mail or any action taken (or not
>>>taken) in reliance on it is strictly prohibited. If you are not an
>>>addressee, please inform sender immediately and delete this message from
>>>your system.
>>>
>>>---------------------------(end of broadcast)---------------------------
>>>TIP 5: Have you checked our extensive FAQ?
>>>
>>>http://www.postgresql.org/docs/faqs/FAQ.html
>>
>>
>>---------------------------(end of broadcast)---------------------------
>>TIP 2: you can get off all lists at once with the unregister command
>> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>>
>
>
>
Whatever program or client which is supplying query could just as easily run shell scripts. And for people who follow behindyou in this design, it will be much less confusing and a more common side effect of the script language to executea shell script, than for a database to execute a shell script. What does an AFTER trigger have to do with a shell script anyway? There's a software term called 'localization', unrelated to character sets, which means that code running in one place ofa program or a suite of program in an application, should only have 'local effect'. Any other changes to take place becauseof one action in one part of a program, should be passed to the code nearest the target of changes. It's like talking to the payroll clerk about the lousy accounting practices by the accounting dept. You don't expect thepayroll clerk to be married or sleeping with the accountant dept head and your comments to immediately have effects inthe accounting dept, (gossip notwithstanding). scott.marlowe wrote: > Bzzzzzzzzt. WRONG. But thanks for playing. > > Generally speaking, createing triggers and functions to go with > them is a safer way of setting up access to your data than allowing Joe Q > Programmer full update/insert/delete access. > > Paul, Bruce Momjian's postgresql book has a nice little section on writing > triggers / functions in plpgsql and a few other languages, and there are > some examples throughout the docs that show you how to, although they > aren't all collected in one place (one example might be in the trigger > section, the next in the plpgsql section.) > > So, Dennis, how do I write a PHP script that does the equivalent of firing > an after trigger? > > On Wed, 7 May 2003, Dennis Gearon wrote: > > >>HOLY S**T! >> >><rant> >>You are basically setting yourself up for a MICROSOFT sized security >>hole. Can you say, "Seeqwell Server?" >> >>You'd be MUCH better off using a PHP, PERL, ASP, JAVA, COLD FUSION, etc. >>script for doing that. THOSE places are the focus for much work in >>preventing the misuse of system resources from an end user perspective. >> >>DATABASES are for holding data, and their relationships. >> >></rant> >> >>"Fontenot, Paul" wrote: >> >>>Is there a good, hold your hand type of tutorial or howto on creating >>>functions and triggers and using them together? I'm learning PostgreSQL >>>after spending years with MySQL and the information at >>>techdocs.postgresql.org - while good, is a little deep for me right now. >>>Specificly I would like to be able to read something that will tell me >>>how to create a function that will can run a shell script when certain >>>words are entered into a record. Thanks for your time and guidance. >>> >>> ***PRIVILEGED & CONFIDENTIAL*** >>>Unless expressly stated otherwise, this message (and any attachment(s) >>>thereto) is confidential and may be privileged. It is intended for the >>>addressee(s) only. If you are not an addressee, any disclosure or >>>copying of the contents of this e-mail or any action taken (or not >>>taken) in reliance on it is strictly prohibited. If you are not an >>>addressee, please inform sender immediately and delete this message from >>>your system. >>> >>>---------------------------(end of broadcast)--------------------------- >>>TIP 5: Have you checked our extensive FAQ? >>> >>>http://www.postgresql.org/docs/faqs/FAQ.html >> >> >>---------------------------(end of broadcast)--------------------------- >>TIP 2: you can get off all lists at once with the unregister command >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >> > > >
Whew! OK, I was wondering why you were dogging on user defined functions, then answering my argument with what appeared to be a random google search :-) On Thu, 8 May 2003, Dennis Gearon wrote: > My sincere aplogies, > My email client must have scrolled one email before I hit reply. I was trying to answer a person who wanted a certainword in database query to trigger a system call, executing programs external to the database. Somehow, I ended upanswering your post instead. I'm terribly sorry. > > scott.marlowe wrote: > > Bzzzzzzzzt. WRONG. But thanks for playing. > > > > Generally speaking, createing triggers and functions to go with > > them is a safer way of setting up access to your data than allowing Joe Q > > Programmer full update/insert/delete access. > > > > Paul, Bruce Momjian's postgresql book has a nice little section on writing > > triggers / functions in plpgsql and a few other languages, and there are > > some examples throughout the docs that show you how to, although they > > aren't all collected in one place (one example might be in the trigger > > section, the next in the plpgsql section.) > > > > So, Dennis, how do I write a PHP script that does the equivalent of firing > > an after trigger? > > > > On Wed, 7 May 2003, Dennis Gearon wrote: > > > > > >>HOLY S**T! > >> > >><rant> > >>You are basically setting yourself up for a MICROSOFT sized security > >>hole. Can you say, "Seeqwell Server?" > >> > >>You'd be MUCH better off using a PHP, PERL, ASP, JAVA, COLD FUSION, etc. > >>script for doing that. THOSE places are the focus for much work in > >>preventing the misuse of system resources from an end user perspective. > >> > >>DATABASES are for holding data, and their relationships. > >> > >></rant> > >> > >>"Fontenot, Paul" wrote: > >> > >>>Is there a good, hold your hand type of tutorial or howto on creating > >>>functions and triggers and using them together? I'm learning PostgreSQL > >>>after spending years with MySQL and the information at > >>>techdocs.postgresql.org - while good, is a little deep for me right now. > >>>Specificly I would like to be able to read something that will tell me > >>>how to create a function that will can run a shell script when certain > >>>words are entered into a record. Thanks for your time and guidance. > >>> > >>> ***PRIVILEGED & CONFIDENTIAL*** > >>>Unless expressly stated otherwise, this message (and any attachment(s) > >>>thereto) is confidential and may be privileged. It is intended for the > >>>addressee(s) only. If you are not an addressee, any disclosure or > >>>copying of the contents of this e-mail or any action taken (or not > >>>taken) in reliance on it is strictly prohibited. If you are not an > >>>addressee, please inform sender immediately and delete this message from > >>>your system. > >>> > >>>---------------------------(end of broadcast)--------------------------- > >>>TIP 5: Have you checked our extensive FAQ? > >>> > >>>http://www.postgresql.org/docs/faqs/FAQ.html > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 2: you can get off all lists at once with the unregister command > >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >> > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
I had a thought/question 'bout this since I was reading some stuff on triggers- especially PL/Perl (sec. 21.4 in the 7.3 Programmer Docs). Isn't the simple answer to this based on the fact that a PL installed as "trusted" will not allow you to execute things that violate localization? Furthermore, if a language is installed as "untrusted", doesn't it prevent non-admin users from using it? Or is this only for PL/Perl? -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com Quoting Dennis Gearon <gearond@cvc.net>: > Whatever program or client which is supplying query could just as easily run > shell scripts. And for people who follow behind you in this design, it will > be much less confusing and a more common side effect of the script language > to execute a shell script, than for a database to execute a shell script. > > What does an AFTER trigger have to do with a shell script anyway? > > There's a software term called 'localization', unrelated to character sets, > which means that code running in one place of a program or a suite of program > in an application, should only have 'local effect'. Any other changes to take > place because of one action in one part of a program, should be passed to the > code nearest the target of changes. > > It's like talking to the payroll clerk about the lousy accounting practices > by the accounting dept. You don't expect the payroll clerk to be married or > sleeping with the accountant dept head and your comments to immediately have > effects in the accounting dept, (gossip notwithstanding). > > scott.marlowe wrote: > > Bzzzzzzzzt. WRONG. But thanks for playing. > > > > Generally speaking, createing triggers and functions to go with > > them is a safer way of setting up access to your data than allowing Joe Q > > Programmer full update/insert/delete access. > > > > Paul, Bruce Momjian's postgresql book has a nice little section on writing > > > triggers / functions in plpgsql and a few other languages, and there are > > some examples throughout the docs that show you how to, although they > > aren't all collected in one place (one example might be in the trigger > > section, the next in the plpgsql section.) > > > > So, Dennis, how do I write a PHP script that does the equivalent of firing > > > an after trigger? > > > > On Wed, 7 May 2003, Dennis Gearon wrote: > > > > > >>HOLY S**T! > >> > >><rant> > >>You are basically setting yourself up for a MICROSOFT sized security > >>hole. Can you say, "Seeqwell Server?" > >> > >>You'd be MUCH better off using a PHP, PERL, ASP, JAVA, COLD FUSION, etc. > >>script for doing that. THOSE places are the focus for much work in > >>preventing the misuse of system resources from an end user perspective. > >> > >>DATABASES are for holding data, and their relationships. > >> > >></rant> > >> > >>"Fontenot, Paul" wrote: > >> > >>>Is there a good, hold your hand type of tutorial or howto on creating > >>>functions and triggers and using them together? I'm learning PostgreSQL > >>>after spending years with MySQL and the information at > >>>techdocs.postgresql.org - while good, is a little deep for me right now. > >>>Specificly I would like to be able to read something that will tell me > >>>how to create a function that will can run a shell script when certain > >>>words are entered into a record. Thanks for your time and guidance. > >>> > >>> ***PRIVILEGED & CONFIDENTIAL*** > >>>Unless expressly stated otherwise, this message (and any attachment(s) > >>>thereto) is confidential and may be privileged. It is intended for the > >>>addressee(s) only. If you are not an addressee, any disclosure or > >>>copying of the contents of this e-mail or any action taken (or not > >>>taken) in reliance on it is strictly prohibited. If you are not an > >>>addressee, please inform sender immediately and delete this message from > >>>your system. > >>> > >>>---------------------------(end of broadcast)--------------------------- > >>>TIP 5: Have you checked our extensive FAQ? > >>> > >>>http://www.postgresql.org/docs/faqs/FAQ.html > >> > >> > >>---------------------------(end of broadcast)--------------------------- > >>TIP 2: you can get off all lists at once with the unregister command > >> (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >> > > > > > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
On Tue, May 13, 2003 at 10:06:36 -0400, Network Administrator <netadmin@vcsn.com> wrote: > I had a thought/question 'bout this since I was reading some stuff on triggers- > especially PL/Perl (sec. 21.4 in the 7.3 Programmer Docs). Isn't the simple > answer to this based on the fact that a PL installed as "trusted" will not allow > you to execute things that violate localization? Furthermore, if a language is > installed as "untrusted", doesn't it prevent non-admin users from using it? Or > is this only for PL/Perl? Untrusted languages can only be used by superusers.
> Untrusted languages can only be used by superusers. I thought it was that functions using untrusted languages can only be DEFINED by superusers. Use of the functions, once defined, can be granted to others. -- Mike Nolan
On Tue, May 13, 2003 at 10:23:14 -0500, nolan@celery.tssi.com wrote: > > Untrusted languages can only be used by superusers. > > I thought it was that functions using untrusted languages can only be > DEFINED by superusers. Use of the functions, once defined, can be > granted to others. That is what I meant. Effectively you use a language when you define a function written in that language. Once the function is defined, then normal function security determines who can use the function.
We're saying the same thing- non-admin user (superusers) can only install untrusted languages. However, I didn't know you could grant rights to a untrusted function. That is interesting because I thought the language's trusted status was based on who owned the database. For instance, if I installed Perl as untrusted into template1 wouldn't any user database based I create for regular users (as the superuser but making them the database owner) run PL/Perl functions as trusted? The initial reason for my post is that I [thought] I saw some talk about writing files as using PL/Perl instead of PL/Sh and I thought PL/Perl did not allow regular users to write files to the file system, no? Funny how 1 question leads to another- which is cool, 'cause I like to learn some'n new everyday! Quoting Bruno Wolff III <bruno@wolff.to>: > On Tue, May 13, 2003 at 10:06:36 -0400, > Network Administrator <netadmin@vcsn.com> wrote: > > I had a thought/question 'bout this since I was reading some stuff on > triggers- > > especially PL/Perl (sec. 21.4 in the 7.3 Programmer Docs). Isn't the > simple > > answer to this based on the fact that a PL installed as "trusted" will not > allow > > you to execute things that violate localization? Furthermore, if a > language is > > installed as "untrusted", doesn't it prevent non-admin users from using it? > Or > > is this only for PL/Perl? > > Untrusted languages can only be used by superusers. > -- Keith C. Perry Director of Networks & Applications VCSN, Inc. http://vcsn.com ____________________________________ This email account is being host by: VCSN, Inc : http://vcsn.com
On Tue, May 13, 2003 at 14:16:52 -0400, Network Administrator <netadmin@vcsn.com> wrote: > We're saying the same thing- non-admin user (superusers) can only install > untrusted languages. However, I didn't know you could grant rights to a > untrusted function. That is interesting because I thought the language's > trusted status was based on who owned the database. For instance, if I installed > Perl as untrusted into template1 wouldn't any user database based I create for > regular users (as the superuser but making them the database owner) run PL/Perl > functions as trusted? The access right for languages is USAGE. I believe this is granted to public by default when a trusted language is created. For untrusted languages you can't grant usage. If you don't have usage access to a language, you can't create functions using that language. Since a normal user can't get usage access to an untrusted language a normal user can't create functions that use untrusted languages. Note that in older versions of postgres the same restriction was there, but things worked a little differently as there was no language version of the grant command. > > The initial reason for my post is that I [thought] I saw some talk about writing > files as using PL/Perl instead of PL/Sh and I thought PL/Perl did not allow > regular users to write files to the file system, no? I haven't played with PL/Perl myself, but I do believe that there are both trusted and untrusted versions of that. Presumably the untrusted one would have full access to perl and be able to write to files.
> > The initial reason for my post is that I [thought] I saw some talk about writing > > files as using PL/Perl instead of PL/Sh and I thought PL/Perl did not allow > > regular users to write files to the file system, no? > > I haven't played with PL/Perl myself, but I do believe that there are > both trusted and untrusted versions of that. Presumably the untrusted > one would have full access to perl and be able to write to files. I haven't done file I/O, but I have written a function in plperlu which connects to another pgsql database, so I think you can probably do just about anything you want in the untrusted version. Looking at the replication project(s) is still on my 'to do' list, but I figure if I have to I can write trigger procedures to implement master/slave databases. -- Mike Nolan
The original idea was protection. If a language offers mechanisms through which a user can gain access to things, he normally does not have access to, then it should be untrusted and require superuser privileges to define functions in that language. Those functions, once created by a superuser, can be considered trusted again depending on their functionality. Quick example: A function that writes a file onto disk. Without any checks, that function could be used to replace the pg_hba.conf file ... and go from there. Surely not trusted, no matter who created it or what language he used. If the function ensures that the file will end up in a certain path, it's okay to trust it. To write such a function requires that the language supports dealing with files on the OS level. PL/Tcl for example does not, so a user writing function in PL/Tcl cannot do it and thus, it's safe to allow Joe Haxor to write functions in it. PL/TclU allows OS level file access, and therefore a superuser better has an eye over the functions coding before it ever gets called. The only mechanism we have for that is to restrict the definition of functions in that said untrusted language to superusers. Jan Network Administrator wrote: > We're saying the same thing- non-admin user (superusers) can only install > untrusted languages. However, I didn't know you could grant rights to a > untrusted function. That is interesting because I thought the language's > trusted status was based on who owned the database. For instance, if I installed > Perl as untrusted into template1 wouldn't any user database based I create for > regular users (as the superuser but making them the database owner) run PL/Perl > functions as trusted? > > The initial reason for my post is that I [thought] I saw some talk about writing > files as using PL/Perl instead of PL/Sh and I thought PL/Perl did not allow > regular users to write files to the file system, no? > > Funny how 1 question leads to another- which is cool, 'cause I like to learn > some'n new everyday! > > > Quoting Bruno Wolff III <bruno@wolff.to>: > > >>On Tue, May 13, 2003 at 10:06:36 -0400, >> Network Administrator <netadmin@vcsn.com> wrote: >> >>>I had a thought/question 'bout this since I was reading some stuff on >> >>triggers- >> >>>especially PL/Perl (sec. 21.4 in the 7.3 Programmer Docs). Isn't the >> >>simple >> >>>answer to this based on the fact that a PL installed as "trusted" will not >> >>allow >> >>>you to execute things that violate localization? Furthermore, if a >> >>language is >> >>>installed as "untrusted", doesn't it prevent non-admin users from using it? >> >> Or >> >>>is this only for PL/Perl? >> >>Untrusted languages can only be used by superusers. >> > > > -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com #