Thread: Oracle and Postgresql

Hello,

I am a CS graduate and I have a brief idea of Postgres and Oracle.
But, I dont have an in-depth knowledge in any of them. I have a couple
of questions and

I want to compare both of them in terms of functionality, performance,
advantages and disadvantages.

Why most enterprises prefer Oracle than Postgres even though it is
free and has a decent enough user community.

--
Thanks,
Srinivas

On Sun, Aug 31, 2008 at 11:29:32AM -0700, M2Y wrote:
> Hello,
>
> I am a CS graduate and I have a brief idea of Postgres and Oracle.
> But, I dont have an in-depth knowledge in any of them. I have a
> couple of questions and
>
> I want to compare both of them in terms of functionality,
> performance, advantages and disadvantages.
>
> Why most enterprises prefer Oracle than Postgres even though it is
> free and has a decent enough user community.

That depends which enterprises.  At the moment, people who know
Postgres are not common, which gives them advantages in negotiations.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Sun, Aug 31, 2008 at 12:29 PM, M2Y <mailtoyahoo@gmail.com> wrote:
> Hello,
>
> I am a CS graduate and I have a brief idea of Postgres and Oracle.
> But, I dont have an in-depth knowledge in any of them. I have a couple
> of questions and
>
> I want to compare both of them in terms of functionality, performance,
> advantages and disadvantages.
>
> Why most enterprises prefer Oracle than Postgres even though it is
> free and has a decent enough user community.

I got started using PostgreSQL for a "throw away" project.  One that
needed to be done fast and cheap and if it didn't work out it was ok,
because we were only gonna spend a week or so setting it up.  After
setting up pgsql to handle the single project, we started building
more things for that server, and it eventually grew into the corporate
intranet server, backending other apaches behind it, handling LDAP
auth for all the major apps in the company, storing files for
different groups, especially files that needed to be on the corporate
intranet, and running postgresql in the background too.

With that kind of project, free often wins out because it lowers your
cost of ownership on a low budget project.

When you get into the multi-million dollar integration projects
involving every sub group in an organization the budget is often huge,
and the bosses want something they've heard of / seen in action and
know can handle the load.  A bad db choice could sink the whole
multi-million dollar project.  A $200k insurance policy (I.e. Oracle)
is a small price to pay.

I think some of it is inertia.  We've always used Oracle, let's just
keep on using it.  The more conservative the IT department is, the
less likely they are to take chances with new technology.

It used to be there was about an 80/20 split between what things you
could do with either postgresql or oracle, and the other 20% was
oracle only land. I think that number is dropping quickly, and we're
into the 1 or 2% club of what Oracle can do that PostgreSQL isn't fast
enough for.

The other thing that holds back PostgreSQL right now is a lack of
experienced pgsql DBAs and application developers.  That will change
over time.

As more people become familiar with using and maintaining pg servers,
and the usage starts to take up, there will be a multiplying effect as
more PHBs hear the name with good things said about it.

At 2:29pm -0400 on Sun, 31 Aug 2008, Srinivas wrote:
> I want to compare both of them in terms of functionality, performance,
> advantages and disadvantages.

If you publish anything, watch out for the Oracle licensing no-nos.
Specifically, I believe they disallow certain comparisons.  I believe
performance is one of them.

> Why most enterprises prefer Oracle than Postgres even though it is
> free and has a decent enough user community.

Many reasons, some legit, some not.  I expect a Greg or two will
seriously add to and correct this list, but:

1. Oracle was "first", and has vendor lock-in momentum.
2. Oracle is still the "de facto" in terms of
   speed/performance/concurrency (but that gap is closing fast)
3. Oracle has application lock-in as well.  There've more than a few
   threads on this list regarding which applications.
4. Oracle is company-backed, so there is ostensibly "someone to blame"
   or sue if something goes wrong.  Don't underestimate the managerial
   "need" for blame.
5. Influencing individuals within a company may prefer it *because* it's
   expensive, as it helps "justify" their higher salary.
6. Mucho better advertising to the right people.  Much harder for an
   open source project to fund advertising to the same level.
7. Though I don't personally buy it, I have heard others complain
   loudly that there is no print-version of Postgres documentation.

There's a starting list.

Kevin

On Sun, Aug 31, 2008 at 1:50 PM, Kevin Hunter <hunteke@earlham.edu> wrote:

> 7. Though I don't personally buy it, I have heard others complain
>   loudly that there is no print-version of Postgres documentation.


This one should be taken off the list.  The postgresql online
reference manual is in print( volumes 1 - 3)
http://www.amazon.com/PostgreSQL-Reference-Manual-SQL-Language/dp/0954612027/ref=pd_sim_b_1


--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

-----BEGIN PGP SIGNED MESSAGE-----
Hash: RIPEMD160


>> 7. Though I don't personally buy it, I have heard others complain
>>   loudly that there is no print-version of Postgres documentation.

> This one should be taken off the list.  The postgresql online
> reference manual is in print( volumes 1 - 3)
> http://www.amazon.com/PostgreSQL-Reference-Manual-SQL-Language/dp/0954612027/ref=pd_sim_b_1

Printable docs is a silly reason, but that link is to the docs for 8.2.4, so the
argument is not negated by the existence of that book. A dearth of books in
general is certainly a weak point for Postgres however.

Need some more reasons why Oracle over Postgres?:

1) Oracle has in-place upgrades. I know for a fact this has prevented some
large companies from going to Postgres.

2) Oracle RAC

3) Oracle sales people and a huge marketing department

4) Oracle runs on Windows. We do too, but did not for a long time and the
perception that we don't (or don't do it well) is still out there.

5) Training, certification, and the aforementioned books. We're very slowly
gaining traction in this area.

6) We've been tainted by our lumping together with MySQL, which makes
open-source databases in general look bad when people familiar with Oracle
examine MySQL and assume Postgres is similar in attitude, community,
features, and robustness.

I'm sure EDB could expand this list a bit as well. :)


- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200808311937
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAki7K7gACgkQvJuQZxSWSsgCkwCg0F3mlLiIu9gRskL8EP3+u2MZ
Da4AniP2XkQOOn++1mYM0BSOe0Urp0B9
=1zO8
-----END PGP SIGNATURE-----

Another thing that has only been mentioned obliquely is support. When an
organization selects an enterprise wide mission critical database system
they get a lot of perceived security from purchasing a world-wide 24x7
support contract from a company with the resources that Oracle can
provide. Yes, you have to write a big check but you are insuring against
a big risk. As others have stated, many of the factors that have a major
influence on the choice of a database are not technical.

Bill

M2Y wrote:
> Hello,
>
> I am a CS graduate and I have a brief idea of Postgres and Oracle.
> But, I dont have an in-depth knowledge in any of them. I have a couple
> of questions and
>
> I want to compare both of them in terms of functionality, performance,
> advantages and disadvantages.
>
> Why most enterprises prefer Oracle than Postgres even though it is
> free and has a decent enough user community.

One significant factor is scaling and clustering.

Right now, PostgreSQL has network-based master-to-slave(s) clustering
using Slony that permits slaves to operate online read-only as reporting
servers etc. It also covers failover with the use of an external
heartbeat/STONITH setup. However, it's not completely transparent to the
application and it's not seen as "part of postgresql".

There are some other options that I'm largely unfamiliar with out there,
but right now it seems that Slony is the way to go with Pg.


Oracle, by contrast, has multiple-master clustering on shared storage
(which can be iSCSI, FireWire, SAS, SCSI, or basically whatever else you
like) with a network side-channel for inter-node messaging. All nodes
can be active readers and writers in cooperation. I haven't used it and
can't speak for its performance, reliability or admin-friendliness, but
it seems to see very wide use in large systems.

I'm pretty sure they also support master->slave replication and
failover, etc.

Multiple master clustering offers scaling options other than upgrading
your DB server to add more disks/RAM/CPU, or moving reporting onto a
separate read-only slave, so it'd be very nice to have on any project
where serious growth looks likely.

That's not to say you can't build some pretty big systems with Pg (and
they certainly exist) ... but the scale-out options added by
multiple-master clustering will certainly weigh decisions between the
two database systems for some types of loads.

--
Craig Ringer

M2Y wrote:
>
> Why most enterprises prefer Oracle than Postgres even though it is
> free and has a decent enough user community.
>

Databases are a critical part of many companies' business.  I work for
telecom company, and if we were to lose our databases, we'd be out of
business, period.  So, "free and decent enough" are not good enough.  If
you are going to bet your business on anything, you want to be as sure
as possible that "it" is reliable and that you can expect quick action
if "it" should break.

I tried to introduce PG into our business 4 years ago.  Managers
listened to the benefits but went with Oracle.  No one ever explained
why, but I've worked in this industry for 30 years and have a pretty
good idea.  Oracle is a big company, and we have a 24/7 support contract
with them.  If something goes wrong any time of the day, we can call
them and open a support issue.

PG is a great piece of work and equal in performance and features to
commercial DBMSs.  You can get support contracts from several companies.
  But there is no single entity that "owns" PG.  CTOs/CIOs like to sleep
at night.

--
Guy Rouillier

On Aug 31, 2008, at 7:44 PM, Guy Rouillier wrote:
> CTOs/CIOs like to sleep at night.

If you buy Oracle, and there's a problem, the conversation with the
CEO is that "Oracle broke."  With PG, even if you have exactly the
same level of support, "that database you selected broke."

The sad reality is that choosing something with a high industry
presence makes it easier to offload blame for failures, especially
when dealing with non-technical management.

On Sun, Aug 31, 2008 at 10:44:38PM -0400, Guy Rouillier wrote:
> M2Y wrote:
>>
>> Why most enterprises prefer Oracle than Postgres even though it is
>> free and has a decent enough user community.
>
> Databases are a critical part of many companies' business.  I work
> for  telecom company, and if we were to lose our databases, we'd be
> out of  business, period.  So, "free and decent enough" are not good
> enough.  If  you are going to bet your business on anything, you
> want to be as sure  as possible that "it" is reliable and that you
> can expect quick action  if "it" should break.

What they want to have is a huge entity they can blame when everything
goes wrong.  They're not interested in the actual response times or
even in the much more important time-to-fix because once they've
blamed Oracle, they know the responsibility is no longer on their
shoulders.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

hi, all:
I use a plproxy to execute sql like below:
CREATE OR REPLACE FUNCTION public.dmlexec(query text,op text) RETURNS
setof integer AS $$
      CLUSTER 'testcluster';
       RUN ON all;
$$ LANGUAGE plproxy;
create or replace function public.dmlExec(query text, op text) returns
integer as $$
declare
   ret integer;
begin
        execute query;
   return 1;
end;
$$ language plpgsql;

when I exectue  sql like 'select dmlexec('delete from table_test where
id = 2', 'test') at the same time in numerous process, the delete sql
will blocked.....
so I must select pg_cancel_backend(...).

is there a deadlock??

postgresql version: 8.3.0

thanks all:)

On Sunday 31 August 2008, Christophe wrote:
> On Aug 31, 2008, at 7:44 PM, Guy Rouillier wrote:
> > CTOs/CIOs like to sleep at night.
>
> If you buy Oracle, and there's a problem, the conversation with the
> CEO is that "Oracle broke."  With PG, even if you have exactly the
> same level of support, "that database you selected broke."
>
> The sad reality is that choosing something with a high industry
> presence makes it easier to offload blame for failures, especially
> when dealing with non-technical management.

Sadly I can second that. I've seen quite some companies go with a completely
inappropriate product, just because "they're the market leader".  And
certainly there is the other side of the coin: The CTO who decides doesn't
want anyone to rock his boat. So often s/he decides to go with biggest market
presence, simply because IF something breaks it's not their fault - the
argument being "we went with the best there is, it's their fault".  (maybe
it's the "best there is", but often not for the application in question).

I've got one customer who didn't care - they're running on postgresql for 8
years now and nobody noticed - hehehe. I just told the CEO "it's a SQL
database" - which satisfied his curiosity :-)
But that's certainly not the case with everyone...

Uwe

I agree with David's comment. The business I work for recently proposed a FOSS based solution for a client, but were
toldno FOSS solutions would be considered. We had a subcontractor offer a support contract for an application based on
thesame FOSS components, but with a support contract.  

This was perfectly acceptable to our client, who now has a working solution, for which they pay annual maintenance, in
returnfor a lack of internal liability. 

For many businesses, risk avoidance is a core driver. What they require is someone else to blame if things go wrong,
hencethe companies making a living with contracts for Postgres support.  


Cheers,

  Brent Wood


Brent Wood
DBA/GIS consultant
NIWA, Wellington
New Zealand
>>> David Fetter <david@fetter.org> 09/01/08 6:09 PM >>>
On Sun, Aug 31, 2008 at 10:44:38PM -0400, Guy Rouillier wrote:
> M2Y wrote:
>>
>> Why most enterprises prefer Oracle than Postgres even though it is
>> free and has a decent enough user community.
>
> Databases are a critical part of many companies' business.  I work
> for  telecom company, and if we were to lose our databases, we'd be
> out of  business, period.  So, "free and decent enough" are not good
> enough.  If  you are going to bet your business on anything, you
> want to be as sure  as possible that "it" is reliable and that you
> can expect quick action  if "it" should break.

What they want to have is a huge entity they can blame when everything
goes wrong.  They're not interested in the actual response times or
even in the much more important time-to-fix because once they've
blamed Oracle, they know the responsibility is no longer on their
shoulders.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, August 31, 2008 10:44 pm, Scott Marlowe wrote:
> The other thing that holds back PostgreSQL right now is a lack of
> experienced pgsql DBAs and application developers.  That will change
> over time.

And built-in, simple to use, reliable, flexible and fast replication.
Many a Pg admin or implementer has looked on with envy at what Oracle does
"out of the box" in terms of replication alone.  Yes, there are
third-party options (Slony, etc), but they're *third party*, and nowhere
near as reliable and robust as Pg itself.   (thank heavens for Skytools,
btw)

This is /finally/ being addressed, although (very) belatedly.  The Pg core
dev team always argued that replication was an add-on and should not form
part of the core (ie, similar nonsense excuses the MySQL team used for
"add-ons" such as triggers, etc).

Leaving this one vital issue so late has caused damage to Pg's reputation
in my view.

Come on Tom, how about talking to the Command Prompt folks (who are
?about? to release Mammoth Replicator as Open Source) and look at possibly
merging their Pg-mod replication work into Pg itself?  If it's doable and
conforms to the Pg requirements, it will make up - and save - a mountain
of time.  The alternative will be a few years of stabilising any new
replication code before it's considered safe to adopt in production.

Their.  I've had my moan for the day, and I feel much better :-)

Cheers
Henry

On Mon, September 1, 2008 4:09 am, Craig Ringer wrote:
> Right now, PostgreSQL has network-based master-to-slave(s) clustering
> using Slony that permits slaves to operate online read-only as reporting
> servers etc. It also covers failover with the use of an external
> heartbeat/STONITH setup. However, it's not completely transparent to the
> application and it's not seen as "part of postgresql".
>
> There are some other options that I'm largely unfamiliar with out there,
> but right now it seems that Slony is the way to go with Pg.

...and Skytools (as used by Skype, I believe, which is now Open Source),
which is far simpler to use and in my experience far more reliable.

Cheers
Henry

Scott Marlowe, 31.08.2008 22:44:
> I think some of it is inertia.  We've always used Oracle, let's just
> keep on using it.  The more conservative the IT department is, the
> less likely they are to take chances with new technology.
>
> It used to be there was about an 80/20 split between what things you
> could do with either postgresql or oracle, and the other 20% was
> oracle only land. I think that number is dropping quickly, and we're
> into the 1 or 2% club of what Oracle can do that PostgreSQL isn't fast
> enough for.

For me (personally) the ratio is more like 70/30, because we are making extensive usage of Oracle's windowing functions
(andability to easily deal with hierarchical data using CONNECT BY).  

The lack of windowing functions is a bit frustrating as I'm otherwise a big Postgres fan!

Regards
Thomas

Henry wrote:
> On Sun, August 31, 2008 10:44 pm, Scott Marlowe wrote:
>> The other thing that holds back PostgreSQL right now is a lack of
>> experienced pgsql DBAs and application developers.  That will change
>> over time.
>
> And built-in, simple to use, reliable, flexible and fast replication.
> Many a Pg admin or implementer has looked on with envy at what Oracle does
> "out of the box" in terms of replication alone.  Yes, there are

Well actually it appears a lot of DBAs are not satisfied with Oracles
"out of the box" solution and buy third party stuff when it comes
to replication. So this argument doesn't really bite.

Regards
Tino

On Mon, Sep 01, 2008 at 10:31:25AM +0200, Thomas Kellerer wrote:
> Scott Marlowe, 31.08.2008 22:44:
>> I think some of it is inertia.  We've always used Oracle, let's
>> just keep on using it.  The more conservative the IT department is,
>> the less likely they are to take chances with new technology.
>>
>> It used to be there was about an 80/20 split between what things
>> you could do with either postgresql or oracle, and the other 20%
>> was oracle only land. I think that number is dropping quickly, and
>> we're into the 1 or 2% club of what Oracle can do that PostgreSQL
>> isn't fast enough for.
>
> For me (personally) the ratio is more like 70/30,

It's about to increase :)

> because we are making extensive usage of Oracle's windowing
> functions

http://umitanuki.net/pgsql/wfv04/design.html

> (and ability to easily deal with hierarchical data using CONNECT BY).

It's even more fun with CTEs.
<http://wiki.postgresql.org/wiki/CTEReadme>

> The lack of windowing functions is a bit frustrating as I'm
> otherwise a big Postgres fan!

Those will both be fixed, at least to a large degree, in 8.4 :)

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter, 01.09.2008 16:08:
>> because we are making extensive usage of Oracle's windowing
>> functions
>
> http://umitanuki.net/pgsql/wfv04/design.html

I knew there was work going on regarding this, but I didn't know how definite the decision was to integrate that into
8.4

It's too bad lead(), lag() won't make it

> It's even more fun with CTEs.
> <http://wiki.postgresql.org/wiki/CTEReadme>

Recursive CTE are quite cool, and a valid "replacement" for CONNECT BY.
I've used it already with SQL Server 2005 and once you get the idea it's really nice.

> Those will both be fixed, at least to a large degree, in 8.4 :)

What exactly means "large degree" :)


Regards
Thomas

On Mon, Sep 01, 2008 at 04:39:09PM +0200, Thomas Kellerer wrote:
> David Fetter, 01.09.2008 16:08:
>>> because we are making extensive usage of Oracle's windowing
>>> functions
>>
>> http://umitanuki.net/pgsql/wfv04/design.html
>
> I knew there was work going on regarding this, but I didn't know how
> definite the decision was to integrate that into 8.4
>
> It's too bad lead(), lag() won't make it

It's not certain they won't make it, only that they're not in the
current patch.

>> It's even more fun with CTEs.
>> <http://wiki.postgresql.org/wiki/CTEReadme>
>
> Recursive CTE are quite cool, and a valid "replacement" for CONNECT
> BY.  I've used it already with SQL Server 2005 and once you get the
> idea it's really nice.

It's also standard, where Oracle's syntax is proprietary.  In
PostgreSQL, when there's a choice between the two, the standard wins.

>> Those will both be fixed, at least to a large degree, in 8.4 :)
>
> What exactly means "large degree" :)

It means we may not get everything the SQL:2008 standard specifies for
CTEs and windowing functions on the first pass.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Richard Broersma wrote:
> On Sun, Aug 31, 2008 at 1:50 PM, Kevin Hunter <hunteke@earlham.edu> wrote:
>
>> 7. Though I don't personally buy it, I have heard others complain
>>   loudly that there is no print-version of Postgres documentation.
>
>
> This one should be taken off the list.  The postgresql online
> reference manual is in print( volumes 1 - 3)
> http://www.amazon.com/PostgreSQL-Reference-Manual-SQL-Language/dp/0954612027/ref=pd_sim_b_1

I would happily provide a print version to any company needing one, charging
only a reasonable fee for my extensive knowledge about invocation of the
PDF-reader "Print" action on one of their workstations.  (Binding extra.)

--
Lew

