pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedExecute permissions for stored functions
2+ messages / 2 participants
[nested] [flat]
* Execute permissions for stored functions
@ 2004-01-19 02:58 Paul Hart <paulhart@redchocolate.ca>
2004-01-19 05:40 ` Re: Execute permissions for stored functions Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: Paul Hart @ 2004-01-19 02:58 UTC (permalink / raw)
To: pgsql-sql; +Cc: Paul Hart <paulhart@redchocolate.ca>
Hi all,
From what I've seen in the archives, questions like this have kind of
been answered in the past, but I was wondering if there have been any
changes in this area, or if anyone has good ideas on how to do what I'm
about to ask :)
In RDBMSs such as Oracle, stored PL/SQL functions run with the
permissions of the user that creates the function. Users who are given
EXECUTE privileges then call the function with the permissions of the
creator of the function.
Is this how things work with PL/pgSQL in PostgreSQL? From my
understanding, the answer is 'no.' If the answer really is 'no,' then
how do I achieve the same thing?
The main benefit for this is in security - I have a dynamic web
application that requires (a lot of) access to a PostgreSQL database. I
want to make sure that the user doesn't have direct access to change
the content of tables, but rather to alter their contents, in
predetermined ways, through a set of functions. It's another layer that
protects against hacking, and because my project involves a lot of
monetary transactions (and database transactions), I want to reduce my
potential for malicious abuse.
Many thanks in advance for you help,
Paul
Attachments:
smime.p7s (application/pkcs7-signature, 2.3K) download
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Execute permissions for stored functions
2004-01-19 02:58 Execute permissions for stored functions Paul Hart <paulhart@redchocolate.ca>
@ 2004-01-19 05:40 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2004-01-19 05:40 UTC (permalink / raw)
To: Paul Hart <paulhart@redchocolate.ca>; +Cc: pgsql-sql
Paul Hart <paulhart@redchocolate.ca> writes:
> In RDBMSs such as Oracle, stored PL/SQL functions run with the
> permissions of the user that creates the function. Users who are given
> EXECUTE privileges then call the function with the permissions of the
> creator of the function.
Use "SECURITY DEFINER" to get this behavior in Postgres.
The SQL99 spec punts as to whether SECURITY DEFINER should be the
default or not, so unfortunately neither we nor Oracle can be said
to be wrong on this point...
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2004-01-19 05:40 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-01-19 02:58 Execute permissions for stored functions Paul Hart <paulhart@redchocolate.ca>
2004-01-19 05:40 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox