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:
Show quoted text
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?
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:
Show quoted text
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?---------------------------(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 certain word in database query to trigger a system call, executing programs external to the database. Somehow, I ended up answering your post instead. I'm terribly sorry.
scott.marlowe wrote:
Show quoted text
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?---------------------------(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 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:
Show quoted text
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?---------------------------(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:
Show quoted text
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 certain word in database query to trigger a system call, executing programs external to the database. Somehow, I ended up answering 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?---------------------------(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?---------------------------(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 #