Thread: pl/pgsql enabled by default
Is there a good reason that pl/pgsql is not installed in databases by default? I think it should be. pl/pgsql is widely used, and having it installed by default would be one less hurdle for newbies to overcome when learning PostgreSQL. It would also make it easier to distribute applications that depend on PostgreSQL and use PL/PgSQL: rather than saying "You need PostgreSQL, and then you need to do [ createlang stuff ]", those applications can just depend on a sufficiently recent version of PostgreSQL. AFAICS, the overhead of installing it by default would not be large: just an extra row in pg_language and a few rows in pg_proc. So I can't really see a major reason *not* to do this -- am I missing one? A related issue is where the PL validator and handler functions for PL/PgSQL would be placed if it was installed by default. I think placing them in pg_catalog (rather than public, where they are currently installed by createlang) would probably be best. -Neil
> Is there a good reason that pl/pgsql is not installed in databases by > default? > > I think it should be. pl/pgsql is widely used, and having it installed > by default would be one less hurdle for newbies to overcome when > learning PostgreSQL. It would also make it easier to distribute > applications that depend on PostgreSQL and use PL/PgSQL: rather than > saying "You need PostgreSQL, and then you need to do [ createlang stuff > ]", those applications can just depend on a sufficiently recent version > of PostgreSQL. > > AFAICS, the overhead of installing it by default would not be large: > just an extra row in pg_language and a few rows in pg_proc. So I can't > really see a major reason *not* to do this -- am I missing one? Problem is people restoring dumps that have the plpgsql create language, etc. commands in them. I strongly think that pgsql should come with pl/pgsql on by default, however ;) Chris
Christopher Kings-Lynne wrote: > Problem is people restoring dumps that have the plpgsql create language, > etc. commands in them. It should be possible to ignore those commands, and possibly issue a warning. It's a bit ugly, but at least we can detect this situation pretty unambiguously. -Neil
Neil, > Is there a good reason that pl/pgsql is not installed in databases by > default? The only one I can think of is "security", which is pretty weak -- we've never had a plpgsql security issue that I know of. -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus wrote: > The only one I can think of is "security", which is pretty weak -- we've never > had a plpgsql security issue that I know of. Well, no -- for instance, http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0245 http://cve.mitre.org/cgi-bin/cvename.cgi?name=CAN-2005-0247 But I agree security is not a good argument against enabling it by default. -Neil
On Fri, May 06, 2005 at 02:59:04PM +1000, Neil Conway wrote: > Is there a good reason that pl/pgsql is not installed in databases by > default? The only reason I've seen was "if we start with including plpgsql, where do we draw the line?" Personally, I think it should be installed by default. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Fri, 6 May 2005 04:45 pm, Jim C. Nasby wrote: > On Fri, May 06, 2005 at 02:59:04PM +1000, Neil Conway wrote: > > Is there a good reason that pl/pgsql is not installed in databases by > > default? > > The only reason I've seen was "if we start with including plpgsql, where > do we draw the line?" Well, I thought and I'm sure it's been said, that plpgsql was our attempt to match oracle's pl/sql. As Tom has already suggested in the Thread regarding whether we should move PL's out or not, plpgsql is the only one that is entirely internal to the db. This is where I would clearly draw the line. If you have a PL, that is only reliant on the PostgreSQL being install, then you may have a case for getting it enabled. Otherwise not a chance. I would say plpgsql is likely to be the only PL in this situation for a long time, if the only one ever. > Personally, I think it should be installed by default. I agree with everybody else, having it enabled by default is a good idea. Regards Russell Smith
Neil Conway <neilc@samurai.com> writes:
> But I agree security is not a good argument against enabling it by default.
Isn't it? Even without anything that we regard as a bug, availability
of a server-side programming language is still a risk factor from the
point of view of any reasonably paranoid DBA. The denial of service
risk in particular (whether intentional or accidental) goes way up.
Another problem with this proposal is that installations without
shared-library support will stop working entirely. I suppose we could
get around that by building plpgsql into the core backend instead of as
a shared library, but that will be risky if the other PLs migrate out
--- plpgsql really should be built the same way as the rest of them, so
that it continues to serve as an early warning system for build/link
problems.
Also, your proposal as worded does not seem to mean "installed by
default", it means "installed, period". How would a DBA who doesn't
want it get rid of it? If he later changes his mind, how does he
return to a standard configuration (short of initdb)? We don't really
have support for removing and re-adding built-in functions.
regards, tom lane
Tom Lane wrote: > >Another problem with this proposal is that installations without >shared-library support will stop working entirely. > > How do we manage to run regression tests for such installations? I guess the philosphical question is "do we make the default the most common case and provide exceptions from it up and down, or do we make it the most widely supportable and hence minimal case?" I can see both sides. I do wish we put the handlers somewhere other than the public schema, though. cheers andrew
Andrew Dunstan <andrew@dunslane.net> writes:
> Tom Lane wrote:
>> Another problem with this proposal is that installations without
>> shared-library support will stop working entirely.
> How do we manage to run regression tests for such installations?
We don't. However, degraded functionality is better than none at all.
I don't want to create a scenario where initdb dies if plpgsql isn't
available.
regards, tom lane
On Fri, May 06, 2005 at 03:37:21PM +1000, Neil Conway wrote: > > But I agree security is not a good argument against enabling it by default. Sure it is. "Don't enable anything you don't need," is the first security rule. Everything is turned off by default. If you want it, enable it. "Enabled by default" is what made early Linux distributions give old UNIX hands the willies. It was bad enough that IRIX shipped with everything turned on and suid root; at least it cost several thousand dollars. Linux was _free_, and had many of the same problems. A -- Andrew Sullivan | ajs@crankycanuck.ca In the future this spectacle of the middle classes shocking the avant- garde will probably become the textbook definition of Postmodernism. --Brad Holland
I think that plpgsql should be maintained in core, enabled by default with the ability to turn it off. This handles the paranoid dba and security issue while giving the majority of people what they already have by default. Of course the dump create language statements will have to be dealt with and any other small gotchas. A large gotcha might be a reason to not do it, but I have not seen mention of any. --elein elein@varlena.com On Fri, May 06, 2005 at 12:19:12PM -0400, Andrew Sullivan wrote: > On Fri, May 06, 2005 at 03:37:21PM +1000, Neil Conway wrote: > > > > But I agree security is not a good argument against enabling it by default. > > Sure it is. "Don't enable anything you don't need," is the first > security rule. Everything is turned off by default. If you want it, > enable it. > > "Enabled by default" is what made early Linux distributions give > old UNIX hands the willies. It was bad enough that IRIX shipped with > everything turned on and suid root; at least it cost several thousand > dollars. Linux was _free_, and had many of the same problems. > > A > > -- > Andrew Sullivan | ajs@crankycanuck.ca > In the future this spectacle of the middle classes shocking the avant- > garde will probably become the textbook definition of Postmodernism. > --Brad Holland > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >
Tom Lane wrote: > The denial of service risk in particular (whether intentional or > accidental) goes way up. Does it really go "way up"? A malicious user who can execute SQL can DOS the database trivially. Doing the (non-trivial) infrastructure work to fix that is probably a good idea, but I don't see that not installing pl/pgsql by default is going to make much of a difference. > Another problem with this proposal is that installations without > shared-library support will stop working entirely. I suppose we could > get around that by building plpgsql into the core backend instead of as > a shared library That would be one solution. Another would be to only install pl/pgsql by default when shared libraries are available. While that would mean pl/pgsql wouldn't be available on platforms without shared libraries, that's no worse than the status quo. > Also, your proposal as worded does not seem to mean "installed by > default", it means "installed, period". How would a DBA who doesn't > want it get rid of it? If we effectively just ran the CREATE FUNCTION and CREATE LANGUAGE commands for pl/pgsql in a late stage of initdb, the language and its associated functions wouldn't be builtin. The DBA would then be able to drop pl/pgsql via droplang (which might need to be hacked up a bit to do this). -Neil
Andrew Sullivan wrote: > Sure it is. "Don't enable anything you don't need," is the first > security rule. Everything is turned off by default. If you want it, > enable it. So would you have us disable all the non-essential builtin functions? (Many of which have has security problems in the past.) What about the builtin encoding conversions, non-btree indexes, or a myriad of features that not all users need or use? What makes sense for the default configuration of an operating system (which by nature must be hardened against attack) does not necessarily make sense for a database system. -Neil
On Sat, 2005-05-07 at 14:52 +1000, Neil Conway wrote: > Andrew Sullivan wrote: > > Sure it is. "Don't enable anything you don't need," is the first > > security rule. Everything is turned off by default. If you want it, > > enable it. > > So would you have us disable all the non-essential builtin functions? > (Many of which have has security problems in the past.) What about the > builtin encoding conversions, non-btree indexes, or a myriad of features > that not all users need or use? I support Andrew's comment, though might reword it to "Don't enable anything that gives users programmable features or user exits by default". You can't use the builtin encoding functions or non-btree indexes to access things you are not supposed to. Anything that is *always* there provides a platform for malware. I'm not really sure what is wrong with the CREATE LANGUAGE statement anyway - it is dynamically accessible, so doesn't require changes that effect other database instance users. I do understand the wish to make the lives of admins easier, but this isn't a hard thing to do... > What makes sense for the default configuration of an operating system > (which by nature must be hardened against attack) does not necessarily > make sense for a database system. Security is everybody's job, not just the OS guys. Personally, I forget that constantly, but the principle seems clear. Best Regards, Simon Riggs
Simon Riggs wrote: > I support Andrew's comment, though might reword it to > "Don't enable anything that gives users programmable features or user > exits by default". Users can already define SQL functions by default, which certainly provides "programmable features". I'm not quite sure what you mean by "user exits." I guess I'm missing how pl/pgsql is a fundamentally greater security risk. > You can't use the builtin encoding functions or non-btree indexes to > access things you are not supposed to. How can you use pl/pgsql to "access things you are not supposed to"? -Neil
On Sat, May 07, 2005 at 02:52:57PM +1000, Neil Conway wrote: > > So would you have us disable all the non-essential builtin functions? > (Many of which have has security problems in the past.) What about the > builtin encoding conversions, non-btree indexes, or a myriad of features > that not all users need or use? This is not really analogous, because those are already on (and in most cases, not easily disabled). What you're arguing for is to add yet another on-by-default feature. Given that there's already a way to turn it on, why make it automatic? Moreover, if some repackager wants to make this more convenient, s/he can do so by turning it on by default. I don't see what's wrong with conservatism here. > What makes sense for the default configuration of an operating system > (which by nature must be hardened against attack) does not necessarily > make sense for a database system. Indeed. But that doesn't mean that the principle isn't sound for both cases. I haven't seen an argument against that yet. A -- Andrew Sullivan | ajs@crankycanuck.ca The plural of anecdote is not data. --Roger Brinner
Neil Conway <neilc@samurai.com> writes:
> Users can already define SQL functions by default, which certainly
> provides "programmable features". I'm not quite sure what you mean by
> "user exits."
> I guess I'm missing how pl/pgsql is a fundamentally greater security risk.
plpgsql has control structures (loops, IF); SQL functions don't.
That makes for a fundamental difference in the power of the programming
language ... at least according to CS theory as I was taught it. Now
admittedly the "primitive statements" of SQL are a lot more powerful
than the primitive statements usually considered in programming language
theory, but I think there is still a pretty significant difference in
capability.
An example of why this could be interesting from a security standpoint
is that, given access to pg_shadow, it'd be pretty trivial to write
a plpgsql function that tries to break user passwords by brute force
(just generate possible passwords, hash them, and look for a match).
I don't see any way to do that in plain SQL, at least not without a
pre-existing SRF to generate the trial passwords for you.
regards, tom lane
On 2005-05-07, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Neil Conway <neilc@samurai.com> writes: >> Users can already define SQL functions by default, which certainly >> provides "programmable features". I'm not quite sure what you mean by >> "user exits." > >> I guess I'm missing how pl/pgsql is a fundamentally greater security risk. > > plpgsql has control structures (loops, IF); SQL functions don't. > That makes for a fundamental difference in the power of the programming > language ... at least according to CS theory as I was taught it. SQL functions do have control structures: CASE WHEN and recursion. I have even implemented generate_series() in pure SQL. > Now > admittedly the "primitive statements" of SQL are a lot more powerful > than the primitive statements usually considered in programming language > theory, but I think there is still a pretty significant difference in > capability. The only thing that makes pg's SQL functions not turing-complete is the fact that recursion depth is not unlimited. In practice this isn't much of a restriction, since you can do large-scale iterations by using SQL sets and joins rather than recursion. > An example of why this could be interesting from a security standpoint > is that, given access to pg_shadow, it'd be pretty trivial to write > a plpgsql function that tries to break user passwords by brute force > (just generate possible passwords, hash them, and look for a match). > I don't see any way to do that in plain SQL, at least not without a > pre-existing SRF to generate the trial passwords for you. Writing a pure SQL SRF that generates a range of trial passwords is in fact not hard at all. The key point to note is that you can generate sets of literal values of moderate size using UNION ALL, and you can then cross-join those sets against themselves multiple times to generate much larger and more complex sets. -- Andrew, Supernews http://www.supernews.com - individual and corporate NNTP services
People: Before we get into more minutia regarding potential security risk of plpgsql, are there any reasons *other* than security to not enable it? -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus <josh@agliodbs.com> writes:
> Before we get into more minutia regarding potential security risk of plpgsql,
> are there any reasons *other* than security to not enable it?
Several potential issues have already been mentioned in this thread,
eg, what about shared library dependency vs linking plpgsql statically
into the backend, how will we deal with existing dump files, how will
it be possible for a DBA to disable the feature if he wishes.
But security is the easiest to handwave about with no facts ;-) ...
so if it gets shot down on that basis then no one need do the work
to figure out solutions to the other questions.
regards, tom lane
Andrew Sullivan wrote: > This is not really analogous, because those are already on Which is my point: you're suggesting we retrofit a security policy onto PG that does not apply to the vast majority of the base system -- and that if applied would require fundamental changes. > Indeed. But that doesn't mean that the principle isn't sound for > both cases. I haven't seen an argument against that yet. Security (in the limited sense of "disabling features by default") is not free; there is a tradeoff between security and convenience, security and administrative simplicity, and so on. Given that I have yet to see a single substantive argument for pl/pgsql being a security risk that has withstood any scrutiny, I don't see that the "security" side of the tradeoff has a lot of merit. -Neil
Neil Conway wrote: > Andrew Sullivan wrote: >> This is not really analogous, because those are already on > Security (in the limited sense of "disabling features by default") is > not free; there is a tradeoff between security and convenience, security > and administrative simplicity, and so on. Given that I have yet to see a > single substantive argument for pl/pgsql being a security risk that has > withstood any scrutiny, I don't see that the "security" side of the > tradeoff has a lot of merit. People who use views to achieve row security, which is a rather common paradigm, cannot allow users to create functions with side effects. Mike Mascari
Mike Mascari wrote: > People who use views to achieve row security, which is a rather common > paradigm, cannot allow users to create functions with side effects. Can you elaborate? I'm not sure I follow you. (I'll note anyway that (1) SQL functions can have side effects: CREATE FUNCTION foo() RETURNS VOID AS 'DELETE FROM ...', for example (2) Administrators can always choose to drop pl/pgsql for a particular database, disable it at initdb time, or REVOKE usage of pl/pgsql for particular users.) -Neil
Neil Conway wrote: > Mike Mascari wrote: > >> People who use views to achieve row security, which is a rather common >> paradigm, cannot allow users to create functions with side effects. > > > Can you elaborate? I'm not sure I follow you. > > (I'll note anyway that (1) SQL functions can have side effects: CREATE > FUNCTION foo() RETURNS VOID AS 'DELETE FROM ...', for example Wow. That's a problem IMHO. Many people use views to allow userA to query his salary without seeing userB's. If userA can just: 1. Create a SQL function which inserts into another table its arguments 2. Issue a query like: SELECT * FROM view_of_salaries_based_on_current_user WHERE my_side_effect_function_that_inserts_into_a_temp_table(salary, employee); and that function is writable with just 'SQL', then many, many people have a serious security risk on their hands. Perhaps this is why Oracle's standard UDFs cannot perform inserts, updates, or deletes. Mike Mascari
Mike Mascari wrote: > Neil Conway wrote: > >> Mike Mascari wrote: >> >>> People who use views to achieve row security, which is a rather >>> common paradigm, cannot allow users to create functions with side >>> effects. >> >> >> >> Can you elaborate? I'm not sure I follow you. >> >> (I'll note anyway that (1) SQL functions can have side effects: >> CREATE FUNCTION foo() RETURNS VOID AS 'DELETE FROM ...', for example > > > Wow. That's a problem IMHO. Many people use views to allow userA to > query his salary without seeing userB's. If userA can just: > > 1. Create a SQL function which inserts into another table its arguments > > 2. Issue a query like: > > SELECT * > FROM view_of_salaries_based_on_current_user > WHERE my_side_effect_function_that_inserts_into_a_temp_table(salary, > employee); > > and that function is writable with just 'SQL', then many, many people > have a serious security risk on their hands. Perhaps this is why > Oracle's standard UDFs cannot perform inserts, updates, or deletes. > > but the side effect function will only run (unless you set it with security definer) with the privileges of the caller - it won't grant visibility to things that user can't otherwise see. In any case, you should define your security setup with the capabilities / limitations of the db engine in mind. If there is any security problem in your scenario, it is that you appear to have made unwarranted assumptions about how postgres works, rather than that postgres has a problem. Either way, this does not illustrate how enabling plpgsql by default is a security risk. cheers andrew
Andrew Dunstan wrote: > > > Mike Mascari wrote: > but the side effect function will only run (unless you set it with > security definer) with the privileges of the caller - it won't grant > visibility to things that user can't otherwise see. If the visibility is determined by view definitions, such as using CURRENT_USER, which is an exceedingly common practice, then the caller will be able to record tuples before they are filtered by the executor. > In any case, you should define your security setup with the > capabilities / limitations of the db engine in mind. If there is any > security problem in your scenario, it is that you appear to have made > unwarranted assumptions about how postgres works, rather than that > postgres has a problem. I think most people coming from any other enterprise-class RDBMS environment will be surprised that they cannot use VIEWs to provide user-specific views on data. I could be wrong, but I'd put money on it... > Either way, this does not illustrate how enabling plpgsql by default is > a security risk. Correct, as the vulnerability exists within the 'SQL' language as well. The only difference is that enabling plpgsql by default changes it from a leak to a full blown flood. Mike Mascari
Mike Mascari wrote: > Correct, as the vulnerability exists within the 'SQL' language as well. > The only difference is that enabling plpgsql by default changes it from > a leak to a full blown flood. How does it make any difference at all? -Neil
> 2. Issue a query like: > > SELECT * > FROM view_of_salaries_based_on_current_user > WHERE my_side_effect_function_that_inserts_into_a_temp_table(salary, > employee); An SRF will guarantee an execution order and work for security purposes, but getting your function with side effects to run early would be rather challenging. Somehow you would need to make the planner think the function is fairly selective without it actually being that way and the only way that I'm aware of to get the planner to consider a functions selectivity is to index it. But how do you index the function without knowing all of the values? --
Mike, > I think most people coming from any other enterprise-class RDBMS > environment will be surprised that they cannot use VIEWs to provide > user-specific views on data. I could be wrong, but I'd put money on it... Well, I'd say that giving regular users the "create" permission on your database/schema is unwise, period. I don't, even when the only user is "phpuser". SQL injections attacks are no fun. Also, as Andrew points out, this can't be used to circumvent view-based security if you've set it up correctly; if the user can't "select * from table", then he can't write a function to "select * from table." -- Josh Berkus Aglio Database Solutions San Francisco
Josh Berkus wrote: >Mike, > > > >>I think most people coming from any other enterprise-class RDBMS >>environment will be surprised that they cannot use VIEWs to provide >>user-specific views on data. I could be wrong, but I'd put money on it... >> >> > >Well, I'd say that giving regular users the "create" permission on your >database/schema is unwise, period. I don't, even when the only user is >"phpuser". SQL injections attacks are no fun. > >Also, as Andrew points out, this can't be used to circumvent view-based >security if you've set it up correctly; if the user can't "select * from >table", then he can't write a function to "select * from table." > > > Seems it's a bit more complicated. Kris Jurka has explained to me how one might be able to, at least theoretically. Perhaps Mike needs to do something like: revoke usage on language sql from public; on his db. Then users could continue to use functions he has defined, but not subvert things via their own functions. That will certainly go into my list of db hardening tips and tricks. cheers andrew
Mike Mascari <mascarm@mascari.com> writes: > 2. Issue a query like: > > SELECT * > FROM view_of_salaries_based_on_current_user > WHERE my_side_effect_function_that_inserts_into_a_temp_table(salary, employee); That's just exactly equivalent to SELECT * FROM (select * from all_salaries where user = CURRENT_USER ) WHERE malicious_function(salary,employee) Hm. If you incorrectly mark your function as IMMUTABLE even though it has side effects then the planner may indeed collapse this. Does the planner know it can't collapse views if the underlying tables aren't accessible to the user? -- greg
Greg Stark <gsstark@mit.edu> writes:
> Hm. If you incorrectly mark your function as IMMUTABLE even though it
> has side effects then the planner may indeed collapse this. Does the
> planner know it can't collapse views if the underlying tables aren't
> accessible to the user?
There are no cases where function or view collapsing elides permissions
checks (if you have a counterexample please provide it!!). They could
change the time at which permissions checks are applied, though; which
has the potential for a REVOKE to not disallow execution of already-
planned queries that ideally it should prevent. I believe that this
risk will be fixed by the planned forcing of replanning after schema
changes.
regards, tom lane
Tom Lane <tgl@sss.pgh.pa.us> writes: > Greg Stark <gsstark@mit.edu> writes: > > Hm. If you incorrectly mark your function as IMMUTABLE even though it > > has side effects then the planner may indeed collapse this. Does the > > planner know it can't collapse views if the underlying tables aren't > > accessible to the user? > > There are no cases where function or view collapsing elides permissions > checks (if you have a counterexample please provide it!!). I'm talking about something like this. In guess there isn't a problem after all but it depends critically on the AND short-circuiting (and the order of evaluation of the expression not being changed). db=> create view vtest as select * from test where a > 1 ; db=> create or replace function f(integer) returns integeras 'begin raise notice ''foo %'', $1; return $1; end' language plpgsql; db=> explain select * from vtest where f(a)>0; QUERY PLAN ------------------------------------------------------- Seq Scan on test (cost=0.00..27.50 rows=112 width=4) Filter:((a > 1) AND (f(a) > 0)) (2 rows) I can't come up with any circumstances where the function will get called before the a>1 clause. If it were indexed then it would be "evaluated" first but it would no longer be relevant since the function wouldn't be getting called. But it's something to watch out for. If ever it seems like a wise idea to have the optimizer fiddle with the order of evaluation, say based on the selectivity or computational expense of the conditions then it could create a problem. -- greg