On Tuesday 02 September 2008 17:21:12 Asko Oja wrote:
> On Tue, Sep 2, 2008 at 2:09 AM, Michael Nolan <htfoot@gmail.com> wrote:
> > Oracle handles connecting to multiple databases (even on multiple/remote
> > computers) fairly seamlessly, PG does not (yet.)
>
> Stuff we do with plProxy on PostgreSQL is in some respects more advanced
> than anything Oracle has to offer :) We have hundreds of databases in quite
> complex network of remote calls and replication.
>

Yes, but it is also far more complex to install, configure, and use, compared
to something simple like oracle's dblink, which comes pre-installed, is
simple to set-up, and has a much more straight-forward syntax for use in day
to day query work.

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

On Tue, 2008-09-02 at 22:56 -0400, Robert Treat wrote:
> On Tuesday 02 September 2008 17:21:12 Asko Oja wrote:
> > On Tue, Sep 2, 2008 at 2:09 AM, Michael Nolan <htfoot@gmail.com> wrote:
> > > Oracle handles connecting to multiple databases (even on multiple/remote
> > > computers) fairly seamlessly, PG does not (yet.)
> >
> > Stuff we do with plProxy on PostgreSQL is in some respects more advanced
> > than anything Oracle has to offer :)

> oracle's dblink, which comes pre-installed, is
> simple to set-up, and has a much more straight-forward syntax for use in day
> to day query work.
>

I've not used oracle's dblink before, but based on PG's dblink, one
thing which I didn't especially like is the need to pre-cast the data
types. Then again, how else will it know what data (type) is being
pulled in?

For simple queries, knowing the data type means an additional lookup to
determine the data-type casting needed for the query.

I can't speak from the dba perspective, but I mainly write applications
against Postgres and Oracle. I've used a dozen or more RDBMS's and
Postgres and Oracle are by far the most similar of any two.

When the two differ, its about an even split for when I say "I wish
Oracle did it like Postgres" and "I wish Postgres did it like Oracle."

Others have mentioned some areas where Postgres is lacking that will
soon be addressed such as CTE's. I'd really like to see Postgres
implement something like MERGE INTO, and stored procedures instead of
just functions.

On the other side of the coin, Oracle doesn't have a boolean type (in
SQL) which is annoying; you don't have to select from 'dual';
generate_series() is quite useful; and custom aggregates are easier in
postgres.

I don't know if Oracle changed recently, but the last few times I used it, it was incredibly annoying having to put everything in a subquery to get a LIMIT-type operation to work AFTER the sort, so that you could use their ROWNUM.  For example, to get the first 50 rows of a SELECT result.  Their ROWNUM worked BEFORE the ORDER BY, so to get the 1st 50 rows, you had to put the query in a subselect, and say SELECT ... where ROWNUM <= 50.

I love OFFSET ... LIMIT in PostgreSQL!  I do a lot of web applications, and it is incredibly handy to page output with.

Susan

----------------------------------------------------
Tiered Data Protection Made Simple
http://www.overlandstorage.com/
----------------------------------------------------

On Wed, Sep 3, 2008 at 9:36 AM,  <SCassidy@overlandstorage.com> wrote:
>
> I don't know if Oracle changed recently, but the last few times I used it,
> it was incredibly annoying having to put everything in a subquery to get a
> LIMIT-type operation to work AFTER the sort, so that you could use their
> ROWNUM.  For example, to get the first 50 rows of a SELECT result.  Their
> ROWNUM worked BEFORE the ORDER BY, so to get the 1st 50 rows, you had to put
> the query in a subselect, and say SELECT ... where ROWNUM <= 50.
>
> I love OFFSET ... LIMIT in PostgreSQL!  I do a lot of web applications, and
> it is incredibly handy to page output with.

Yeah, those kinds of things drove me a little crazy.  I think
PostgreSQL's equivalent parsing peculiarity is the need to put certain
things in an extra set of parens for creating an index.  But really
that's pretty minor compared to rownum versus order by firing order.

One of the things an Oracle DBA coworker of mine found really
bothersome at first in pgsql was that suddenly on large queries that
he'd never needed an order by on Oracle, he was getting randomly
ordered reporting data.  He was flabbergasted that something would use
an aggregation method that wouldn't result in an ordered set.  He was
sure it would be a loser with an order by but it was very very fast on
large numbers of rows.  I think that if you're really used to one
database you'll find a new one bothersome in some ways, and a pleasure
to use in others.

Once I had installed rlwrapper on sqlplus I was ok.  It wasn't in the
same league as psql, which is the greatest command line sql monitor
ever created, but having line editing and history made sqlplus usable.
 It was cool to hit !alter sess and get my previous default schema
statement.  All the Oracle DBAs around me were working in guis and
they'd walk by and think I in psql, I'd ask them to look at something,
like a query, and it would take them about 10 seconds to realize I was
working on oracle from the command line.

If I could ask for one thing from Oracle it would be that they borrow
the basic operational characteristics of psql and stuff it into
sqlplus or whatever they use.  Having the \ commands and expanded
versions in sqlplus would have been awesome.

David Fetter wrote:
> What they want to have is a huge entity they can blame when everything
> goes wrong.  They're not interested in the actual response times or
> even in the much more important time-to-fix because once they've
> blamed Oracle, they know the responsibility is no longer on their
> shoulders.
>
>
That is only a perceived sense of risk avoidance, if you read the EULA
etc that ship with Oracle, MS SQL server etc, they are not responsible
for anything that may happen to your data.  Sure management could blame
them, but that's about it.  They would get the same amount of
satisfaction from blaming the FOSS community.  No matter what management
says any blame rests squarely on their shoulders and the people they
have entrusted to create their corp projects/products when something
goes wrong.

On Wed, Sep 3, 2008 at 2:07 PM, Tony Caduto
<tony_caduto@amsoftwaredesign.com> wrote:
> David Fetter wrote:
>>
>> What they want to have is a huge entity they can blame when everything
>> goes wrong.  They're not interested in the actual response times or
>> even in the much more important time-to-fix because once they've
>> blamed Oracle, they know the responsibility is no longer on their
>> shoulders.
>
> That is only a perceived sense of risk avoidance, if you read the EULA etc
> that ship with Oracle, MS SQL server etc, they are not responsible
> for anything that may happen to your data.  Sure management could blame
> them, but that's about it.  They would get the same amount of satisfaction
> from blaming the FOSS community.  No matter what management says any blame
> rests squarely on their shoulders and the people they have entrusted to
> create their corp projects/products when something goes wrong.

I had a boss (great one, really) who was getting a lot of crap from
upstairs about our use of open source, and that was one of the PHB's
arguments above him.  When he parroted the thing about having someone
to blame, I asked which would let him sleep better at night, having
someone to blame, or a system that didn't break?  I made it clear that
all he'd get was someone to blame, and nothing else.

At that time I'd tested several commercial components and found them
wanting compared to open source.  One component was LDAP, and at the
time, OpenLDAP was a solid, fast, lightweight choice, while everything
else was a hog.  The recommended memory for a linux OpenLDAP server
was something like 256M, while the memory for the "big name" vendor
was something like 1 Gig minimum, with recommendations for 2Gig or
more.

I think he repeated what I'd said to him to upper management, and we
went ahead and installed OpenLDAP, and it was still running complaint
free the day I left years later.

Oh, as I was writing a CUBE query today and realized that I forgot to
mention this. And unlike most gripes, like MERGE INTO or CTE's which are
  really convenience things, this is key piece of functionality that you
just can't reproduce in Postgres.

That said, there's not the same sense of community when it comes to
Oracle. And how many of you have ever asked a question and had it
answered by the Oracle equivalent of Tom Lane?

>
> Oh, as I was writing a CUBE query today and realized that I forgot to
> mention this. And unlike most gripes, like MERGE INTO or CTE's which
are
>   really convenience things, this is key piece of functionality that
you
> just can't reproduce in Postgres.
>
> That said, there's not the same sense of community when it comes to
> Oracle. And how many of you have ever asked a question and had it
> answered by the Oracle equivalent of Tom Lane?

I have.  http://asktom.oracle.com   But I've had better luck with Tom.
:)



Jon

* richard.broersma@gmail.com ("Richard Broersma") wrote:
>
> On Sun, Aug 31, 2008 at 1:50 PM, Kevin Hunter <hunteke@earlham.edu> wrote:
>
> > 7. Though I don't personally buy it, I have heard others complain
> >   loudly that there is no print-version of Postgres documentation.
>
>
> This one should be taken off the list.  The postgresql online
> reference manual is in print( volumes 1 - 3)
> http://www.amazon.com/PostgreSQL-Reference-Manual-SQL-Language/dp/0954612027/ref=pd_sim_b_1

Though I mostly use the online version, I'm considering buying these.
However, I'm probably going to kill myself if I find a new edition for
sale only a short while after I've bought them. Are there any plans to
release an updated set in the near future?

--
Lars Haugseth

"If anyone disagrees with anything I say, I am quite prepared not only to
 retract it, but also to deny under oath that I ever said it." -Tom Lehrer

On Wednesday 03 September 2008 09:17:54 Asko Oja wrote:
> On Wed, Sep 3, 2008 at 5:56 AM, Robert Treat
>
> <xzilla@users.sourceforge.net>wrote:
> > On Tuesday 02 September 2008 17:21:12 Asko Oja wrote:
> > > On Tue, Sep 2, 2008 at 2:09 AM, Michael Nolan <htfoot@gmail.com> wrote:
> > > > Oracle handles connecting to multiple databases (even on
> >
> > multiple/remote
> >
> > > > computers) fairly seamlessly, PG does not (yet.)
> > >
> > > Stuff we do with plProxy on PostgreSQL is in some respects more
> > > advanced than anything Oracle has to offer :) We have hundreds of
> > > databases in
> >
> > quite
> >
> > > complex network of remote calls and replication.
> >
> > Yes, but it is also far more complex to install, configure, and use,
> > compared
> > to something simple like oracle's dblink, which comes pre-installed, is
> > simple to set-up, and has a much more straight-forward syntax for use in
> > day
> > to day query work.
>
> We are working on these matters and hopefully get some of them solved in
> 8.4
>
> :)
>
> Configure and use part is NO more complex than Oracle and has several use
> cases for which neither of dblinks is suitable.
> Or are you claiming that calling functions is not straight forward and
> seamless in PostgreSQL.

It is not as simple as Oracles database link syntax. Setting up a connection
involves a couple of sql looking commands, and once you setup a connection to
a remote database, you can reference a table with something like select *
from mytable@myotherdb.  There's no way a function oriented solution can
match that imho. (BTW, if you want to see more, Lewis has a pretty good write
up;
http://it.toolbox.com/blogs/oracle-guide/database-links-a-definition-in-plain-english-7023)

--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

I'm running all 8.3. But I don't think it makes a difference. There is
some geometry type cube function but its not at all like the OLAP cube
that I'm talking about.

> What version of Postgres are you running?
>
> On Wed, Sep 3, 2008 at 10:21 PM, Artacus <artacus@comcast.net
> <mailto:artacus@comcast.net>> wrote:
>
>     Oh, as I was writing a CUBE query today and realized that I forgot
>     to mention this. And unlike most gripes, like MERGE INTO or CTE's
>     which are  really convenience things, this is key piece of
>     functionality that you just can't reproduce in Postgres.
>
>     That said, there's not the same sense of community when it comes to
>     Oracle. And how many of you have ever asked a question and had it
>     answered by the Oracle equivalent of Tom Lane?
>
>     --
>     Sent via pgsql-general mailing list (pgsql-general@postgresql.org
>     <mailto:pgsql-general@postgresql.org>)
>     To make changes to your subscription:
>     http://www.postgresql.org/mailpref/pgsql-general
>
>

So that is for real huh? I've been to that web site before and figured
it was more marketing talk about what they wanted to do rather than a
product that was already to market.


> 2008/9/4 Artacus <artacus@comcast.net>:
>> Oh, as I was writing a CUBE query today and realized that I forgot to
>> mention this. And unlike most gripes, like MERGE INTO or CTE's which are
>>  really convenience things, this is key piece of functionality that you just
>> can't reproduce in Postgres.
>>
>
> It is not true. First look at this:
> http://www.analyticsql.org/
>
> In atachment is two snapshots from production system (Analytic SQL
> Server based on PostgreSQL 8.3)

On Thu, 4 Sep 2008, Lars Haugseth wrote:

>> http://www.amazon.com/PostgreSQL-Reference-Manual-SQL-Language/dp/0954612027/ref=pd_sim_b_1
>
> Though I mostly use the online version, I'm considering buying these.
> However, I'm probably going to kill myself if I find a new edition for
> sale only a short while after I've bought them.

They're already one rev off from current and will soon be two.  Bruce says
he was able to print a one-off for the whole thing for around the same
price as that set, and you could print the 8.3 docs instead:
http://momjian.us/main/blogs/pgblog.html#January_10_2008_4

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

Kevin Hunter wrote:
> 1. Oracle was "first", and has vendor lock-in momentum.
> 2. Oracle ...speed/performance/concurrency...
> 3. Oracle has application lock-in as well.  ...
> 4. Oracle is company-backed, so there is ostensibly "someone to blame"..
> 5. ... individuals ... may prefer it *because* it's expensive...
> 6. Mucho better advertising to the right people....
> 7. ...print-version...

8. Oracle salespeople will help B2B software companies help sell
and bid on larger oracle-based products and projects.

2008/9/9 0123 zyxw <0123zyxw@gmail.com>:
> Kevin Hunter wrote:
>>
>> 1. Oracle was "first", and has vendor lock-in momentum.
>> 2. Oracle ...speed/performance/concurrency...
>> 3. Oracle has application lock-in as well.  ...
>> 4. Oracle is company-backed, so there is ostensibly "someone to blame"..
>> 5. ... individuals ... may prefer it *because* it's expensive...
>> 6. Mucho better advertising to the right people....
>> 7. ...print-version...
>
> 8. Oracle salespeople will help B2B software companies help sell
> and bid on larger oracle-based products and projects.
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

1. Maybe ...
2. Of course, like others DBMS, but how many people knows how it's
configure? Are you configure ORACLE DBMS? 1000+ strange parameters and
don't tell me that they are well document. The same with Oracle Apps.
3. Are you kidding? Are you work with Oracle AS, ifs, cmsdk etc? I've
never seen so "good", "fast" and "scalable" software.
4. Software "AS IS" - no comment.
5. ? Lets look:
with Oracle:
App = Oracle licences + yours work = X (and pay for everything - help,
support etc.),
with PostgreSQL:
App = 0 for licences + work = Y,
if X=Y who has more profits?

6. Agree :-((( Oracle marketing is realy the best.
7. ?
8. As I say in 5. - pay for everything - if you are money everybady
helps you (even I, and if you pay 1.000.000 $ I say Oracle is the
best, like other Oracle experts).

Regards,
Blazej

> -----Original Message-----
> From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-
> owner@postgresql.org] On Behalf Of Blazej
> Sent: Saturday, September 13, 2008 9:24 PM
> To: 0123 zyxw
> Cc: Kevin Hunter; mailtoyahoo@gmail.com; Postgres General List; Ron
Mayer
> Subject: Re: [GENERAL] Oracle and Postgresql
>
> 2008/9/9 0123 zyxw <0123zyxw@gmail.com>:
> > Kevin Hunter wrote:
> >>
> >> 1. Oracle was "first", and has vendor lock-in momentum.
> >> 2. Oracle ...speed/performance/concurrency...
> >> 3. Oracle has application lock-in as well.  ...
> >> 4. Oracle is company-backed, so there is ostensibly "someone to
> blame"..
> >> 5. ... individuals ... may prefer it *because* it's expensive...
> >> 6. Mucho better advertising to the right people....
> >> 7. ...print-version...
> >
> > 8. Oracle salespeople will help B2B software companies help sell
> > and bid on larger oracle-based products and projects.
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general
> >
>
> 1. Maybe ...
> 2. Of course, like others DBMS, but how many people knows how it's
> configure? Are you configure ORACLE DBMS? 1000+ strange parameters and
> don't tell me that they are well document. The same with Oracle Apps.
> 3. Are you kidding? Are you work with Oracle AS, ifs, cmsdk etc? I've
> never seen so "good", "fast" and "scalable" software.
> 4. Software "AS IS" - no comment.
> 5. ? Lets look:
> with Oracle:
> App = Oracle licences + yours work = X (and pay for everything - help,
> support etc.),
> with PostgreSQL:
> App = 0 for licences + work = Y,
> if X=Y who has more profits?
>
> 6. Agree :-((( Oracle marketing is realy the best.
> 7. ?
> 8. As I say in 5. - pay for everything - if you are money everybady
> helps you (even I, and if you pay 1.000.000 $ I say Oracle is the
> best, like other Oracle experts).
>
> Regards,
> Blazej
>

My top 10 reasons why companies pick Oracle.
1.  24x7 Support
2.  Security: Patches, Row Level Security, Roles, encryption, protection
of database code, etc.
3.  Software indemnification (which is open source's biggest problem)
4.  Scalability of shared disk (Oracle RAC)
5.  Works extremely well from anything from hosting LDAP to OLTP
applications to data warehouses with ROLAP and MOLAP
6.  Best, oldest, and most proven concurrency model for any commercial
database product
7.  Runs great on various platforms not just Linux or just Windows
8.  Recruiting a senior level Oracle professional with over 10 years of
experience is not very difficult
9.  Deep, deep discounts.  I've never seen any company pay list price
for Oracle products.  It has always been at least 50% off if not more.
10.  Sales employees that will do anything to retain or grow your
business.

The initial price of the product factors into the equation for big
companies but when you look at all the value add of Oracle, it is very
tempting.

Jon

On Mon, Sep 15, 2008 at 08:25:53AM -0500, Roberts, Jon wrote:
> My top 10 reasons why companies pick Oracle.

Do you mean they actually get these things, or they imagine they do?

There certainly are a lot of false perceptions out in the world about
Oracle, and about proprietary software in general.

> 1.  24x7 Support

At several different places over the years, I've seen their top-tier
support simply not respond.

> 2.  Security: Patches,

When they get good and ready.  There are outstanding security issues
in Oracle that have been there for years.

> Row Level Security,

I think you mean access control.  Access control has so little overlap
with security that they really need to be discussed as separate
subjects.

> Roles,

We have 'em.

> encryption,

We have it.

> protection of database code, etc.

Are you saying that the fact that the source isn't legally available
to the population at large is a feature?  If you are, it's an argument
for security by obscurity, a system with a lot of deep known flaws.

> 3.  Software indemnification (which is open source's biggest
> problem)

Are you kidding?!?  Read the EULA for Oracle or any other proprietary
software package and then read the BSD license.  They both indemnify
about the same thing, i.e. nothing.  If you have any examples in case
law that show otherwise, they'd be a great thing to bring forth.

> 4.  Scalability of shared disk (Oracle RAC)

RAC doesn't scale outside Oracle's sales literature, as far as I've
seen.

> 5.  Works extremely well from anything from hosting LDAP to OLTP
> applications to data warehouses with ROLAP and MOLAP

Now we're vaguely getting somewhere within shouting range of reality.
While the first half of that is hotly debatable, they've got decent
*OLAP.

> 6.  Best, oldest, and most proven concurrency model for any
> commercial database product

It's none of those things.

> 7.  Runs great on various platforms not just Linux or just Windows

Is this different from some other RDBMS(s) out there, and if so, which
one(s)?  The only "just Windows" RDBMS I've ever heard of is MS SQL
Server, and I know of no "just Linux" ones.

> 8.  Recruiting a senior level Oracle professional with over 10 years
> of experience is not very difficult

Finding somebody with 10 years' experience is no problem.  Finding
somebody half-way competent is a different story.

> 9.  Deep, deep discounts.  I've never seen any company pay list
> price for Oracle products.  It has always been at least 50% off if
> not more.

50% off a price that's bloated by 1000% or more isn't much of a
muchness.

> 10.  Sales employees that will do anything to retain or grow your
> business.

That's just great if you prefer hookers and blow to a working RDBMS.

> The initial price of the product factors into the equation for big
> companies but when you look at all the value add of Oracle, it is
> very tempting.

Their sales and marketing people have certainly done an excellent job
creating the perceptions above, among others, and spreading them
around the industry.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Hi all.
I'm much interested in this discussion, as these points (kind of) arise every
while when discussing with customers.

I think that real world facts, not just opinions from either part would be
better.
Some answers from David and Jon make sense to me, the other ones smell more
like flames.

Il Monday 15 September 2008 15:58:47 David Fetter ha scritto:
> On Mon, Sep 15, 2008 at 08:25:53AM -0500, Roberts, Jon wrote:
> > My top 10 reasons why companies pick Oracle.
>
> Do you mean they actually get these things, or they imagine they do?
>
> There certainly are a lot of false perceptions out in the world about
> Oracle, and about proprietary software in general.
>
> > 1.  24x7 Support
>
> At several different places over the years, I've seen their top-tier
> support simply not respond.
>
> > 2.  Security: Patches,
>
> When they get good and ready.  There are outstanding security issues
> in Oracle that have been there for years.
>
> > Row Level Security,
>
> I think you mean access control.  Access control has so little overlap
> with security that they really need to be discussed as separate
> subjects.
>
> > Roles,
>
> We have 'em.
>
> > encryption,
>
> We have it.
>
> > protection of database code, etc.
>
> Are you saying that the fact that the source isn't legally available
> to the population at large is a feature?  If you are, it's an argument
> for security by obscurity, a system with a lot of deep known flaws.
>
> > 3.  Software indemnification (which is open source's biggest
> > problem)
>
> Are you kidding?!?  Read the EULA for Oracle or any other proprietary
> software package and then read the BSD license.  They both indemnify
> about the same thing, i.e. nothing.  If you have any examples in case
> law that show otherwise, they'd be a great thing to bring forth.
>
> > 4.  Scalability of shared disk (Oracle RAC)
>
> RAC doesn't scale outside Oracle's sales literature, as far as I've
> seen.
>
> > 5.  Works extremely well from anything from hosting LDAP to OLTP
> > applications to data warehouses with ROLAP and MOLAP
>
> Now we're vaguely getting somewhere within shouting range of reality.
> While the first half of that is hotly debatable, they've got decent
> *OLAP.
>
> > 6.  Best, oldest, and most proven concurrency model for any
> > commercial database product
>
> It's none of those things.
>
> > 7.  Runs great on various platforms not just Linux or just Windows
>
> Is this different from some other RDBMS(s) out there, and if so, which
> one(s)?  The only "just Windows" RDBMS I've ever heard of is MS SQL
> Server, and I know of no "just Linux" ones.
>
> > 8.  Recruiting a senior level Oracle professional with over 10 years
> > of experience is not very difficult
>
> Finding somebody with 10 years' experience is no problem.  Finding
> somebody half-way competent is a different story.
>
> > 9.  Deep, deep discounts.  I've never seen any company pay list
> > price for Oracle products.  It has always been at least 50% off if
> > not more.
>
> 50% off a price that's bloated by 1000% or more isn't much of a
> muchness.
>
> > 10.  Sales employees that will do anything to retain or grow your
> > business.
>
> That's just great if you prefer hookers and blow to a working RDBMS.
>
> > The initial price of the product factors into the equation for big
> > companies but when you look at all the value add of Oracle, it is
> > very tempting.
>
> Their sales and marketing people have certainly done an excellent job
> creating the perceptions above, among others, and spreading them
> around the industry.
>
> Cheers,
> David.
> --
> David Fetter <david@fetter.org> http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david.fetter@gmail.com
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate

Roberts, Jon wrote:
> > My top 10 reasons why companies pick Oracle.
>
> Do you mean they actually get these things, or they imagine they do?
>
Huh?  Companies buy Oracle all of the time.

> There certainly are a lot of false perceptions out in the world about
> Oracle, and about proprietary software in general.
>
> > 1.  24x7 Support
>
> At several different places over the years, I've seen their top-tier
> support simply not respond.
>
That is highly unusual.  I've always had excellent experience with
Oracle's support especially their top tier support.

> > 2.  Security: Patches,
>
> When they get good and ready.  There are outstanding security issues
> in Oracle that have been there for years.
>

I'm not saying Oracle is more or less secure.  I think people feel
better about security from a company like Oracle rather than a bunch of
hackers on an email list.  It is perceived as more secure by many,
especially large companies.

> > Row Level Security,
>
> I think you mean access control.  Access control has so little overlap
> with security that they really need to be discussed as separate
> subjects.
>
> > Roles,
>
> We have 'em.
>
> > encryption,
>
> We have it.
>
PG can't encrypt code.


> > protection of database code, etc.
>
> Are you saying that the fact that the source isn't legally available
> to the population at large is a feature?  If you are, it's an argument
> for security by obscurity, a system with a lot of deep known flaws.
>
No, I'm saying that if I create a function in PG, ANYONE with access to
the database can see my code.  That is not secure.  It is a security
hole for the database.


> > 3.  Software indemnification (which is open source's biggest
> > problem)
>
> Are you kidding?!?  Read the EULA for Oracle or any other proprietary
> software package and then read the BSD license.  They both indemnify
> about the same thing, i.e. nothing.  If you have any examples in case
> law that show otherwise, they'd be a great thing to bring forth.

The contracts you sign when you buy Oracle indemnify you from lawsuits.
For instance, Oracle bundles Apache with various products.  If someone
sues a company for using Oracle HTTP Server because it uses Apache and
Apache was allegedly illegally contributed to by a rogue employee, then
Oracle protects their customers.

After SCO went after companies using Linux, it is a concern of large
companies and worth the extra cost of paying Oracle for Apache rather
than using Apache all by itself.

>
> > 4.  Scalability of shared disk (Oracle RAC)
>
> RAC doesn't scale outside Oracle's sales literature, as far as I've
> seen.

I have.
>
> > 5.  Works extremely well from anything from hosting LDAP to OLTP
> > applications to data warehouses with ROLAP and MOLAP
>
> Now we're vaguely getting somewhere within shouting range of reality.
> While the first half of that is hotly debatable, they've got decent
> *OLAP.
>
Oracle Internet Directory is LDAP compliant and stored in an Oracle
database.  They have rollback which provides similar MVCC functionality
of PG.  It has also been around longer than PG MVCC.


> > 6.  Best, oldest, and most proven concurrency model for any
> > commercial database product
>
> It's none of those things.
>
Which commercial database is better?  MS SQL Server, Sybase, DB2, what?


> > 7.  Runs great on various platforms not just Linux or just Windows
>
> Is this different from some other RDBMS(s) out there, and if so, which
> one(s)?  The only "just Windows" RDBMS I've ever heard of is MS SQL
> Server, and I know of no "just Linux" ones.
>
PG doesn't scale well on Windows.  DB2 seems to work best on a
mainframe.  Sybase works best on Unix.  MS SQL Server only runs on
Windows.

> > 8.  Recruiting a senior level Oracle professional with over 10 years
> > of experience is not very difficult
>
> Finding somebody with 10 years' experience is no problem.  Finding
> somebody half-way competent is a different story.
>
> > 9.  Deep, deep discounts.  I've never seen any company pay list
> > price for Oracle products.  It has always been at least 50% off if
> > not more.
>
> 50% off a price that's bloated by 1000% or more isn't much of a
> muchness.
>
It is only the perception of a good deal.  So what?

> > 10.  Sales employees that will do anything to retain or grow your
> > business.
>
> That's just great if you prefer hookers and blow to a working RDBMS.
>
LOL.  That is pretty funny.  I'm talking about doing free work like a
proof of concepts, demonstrations of products, etc.  I've even seen
technical sales guys help out onsite for free for performance tuning an
application.

> > The initial price of the product factors into the equation for big
> > companies but when you look at all the value add of Oracle, it is
> > very tempting.
>
> Their sales and marketing people have certainly done an excellent job
> creating the perceptions above, among others, and spreading them
> around the industry.

Perception = sales.


Jon

On Mon, Sep 15, 2008 at 8:31 AM, Roberts, Jon <Jon.Roberts@asurion.com> wrote:
>
> Roberts, Jon wrote:
>> > My top 10 reasons why companies pick Oracle.
>>
>> Do you mean they actually get these things, or they imagine they do?
>>
> Huh?  Companies buy Oracle all of the time.

I think he meant the features, not the db.

>> > 2.  Security: Patches,
>>
>> When they get good and ready.  There are outstanding security issues
>> in Oracle that have been there for years.
>>
>
> I'm not saying Oracle is more or less secure.  I think people feel
> better about security from a company like Oracle rather than a bunch of
> hackers on an email list.  It is perceived as more secure by many,
> especially large companies.

Oracle has had security issues that they've sat on for years in the
past.  years.  Google for more info.  I have never seen the "hackers
on an email list" who make pgsql do that.  You find a security hole,
they patch it.

>> > encryption,
>>
>> We have it.
>>
> PG can't encrypt code.

PG can compile to C.

I'm willing to bet any halfway decent hacker could get the plsql code
out of an encrypted oracle procedure.

>> Are you saying that the fact that the source isn't legally available
>> to the population at large is a feature?  If you are, it's an argument
>> for security by obscurity, a system with a lot of deep known flaws.
>>
> No, I'm saying that if I create a function in PG, ANYONE with access to
> the database can see my code.  That is not secure.  It is a security
> hole for the database.

It is NOT a security hole.  Seeing the code and running the code are
two entirely different things.

>> > 3.  Software indemnification (which is open source's biggest
>> > problem)
>>
>> Are you kidding?!?  Read the EULA for Oracle or any other proprietary
>> software package and then read the BSD license.  They both indemnify
>> about the same thing, i.e. nothing.  If you have any examples in case
>> law that show otherwise, they'd be a great thing to bring forth.
>
> The contracts you sign when you buy Oracle indemnify you from lawsuits.
> For instance, Oracle bundles Apache with various products.  If someone
> sues a company for using Oracle HTTP Server because it uses Apache and
> Apache was allegedly illegally contributed to by a rogue employee, then
> Oracle protects their customers.
>
> After SCO went after companies using Linux, it is a concern of large
> companies and worth the extra cost of paying Oracle for Apache rather
> than using Apache all by itself.

Any PHB who was scared of SCO's arm waving legal arguments deserves to
be run out of town on a rail.  If SCO is the sum total of your
argument for software indemnifictation, you really don't have an
argument, except in the most abstract sense, that somewhere some PHB
is worried about a non-issue.

>> > 4.  Scalability of shared disk (Oracle RAC)
>>
>> RAC doesn't scale outside Oracle's sales literature, as far as I've
>> seen.
>
> I have.

Sadly, you can't publish any numbers to prove it.  :)

Seriously, very few instances is RAC any faster than just throwing
more hardware in a single image at the problem.  Given the cost of the
sysadmins, licenses, crazy expensive SAN hardware, and extra
machinery, you're better off just buying a monstrously huge SUN with
100 disk RAID-10 array under it.

>> > 6.  Best, oldest, and most proven concurrency model for any
>> > commercial database product
>>
>> It's none of those things.
>>
> Which commercial database is better?  MS SQL Server, Sybase, DB2, what?

Every db has advantages and disadvantages in it's concurrency handling
ability.  I'm willing to bet that to this TPF will outperform oracle.
But I don't want to run either of those.

OTOH, I have set up benchmarks that ran for weeks to burn in pgsql on
decent sized machines that hadd 1,000 concurrent accesses, and this
slowed queries down to 1 to 5 seconds instead of the usual sub
millisecond performance.

I'd like to say I've tested or seen others test Oracle like this and
it ran well, but whether or not I have, I am forbidden to tell you by
Oracle's licensing scheme.

>> > 7.  Runs great on various platforms not just Linux or just Windows
>>
>> Is this different from some other RDBMS(s) out there, and if so, which
>> one(s)?  The only "just Windows" RDBMS I've ever heard of is MS SQL
>> Server, and I know of no "just Linux" ones.
>>
> PG doesn't scale well on Windows.  DB2 seems to work best on a
> mainframe.  Sybase works best on Unix.  MS SQL Server only runs on
> Windows.

Oh no!  That means we can only run pgsql on various flavours of unix
with good performance.  That only gives us Linux, BSD(s), Solaris,
AIX, HPUX and a few others.

Sure, Oracle runs fast on those too, but don't try to tell me oracle
runs as fast and scales as well on Windows.  That I know isn't true,
I've seen the performance myself a few times.

>> > 9.  Deep, deep discounts.  I've never seen any company pay list
>> > price for Oracle products.  It has always been at least 50% off if
>> > not more.
>>
>> 50% off a price that's bloated by 1000% or more isn't much of a
>> muchness.
>>
> It is only the perception of a good deal.  So what?

Really?  Do you light your cigars with 100 dollar bills or something?
I can't just throw $250k at a problem at work without justification.
The fact that 250k is half the retail price of $500k doesn't really
fly with my boss, as much as he loves a deal.

>> > 10.  Sales employees that will do anything to retain or grow your
>> > business.
>>
>> That's just great if you prefer hookers and blow to a working RDBMS.
>>
> LOL.  That is pretty funny.  I'm talking about doing free work like a
> proof of concepts, demonstrations of products, etc.  I've even seen
> technical sales guys help out onsite for free for performance tuning an
> application.

Well, I never got that kind of treatment at my last company.  Oracle
was quite willing to renegotiate pricing (i.e. 500k to 250k woohoo!)
but never once stepped foot in our office, and we were having massive
problems getting RAC working.

>> > The initial price of the product factors into the equation for big
>> > companies but when you look at all the value add of Oracle, it is
>> > very tempting.
>>
>> Their sales and marketing people have certainly done an excellent job
>> creating the perceptions above, among others, and spreading them
>> around the industry.
>
> Perception = sales.

Yes.  If the person doing the buying is easily entertained by small
shiny objects, yes, that's true.

Harald wrote:

>> protection of database code, etc.
> Are you saying that the fact that the source isn't legally available
> to the population at large is a feature?  If you are, it's an argument
> for security by obscurity, a system with a lot of deep known flaws.

> That would be more the "Oracle stored procedures can be encrypted." Which
is an argument for ISVs, as they can easier force their customers to pay >
"software maintainance".

> With open code, clients could buy support from others, who may be cheaper
or, even more dangerous, more qualified. 

> As long as the disease of "Intellectual property" is running around, that
"I can encrypt my code" will provide some felt benefit for PHBs....

I have to disagree here. Encrypting stored procedures is not just about
forcing customers to pay software maintenance.

For us, it's about *protecting hard work and intellectual property*. For
example (very recent), we designed a fairly complicated inventory system
using PL/pgSQL for a web platform. This customer is now looking into using
Microsoft Dynamics CRM. This customer is looking for quotes from other
companies to integrate their web platform (pgsql) with the CRM.

What now prevents these other companies from stealing our inventory system
and putting it into MS SQL server? And better yet, using the inventory
system and selling it to other customers? Luckily copyright offers
protection, but as we all know some companies always tend to stretch the
rules as far as they can.

For me, "Oracle stored procedures can be encrypted." is a very real and
valuable argument.
It would certainly be a valuable feature in pgsql (in the enterprise space).

Jonathan Bond-Caron escribió:

> For me, "Oracle stored procedures can be encrypted." is a very real and
> valuable argument.
> It would certainly be a valuable feature in pgsql (in the enterprise space).

Fortunately for you, PostgreSQL is extensible, even in the procedural
languages area.  This means that if you want to create a new PL that
takes the encrypted code, decrypts it, and passes it to plpgsql, you can
do it.  It shouldn't be very difficult.  There was some talk about doing
this as a pgfoundry project, but no one stepped up to actually doing the
work.

Of course, as pointed out by Scott Marlowe, any half-decent hacker will
be able to get the actual code out just like he would on encrypted
PL/SQL, but the barrier for stealing the code got considerably higher at
that point.

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

On Mon, Sep 15, 2008 at 9:35 AM, Jonathan Bond-Caron <jbondc@openmv.com> wrote:
> For me, "Oracle stored procedures can be encrypted." is a very real and
> valuable argument.
> It would certainly be a valuable feature in pgsql (in the enterprise space).

I don't see how that's any more effective than writing your stored
procs in C in postgresql.

In response to "Scott Marlowe" <scott.marlowe@gmail.com>:

> On Mon, Sep 15, 2008 at 9:35 AM, Jonathan Bond-Caron <jbondc@openmv.com> wrote:
> > For me, "Oracle stored procedures can be encrypted." is a very real and
> > valuable argument.
> > It would certainly be a valuable feature in pgsql (in the enterprise space).
>
> I don't see how that's any more effective than writing your stored
> procs in C in postgresql.

Perhaps the fact that the implementation time/effort for a C procedure
is something on the order of 10x that for a pl/pgsql procedure?

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

On Mon Sep 15 11:48 AM, Scott Marlowe wrote:
> On Mon, Sep 15, 2008 at 9:35 AM, Jonathan Bond-Caron
> <jbondc@openmv.com> wrote:
>> For me, "Oracle stored procedures can be encrypted." is a very real
>> and valuable argument.
>> It would certainly be a valuable feature in pgsql (in the enterprise
> space).
>
> I don't see how that's any more effective than writing your stored
> procs in C in postgresql.

I'll definitely look into that, but "PL that takes the encrypted code,
decrypts it, and passes it to plpgsql, you can do it"
at first sounds easier to maintain

thanks for the tips

> I'll definitely look into that, but "PL that takes the encrypted code,
> decrypts it, and passes it to plpgsql, you can do it"
> at first sounds easier to maintain

Out of curiosity (having never used them), how does Oracle handle the
key for the encrypted code?

"Jonathan Bond-Caron" <jbondc@openmv.com> writes:

> What now prevents these other companies from stealing our inventory system
> and putting it into MS SQL server? And better yet, using the inventory
> system and selling it to other customers? Luckily copyright offers
> protection, but as we all know some companies always tend to stretch the
> rules as far as they can.

Well one thing which won't prevent it is DRM which an open source database has
the key to decrypt... All someone has to do is look at how Postgres decrypts
it and do the same thing.

Normally what prevents it is the value you offer in support and expertise with
the system you wrote. Why would someone want to put your inventory system in
an SQL Server anyways? You're not going to support it and they would be able
to support their system much better if they wrote it themselves.

The reality is that the value in software comes from the people supporting it.
Being able to add features as needed and fix problems that occur. The actual
bits of code are pretty worthless, they're cheap to produce.

--
  Gregory Stark

On Mon, Sep 15, 2008 at 11:35:30AM -0400, Jonathan Bond-Caron wrote:
> Harald wrote:
>
> > > If you are,
> > > it's an argument for security by obscurity, a system with a lot of
> > > deep known flaws.
>
> > That would be more the "Oracle stored procedures can be encrypted."
> > Which is an argument for ISVs, as they can easier force their
> > customers to pay "software maintainance".
>
> > With open code, clients could buy support from others, who may be
> > cheaper or, even more dangerous, more qualified.
>
> > As long as the disease of "Intellectual property" is running around,
> > that "I can encrypt my code" will provide some felt benefit for
> > PHBs....
>
> I have to disagree here.  Encrypting stored procedures is not just
> about forcing customers to pay software maintenance.

Please to explain how you imagine this to be so.

> For us, it's about *protecting hard work and intellectual property*.

Yes, and of course nobody else does "hard work" either.  Don't you
mean you're "protecting" this stuff by forcing people to pay for it?

> For example (very recent), we designed a fairly complicated
> inventory system using PL/pgSQL for a web platform.  This customer
> is now looking into using Microsoft Dynamics CRM.  This customer is
> looking for quotes from other companies to integrate their web
> platform (pgsql) with the CRM.

What of it?

> What now prevents these other companies from stealing our inventory
> system and putting it into MS SQL server?

What would motivate them to port the code, document same, develop
in-house expertise, etc., etc.?  You've placed the burden of proof on
the wrong side here.

> And better yet, using the inventory system and selling it to other
> customers?  Luckily copyright offers protection, but as we all know
> some companies always tend to stretch the rules as far as they can.

Copyright and contract law are precisely where you're going to be
fighting these battles anyhow, and that's why if you insist on this
kind of stuff, you hire attorneys with a good track record instead of
taking the fundamentally broken approach of obfuscating your code.

> For me, "Oracle stored procedures can be encrypted." is a very real
> and valuable argument.

It speaks to your judgement, and not favorably.

> It would certainly be a valuable feature in pgsql (in the enterprise
> space).

If by "enterprise" you mean "bloated and buggy," I quite agree.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Mon, 15 Sep 2008 10:06:08 -0700
David Fetter <david@fetter.org> wrote:

> > > As long as the disease of "Intellectual property" is running
> > > around, that "I can encrypt my code" will provide some felt
> > > benefit for PHBs....
> >
> > I have to disagree here.  Encrypting stored procedures is not just
> > about forcing customers to pay software maintenance.
>
> Please to explain how you imagine this to be so.
>
> > For us, it's about *protecting hard work and intellectual property*.
>
> Yes, and of course nobody else does "hard work" either.  Don't you
> mean you're "protecting" this stuff by forcing people to pay for it?

Are we going to start a VI vs Emacs argument too?

Joshua D. Drake
--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

accessing:
i *thought* the advantage of creating any SQL procedure/function was the entity is stored in procedure cache
load time:
Java vs C++ compare here
http://www.idiom.com/~zilla/Computer/javaCbenchmark.html

Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.


> Date: Mon, 15 Sep 2008 11:54:44 -0400
> From: wmoran@collaborativefusion.com
> To: scott.marlowe@gmail.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Oracle and Postgresql
>
> In response to "Scott Marlowe" <scott.marlowe@gmail.com>:
>
> > On Mon, Sep 15, 2008 at 9:35 AM, Jonathan Bond-Caron <jbondc@openmv.com> wrote:
> > > For me, "Oracle stored procedures can be encrypted." is a very real and
> > > valuable argument.
> > > It would certainly be a valuable feature in pgsql (in the enterprise space).
> >
> > I don't see how that's any more effective than writing your stored
> > procs in C in postgresql.
>
> Perhaps the fact that the implementation time/effort for a C procedure
> is something on the order of 10x that for a pl/pgsql procedure?
>
> --
> Bill Moran
> Collaborative Fusion Inc.
> http://people.collaborativefusion.com/~wmoran/
>
> wmoran@collaborativefusion.com
> Phone: 412-422-3463x4023
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

See how Windows Mobile brings your life together—at home, work, or on the go. See Now

On Mon, Sep 15, 2008 at 09:31:14AM -0500, Roberts, Jon wrote:
>
> Roberts, Jon wrote:
> > > My top 10 reasons why companies pick Oracle.
> >
> > Do you mean they actually get these things, or they imagine they do?
> >
> Huh?  Companies buy Oracle all of the time.

They don't get stuff promised in the "top 10 reasons" by any stretch
of the imagination.

> > There certainly are a lot of false perceptions out in the world
> > about Oracle, and about proprietary software in general.
> >
> > > 1.  24x7 Support
> >
> > At several different places over the years, I've seen their
> > top-tier support simply not respond.
> >
> That is highly unusual.  I've always had excellent experience with
> Oracle's support especially their top tier support.

Try calling them when you've hit one of their many un-fixed-for-years
bugs.

> > > 2.  Security: Patches,
> >
> > When they get good and ready.  There are outstanding security
> > issues in Oracle that have been there for years.
>
> I'm not saying Oracle is more or less secure.  I think people feel
> better about security from a company like Oracle rather than a bunch
> of hackers on an email list.

People may well feel that monkeys are going to fly out of Larry
Ellison's butt, but that does not make it true.

> It is perceived as more secure by many, especially large companies.

Large companies, as you put it, have a lousy track record on access
control, which is what you appear to mean by security.

> > > encryption,
> >
> > We have it.
> >
> PG can't encrypt code.

What makes you think this would improve access control or anything
related to security?  I'd submit that encrypting stored procedures
produces a false sense of security by giving people the impression
that they've done something when they have really not.

> > > protection of database code, etc.
> >
> > Are you saying that the fact that the source isn't legally
> > available to the population at large is a feature?  If you are,
> > it's an argument for security by obscurity, a system with a lot of
> > deep known flaws.
> >
> No, I'm saying that if I create a function in PG, ANYONE with access
> to the database can see my code.  That is not secure.  It is a
> security hole for the database.

This is precisely an argument for security by obscurity.  Feel free to
educate us all on why this suddenly became a good idea.

> > > 3.  Software indemnification (which is open source's biggest
> > > problem)
> >
> > Are you kidding?!?  Read the EULA for Oracle or any other
> > proprietary software package and then read the BSD license.  They
> > both indemnify about the same thing, i.e. nothing.  If you have
> > any examples in case law that show otherwise, they'd be a great
> > thing to bring forth.
>
> The contracts you sign when you buy Oracle indemnify you from
> lawsuits.

Feel free to cite one single case where this has actually worked.
Federal or state court, either one.

> > > 4.  Scalability of shared disk (Oracle RAC)
> >
> > RAC doesn't scale outside Oracle's sales literature, as far as
> > I've seen.
>
> I have.

We only have your word on that because Oracle's EULA expressly forbids
you from ever publishing such a thing.  Throwing more hardware at a
single-image system is much cheaper and more reliable in my
experience.

> > > 6.  Best, oldest, and most proven concurrency model for any
> > > commercial database product
> >
> > It's none of those things.
> >
> Which commercial database is better?  MS SQL Server, Sybase, DB2,
> what?

Depends what you're doing.  DB2 consistently beats Oracle for giant
transaction processing loads.  MS-SQL Server has way better end-user
OLAP tools.

> > > 7.  Runs great on various platforms not just Linux or just
> > > Windows
> >
> > Is this different from some other RDBMS(s) out there, and if so,
> > which one(s)?  The only "just Windows" RDBMS I've ever heard of is
> > MS SQL Server, and I know of no "just Linux" ones.
> >
> PG doesn't scale well on Windows.  DB2 seems to work best on a
> mainframe.  Sybase works best on Unix.  MS SQL Server only runs on
> Windows.

This is pretty specious.  *Nothing* scales well on Windows, and when
you get into RDBMSs that run on actual server OSs, they all do a
pretty creditable job.

> > > 8.  Recruiting a senior level Oracle professional with over 10
> > > years of experience is not very difficult
> >
> > Finding somebody with 10 years' experience is no problem.  Finding
> > somebody half-way competent is a different story.

*crickets*

> > > 9.  Deep, deep discounts.  I've never seen any company pay list
> > > price for Oracle products.  It has always been at least 50% off
> > > if not more.
> >
> > 50% off a price that's bloated by 1000% or more isn't much of a
> > muchness.
> >
> It is only the perception of a good deal.  So what?

For 90% off of your next 5 years of Oracle licenses and HR, I'll get
you converted to PostgreSQL.  The difference is that I can actually do
that, and you'll actually save money.

> > > 10.  Sales employees that will do anything to retain or grow
> > > your business.
> >
> > That's just great if you prefer hookers and blow to a working
> > RDBMS.
> >
> LOL.  That is pretty funny.  I'm talking about doing free work like
> a proof of concepts, demonstrations of products, etc.  I've even
> seen technical sales guys help out onsite for free for performance
> tuning an application.

You're being astoundingly naïf if you imagine that the hookers and
blow thing is a joke.

> > > The initial price of the product factors into the equation for
> > > big companies but when you look at all the value add of Oracle,
> > > it is very tempting.
> >
> > Their sales and marketing people have certainly done an excellent
> > job creating the perceptions above, among others, and spreading
> > them around the industry.
>
> Perception = sales.

So basically lying is a legitimate tactic.  This tells a lot about
you.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Mon, Sep 15, 2008 at 11:12 AM, Joshua Drake <jd@commandprompt.com> wrote:
> Are we going to start a VI vs Emacs argument too?

You insensitive clod!  I use nano!!1eleventy!1

On Mon, 15 Sep 2008 11:35:48 -0600
"Scott Marlowe" <scott.marlowe@gmail.com> wrote:

> On Mon, Sep 15, 2008 at 11:12 AM, Joshua Drake <jd@commandprompt.com>
> wrote:
> > Are we going to start a VI vs Emacs argument too?
>
> You insensitive clod!  I use nano!!1eleventy!1
>

I see your nano and raise you a joe .

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Hi People,

What an "interesting" turn this has taken ...

I _was_ following this thread with some interest where very good points
have been stated (with all the classics being addressed too! :) )

Regards,

--
Pedro Doria Meunier <pdoria@netmadeira.com>

On Mon, 2008-09-15 at 10:42 -0700, Joshua Drake wrote:
> On Mon, 15 Sep 2008 11:35:48 -0600
> "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
>
> > On Mon, Sep 15, 2008 at 11:12 AM, Joshua Drake <jd@commandprompt.com>
> > wrote:
> > > Are we going to start a VI vs Emacs argument too?
> >
> > You insensitive clod!  I use nano!!1eleventy!1
> >
>
> I see your nano and raise you a joe .
>
> Joshua D. Drake
>
> --
> The PostgreSQL Company since 1997: http://www.commandprompt.com/
> PostgreSQL Community Conference: http://www.postgresqlconference.org/
> United States PostgreSQL Association: http://www.postgresql.us/
> Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
>
>
>

On Mon, September 15, 2008 10:31 am, Roberts, Jon wrote:

>>> 3.  Software indemnification (which is open source's biggest problem)
>>>
>>
>> Are you kidding?!?  Read the EULA for Oracle or any other proprietary
>> software package and then read the BSD license.  They both indemnify
>> about the same thing, i.e. nothing.  If you have any examples in case
>> law that show otherwise, they'd be a great thing to bring forth.
>
> The contracts you sign when you buy Oracle indemnify you from lawsuits.
> For instance, Oracle bundles Apache with various products.  If someone
> sues a company for using Oracle HTTP Server because it uses Apache and
> Apache was allegedly illegally contributed to by a rogue employee, then
> Oracle protects their customers.
>
> After SCO went after companies using Linux, it is a concern of large
> companies and worth the extra cost of paying Oracle for Apache rather
> than using Apache all by itself.

We all know how well that went...

>>> 9.  Deep, deep discounts.  I've never seen any company pay list price
>>> for Oracle products.  It has always been at least 50% off if not
>>> more.
>>
>> 50% off a price that's bloated by 1000% or more isn't much of a
>> muchness.
>>
> It is only the perception of a good deal.  So what?

If you're comfortable wasting your company's money, then yes. "So what"
applies.

>>> 10.  Sales employees that will do anything to retain or grow your
>>> business.
>>
>> That's just great if you prefer hookers and blow to a working RDBMS.
>>
> LOL.  That is pretty funny.  I'm talking about doing free work like a
> proof of concepts, demonstrations of products, etc.  I've even seen
> technical sales guys help out onsite for free for performance tuning an
> application.

Nothing is free with Oracle. They get your money eventually. This
procedure = advertising expenses.

>>> The initial price of the product factors into the equation for big
>>> companies but when you look at all the value add of Oracle, it is
>>> very tempting.
>>
>> Their sales and marketing people have certainly done an excellent job
>> creating the perceptions above, among others, and spreading them around
>> the industry.
>
> Perception = sales.

Ignorance = sales as well.

On Mon, September 15, 2008 1:42 pm, Joshua Drake wrote:
> On Mon, 15 Sep 2008 11:35:48 -0600
> "Scott Marlowe" <scott.marlowe@gmail.com> wrote:
>
>> On Mon, Sep 15, 2008 at 11:12 AM, Joshua Drake <jd@commandprompt.com>
>> wrote:
>> > Are we going to start a VI vs Emacs argument too?
>>
>> You insensitive clod!  I use nano!!1eleventy!1
>>
>
> I see your nano and raise you a joe .

*grabs popcorn*

This should end well.

TECO FTW!!!

>
> On Mon, Sep 15, 2008 at 11:35:30AM -0400, Jonathan Bond-Caron wrote:
> > Harald wrote:
> >
> > > > If you are,
> > > > it's an argument for security by obscurity, a system with a lot
of
> > > > deep known flaws.
> >
> > > That would be more the "Oracle stored procedures can be
encrypted."
> > > Which is an argument for ISVs, as they can easier force their
> > > customers to pay "software maintainance".
> >
> > > With open code, clients could buy support from others, who may be
> > > cheaper or, even more dangerous, more qualified.
> >
> > > As long as the disease of "Intellectual property" is running
around,
> > > that "I can encrypt my code" will provide some felt benefit for
> > > PHBs....
> >
> > I have to disagree here.  Encrypting stored procedures is not just
> > about forcing customers to pay software maintenance.
>
> Please to explain how you imagine this to be so.
>

It could be intra-company as well.  For instance, a Finance group doing
forecasting in PG wants to prevent others from looking calculations.  Or
an M&A group models different scenarios and doesn't want to share this
code ever with people outside of their group.  Asking them to write C is
too much.

There are lots of scenarios where a group wants to protect their
PL/PGSQL code from others but wants to grant users access to the
database.  The current openness of PG code (code written by users, not
the source code of PG), is not ideal for these scenarios.

In Oracle, you must be granted the ability to see someone's PL/SQL code.
Secondly, you can "wrap" the code so no one can read it which protects
the intellectual property aspect.  I'm far less interested in protecting
intellectual property because I'm not in that business.  I am much more
interested in meeting business needs which means securing not only the
data in the database but also the code users write.


Jon

David Fetter wrote:

2.  Security: Patches,   

When they get good and ready.  There are outstanding security issues
in Oracle that have been there for years. 

Isn't that all software out there??

3.  Software indemnification (which is open source's biggest
problem)   

Are you kidding?!?  Read the EULA for Oracle or any other proprietary
software package and then read the BSD license.  They both indemnify
about the same thing, i.e. nothing.  If you have any examples in case
law that show otherwise, they'd be a great thing to bring forth. 

Can i have an AMEN
Being a manufacture that lives in liability. software companies license only say one thing
WE GUARANTEE NOT TO GUARANTEE the used car sale men of the world we all have to put up with.

8.  Recruiting a senior level Oracle professional with over 10 years
of experience is not very difficult    

Finding somebody with 10 years' experience is no problem.  Finding
somebody half-way competent is a different story.
 

That's one of the big problems with the Generation X & Yer's  the work ethic is crap and yes i am a Gen X

9.  Deep, deep discounts.  I've never seen any company pay list
price for Oracle products.  It has always been at least 50% off if
not more.   

50% off a price that's bloated by 1000% or more isn't much of a
muchness.
 

What is the value of what you buying.  Those discounts only make people feel better about the purchase. 
Just went through this with Kenametal, right off the bat got 25% discount on a $30,000 plus order.  If we purchase more it could have gotten 45% discount
So what is the value of item. 
But made you fill better about the purchase see all the money i saved you.  I got my boss to agree to 25% discount see i'm a great guy sales men i'm working for you.

Does this product make the company money or is it a liability ????

On Mon, Sep 15, 2008 at 1:16 PM, Roberts, Jon <Jon.Roberts@asurion.com> wrote:

> In Oracle, you must be granted the ability to see someone's PL/SQL code.
> Secondly, you can "wrap" the code so no one can read it which protects
> the intellectual property aspect.  I'm far less interested in protecting
> intellectual property because I'm not in that business.  I am much more
> interested in meeting business needs which means securing not only the
> data in the database but also the code users write.

I could totally get behind needing permission to see the plpgsql code.

On Mon, Sep 15, 2008 at 1:53 PM, justin <justin@emproshunts.com> wrote:
>
> David Fetter wrote:
>
> 2.  Security: Patches,
>
> When they get good and ready.  There are outstanding security issues
> in Oracle that have been there for years.
>
>
> Isn't that all software out there??

No it's not.  It is true of an aweful lot of commercial software, but
most open source packages fix SECURITY issues pretty quickly, usually
< 1 month.  Witness apache, the linux kernel, postgresql and so on.

On Sep 15, 2008, at 12:56 PM, Scott Marlowe wrote:
> I could totally get behind needing permission to see the plpgsql code.

:)

More seriously, this is the issue with code-encryption on an open
source platform: Where do you keep the key?  From my (admittedly
brief) research, it appears that Oracle bakes it into the server
binary, which isn't going to work for PG.  It sounds like a separate,
binary-only plug-in that takes the encrypted procedure, decrypts it,
and passes it on to PL/pgSQL for execution is the best bet, if this
is really a critical feature for someone.

On Mon, Sep 15, 2008 at 2:04 PM, Christophe <xof@thebuild.com> wrote:
>
> On Sep 15, 2008, at 12:56 PM, Scott Marlowe wrote:
>>
>> I could totally get behind needing permission to see the plpgsql code.
>
> :)

I wasn't kidding up there.  Setting view permissions on plpgsql (or
any pl code really) would be understandable.  If you're not a super
user or the owner, you need permission to see it.

> More seriously, this is the issue with code-encryption on an open source
> platform: Where do you keep the key?  From my (admittedly brief) research,
> it appears that Oracle bakes it into the server binary, which isn't going to
> work for PG.  It sounds like a separate, binary-only plug-in that takes the
> encrypted procedure, decrypts it, and passes it on to PL/pgSQL for execution
> is the best bet, if this is really a critical feature for someone.

But whether it's oracle or postgresql, this is just security through
obscurity. If you have root access on the server either method would
be trivial to hack.  anyone who's used a debugger with stop points
could hack the binary only oracle or postgresql.

A lot of people think binary only is some kind of magic pixie dust
that keeps people from seeing what the code's doing.  modern
disassemblers and debuggers make figuring out binaries pretty simple.

My point being that encrypting plsql or plpgsql is really just smoke
and mirrors.

On Mon, 2008-09-15 at 06:58 -0700, David Fetter wrote:
>
> > 4.  Scalability of shared disk (Oracle RAC)
>
> RAC doesn't scale outside Oracle's sales literature, as far as I've
> seen.

Yeah. I chatted a few Oracle guys a few weeks before, and they said
"Even we don't suggest RAC for more than 3 server".

Scalability? No.
--
Devrim GÜNDÜZ, RHCE
devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr
                   http://www.gunduz.org

On Mon, Sep 15, 2008 at 1:10 PM, Martin Gainty <mgainty@hotmail.com> wrote:
> accessing:
> i *thought* the advantage of creating any SQL procedure/function was the
> entity is stored in procedure cache
> load time:
> Java vs C++ compare here
> http://www.idiom.com/~zilla/Computer/javaCbenchmark.html

This is completely off topic as it is, but I can't help it: anyone who
is arguing that Java is faster than C must be oblivious to the fact
that Java internals are *written in C*.  If Java was really faster
than C, it would be self hosting, and we would be writing operating
systems, databases, and various other systems level stuff in Java.
(yes, there are a few well known projects, notably lucene, but that's
the exception rather than the rule).

Anybody making the case that Java is faster than C simply doesn't know
how a computer works.

merlin

On Sep 15, 2008, at 1:15 PM, Scott Marlowe wrote:
> I wasn't kidding up there.  Setting view permissions on plpgsql (or
> any pl code really) would be understandable.  If you're not a super
> user or the owner, you need permission to see it.

Ah, sorry, I parsed that as needing permission to see the code of the
Pl/pgSQL interpreter.

> But whether it's oracle or postgresql, this is just security through
> obscurity. If you have root access on the server either method would
> be trivial to hack.

Oh, sure.  (Same is true of Oracle, of course.)  I wouldn't use it
myself, but if this is a feature-checkbox item, that's how you'd need
to go about doing it.

On Mon, Sep 15, 2008 at 3:29 PM, Christophe <xof@thebuild.com> wrote:
>
> On Sep 15, 2008, at 1:15 PM, Scott Marlowe wrote:
>> But whether it's oracle or postgresql, this is just security through
>> obscurity. If you have root access on the server either method would
>> be trivial to hack.
>
> Oh, sure.  (Same is true of Oracle, of course.)  I wouldn't use it myself,
> but if this is a feature-checkbox item, that's how you'd need to go about
> doing it.

But as long as there is real progress to be made elsewhere, I'd rather
the hackers use their efforts there.  I'm not such a big fan of
checkboxes, and tend to look under the hood at what was done to make
the checkbox work.  Like MySQL has built in replication.  Sure, it's
not suitable for a large percentage of uses, is unreliable, and prone
to silent failure, but they got the box checked for it.

The problem is that people often assume that once the box is checked
they don't need to go / look any farther. I'd rather have postgresql's
slightly confusing but huge choice in replication engines than the one
in mysql that's limited and unreliable.

On Mon, 15 Sep 2008, Jonathan Bond-Caron wrote:

> For me, "Oracle stored procedures can be encrypted." is a very real and
> valuable argument.

Let's just hope none of your rogue customers find
http://www.petefinnigan.com/orasec.htm or learn that "unwrap" is the magic
word to find utilities to do that.

To answer one of the questions that keeps popping up in this thread (the
details are in the "How to unwrap Oracle PL/SQL" presentation there):
the short answer is that in earlier revs the "encrypted" Oracle PL/SQL is
just the code transformed (reversably!) into the intermediate language
actually used to execute it.  In 10g the "encryption" is hardened with
some 31337 base 64 tricks.  I hear the next version will include such
cutting-edge encryption technologies as rot13.

> It would certainly be a valuable feature in pgsql (in the enterprise space).

The problem here is that the PostgreSQL community is fully aware how bogus
any encryption method is and doesn't even bother, while Oracle is
perfectly happy selling a solution that is easily bypassed.  Don't get me
wrong--the work involved is just difficult enough that I'm sure most
PL/SQL procedures are quite safe from being reversed, and what you get
back again will be kind of crummy code, so that's good enough for your
typical ISV.  But the security doesn't stand up to simple scrutiny, and a
highly visible open-source project doing the same quality of
implementation would receive seriously bad press for releasing something
so shoddy.  PostgreSQL would be compelled to name it something like
"half-assed obfuscation" in order to make it clear just how limited the
protection actually is, and then you've kind of lost the sales pitch that
motivated the feature in the first place.

I feel like I should have been wearing a DeCSS t-shirt while typing the
above.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Mon, Sep 15, 2008 at 4:55 PM, Greg Smith <gsmith@gregsmith.com> wrote:
> On Mon, 15 Sep 2008, Jonathan Bond-Caron wrote:

> used to execute it.  In 10g the "encryption" is hardened with some 31337
> base 64 tricks.  I hear the next version will include such cutting-edge
> encryption technologies as rot13.

Well, if they start using double-rot13 encoding then no will ever figure it out!

Greg Smith <gsmith@gregsmith.com> wrote:
>
> The problem here is that the PostgreSQL community is fully aware how bogus
> any encryption method is and doesn't even bother, while Oracle is
> perfectly happy selling a solution that is easily bypassed.  Don't get me
> wrong--the work involved is just difficult enough that I'm sure most
> PL/SQL procedures are quite safe from being reversed, and what you get
> back again will be kind of crummy code, so that's good enough for your
> typical ISV.  But the security doesn't stand up to simple scrutiny, and a
> highly visible open-source project doing the same quality of
> implementation would receive seriously bad press for releasing something
> so shoddy.  PostgreSQL would be compelled to name it something like
> "half-assed obfuscation" in order to make it clear just how limited the
> protection actually is, and then you've kind of lost the sales pitch that
> motivated the feature in the first place.

I don't understand why this is so bloody difficult to implement:
Extend SECURITY DEFINER to include allowing only the definer to read
the code.

What more than that needs to be done to have honest to goodness secure
procedures?

--
Bill Moran
Collaborative Fusion Inc.

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

On Mon, Sep 15, 2008 at 08:29:22PM -0400, Bill Moran wrote:
> Greg Smith <gsmith@gregsmith.com> wrote:
> >
> > The problem here is that the PostgreSQL community is fully aware
> > how bogus any encryption method is and doesn't even bother, while
> > Oracle is perfectly happy selling a solution that is easily
> > bypassed.  Don't get me wrong--the work involved is just difficult
> > enough that I'm sure most PL/SQL procedures are quite safe from
> > being reversed, and what you get back again will be kind of crummy
> > code, so that's good enough for your typical ISV.  But the
> > security doesn't stand up to simple scrutiny, and a highly visible
> > open-source project doing the same quality of implementation would
> > receive seriously bad press for releasing something so shoddy.
> > PostgreSQL would be compelled to name it something like
> > "half-assed obfuscation" in order to make it clear just how
> > limited the protection actually is, and then you've kind of lost
> > the sales pitch that motivated the feature in the first place.
>
> I don't understand why this is so bloody difficult to implement:

First, make a case for implementing PL obfuscation under any
circumstances.

While you are making your case, please bear in mind that security by
obscurity is in effect an attack launched from that nastiest of places
to have an attacker, the inside of your trust boundaries.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter shaped the electron traffic to say:
<...snip...>
>
> First, make a case for implementing PL obfuscation under any
> circumstances.
>
> While you are making your case, please bear in mind that security by
> obscurity is in effect an attack launched from that nastiest of places
> to have an attacker, the inside of your trust boundaries.

Devil's advocate since I don't like any form of security by obscurity [or most any other o-word].

We can set permissions to prevent a user from seeing the data in a table (REVOKE SELECT FROM ...) but if they have access to the database it is hard (impossible ? I have never tried...) to prevent them from seeing the _existence_ of the table and even the structure.

Isn't this analogous to not allowing users to see a procedure's innards, but allowing them to see the name, parameters and return type ?

Ditto for trust ... let me be root and I will be able to get around any security you have. Roles and users not withstanding.

Greg Williamson
Senior DBA
DigitalGlobe

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)

Em Monday 15 September 2008 11:31:40 Harald Armin Massa escreveu:
>
> That would be more the "Oracle stored procedures can be encrypted." Which
> is an argument for ISVs, as they can easier force their customers to pay
> "software maintainance".
>
> With open code, clients could buy support from others, who may be cheaper
> or, even more dangerous, more qualified.

The problem is not just competency or qualification.

It is not everywhere where people respect copyright or licenses.  And it is
not uncommon to have less qualified people to steal code and software to
resell.

Sometimes the guy from the hardware store makes a copy of the hard disk before
wiping it out and it is done: he has access to everything in there...  If he's
just a little smart he'll be able to access the whole DB, including stored
procedures that took a long time to be debugged and optimized.

It is not always, but sometimes I see that using languages that allows linking
to "compiled" code is a beneficial thing.  I am thinking about Python (.pyc,
.pyo files), C/C++ and Java here...

With enough will one can read the code of any of those, but if we can make it
harder why not?

--
Jorge Godoy      <jgodoy@gmail.com>

David Fetter <david@fetter.org> wrote:
>
> On Mon, Sep 15, 2008 at 08:29:22PM -0400, Bill Moran wrote:
> > Greg Smith <gsmith@gregsmith.com> wrote:
> > >
> > > The problem here is that the PostgreSQL community is fully aware
> > > how bogus any encryption method is and doesn't even bother, while
> > > Oracle is perfectly happy selling a solution that is easily
> > > bypassed.  Don't get me wrong--the work involved is just difficult
> > > enough that I'm sure most PL/SQL procedures are quite safe from
> > > being reversed, and what you get back again will be kind of crummy
> > > code, so that's good enough for your typical ISV.  But the
> > > security doesn't stand up to simple scrutiny, and a highly visible
> > > open-source project doing the same quality of implementation would
> > > receive seriously bad press for releasing something so shoddy.
> > > PostgreSQL would be compelled to name it something like
> > > "half-assed obfuscation" in order to make it clear just how
> > > limited the protection actually is, and then you've kind of lost
> > > the sales pitch that motivated the feature in the first place.
> >
> > I don't understand why this is so bloody difficult to implement:
>
> First, make a case for implementing PL obfuscation under any
> circumstances.

The fact that it comes up every now and again on this list and creates
a thread of discussion eight miles long ...

But that's not even important, I'm not asking anyone to _do_ it, I'm
asking why, every time this topic comes up, it provokes such a long,
painful thread of argument.

> While you are making your case, please bear in mind that security by
> obscurity is in effect an attack launched from that nastiest of places
> to have an attacker, the inside of your trust boundaries.

Since when am I asking for security through obscurity?  I'm not telling
you to move ssh to an unusual port in the hopes that nobody will notice
that your root password is "password1".

What I'm _asking_ is why would extending SECURITY DEFINER to include
preventing unauthorized users from viewing code _not_ be a valid method
of securing the code.  Whether or not enough people want it to justify
the manpower to do it is a different discussion altogether.

--
Bill Moran
Collaborative Fusion Inc.

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

Bill Moran <wmoran@collaborativefusion.com> writes:
> What I'm _asking_ is why would extending SECURITY DEFINER to include
> preventing unauthorized users from viewing code _not_ be a valid method
> of securing the code.

Because it's so full of obvious loopholes.  Yes, it might slow down
someone who didn't have superuser access to the database or root access
to the machine it's on; but that doesn't count as secure really.  The
problem is that the people who ask for this type of feature are usually
imagining that they can put their code on customer-controlled machines
and it will be safe from the customer's eyes.  Well, it isn't, and
I don't think Postgres should encourage them to think it is.

            regards, tom lane

On Monday 15 September 2008 20:50:25 David Fetter wrote:
> On Mon, Sep 15, 2008 at 08:29:22PM -0400, Bill Moran wrote:
> > Greg Smith <gsmith@gregsmith.com> wrote:
> > > The problem here is that the PostgreSQL community is fully aware
> > > how bogus any encryption method is and doesn't even bother, while
> > > Oracle is perfectly happy selling a solution that is easily
> > > bypassed.  Don't get me wrong--the work involved is just difficult
> > > enough that I'm sure most PL/SQL procedures are quite safe from
> > > being reversed, and what you get back again will be kind of crummy
> > > code, so that's good enough for your typical ISV.  But the
> > > security doesn't stand up to simple scrutiny, and a highly visible
> > > open-source project doing the same quality of implementation would
> > > receive seriously bad press for releasing something so shoddy.
> > > PostgreSQL would be compelled to name it something like
> > > "half-assed obfuscation" in order to make it clear just how
> > > limited the protection actually is, and then you've kind of lost
> > > the sales pitch that motivated the feature in the first place.
> >
> > I don't understand why this is so bloody difficult to implement:
>
> First, make a case for implementing PL obfuscation under any
> circumstances.
>
> While you are making your case, please bear in mind that security by
> obscurity is in effect an attack launched from that nastiest of places
> to have an attacker, the inside of your trust boundaries.
>
> Cheers,
> David.
> --
> David Fetter <david@fetter.org> http://fetter.org/
> Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
> Skype: davidfetter      XMPP: david.fetter@gmail.com
>
> Remember to vote!
> Consider donating to Postgres: http://www.postgresql.org/about/donate

In response to Tom Lane <tgl@sss.pgh.pa.us>:

> Bill Moran <wmoran@collaborativefusion.com> writes:
> > What I'm _asking_ is why would extending SECURITY DEFINER to include
> > preventing unauthorized users from viewing code _not_ be a valid method
> > of securing the code.
>
> Because it's so full of obvious loopholes.  Yes, it might slow down
> someone who didn't have superuser access to the database or root access
> to the machine it's on; but that doesn't count as secure really.  The
> problem is that the people who ask for this type of feature are usually
> imagining that they can put their code on customer-controlled machines
> and it will be safe from the customer's eyes.  Well, it isn't, and
> I don't think Postgres should encourage them to think it is.

Shame that.  I can imagine it being a useful feature in certain situations
(such as a hosted environment), although I understand the concern.

Code obfuscation is the norm, though.  The world at large still seems to
believe that compiling code make it secret, despite the fact that crooks
have demonstrated again and again that they're more than willing to read
through opcodes, and the fact that there are decompilers available for
just about every major compiled format.

--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/

wmoran@collaborativefusion.com
Phone: 412-422-3463x4023

> Because it's so full of obvious loopholes.  Yes,
> it might slow down
> > someone who didn't have superuser access to the
> database or root access
> > to the machine it's on; but that doesn't count
> as secure really.  The
> > problem is that the people who ask for this type of
> feature are usually
> > imagining that they can put their code on
> customer-controlled machines
> > and it will be safe from the customer's eyes.
> Well, it isn't, and
> > I don't think Postgres should encourage them to
> think it is.
>

As much as I'm impressed with the "we do it properly or not at all" attitude, it'd be nice if there was an option to
stopthe casual user from viewing code. 

I'll admit to obfusicating bits and pieces using C, even though the function and everything it acts on are tied down
withpermissions. I understand in reality it provides no real extra security but somehow users being able to easily view
somethingthey don't have access to execute beyond it's name just feels wrong. 

On Tue, Sep 16, 2008 at 9:15 AM, Glyn Astill <glynastill@yahoo.co.uk> wrote:
>
> As much as I'm impressed with the "we do it properly or not at all" attitude, it'd be nice if there was an option to
stopthe casual user from viewing code. 
>
> I'll admit to obfusicating bits and pieces using C, even though the function and everything it acts on are tied down
withpermissions. I understand in reality it provides no real extra security but somehow users being able to easily view
somethingthey don't have access to execute beyond it's name just feels wrong. 

This is one of those threads that reappears like magic every six
months or so.  The last round of discussion went longer than normal
including a couple of routes to implementation.

One big reason why nothing hasn't been done is that there is a decent
'low tech' obfuscation tactic already: remove select access from
pg_proc to the user accounts in question and 'public'.  This will
essentially disable casual browsing of procedure code from user
accounts.

Any real solution should focus on:
*) key management (any serious discussion with encryption starts here)
*) other things you can do with function source besides encryption

for example, take a look at one idea I had (not at all vetted, but a start):
http://archives.postgresql.org/pgsql-performance/2007-12/msg00337.php

merlin

On Tue Sep 16 08:40 AM, Bill Moran wrote:
> In response to Tom Lane <tgl@sss.pgh.pa.us>:
>
>> Bill Moran <wmoran@collaborativefusion.com> writes:
>>> What I'm _asking_ is why would extending SECURITY DEFINER to include
>>> preventing unauthorized users from viewing code _not_ be a valid
>>> method of securing the code.
>>
>> Because it's so full of obvious loopholes.  Yes, it might slow down
>> someone who didn't have superuser access to the database or root
>> access to the machine it's on; but that doesn't count as secure
>> really.  The problem is that the people who ask for this type of
>> feature are usually imagining that they can put their code on
>> customer-controlled machines and it will be safe from the customer's
>> eyes.  Well, it isn't, and I don't think Postgres should encourage
> them to think it is.
>
> Shame that.  I can imagine it being a useful feature in certain
> situations (such as a hosted environment), although I understand the
> concern.
>
> Code obfuscation is the norm, though.  The world at large still seems
> to believe that compiling code make it secret, despite the fact that
> crooks have demonstrated again and again that they're more than
> willing to read through opcodes, and the fact that there are
> decompilers available for just about every major compiled format.
>

I agree here. I hope there's a consensus that it does offer some level of
protection.

After some research, I found this article that I believe will make a
stronger use case:
http://www.iosn.net/network/news/Managing%20the%20insider%20threat%20through
%20code%20obfuscation

Whether or not it belongs in PG I don't really have an opinion.

Nice trick ... thanks!

> One big reason why nothing hasn't been done is that there is a decent
> 'low tech' obfuscation tactic already: remove select access from
> pg_proc to the user accounts in question and 'public'.  This will
> essentially disable casual browsing of procedure code from user
> accounts.
<....>
> merlin

> One big reason why nothing hasn't been done is that
> there is a decent
> 'low tech' obfuscation tactic already: remove
> select access from
> pg_proc to the user accounts in question and
> 'public'.  This will
> essentially disable casual browsing of procedure code from
> user
> accounts.

Neat :-)

On Tue, Sep 16, 2008 at 09:39:03AM -0400, Jonathan Bond-Caron wrote:
>
> I agree here. I hope there's a consensus that it does offer some level of
> protection.

There is not, in fact, in the security community a consensus that it
offers some level of protection.  There are some security people who
think that obfuscation and secrets are one of the tools (and one of
the weakest) to protect the computing infrastructure.  There are
others who claim, as strongly and with as great authority, that
anything that cannot be secured even if the attacker knows all about
it (excluding the private key itself) cannot be secured at all.

> After some research, I found this article that I believe will make a
> stronger use case:
> http://www.iosn.net/network/news/Managing%20the%20insider%20threat%20through
> %20code%20obfuscation

That article is not evidence for your conclusion.  Obfuscated code in
the example cases in the article would not be a good investment.

In the first example, an insider who is an expert in the system
deleted a key file: it would make no difference if the code were
obfuscated, because an expert operator doesn't need to understand the
innards in order to be able to know which files are critical.  (Your
expert operators _have_ to know which are critical, because they have
to pay more attention to those files.)

In the second example, of Mallory the disgruntled employee, we don't
have any information about what Mallory knows; so either Mallory's
defeat of the access controls (which is the real problem) will give
Mallory access to a system she understands, or else she'll have to
comprehend a complex system on the fly while evading detection of the
access control failure.  Obfuscation isn't likely to provide
additional defence against a vandal, which is what the
disgruntled-employee scenario represents.

Finally, the bigger example of distributed code that is decompilable
doesn't prove much, either.  If you are analysing the code in order to
attack it for a given end, you can do this just as easily with opaque
bytes (if you couldn't, Windows wouldn't be plagued with the security
problems it is: there wasn't a remarkable rash of new exploits that
appeared after the apparently leaking of the Windows source).

Obfuscation is mostly useful against the casual attacker.  There are
better and less costly measures against such attackers.  In
particular, careful management of access controls is far more
important.

The important thing to remember is that, if the secrecy of the system
is your main or only defence, you're going to be in trouble because
such systems are brittle.  When they fail, they fail spectacularly.

All of that said, I can in fact think of use cases where casual users
not being able to view the source of a function could be useful.
These cases have more to do with corporate governance and internal
"Chinese walls" than any real security.  They're the sort of thing
that would make auditors happy, and for that reason they might be
worth implementing.  So far, I don't believe anyone's had an itch of
this sort strong enough to scratch.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

>
> On Mon, Sep 15, 2008 at 2:04 PM, Christophe <xof@thebuild.com> wrote:
> >
> > On Sep 15, 2008, at 12:56 PM, Scott Marlowe wrote:
> >>
> >> I could totally get behind needing permission to see the plpgsql
code.
> >
> > :)
>
> I wasn't kidding up there.  Setting view permissions on plpgsql (or
> any pl code really) would be understandable.  If you're not a super
> user or the owner, you need permission to see it.
>

How can I make that a feature request?


Jon

On Tue, 16 Sep 2008 11:54:18 -0500
"Roberts, Jon" <Jon.Roberts@asurion.com> wrote:

> > I wasn't kidding up there.  Setting view permissions on plpgsql (or
> > any pl code really) would be understandable.  If you're not a super
> > user or the owner, you need permission to see it.
> >
>
> How can I make that a feature request?

You need to send it to -hackers and get support for it.  You might have
better luck if you or someone you agrees with you actually does the
work to get it implemented.

Sincerely,

Joshua D. Drake

--
The PostgreSQL Company since 1997: http://www.commandprompt.com/
PostgreSQL Community Conference: http://www.postgresqlconference.org/
United States PostgreSQL Association: http://www.postgresql.us/
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Jon.Roberts@asurion.com ("Roberts, Jon") writes:
> Roberts, Jon wrote:
>> > My top 10 reasons why companies pick Oracle.
>>
>> Do you mean they actually get these things, or they imagine they do?
>>
> Huh?  Companies buy Oracle all of the time.

That's not the point.

The question isn't whether they buy *Oracle* or not, as the sales
figures obviously indicate that they do.

The question is whether or not the actually get the 10 things that you
named.

I don't expect that they actually get all 10 of them, nor that they
get particularly close to getting all 10 of them.

>> There certainly are a lot of false perceptions out in the world about
>> Oracle, and about proprietary software in general.
>>
>> > 1.  24x7 Support
>>
>> At several different places over the years, I've seen their top-tier
>> support simply not respond.
>>
> That is highly unusual.  I've always had excellent experience with
> Oracle's support especially their top tier support.

I can't comment on that usefully.

>> > 2.  Security: Patches,
>>
>> When they get good and ready.  There are outstanding security issues
>> in Oracle that have been there for years.
>
> I'm not saying Oracle is more or less secure.  I think people feel
> better about security from a company like Oracle rather than a bunch of
> hackers on an email list.  It is perceived as more secure by many,
> especially large companies.

Fortunately for sales, perception is much more important than reality.
The customers may not be *getting* better or more-quickly-available
security patches, but if they feel more comfortable, they may be
happier.

>> > Row Level Security,
>>
>> I think you mean access control.  Access control has so little overlap
>> with security that they really need to be discussed as separate
>> subjects.
>>
>> > Roles,
>>
>> We have 'em.
>>
>> > encryption,
>>
>> We have it.
>>
> PG can't encrypt code.

It's not evident that "encrypting code" actually provides security.
In order for the code to *run*, any encryption keys need to reside
some place accessible by the server, which therefore implies that the
keys are accessible to operations staff.

This is a common problem with the use of encryption.

>> > protection of database code, etc.
>>
>> Are you saying that the fact that the source isn't legally available
>> to the population at large is a feature?  If you are, it's an argument
>> for security by obscurity, a system with a lot of deep known flaws.
>>
> No, I'm saying that if I create a function in PG, ANYONE with access to
> the database can see my code.  That is not secure.  It is a security
> hole for the database.

Is this truly a security hole?

It is not at all evident to me that this indicates a security hole,
and simply asserting this to be so does not make it so.

>> > 3.  Software indemnification (which is open source's biggest
>> > problem)
>>
>> Are you kidding?!?  Read the EULA for Oracle or any other proprietary
>> software package and then read the BSD license.  They both indemnify
>> about the same thing, i.e. nothing.  If you have any examples in case
>> law that show otherwise, they'd be a great thing to bring forth.
>
> The contracts you sign when you buy Oracle indemnify you from lawsuits.
> For instance, Oracle bundles Apache with various products.  If someone
> sues a company for using Oracle HTTP Server because it uses Apache and
> Apache was allegedly illegally contributed to by a rogue employee, then
> Oracle protects their customers.
>
> After SCO went after companies using Linux, it is a concern of large
> companies and worth the extra cost of paying Oracle for Apache rather
> than using Apache all by itself.

SCO *lost* their case, and got slapped down plenty hard enough by
people intent on doing so that SCO no longer represents any sort of
example, aside from being an example that "in the Land of the
Litigious, anyone with deep pockets can potentially sue anyone over
anything," which isn't useful guidance.

You haven't pointed to any case law.

>> > 4.  Scalability of shared disk (Oracle RAC)
>>
>> RAC doesn't scale outside Oracle's sales literature, as far as I've
>> seen.
>
> I have.

And you can't prove it without breaking contracts that were signed
promising not to publish results, so you don't have any *useful*
proof.

>> > 5.  Works extremely well from anything from hosting LDAP to OLTP
>> > applications to data warehouses with ROLAP and MOLAP
>>
>> Now we're vaguely getting somewhere within shouting range of reality.
>> While the first half of that is hotly debatable, they've got decent
>> *OLAP.
>>
> Oracle Internet Directory is LDAP compliant and stored in an Oracle
> database.  They have rollback which provides similar MVCC functionality
> of PG.  It has also been around longer than PG MVCC.

Stone tablets have been around longer than MVCC, but that's not an
example of anything.

>> > 6.  Best, oldest, and most proven concurrency model for any
>> > commercial database product
>>
>> It's none of those things.
>>
> Which commercial database is better?  MS SQL Server, Sybase, DB2, what?

You set a much higher bar than that.  You claimed it was best, oldest,
and most proven in concurrency model.

>> > 7.  Runs great on various platforms not just Linux or just Windows
>>
>> Is this different from some other RDBMS(s) out there, and if so, which
>> one(s)?  The only "just Windows" RDBMS I've ever heard of is MS SQL
>> Server, and I know of no "just Linux" ones.
>>
> PG doesn't scale well on Windows.  DB2 seems to work best on a
> mainframe.  Sybase works best on Unix.  MS SQL Server only runs on
> Windows.
>
>> > 8.  Recruiting a senior level Oracle professional with over 10 years
>> > of experience is not very difficult
>>
>> Finding somebody with 10 years' experience is no problem.  Finding
>> somebody half-way competent is a different story.
>>
>> > 9.  Deep, deep discounts.  I've never seen any company pay list
>> > price for Oracle products.  It has always been at least 50% off if
>> > not more.
>>
>> 50% off a price that's bloated by 1000% or more isn't much of a
>> muchness.
>>
> It is only the perception of a good deal.  So what?

It's *ALL* about perception, indeed.  All 10 items you mention are, to
the extent that they are *perceived* to be true, a victory of
perception over reality.

>> > 10.  Sales employees that will do anything to retain or grow your
>> > business.
>>
>> That's just great if you prefer hookers and blow to a working RDBMS.
>>
> LOL.  That is pretty funny.  I'm talking about doing free work like a
> proof of concepts, demonstrations of products, etc.  I've even seen
> technical sales guys help out onsite for free for performance tuning an
> application.

There's the old tale of the "lady of the evening" who offered to "do
anything" for $100, and the gentleman in question said, "Great!  My
house needs painting!"

Unfortunately, "do anything" has some questionable ethical
implications, and "hookers and blow" *do* lie down that road :-(.

>> > The initial price of the product factors into the equation for big
>> > companies but when you look at all the value add of Oracle, it is
>> > very tempting.
>>
>> Their sales and marketing people have certainly done an excellent job
>> creating the perceptions above, among others, and spreading them
>> around the industry.
>
> Perception = sales.

100% agreed.

But we're not sales people, so we're not overly interested in that
reality.

We're much more interested in the technical realities.
--
output = ("cbbrowne" "@" "acm.org")
http://linuxfinances.info/info/linuxxian.html
Rules of the  Evil Overlord #64. "I will  see a competent psychiatrist
and get cured of all  extremely unusual phobias and bizarre compulsive
habits which could prove to be a disadvantage."
<http://www.eviloverlord.com/>

Merlin Moncure wrote:
> On Mon, Sep 15, 2008 at 1:10 PM, Martin Gainty <mgainty@hotmail.com> wrote:
>
>> accessing:
>> i *thought* the advantage of creating any SQL procedure/function was the
>> entity is stored in procedure cache
>> load time:
>> Java vs C++ compare here
>> http://www.idiom.com/~zilla/Computer/javaCbenchmark.html
>>
>
> This is completely off topic as it is, but I can't help it: anyone who
> is arguing that Java is faster than C must be oblivious to the fact
> that Java internals are *written in C*.  If Java was really faster
> than C, it would be self hosting, and we would be writing operating
> systems, databases, and various other systems level stuff in Java.
> (yes, there are a few well known projects, notably lucene, but that's
> the exception rather than the rule).
>
> Anybody making the case that Java is faster than C simply doesn't know
> how a computer works.
>
> merlin
>
According to your argument any hardware implementation is limited to
software performance, as it is compiled by a software compiler? In fact,
compiling assembly with a compiler written in C will destroy its
performance to be worse than C? Come on... This is utter bullshit. You
should rather reflect on your last line...

Its worst yet, there are actually situation where the Java optimizer
does a better job than many C compiler. The reverse is also true. If you
are a software guy you SHOULD know that code optimization and algorithm
design are NP problems and have no obvious optimal solutions. It seems
easier to optimize Java code than it is optimizing C code.

Better yet, you should actually read the article. It has the explanation
contained inside it. The domain of application Java has (eventually)
been developed for is just different.

Regards,

- Joris

Am 2008-09-15 10:12:08, schrieb Joshua Drake:
> Are we going to start a VI vs Emacs argument too?

They are out of concurence since I am using mc (Midnight Commander). :-P

Thanks, Greetings and nice Day/Evening
    Michelle Konzack
    Systemadministrator
    24V Electronic Engineer
    Tamay Dogan Network
    Debian GNU/Linux Consultant


--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
+49/177/9351947    50, rue de Soultz         MSN LinuxMichi
+33/6/61925193     67100 Strasbourg/France   IRC #Debian (irc.icq.com)

Added to TODO under features not wanted:

    Incomplete itemObfuscated function source code (not wanted)

        Obfuscating function source code has minimal protective benefits
    because anyone with super-user access can find a way to view the code.
    To prevent non-super-users from viewing function source code, remove
    SELECT permission on pg_proc.


---------------------------------------------------------------------------

Merlin Moncure wrote:
> On Tue, Sep 16, 2008 at 9:15 AM, Glyn Astill <glynastill@yahoo.co.uk> wrote:
> >
> > As much as I'm impressed with the "we do it properly or not at all" attitude, it'd be nice if there was an option
tostop the casual user from viewing code. 
> >
> > I'll admit to obfusicating bits and pieces using C, even though the function and everything it acts on are tied
downwith permissions. I understand in reality it provides no real extra security but somehow users being able to easily
viewsomething they don't have access to execute beyond it's name just feels wrong. 
>
> This is one of those threads that reappears like magic every six
> months or so.  The last round of discussion went longer than normal
> including a couple of routes to implementation.
>
> One big reason why nothing hasn't been done is that there is a decent
> 'low tech' obfuscation tactic already: remove select access from
> pg_proc to the user accounts in question and 'public'.  This will
> essentially disable casual browsing of procedure code from user
> accounts.
>
> Any real solution should focus on:
> *) key management (any serious discussion with encryption starts here)
> *) other things you can do with function source besides encryption
>
> for example, take a look at one idea I had (not at all vetted, but a start):
> http://archives.postgresql.org/pgsql-performance/2007-12/msg00337.php
>
> merlin
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

I'm not sure what the policy is on putting stuff in the docs, but how about putting that in the relevant place, as well
asa note about the other option; using C and SPI. 


> Added to TODO under features not wanted:
>
>     Incomplete itemObfuscated function source code (not
> wanted)
>
>         Obfuscating function source code has minimal
> protective benefits
>     because anyone with super-user access can find a way to
> view the code.
>     To prevent non-super-users from viewing function source
> code, remove
>     SELECT permission on pg_proc.

On Wed, Sep 24, 2008 at 02:12:19PM +0000, Glyn Astill wrote:
> I'm not sure what the policy is on putting stuff in the docs, but
> how about putting that in the relevant place, as well as a note
> about the other option; using C and SPI.

C is not magic obfuscation gear.  Anybody with a debugger can expose
what it's doing.  There have been math papers showing that it's
impossible to hide the functionality of a piece of software based only
on the ability to run it, so the entire prospect of obscuring the
software's functionality when people can send arbitrary inputs to it
is one of those "known-impossible" problems like the halting problem.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

David Fetter wrote:
> On Wed, Sep 24, 2008 at 02:12:19PM +0000, Glyn Astill wrote:
> > I'm not sure what the policy is on putting stuff in the docs, but
> > how about putting that in the relevant place, as well as a note
> > about the other option; using C and SPI.
>
> C is not magic obfuscation gear.  Anybody with a debugger can expose
> what it's doing.  There have been math papers showing that it's

I bet 'strings' shows all the SQL queries in a C object file too.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

On Wed, Sep 24, 2008 at 08:05:18AM -0700, David Fetter wrote:

> C is not magic obfuscation gear.  Anybody with a debugger can expose
> what it's doing. There have been math papers showing that it's
> impossible to hide the functionality of a piece of software based only
> on the ability to run it, so the entire prospect of obscuring the
> software's functionality when people can send arbitrary inputs to it
> is one of those "known-impossible" problems like the halting problem.

To be fair, one of the points that others are trying to make is not
"secure this function for real" but "secure this function enough to
make it a little costly."  Sure, someone with a debugger and probably
not much work could figure out what the function is.  If all you're
trying to do is make it expensive for dodgy software shops to re-use
your code, however, this is probably enough: the sort of person who
thinks re-using someone else's undocumented code is easier than
writing it from scratch is probably not going to go to the trouble of
really learning the code via debugging tools.  As a defence against
criminally lazy developers, "compliled C code" is probably good
enough.  (Of course, clever non-C code is probably also enough, in my
opinion, but obviously others disagree.)

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

On Aug 31, 2008, at 8:44 PM, David Fetter wrote:
> What they want to have is a huge entity they can blame when everything
> goes wrong.  They're not interested in the actual response times or
> even in the much more important time-to-fix because once they've
> blamed Oracle, they know the responsibility is no longer on their
> shoulders.

The usual individual developer, open source community, and small
company attitude is one that prefers to employ intelligent staff and
give them a lot of responsibility (with varying degrees of success).
They would rather spend a week refactoring code for performance, or
experimenting with another language or database, sending an employee
to training or conferences, contribute patches to open source
projects, etc.  They will try to make a well thought-out decision up
front when possible, and often this results in an early choice for
PostgreSQL, especially because it fits within any budget.  When these
people end up working in larger companies with different mindsets,
they sometimes are successful at getting PostgreSQL utilized through
resources like Command Prompt, EnterpriseDB, Greenplum, and so on, to
replace the vendor support that Oracle comes with.  They might start
off with MySQL as a first database, but once learning about
PostgreSQL, will invest lots of time into porting if they understand
the advantages (this can be evidenced all the time by people
communication in the #postgresql IRC channel), and will put a lot of
effort into doing things the architecturally best way over time rather
than just slopping together bandaided bits and poor lazy table design.

Others are lazy, go with some popular name of something free they
hear, and end up as Red Hat/PHP/MySQL shops, with a huge pile of
random crappy free apps bandaided together - hoping to make some quick
cash.  If they end up with PostgreSQL it's not a thought-out decision
(well or otherwise), and they use it very irresponsibly and then
everyone will blame "PostgreSQL" for all their problems simply because
it's not the most common name.  There's a perception here that MySQL
is better for them because it's more popular, has more random free
crappy apps available for it, and they don't care much about the added
reliability of PostgreSQL (often they'll run with fsync=off), as
they're a rickety shop anyways.  They'll also have a perception that
Oracle is some magical holy grail simply because it is so out of their
reach during early development, but as they become profitable enough,
the idea of buying Oracle becomes very exciting.

The corporate attitude is one that prefers things to be as
encompassing, bundled, automatic, and self-maintaining as possible.
They prefer Oracle because they provide a wide array of inbuilt
replication, backup, and administrative functionality - things like
raw device management (Oracle has implemented a couple different
filesystems as well), early integration with Java when it was all the
rage, tons of complicated shinies for permission control that managers
don't really comprehend but think they like and need and are using
correctly.  These are typically Java shops, with no diversity at all -
you won't find a single perl or bash script lying around - they'll
load up a slow common Java framework for even the simplest task.  Code
quality tends to be pretty decent, but there is heavy denial of any
problems and fixes are slow and obstructed by managerial complexity
and too much influence from Sales. :P

Another similar example can be found with ZFS or VxFS versus
traditional simple filesystems.  ZFS has few tuning options and their
use is discouraged.  It does a lot of things automatically and there
is a favoring of defaults over workload-specific tuning and
administrative overhead.  It builds in every filesystem-related thing
(the filesystem itself, a logical volume manager, clustering tools,
and even it's own ZFS-specific filesystem cache daemon) into a single
manager-friendly bundle.  You can't get the same levels of performance
out of it as you can by carefully tuning other systems, but that
tuning requires hiring intelligent staff and this seems to be
amazingly challenging for large corporations, and they'd rather pay
some middle-level manager a salary worth 5 developers, and have him
buy and assemble big packaged solutions instead.

PostgreSQL can't really take over the corporate market - Oracle and
DB2 will always be around too. :)  To do that we'd need to do a lot of
unwanted things to the code, build in much more unecessary complexity
that most will never use, reduce flexibility and options in the
process, spange up incredible amounts of well-placed marketing dollars
and slowly get more acceptance by proving years of experience at an
increasing number of corporate PostgreSQL users.

I worked with PostgreSQL at Cingular - and the simple fact is that it
was not a good choice for the requirements of the task.  The task as
defined could easily be called stupid and easy to change to do a
better way, but in that environment you can't stop a ball once it's
rolling and getting that ball or another one for change rolling takes
months and many meetings.  Their minds were steeped in the ways of
Oracle, and Oracle has better optimizations for some stupid common
design/architectural/PHB mistakes.  So then they try to bring their
broken methods to PostgreSQL verbatim, and it fails terribly.  This is
not PostgreSQL's fault, but "PostgreSQL" gets the blame and label as a
substandard ("open source" in disdaining scoffing voice) database that
people only use because they can't afford Oracle, and even if the
effort is put in to do things right and it *is* faster, they still
won't like it and will feel that it's chosen out of resignation.  So
really, it's better that we don't get the corporate market.  This is
good, because it enables intelligent small businesses to have more of
a competitive edge and saner budget.  Things like RAC and multimaster
replication are not good designs architecturally, but they are
encompassing ways to scale pretty much any existing design with
minimal effort.  Corporations would much rather throw money/hardware
at a problem than refactor.

Right now where I work, our biggest performance problem could easily
be solved by moving  a couple tables into an Oracle database.  It
would be an easy port, and Oracle would handle the bad design much
better.  Fixing it correctly is going to be a tedious process that
takes months to reach completion.  As a small company, we plan to do
the latter, seek out quick fixes to buy us a little breathing room
until then, and avoid spending money (we'd much rather spend the
Oracle dollars on hardware improvements, etc.).  A corporation will
usually throw money at Oracle and avoid spending developer time.

Currently PostgreSQL has a very good, strong, intelligent user
community.  This has been the case consistently for all the years that
I've been using it, and it grows steadily over time without
significant changes in the overall community vibe.  That said, when we
ported to Windows, there was an influx of a lot of former MySQL and
Microsoft SQL folks with very little general database knowledge
compared to the average PostgreSQL user.  Fortunately this has not
been overpowering, and has lead to more people learning and doing
things more intelligently.  But if we somehow magically took over the
majority of the corporate market, we'd have a lot more people involved
who frankly don't care to learn and just bicker and blame, which would
deplete the community resources for everyone else.

A knowledgeable PostgreSQL DBA can make significantly more than an
Oracle DBA as they're a scarcer resource and generally higher quality
on average.  But it may be harder for them to find work - they may end
up having to move, telecommute, or commute a longer distance simply
because there are less PostgreSQL shops.  It also means a much higher
probability of working for a small-medium company versus a corporation.

An Oracle DBA can be a lot lazier, and lean on the vendor a lot more.
There are open Oracle DBA positions everywhere, and it is very easy
for them to find another job, so learning a lot and focusing on doing
a good job are not important to them.  In the corporate environment in
which most of these jobs are, they are responsible for far less in
their job role, whereas the PostgreSQL DBA tends to end up responsible
for a lot more pieces of the puzzle.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

> C is not magic obfuscation gear.  Anybody with a debugger
> can expose
> what it's doing.  There have been math papers showing
> that it's
> impossible to hide the functionality of a piece of software
> based only
> on the ability to run it, so the entire prospect of
> obscuring the
> software's functionality when people can send arbitrary
> inputs to it
> is one of those "known-impossible" problems like
> the halting problem.

And the first word in the title is "obfuscated", not encrypted, secured or anything else...

On Wed, Sep 24, 2008 at 1:02 PM, Casey Allen Shobe <cshobe@bepress.com> wrote:

> A knowledgeable PostgreSQL DBA can make significantly more than an Oracle
> DBA as they're a scarcer resource and generally higher quality on average.
>  But it may be harder for them to find work - they may end up having to
> move, telecommute, or commute a longer distance simply because there are
> less PostgreSQL shops.  It also means a much higher probability of working
> for a small-medium company versus a corporation.
>
> An Oracle DBA can be a lot lazier, and lean on the vendor a lot more.  There
> are open Oracle DBA positions everywhere, and it is very easy for them to
> find another job, so learning a lot and focusing on doing a good job are not
> important to them.  In the corporate environment in which most of these jobs
> are, they are responsible for far less in their job role, whereas the
> PostgreSQL DBA tends to end up responsible for a lot more pieces of the
> puzzle.

These two paragraphs really ring true for me.  I've yet to meet an
oracle dba who was the jack of all trades that being a postgresql DBA
requires.

Andrew Sullivan <ajs@commandprompt.com> writes:
> On Wed, Sep 24, 2008 at 08:05:18AM -0700, David Fetter wrote:
>> C is not magic obfuscation gear. ...

> To be fair, one of the points that others are trying to make is not
> "secure this function for real" but "secure this function enough to
> make it a little costly."

Agreed, but there seems no particular need to have such a feature in
core Postgres.  An add-on PL could accomplish that task just as well;
perhaps more so, if you don't make the add-on available to all and
sundry.

            regards, tom lane

On Sep 4, 2008, at 7:40 PM, Robert Treat wrote:
> It is not as simple as Oracles database link syntax. Setting up a
> connection
> involves a couple of sql looking commands, and once you setup a
> connection to
> a remote database, you can reference a table with something like
> select *
> from mytable@myotherdb.  There's no way a function oriented solution
> can
> match that imho.

I have long thought that what would be really useful is a standard way
for third-party modules to extend or override the SQL language support
within PostgreSQL itself without needing to be integrated in core.

E.g. it should be possible for all of EnterpriseDB's Oracle-compatible
SQL changes to exist as a separate module, somebody could change the
behavior of a select to default ordering to imitate Oracle etc.  It
should be possible for a replication engine to add syntax for options
specific to it.  Contrib modules like dblink could install SQL-like
command support.

This would be both invaluable for compatibility efforts and probably
raise the amount of 3rd party stuff that actually gets used
(currently, many places I've seen avoid Slony because they fear having
to use the commandline scripts it comes with, and if you want to
manipulate Slony from the database itself, oftentimes this means you
have to use pl/perlu or another untrusted language.

Don't get me wrong, functions are great too. :)  But currently the
above means that a lot of risk is introduced and you have to put a lot
of faith in the perl code - an exploit poses a lot of risk.  If Slony
exposed it's own data to PG via custom SQL extensions, this would be
more secure by design.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 1, 2008, at 12:42 AM, Henry wrote:
> This is /finally/ being addressed, although (very) belatedly.  The
> Pg core
> dev team always argued that replication was an add-on and should not
> form
> part of the core (ie, similar nonsense excuses the MySQL team used for
> "add-ons" such as triggers, etc).

I believe the developer stance is more the same than you seem to
imagine.  The upcoming developments allow replication utilities to tie
in at a deeper and more effective level, and with that new replication
solutions will come along.  But I do not think there is any goal to
implement a single replication solution within core and not support
external solutions.

The point of the PostgreSQL developer stance is that until something
can be done correctly, even if it's a lot more work, it's sometimes
better not to do at all.  It was recognized early on that if we tried
to figure out the replication puzzle ourself, it would invariably be
complex and never ideally suited for every situation.  It would cost a
lot of resources that the team really needed to spend elsewhere at the
time.

MySQL's stance on things like triggers and subselects and so on is
*not* that at all.  They recognize that a proper implementation would
be complicated and take a lot of time, so they strongly want to avoid
it, and make lame excuses a lot.  When they do finally get around to
implementing something, they have traditionally done it in a broken or
lazy way - e.g. you cannot have two triggers on the same type of
action on the same table, instead you must write a wrapper function
that calls other functions; subselects are always evaluated
independently meaning they usually equate to "horribly slow", there's
a lot of bugs, etc.

I prefer the way PostgreSQL development has been going, personally. :)

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

> the sort of person who
> thinks re-using someone else's undocumented code is easier than
> writing it from scratch is probably not going to go to the trouble of
> really learning the code via debugging tools.

Fixed that for you:

the sort of person who
thinks re-using someone else's undocumented code is easier than
writing it from scratch is probably not going to be able to learn the code
via debugging tools.


--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 722-0567 voice

On Sep 15, 2008, at 6:58 AM, David Fetter wrote:
>> Roles,
>
> We have 'em.

We do NOT have secure application roles or anywhere near the level of
configurability in security aspects as Oracle.  We've got a great
foundation, but we lack a lot of fine-grained granularity (e.g. an
Oracle SAR can allow a role to execute a particular function based on
the result of another function call or query, which has rather a lot
of possibilities - consider grant connect on database to staff when
hour_of_day () between 9 and 6; also consider row-level and column-
level and even field-level access controls).

It's complicated in Oracle, but there's a lot of possibilities there
that we simply cannot reproduce.  But this could be extended one day. :)

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 4, 2008, at 7:40 PM, Robert Treat wrote:
> It is not as simple as Oracles database link syntax. Setting up a
> connection
> involves a couple of sql looking commands, and once you setup a
> connection to
> a remote database, you can reference a table with something like
> select *
> from mytable@myotherdb.  There's no way a function oriented solution
> can
> match that imho.

I have long thought that what would be really useful is a standard way
for third-party modules to extend or override the SQL language support
within PostgreSQL itself without needing to be integrated in core.

E.g. it should be possible for all of EnterpriseDB's Oracle-compatible
SQL changes to exist as a separate module, somebody could change the
behavior of a select to default ordering to imitate Oracle etc.  It
should be possible for a replication engine to add syntax for options
specific to it.  Contrib modules like dblink could install SQL-like
command support.

This would be both invaluable for compatibility efforts and probably
raise the amount of 3rd party stuff that actually gets used
(currently, many places I've seen avoid Slony because they fear having
to use the commandline scripts it comes with, and if you want to
manipulate Slony from the database itself, oftentimes this means you
have to use pl/perlu or another untrusted language.

Don't get me wrong, functions are great too. :)  But currently the
above means that a lot of risk is introduced and you have to put a lot
of faith in the perl code - an exploit poses a lot of risk.  If Slony
exposed it's own data to PG via custom SQL extensions, this would be
more secure by design.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 1, 2008, at 12:42 AM, Henry wrote:
> This is /finally/ being addressed, although (very) belatedly.  The
> Pg core
> dev team always argued that replication was an add-on and should not
> form
> part of the core (ie, similar nonsense excuses the MySQL team used for
> "add-ons" such as triggers, etc).

I believe the developer stance is more the same than you seem to
imagine.  The upcoming developments allow replication utilities to tie
in at a deeper and more effective level, and with that new replication
solutions will come along.  But I do not think there is any goal to
implement a single replication solution within core and not support
external solutions.

The point of the PostgreSQL developer stance is that until something
can be done correctly, even if it's a lot more work, it's sometimes
better not to do at all.  It was recognized early on that if we tried
to figure out the replication puzzle ourself, it would invariably be
complex and never ideally suited for every situation.  It would cost a
lot of resources that the team really needed to spend elsewhere at the
time.

MySQL's stance on things like triggers and subselects and so on is
*not* that at all.  They recognize that a proper implementation would
be complicated and take a lot of time, so they strongly want to avoid
it, and make lame excuses a lot.  When they do finally get around to
implementing something, they have traditionally done it in a broken or
lazy way - e.g. you cannot have two triggers on the same type of
action on the same table, instead you must write a wrapper function
that calls other functions; subselects are always evaluated
independently meaning they usually equate to "horribly slow", there's
a lot of bugs, etc.

I prefer the way PostgreSQL development has been going, personally. :)

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Thu, Sep 25, 2008 at 01:13:29PM +0300, Asko Oja wrote:
>
> but why would you put part of your business logic into some configuration
> tables while you could keep it in your own functions

Because the parameters of the business logic should not be in the
code.  The parameters should be part of the configuration, to be
administered by the administrators (i.e. the DBAs) and not by the
database developers.  In traditional large database shops, that is the
division of responsibility, and the inability to work in that way will
hamper Postgres adoption in that environment.  (Maybe we don't care,
but let's at least be honest that changing the culture of such
database shops is not something we're going to achieve quickly.)

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

On Sep 15, 2008, at 6:58 AM, David Fetter wrote:
>> Roles,
>
> We have 'em.

We do NOT have secure application roles or anywhere near the level of
configurability in security aspects as Oracle.  We've got a great
foundation, but we lack a lot of fine-grained granularity (e.g. an
Oracle SAR can allow a role to execute a particular function based on
the result of another function call or query, which has rather a lot
of possibilities - consider grant connect on database to staff when
hour_of_day () between 9 and 6; also consider row-level and column-
level and even field-level access controls).

It's complicated in Oracle, but there's a lot of possibilities there
that we simply cannot reproduce.  But this could be extended one day. :)

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 4, 2008, at 7:40 PM, Robert Treat wrote:
> It is not as simple as Oracles database link syntax. Setting up a
> connection
> involves a couple of sql looking commands, and once you setup a
> connection to
> a remote database, you can reference a table with something like
> select *
> from mytable@myotherdb.  There's no way a function oriented solution
> can
> match that imho.

I have long thought that what would be really useful is a standard way
for third-party modules to extend or override the SQL language support
within PostgreSQL itself without needing to be integrated in core.

E.g. it should be possible for all of EnterpriseDB's Oracle-compatible
SQL changes to exist as a separate module, somebody could change the
behavior of a select to default ordering to imitate Oracle etc.  It
should be possible for a replication engine to add syntax for options
specific to it.  Contrib modules like dblink could install SQL-like
command support.

This would be both invaluable for compatibility efforts and probably
raise the amount of 3rd party stuff that actually gets used
(currently, many places I've seen avoid Slony because they fear having
to use the commandline scripts it comes with, and if you want to
manipulate Slony from the database itself, oftentimes this means you
have to use pl/perlu or another untrusted language.

Don't get me wrong, functions are great too. :)  But currently the
above means that a lot of risk is introduced and you have to put a lot
of faith in the perl code - an exploit poses a lot of risk.  If Slony
exposed it's own data to PG via custom SQL extensions, this would be
more secure by design.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 1, 2008, at 12:42 AM, Henry wrote:
> This is /finally/ being addressed, although (very) belatedly.  The
> Pg core
> dev team always argued that replication was an add-on and should not
> form
> part of the core (ie, similar nonsense excuses the MySQL team used for
> "add-ons" such as triggers, etc).

I believe the developer stance is more the same than you seem to
imagine.  The upcoming developments allow replication utilities to tie
in at a deeper and more effective level, and with that new replication
solutions will come along.  But I do not think there is any goal to
implement a single replication solution within core and not support
external solutions.

The point of the PostgreSQL developer stance is that until something
can be done correctly, even if it's a lot more work, it's sometimes
better not to do at all.  It was recognized early on that if we tried
to figure out the replication puzzle ourself, it would invariably be
complex and never ideally suited for every situation.  It would cost a
lot of resources that the team really needed to spend elsewhere at the
time.

MySQL's stance on things like triggers and subselects and so on is
*not* that at all.  They recognize that a proper implementation would
be complicated and take a lot of time, so they strongly want to avoid
it, and make lame excuses a lot.  When they do finally get around to
implementing something, they have traditionally done it in a broken or
lazy way - e.g. you cannot have two triggers on the same type of
action on the same table, instead you must write a wrapper function
that calls other functions; subselects are always evaluated
independently meaning they usually equate to "horribly slow", there's
a lot of bugs, etc.

I prefer the way PostgreSQL development has been going, personally. :)

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 25, 2008, at 3:13 AM, Asko Oja wrote:
> but why would you put part of your business logic into some
> configuration tables while you could keep it in your own functions

Because as bad as my Not Invented Here syndrome might be at times, I
know that I would not be able to alone build as elegant of a system as
the community could together, which would be more adaptable and
generic.  Things tend not to make it into PostgreSQL releases until
they are very solid.

Then instead of worrying about whether or not my function code is
good, I can rely on the PostgreSQL-provided foundation, using things
in a standard, supported way.  If there is a bug, it's found, fixed,
and the whole community benefits.  Many people could benefit from SAR
support, so why make them all spend time reimplementing thigs?


There is a much, much more important reason for this though, which is
that I can only extend security functionality by adding additional
restrictions within my function call.  The SAR stuff in Oracle is
superior to this because it affects *all use of the database*, not
just function calls.  I don't put much value in security through
obscurity - sorry.

To an extent we *can* simulate row-level and column-level security
through the use of very restricted data tables and more generally-
available or specific-purpose views, but we cannot make PostgreSQL
call a custom function to determine from it's output whether or not it
should allow a particular action.

> I see it as a strong side of PostgreSQL that we have not bloated our
> code with all this fancy micromanagement that seems too complex to
> be useful anyway considering that quality of Oracle database
> management tools :)

Lack of bloat is a strong point.  Bloat is more the result of putting
things in without enough forethought, and then needing to maintain
compatibility with old stuff even after you add better - the
maintenance of the old code interfering with the time/quality put into
the new, etc.  It's also a question of how well they are implemented.
If 0.1% of the user base will use a feature, it's probably not worth
adding.  If 10% will use it, but it's implementation requires added
overhead for the 90% of others who don't - that's bloat.  If 10% will
use it, and it doesn't cost anything to those who don't, it's worth
doing.

I would like to clarify that I'm not saying anything like "PostgreSQL
needs / should have SAR support" in this thread, although I would
personally find them handy.  I just wanted to clarify that what
"roles" means to an Oracle DBA is a lot different from what it means
to a PostgreSQL DBA.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 15, 2008, at 1:04 PM, Christophe wrote:
> More seriously, this is the issue with code-encryption on an open
> source platform: Where do you keep the key?  From my (admittedly
> brief) research, it appears that Oracle bakes it into the server
> binary, which isn't going to work for PG.

Just because Oracle implements something poorly doesn't mean it's the
only way.  I don't know what Oracle actually does, but I wouldn't put
much faith in the safety of code protection if that's the way they do
it, because an Oracle employee in the right position could easily
disclose the key one day.

If this functionality were to be implemented, the proper way to do it
would be to require a key file stored on the server or maybe within
postgresql.conf.  Users who wish to use this functionality could be
required to create this by hand, or it could be autogenerated at
initdb time.

I don't find this functionality useful, but I also don't think that
it's completely worthless.  There are enterprises with very different
needs and perspectives.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 15, 2008, at 1:15 PM, Scott Marlowe wrote:
> But whether it's oracle or postgresql, this is just security through
> obscurity. If you have root access on the server either method would
> be trivial to hack.

You just contradicted yourself.  If you have root access on the server
all bets are off, period.  No database security at all applies any
longer.  Even just non-privileged read access to the files is
sufficient.

Database-level security only pertains to accesses within the database,
and for this reason, it would not be security through obscurity,
unless you could work around it by querying the catalog tables as a
non-superuser.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

depends on Postgres support for Oracle java packages which is now available thru PL/Java
http://my.safaribooksonline.com/0672327562/ch19lev1sec1

Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.


> From: cshobe@bepress.com
> To: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Oracle and Postgresql
> Date: Thu, 25 Sep 2008 11:15:24 -0700
>
> On Sep 4, 2008, at 7:40 PM, Robert Treat wrote:
> > It is not as simple as Oracles database link syntax. Setting up a
> > connection
> > involves a couple of sql looking commands, and once you setup a
> > connection to
> > a remote database, you can reference a table with something like
> > select *
> > from mytable@myotherdb. There's no way a function oriented solution
> > can
> > match that imho.
>
> I have long thought that what would be really useful is a standard way
> for third-party modules to extend or override the SQL language support
> within PostgreSQL itself without needing to be integrated in core.
>
> E.g. it should be possible for all of EnterpriseDB's Oracle-compatible
> SQL changes to exist as a separate module, somebody could change the
> behavior of a select to default ordering to imitate Oracle etc. It
> should be possible for a replication engine to add syntax for options
> specific to it. Contrib modules like dblink could install SQL-like
> command support.
>
> This would be both invaluable for compatibility efforts and probably
> raise the amount of 3rd party stuff that actually gets used
> (currently, many places I've seen avoid Slony because they fear having
> to use the commandline scripts it comes with, and if you want to
> manipulate Slony from the database itself, oftentimes this means you
> have to use pl/perlu or another untrusted language.
>
> Don't get me wrong, functions are great too. :) But currently the
> above means that a lot of risk is introduced and you have to put a lot
> of faith in the perl code - an exploit poses a lot of risk. If Slony
> exposed it's own data to PG via custom SQL extensions, this would be
> more secure by design.
>
> Cheers,
> --
> Casey Allen Shobe
> Database Architect, The Berkeley Electronic Press
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Stay up to date on your PC, the Web, and your mobile phone with Windows Live. See Now

On Sep 15, 2008, at 2:40 PM, Scott Marlowe wrote:
> Like MySQL has built in replication.

You know, I hear this particular example about MySQL's replication
implementation a lot against any sort of new feature, and it's
important to recognize the difference here.

Replication is *not* a function of the database, it's a function of
the network/cluster.  For this reason, it's entirely appropriate to
not build it into the core binary.  It's entirely a different matter
when it is core database functionality being discussed.  Modular
programming exists for a reason.

Similarly, it has been argued that psql -l and things like
(php)pgadmin should not show databases you cannot log in to in MySQL
style.  For many users, this may not matter, but I've done shared
hosting before, and it matters a lot there - not for any good reason
so much as the fact that every client gets concerned and complains
about being able to see the existence of others' databases and vice
versa.  However once logged in, you can get at this information by
querying the catalog, so that *is* security through obscurity.
However, if the catalog workaround were also addressed, perhaps with
row-level access control, this would have the same effect and *not* be
security through obscurity.

People love throwing the "security through obscurity" and "bloat"
terms around when discussing new features, but these are really more a
question about how and where the implementation is done than it is
about the feature itself.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 15, 2008, at 7:19 PM, Tom Lane wrote:
> The problem is that the people who ask for this type of feature are
> usually
> imagining that they can put their code on customer-controlled machines
> and it will be safe from the customer's eyes.

That's a broken expectation.  All that can realistically be expected
is database/catalog-level constraints.

> Well, it isn't, and I don't think Postgres should encourage them to
> think it is.

Adding such a feature would NOT be encouraging them to think this -
the documentation could be very explicit about this fact.  Maybe
that's what Oracle is selling, and that's crappy of them, but that
doesn't mean we should use that as justification to not add a more
appropriate implementation.

As for the expectation above - could pl/pgsql be made compilable?  It
would seem easy to translate pl/pgsql code into C and compile a C
function.  That *could* go onto customer-controlled machines and be
safe from the customer's eyes.


FWIW, I think most people who want to hide code aren't concerned about
IP, they're concerned about clients seeing embarrassingly bad/sloppy
code.  But there *are* some very real and legitimate needs for this,
though it's a small minority of those who think they do.


Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

Casey Allen Shobe wrote:
> To an extent we *can* simulate row-level and column-level security
> through the use of very restricted data tables and more generally-
> available or specific-purpose views, but we cannot make PostgreSQL
> call a custom function to determine from it's output whether or not it
> should allow a particular action.

We were just talking today about adding column and row-level security to
Postgres 8.4:

    http://archives.postgresql.org/pgsql-hackers/2008-09/msg01654.php

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

On Thu, Sep 25, 2008 at 01:05:26PM -0700, Casey Allen Shobe wrote:
> On Sep 15, 2008, at 7:19 PM, Tom Lane wrote:
>> The problem is that the people who ask for this type of feature are
>> usually imagining that they can put their code on
>> customer-controlled machines and it will be safe from the
>> customer's eyes.
>
> That's a broken expectation.  All that can realistically be expected
> is database/catalog-level constraints.

It's far from clear that those offer protection of any reasonable kind.

>> Well, it isn't, and I don't think Postgres should encourage them to
>> think it is.
>
> Adding such a feature would NOT be encouraging them to think this - the
> documentation could be very explicit about this fact.  Maybe that's what
> Oracle is selling, and that's crappy of them, but that doesn't mean we
> should use that as justification to not add a more appropriate
> implementation.

You've got the burden of proof exactly backwards there.  It's on you
or anyone who cares to to explain why it might be a good idea to add
this "feature," understanding that every feature has a maintenance
cost and is a potential source of bugs.

> As for the expectation above - could pl/pgsql be made compilable?
> It  would seem easy to translate pl/pgsql code into C and compile a
> C  function.  That *could* go onto customer-controlled machines and
> be safe from the customer's eyes.

No, it would not.  As many others have mentioned, "strings" does a
pretty good job on such stuff, let alone the impossibility even in
theory of hiding what a program does from someone with access to run
it using arbitrary inputs, even when they have no binary to examine.

> FWIW, I think most people who want to hide code aren't concerned about
> IP, they're concerned about clients seeing embarrassingly bad/sloppy
> code.  But there *are* some very real and legitimate needs for this,
> though it's a small minority of those who think they do.

Please elucidate those needs in detail, then explain why it might be
PostgreSQL's job to meet them.

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

On Sep 25, 2008, at 1:05 PM, Casey Allen Shobe wrote:
> As for the expectation above - could pl/pgsql be made compilable?

Without getting into the argument as to the level of security
provided, it strikes me that a reasonable approach would be a non-
core pluggable language which accepts encrypted strings as functions,
decrypts them (using a key compiled into the language module), and
passes them on to PL/pgSQL for execution.  This would keep the
functionality out of core, allow the developer who distributes the
code to plop in their own key and distribute the code as a compiled
module, and minimizes reimplementation.

This would, of course, be easily hacked with someone who can step
through the language module with a debugger, but I don't see any
reasonable way of preventing someone with that level of access from
breaking the code without OS-level support.

On Sep 16, 2008, at 6:39 AM, Jonathan Bond-Caron wrote:
> After some research, I found this article that I believe will make a
> stronger use case:
> http://www.iosn.net/network/news/Managing%20the%20insider%20threat%20through
> %20code%20obfuscation

I can tell without even clicking the link that it would be a waste of
time to read.

The only way to manage internal threats are to have a good security
policy that limits what each person can do to what they need to do,
and having backups.

If I work as a developer or DBA and cannot immediately go looking at
code when there's a problem, it's very detrimental to productivity.
Gee, I wonder why companies that support these antics grow to insane
sizes of employees?  Having access to a copy of the code should not be
a security threat in any way.  If it is, then the underlying
infrastructure/security policy is broken.  Not having visibility into
what unrelated code does leads to crappy, bloated code.  For instance,
I was updating the SQL in a class method the other day.  Technically,
I don't require any visibility into the calling code, and the above
page probably advocates that it would be hidden from me.  But I noted
that the database results were coming back in an ordered fashion, and
being returned as an unordered hash.  I did some grepping through the
rest of the codebase, and found that the callers then did their own
sort in perl to reproduce the lost database sorting.  So I was able to
easily fix more than required and improve overall efficiency.  With a
code obfuscation approach, this would be impossible and the current
code would be more bloated and slow.

My previous employer has some ~100 PostgreSQL servers serving up one
of the most popular websites in the world.  The only thing standing
between me and complete destruction of all that data is a wifi
password (WEP even, because that's more convenient than requiring
folks to have WPA2 support) because convenience is the first priority
and any security architects are lazy or nonexistent (in the latter
case, the responsibility lands on sysadmins, which does not work out
well in most cases).  In than environment, they give postgres user
trust access to anyone on the production network, and then make the
office a part of the production network via a permanent VPN link (it's
a VPN, so it must be secure, right?) to enable the developers to work
easier (like many places, they don't have a completely isolated dev
environment).

I've seen a lot of PostgreSQL environments in the last few years, and
every single one of them connects to the database as the "postgres"
user or another superuser, requires ability to execute DDL, and all
sorts of other gaping security issues, and I cannot think of the last
time I heard somebody else in a work setting speak of a GRANT that was
not a GRANT ALL, even when all that is needed is select access, for
example.

Companies need to fix their security architecture and keep production
isolated from development, not go down the obfuscation rathole.  If
they do that and still have a threat that they think code obfuscation
would help manage, I'd love to see it.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 24, 2008, at 8:05 AM, David Fetter wrote:
> C is not magic obfuscation gear.  Anybody with a debugger can expose
> what it's doing.

Yes, but you don't get original code, comments, etc. and it takes a
lot of effort to refine it back down into something maintainable.
People looking to protect IP are often not looking to prevent the same
functionality from being executed elsewhere, but to prevent somebody
from taking the results of their hard work with minimal effort (think
of Microsoft and the FreeBSD TCP/IP stack).  If it costs the
competition a lot of time to decompile code and then rebuild
maintainable code out of it that probably doesn't take all the same
things into consideration and will lead to difficulties keeping up
with new features, that's adequate protection.

Of course, there are idiots out there who think that not making pl/
pgsql code visible should protect even against root-level access and
that compilation equals irreversible encryption, but not everyone
using these techniques is one of those idiots, and a few do have
pretty good reasons.  Consider for example chipmakers, who compete
against each other selling to a very small number of clients.  A board
maker will buy whatever does the job well at lowest cost, and the cost
associated with creating these is purely development time.  If you put
months into making a really fast/efficient chip to do a specific task,
protecting this is very important, or you go out of business.  This is
much the opposite of a business that provides a service.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 24, 2008, at 6:12 PM, Scott Ribe wrote:
> the sort of person who
> thinks re-using someone else's undocumented code is easier than
> writing it from scratch is probably not going to be able to learn
> the code
> via debugging tools.

There are two distinct extremes here, and I think most people on the
list are more familiar with the former.

The first is where you build a big system/application, and any code
you do go out and get from the OSS community or by stealing or
whatever just does some small function within your overall
architecture.  Maintainability of any acquired code is important here.

The second is where you release a product that is never updated, and
designed for a specific purpose.  If somebody else can produce an
equivalent product without the associated development time, even
without any understanding, through use of theft, it can bring down the
original business.  Maintainability does NOT matter here because the
dodgy competitor only cares to produce something functionally
equivalent, not to have something maintainable.

Realistically, it's a pretty stupid long-term decision to use acquired
code and screw over the original business, because then once you've
killed the competitor or lost the ability to steal more, you no longer
have the ability to keep up with customer demand, but many businesses
don't care about the long-term so much as a quick buck, unfortunately.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 25, 2008, at 11:16 AM, Asko Oja wrote:
> What i see is lack of useless bells and whistles in PostgreSQL and i
> like it.

Then you aren't paying attention very well.  PostgreSQL comes with an
extremely rich and useful set of bells and whistles than most people
never use, in a non-detrimental way thanks to proper implementation
techniques.  Most people use a fraction of possible SQL techniques,
let alone most of the awesome functions that come provided in
pg_catalog.  But the implementation is not bloated, so you don't see
it as a problem. :)

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 25, 2008, at 1:14 PM, David Fetter wrote:
> On Thu, Sep 25, 2008 at 01:05:26PM -0700, Casey Allen Shobe wrote:
>> On Sep 15, 2008, at 7:19 PM, Tom Lane wrote:
>>> The problem is that the people who ask for this type of feature are
>>> usually imagining that they can put their code on
>>> customer-controlled machines and it will be safe from the
>>> customer's eyes.
>>
>> That's a broken expectation.  All that can realistically be expected
>> is database/catalog-level constraints.
>
> It's far from clear that those offer protection of any reasonable
> kind.

