Thread: Creating functions and triggers

Creating functions and triggers

From
"Fontenot, Paul"
Date:
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.


Re: Creating functions and triggers

From
Richard Huxton
Date:
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


Re: Creating functions and triggers

From
Dennis Gearon
Date:
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


Re: Creating functions and triggers

From
"scott.marlowe"
Date:
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)
>


Re: Creating functions and triggers

From
Dennis Gearon
Date:
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)
>>
>
>
>


Re: Creating functions and triggers

From
Dennis Gearon
Date:
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)
>>
>
>
>


Re: Creating functions and triggers

From
"scott.marlowe"
Date:
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
>
>


Re: Creating functions and triggers

From
Network Administrator
Date:
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


Re: Creating functions and triggers

From
Bruno Wolff III
Date:
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.


Re: Creating functions and triggers

From
nolan@celery.tssi.com
Date:
> 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


Re: Creating functions and triggers

From
Bruno Wolff III
Date:
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.


Re: Creating functions and triggers

From
Network Administrator
Date:
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


Re: Creating functions and triggers

From
Bruno Wolff III
Date:
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.


Re: Creating functions and triggers

From
nolan@celery.tssi.com
Date:
> > 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


Re: Creating functions and triggers

From
Jan Wieck
Date:
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 #