Define "protection".  If there is no effective way to query the
catalog and see function code at the SQL level, this is a complete and
effective form of protection - it's just not protection from somebody
with server-level access.

> You've got the burden of proof exactly backwards there.  It's on you
> or anyone who cares to to explain why it might be a good idea to add
> this "feature," understanding that every feature has a maintenance
> cost and is a potential source of bugs.

I don't personally want this feature.  But I can see where it's
valuable in some contexts, and if the understanding is correct, it can
be used responsibly.  People misuse basic SQL all the time, but that
doesn't mean the basic SQL should be nonexistent or stupider.

You do have a very valid point in indicating the added maintenance
cost of any new feature, but protecting stuff at the SQL level is not
a complicated thing to do, and well, people concerned with this
feature can be the ones maintaining it - there seems to be a good many
already and existence of the feature would attract more.  Could this
be implemented via a contrib/ module?

>> As for the expectation above - could pl/pgsql be made compilable?
>> It  would seem easy to translate pl/pgsql code into C and compile a
>> C  function.  That *could* go onto customer-controlled machines and
>> be safe from the customer's eyes.
>
> No, it would not.  As many others have mentioned, "strings" does a
> pretty good job on such stuff, let alone the impossibility even in
> theory of hiding what a program does from someone with access to run
> it using arbitrary inputs, even when they have no binary to examine.

I am not saying that C is an encryption technique.  It does however
protect code from customer eyes.  People are often not trying to truly
encrypt a protected algorithm, but removing maintainability, adding
cost to theft, and hiding code that's badly done.

>> FWIW, I think most people who want to hide code aren't concerned
>> about
>> IP, they're concerned about clients seeing embarrassingly bad/sloppy
>> code.  But there *are* some very real and legitimate needs for this,
>> though it's a small minority of those who think they do.
>
> Please elucidate those needs in detail, then explain why it might be
> PostgreSQL's job to meet them.

See my other posts talking about chipmakers.  We should NOT add this
feature /for/ idiots, but the fact that idiots will inevitably end up
misusing any feature should not be a justification for not
implementing it.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press
cshobe@bepress.com (email/jabber/aim/msn)
http://www.bepress.com | +1 (510) 665-1200 x163

On Thu, Sep 25, 2008 at 01:25:25PM -0700, Casey Allen Shobe wrote:

> Gee, I wonder why companies that support these antics grow to insane
> sizes of employees?

Meetings.  Lots and lots of meetings.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

On Thu, 25 Sep 2008, Christophe wrote:

> it strikes me that a reasonable approach would be a non-core pluggable
> language which accepts encrypted strings as functions, decrypts them
> (using a key compiled into the language module), and passes them on to
> PL/pgSQL for execution...This would, of course, be easily hacked with
> someone who can step through the language module with a debugger

If we presume that the module doing the encryption/decryption is itself is
a common open-source implementation, all I have to do is read in the
de-obfuscator code byte at a time, stopping every time I have a key length
worth of bytes to see if they unlock something that looks like plaintext.
You have to move to at least another layer of relatively serious security
before you need debugger-level skills to crack it.

People routinely tear through protection like this even on closed-source
systems that benefit some from security by obscurity, and if you can know
the method used that usually allows an even easier approach.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Sep 25, 2008, at 1:16 PM, Christophe wrote:
> Without getting into the argument as to the level of security
> provided, it strikes me that a reasonable approach would be a non-
> core pluggable language which accepts encrypted strings as
> functions, decrypts them (using a key compiled into the language
> module), and passes them on to PL/pgSQL for execution.

The only way this could work is if the key is set at compile time.
Using a single key is impossible in an open source product as Greg
pointed out, and very stupid in any other.  Now I'll ignore the fact
that you can reverse engineer the key out of compiled code, as you
already acknowledged that - this is still problematic for another
reason.

Let's consider the original goal of "I want to keep customers, with
full control of the server machine, from being able to see clearly
what a function does".  In cases where you just want to keep database
users from seeing a function code, the implementation should be easy,
and that's the only form I see any value in adding, really.

You could add encryption properly by storing the key in an external
file with very restrictive permissions, and that would solve the goal
of "I don't want people to be able to read function code in pg_dump
output", etc., so it takes things a step farther.  But of course on
customer-controlled boxes, they can read any file they want, defeating
the encryption.

So you think "ah I'll just compile it in by requiring ./configure --
key=whatever to compile the thing.  Well, now you suddenly have to
build every release of PostgreSQL for every single one of those
customers - they cannot upgrade or rebuild themselves, without losing
all the encrypted functions.  Maybe that's something you can accept,
but I would say that most people who want to hide code from customers
want nothing to do with setting up the database for the customer.  In
cases where you fully dictate the PostgreSQL build and upgrade policy
and mandate it for your clients, this could work, but I'm guessing
that's a pretty esoteric corner case.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

i would recommend
dont publish in HTML/JS as with a simple View Page Source any browser client can figure out what is doing what
also i would shy from Scripting macro languages as they are not compiled modules and anyone with a text editor can easily see your code
Functions and procedure are another story as one would need DB access to view..so someone who is doing
a simple query with Toad or sqlplus suddenly sees this interesting procedure hmm..

can the more proprietary routines be placed in a compiled language like Java and then use PL/Java packaging to pull in the packages you need?
?
Martin
______________________________________________
Disclaimer and confidentiality note
Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission.


> CC: pgsql-general@postgresql.org
> From: cshobe@bepress.com
> To: xof@thebuild.com
> Subject: Re: Obfuscated stored procedures (was Re: [GENERAL] Oracle and Postgresql)
> Date: Thu, 25 Sep 2008 16:38:18 -0700
>
> On Sep 25, 2008, at 1:16 PM, Christophe wrote:
> > Without getting into the argument as to the level of security
> > provided, it strikes me that a reasonable approach would be a non-
> > core pluggable language which accepts encrypted strings as
> > functions, decrypts them (using a key compiled into the language
> > module), and passes them on to PL/pgSQL for execution.
>
> The only way this could work is if the key is set at compile time.
> Using a single key is impossible in an open source product as Greg
> pointed out, and very stupid in any other. Now I'll ignore the fact
> that you can reverse engineer the key out of compiled code, as you
> already acknowledged that - this is still problematic for another
> reason.
>
> Let's consider the original goal of "I want to keep customers, with
> full control of the server machine, from being able to see clearly
> what a function does". In cases where you just want to keep database
> users from seeing a function code, the implementation should be easy,
> and that's the only form I see any value in adding, really.
>
> You could add encryption properly by storing the key in an external
> file with very restrictive permissions, and that would solve the goal
> of "I don't want people to be able to read function code in pg_dump
> output", etc., so it takes things a step farther. But of course on
> customer-controlled boxes, they can read any file they want, defeating
> the encryption.
>
> So you think "ah I'll just compile it in by requiring ./configure --
> key=whatever to compile the thing. Well, now you suddenly have to
> build every release of PostgreSQL for every single one of those
> customers - they cannot upgrade or rebuild themselves, without losing
> all the encrypted functions. Maybe that's something you can accept,
> but I would say that most people who want to hide code from customers
> want nothing to do with setting up the database for the customer. In
> cases where you fully dictate the PostgreSQL build and upgrade policy
> and mandate it for your clients, this could work, but I'm guessing
> that's a pretty esoteric corner case.
>
> Cheers,
> --
> Casey Allen Shobe
> Database Architect, The Berkeley Electronic Press
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

---

Want to do more with Windows Live? Learn “10 hidden secrets” from Jamie. Learn Now

For an alternative view of the security argument, which may be a little
off topic...

One consideration in regard to arguments for additional security,
whether column and row level security or the divergent thread on
obfuscated stored procedures is whether postgresql currently supports
PCI (international), Basel II (EU - international) and Sarbanes-Oxley
(US) requirements for restricted access, logging and differentiation of
roles and responsibilities, or whether the additional security is
required to provide better matching support.  These are important
considerations in the corporate, and especially financial institutions,
though I would suspect that postgresql does not have great penetration
into such organisations.

In my mind, postgresql as is, in combination with application design
considerations and OS level security, does support PCI, Basel II and
Sarbanes-Oxley security requirements.  However, I thought I would bring
this up as some people may have different interpretations on what it
means to be compliant to these standards and regulations, and may have a
convincing argument for their case based on what is needed to support them.

This is assuming that the postgresql development community see any value
in being seen to be enablers of PCI, Basel II or Sarbanes-Oxley
requirements.  Many commercial version control systems and database
systems now throw in Sarbanes-Oxley compliant in their advertising,
though I have not seen any open source applications do so (which doesn't
mean that they haven't), and personally I think it is a somewhat
misrepresentative to imply that the application itself makes you compliant.

If interested, the following are the relevant Wikipedia links, with
references to the standards and regulations themselves:
PCI:         http://en.wikipedia.org/wiki/PCI_DSS
Basel II:   http://en.wikipedia.org/wiki/Basel_II
SOX:      http://en.wikipedia.org/wiki/Sarbanes-oxley

Not that any of these regulations have done much to avert the market
turmoil of the last few months, despite the bureaucratic overhead that
they generated...  But that is another story.

Cheers,

Andy

On Sep 1, 2008, at 12:42 AM, Henry wrote:
> This is /finally/ being addressed, although (very) belatedly.  The
> Pg core
> dev team always argued that replication was an add-on and should not
> form
> part of the core (ie, similar nonsense excuses the MySQL team used for
> "add-ons" such as triggers, etc).

I believe the developer stance is more the same than you seem to
imagine.  The upcoming developments allow replication utilities to tie
in at a deeper and more effective level, and with that new replication
solutions will come along.  But I do not think there is any goal to
implement a single replication solution within core and not support
external solutions.

The point of the PostgreSQL developer stance is that until something
can be done correctly, even if it's a lot more work, it's sometimes
better not to do at all.  It was recognized early on that if we tried
to figure out the replication puzzle ourself, it would invariably be
complex and never ideally suited for every situation.  It would cost a
lot of resources that the team really needed to spend elsewhere at the
time.

MySQL's stance on things like triggers and subselects and so on is
*not* that at all.  They recognize that a proper implementation would
be complicated and take a lot of time, so they strongly want to avoid
it, and make lame excuses a lot.  When they do finally get around to
implementing something, they have traditionally done it in a broken or
lazy way - e.g. you cannot have two triggers on the same type of
action on the same table, instead you must write a wrapper function
that calls other functions; subselects are always evaluated
independently meaning they usually equate to "horribly slow", there's
a lot of bugs, etc.

I prefer the way PostgreSQL development has been going, personally. :)

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 4, 2008, at 7:40 PM, Robert Treat wrote:
> It is not as simple as Oracles database link syntax. Setting up a
> connection
> involves a couple of sql looking commands, and once you setup a
> connection to
> a remote database, you can reference a table with something like
> select *
> from mytable@myotherdb.  There's no way a function oriented solution
> can
> match that imho.

I have long thought that what would be really useful is a standard way
for third-party modules to extend or override the SQL language support
within PostgreSQL itself without needing to be integrated in core.

E.g. it should be possible for all of EnterpriseDB's Oracle-compatible
SQL changes to exist as a separate module, somebody could change the
behavior of a select to default ordering to imitate Oracle etc.  It
should be possible for a replication engine to add syntax for options
specific to it.  Contrib modules like dblink could install SQL-like
command support.

This would be both invaluable for compatibility efforts and probably
raise the amount of 3rd party stuff that actually gets used
(currently, many places I've seen avoid Slony because they fear having
to use the commandline scripts it comes with, and if you want to
manipulate Slony from the database itself, oftentimes this means you
have to use pl/perlu or another untrusted language.

Don't get me wrong, functions are great too. :)  But currently the
above means that a lot of risk is introduced and you have to put a lot
of faith in the perl code - an exploit poses a lot of risk.  If Slony
exposed it's own data to PG via custom SQL extensions, this would be
more secure by design.

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

On Sep 15, 2008, at 6:58 AM, David Fetter wrote:
>> Roles,
>
> We have 'em.

We do NOT have secure application roles or anywhere near the level of
configurability in security aspects as Oracle.  We've got a great
foundation, but we lack a lot of fine-grained granularity (e.g. an
Oracle SAR can allow a role to execute a particular function based on
the result of another function call or query, which has rather a lot
of possibilities - consider grant connect on database to staff when
hour_of_day () between 9 and 6; also consider row-level and column-
level and even field-level access controls).

It's complicated in Oracle, but there's a lot of possibilities there
that we simply cannot reproduce.  But this could be extended one day. :)

Cheers,
--
Casey Allen Shobe
Database Architect, The Berkeley Electronic Press

There is some my publications about SART AML System, where is more
detailed described all this things (and more) that I wrote below.

http://www.analyticsql.org/documentation.html

Regards,
Blazej Oleszkiewicz

2008/9/4 Blazej <bl.oleszkiewicz@gmail.com>:
> Hi Artacus
>
> 2008/9/4 Artacus <artacus@comcast.net>:
>> Oh, as I was writing a CUBE query today and realized that I forgot to
>> mention this. And unlike most gripes, like MERGE INTO or CTE's which are
>>  really convenience things, this is key piece of functionality that you just
>> can't reproduce in Postgres.
>>
>
> It is not true. First look at this:
> http://www.analyticsql.org/
>
> In atachment is two snapshots from production system (Analytic SQL
> Server based on PostgreSQL 8.3):
>
> Picture: non_uniform_dimension.JPG
>
> Heterogeneous data warehouse dimensions where each class of dimension:
> Chart of Account (PK), Synthetic Account (KS), Analytical Account (KA)
> may have a heterogeneous structure of the hierarchy (~60 000 items in
> hierarchy, max depth 9).
>
> For example, on picture marked heterogeneity of the hierarchy:
> -- Green: [PK, KS "operations involving cash and interbank
> operations," KS "Kasa," KS "Cash / banknotes and coins /" KA "Cash in
> hand GBP"]
> -- Red: [PK, KS "operations involving cash and interbank operations,"
> KS "Kasa," KS "bankers' cheques," KS "foreign bankers' cheques," KS
> "bankers' cheques, foreign currency," KA "bankers' cheques in USD "]
>
> Hence, we can see that both hierarchical structure:
> -- [PK, KS, KS, KS, KA] for a KA "Cash in hand GBP (ID: 339 in column KNT_ID);
> -- [PK, KS, KS, KS, KS, KS, KA] for a KA "foreign bankers' cheques in
> U.S. dollars" (ID: 363 in column KNT_ID)
>
> are different from each other in terms of the structure of the hierarchy.
>
> Picture:olap_raport_non_uniform_dimension.JPG - example of OLAP raport
> based on Chart of Account.
>
> so it is much more then "OLAP" ORACLE extensions like CUBE etc.
>
> Regards,
> Blazej Oleszkiewicz
>