modules

I am trying to port a MySQL db to postgres 8.3rc1.
In MySQL I have a function SHA1, which is critical for storing and
authenticating passwords.
I see some old posts on how to add this function myself. see:
http://raveica.comdurav.com/blog/programming/how-do-i-add-sha1-to-postgresql/

Has sha1 been added to the standard postgres 8.3? or do I still need
to build my own version?
Are there updated instructions for this for 8.3?

thanks, Jon

On Sun, Jan 20, 2008 at 12:21:01AM -0800, Jon Hancock wrote:
> I am trying to port a MySQL db to postgres 8.3rc1.
> In MySQL I have a function SHA1, which is critical for storing and
> authenticating passwords.
> I see some old posts on how to add this function myself. see:
> http://raveica.comdurav.com/blog/programming/how-do-i-add-sha1-to-postgresql/

It's in contrib in the pgcrypto module. If you use a distribution you
can usually simply just install it.

http://www.postgresql.org/docs/8.3/static/pgcrypto.html

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy

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

> In MySQL I have a function SHA1, which is critical for storing and
> authenticating passwords.

It sure would be nice to have this in core. Yeah, there's pgcrypto,
but it's a bit overkill for people who simply want to do a SHA1,
especially when they see we already have a md5(). I also realize that
SHA1 is not a great solution these days either, but I'd at least like
to see a discussion on moving Postgres to somewhere between
"only has md5()" and "all pg_crypto functions inside core", even if
it only means a handful of SHA functions. Moving this over to -hackers.

In summary: what would objections be to my writing a sha1() patch?

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200801201218
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFHk4NIvJuQZxSWSsgRA83ZAJ0SIk36sYvLF30q7hog2sBaQU1+LACeOv15
WkDwrzgzHyrwmNFP85plbBA=
=6P4y
-----END PGP SIGNATURE-----

Greg Sabino Mullane wrote:

> I also realize that SHA1 is not a great solution these days either,
> but I'd at least like to see a discussion on moving Postgres to
> somewhere between "only has md5()" and "all pg_crypto functions inside
> core", even if it only means a handful of SHA functions. Moving this
> over to -hackers.
>
> In summary: what would objections be to my writing a sha1() patch?

Isn't sha1 considered broken for some uses anyway? Perhaps if you're
going to do that it would make sense to move the whole pgcrypto/sha2.c
stuff to core, I think.

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

On Sun, Jan 20, 2008 at 05:24:11PM -0000, Greg Sabino Mullane wrote:
> It sure would be nice to have this in core. Yeah, there's pgcrypto,
> but it's a bit overkill for people who simply want to do a SHA1,
> especially when they see we already have a md5().

md5() was added with the following commit message:

> Attached are two small patches to expose md5 as a user function --
> including documentation and regression test mods. It seemed small and
> unobtrusive enough to not require a specific proposal on the hackers
> list -- but if not, let me know and I'll make a pitch. Otherwise, if
> there are no objections please apply.

http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/varlena.c#rev1.94

> In summary: what would objections be to my writing a sha1() patch?

There wasn't any discussion about it last time. It does seem a bit
wierd to support one but not the other. It's also interesting to note
that the implementation in the backed is commented with:

> I do not expect this file to be used for general purpose MD5'ing of
> large amounts of data, only for generating hashed passwords from
> limited input.

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Those who make peaceful revolution impossible will make violent revolution inevitable.
> -- John F Kennedy

Alvaro Herrera wrote:
> Greg Sabino Mullane wrote:
>
>> I also realize that SHA1 is not a great solution these days either,
>> but I'd at least like to see a discussion on moving Postgres to
>> somewhere between "only has md5()" and "all pg_crypto functions inside
>> core", even if it only means a handful of SHA functions. Moving this
>> over to -hackers.
>>
>> In summary: what would objections be to my writing a sha1() patch?
>
> Isn't sha1 considered broken for some uses anyway? Perhaps if you're
> going to do that it would make sense to move the whole pgcrypto/sha2.c
> stuff to core, I think.

IIRC not anymore than md5, which we already do...

That said, it would make sense to include sha1() for compatibility
reasons and a stronger sha for people that need something better.

//Magnus

"Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
> In summary: what would objections be to my writing a sha1() patch?

Mainly that no one else is dissatisfied with the current split between
core and pgcrypto.

The only reason md5() is in core is to support encryption of passwords
in pg_shadow. There are good reasons not to have any more crypto
capability in core than we absolutely have to; mainly to do with
benighted laws in some countries.

regards, tom lane

Martijn van Oosterhout wrote:
>> In summary: what would objections be to my writing a sha1() patch?
>
> There wasn't any discussion about it last time. It does seem a bit
> wierd to support one but not the other. It's also interesting to note
> that the implementation in the backed is commented with:

I proposed md5 without sha1 because we already had md5 code in the
backend, and we did not have sha1 (and still don't). At the time I was
afraid that if I proposed sha1 as well it would become a debate and we
would have ended up with neither.

Personally I'm in favor of having sha1 and one or more of the newer
replacements in the backend. I'd also like to see HMAC built in. But I
think we need to be careful about running afoul of various export
regulations. Keeping the crypto stuff separate allows distributions to
leave the crypto out if they need to. Perhaps cryptographic hashes/HMAC
are not an issue though. Anyone know?

Joe

On Sun, Jan 20, 2008 at 01:42:21PM -0500, Tom Lane wrote:
> "Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
> > In summary: what would objections be to my writing a sha1() patch?
>
> Mainly that no one else is dissatisfied with the current split
> between core and pgcrypto.
>
> The only reason md5() is in core is to support encryption of
> passwords in pg_shadow. There are good reasons not to have any more
> crypto capability in core than we absolutely have to; mainly to do
> with benighted laws in some countries.

Is there any country with laws so benighted that they restrict secure
hashing algorithms? Right now, there's a contest between SHA1 and
MD5 as to which one gets broken first, and SHA1 appears to be in the
lead. SHAn for n>1 could preempt the awfulness of losing this race.

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

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

* David Fetter:

> Is there any country with laws so benighted that they restrict secure
> hashing algorithms? Right now, there's a contest between SHA1 and
> MD5 as to which one gets broken first, and SHA1 appears to be in the
> lead. SHAn for n>1 could preempt the awfulness of losing this race.

MD5 is broken in the sense that you can create two or more meaningful
documents with the same hash. This is not currently possible for
SHA-1 (at least no one has publicly demonstrated this capability).
SHA-256 etc. are sufficiently similar to MD5 and SHA-1, so it's not
clear if they add significant additional security.

(Sorry if this is what you've said.)

--
Florian Weimer <fweimer(at)bfk(dot)de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

On 1/20/08, Greg Sabino Mullane <greg(at)turnstep(dot)com> wrote:
> In summary: what would objections be to my writing a sha1() patch?

Well.

If you do start adding hashes to core then _please_ pick a path
that allows having all the standard hashes in advance. That means
both md5 and sha-1, sha2 (4 hashes) and there is also sha-3 in the
horizon.

Basically there seems to be 2 variants:

1) Continue the md5() style: md5(), sha1(), sha224(), sha256(),
sha384(), sha512(), plus another 4 for SHA-3.

2) Move hashing functions from pgcrypto to core. That means
digest() and I would suggest hmac() and crypt() too.
I'm also starting to think it may be worth having hexdigest().

I prefer 2). There is some common infrastructure in pgcrypto,
the hash specific parts can be either split out or rewritten
from scratch, hashes need very small amount of code.

I agree that having all of pgcrypto in core is bit overkill,
so please don't think of it as all-or-nothing affair.

--
marko

Florian Weimer <fweimer(at)bfk(dot)de> writes:
> * David Fetter:
>> Is there any country with laws so benighted that they restrict secure
>> hashing algorithms? Right now, there's a contest between SHA1 and
>> MD5 as to which one gets broken first, and SHA1 appears to be in the
>> lead. SHAn for n>1 could preempt the awfulness of losing this race.

> MD5 is broken in the sense that you can create two or more meaningful
> documents with the same hash.

Note that this isn't actually very interesting for the purpose for
which the md5() function was put into core: namely, hashing passwords
before they are stored in pg_authid. pg_authid is already secured
against viewing by non-superusers, and a superuser can crack into
your database account anyway, nyet? So the only reason we bother
with hashing here is to keep a superuser from finding out your cleartext
password, which might possibly let him crack into non-database services
that you foolishly used the same password for. Therefore, it doesn't
really matter if he can find another password with the same hash ---
that's not guarding against anything interesting.

Of course, if you want to store other sorts of protected stuff in the
database, you might not want md5, but at that point you're a candidate
to use contrib/pgcrypto.

regards, tom lane

* Tom Lane:

>> MD5 is broken in the sense that you can create two or more meaningful
>> documents with the same hash.
>
> Note that this isn't actually very interesting for the purpose for
> which the md5() function was put into core: namely, hashing passwords
> before they are stored in pg_authid.

No doubt about that. But there are checklists out there, and if you
use MD5 at some point, you need to go to some lengths to explain that
it's okay. That's why I can understand the desire to have sha1 easily
available (even though SHA-1 isn't much better, really, and the
difference doesn't actually matter for many application).

It's a bit like justifying that you don't need a virus scanner on your
non-Windows server or database server. 8-P

BTW, I'd like to see MD5/SHA-1 for BYTEA, not just TEXT, and with a
BYTEA return value. Does pgcrypto provide that?
--
Florian Weimer <fweimer(at)bfk(dot)de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

On 1/21/08, Florian Weimer <fweimer(at)bfk(dot)de> wrote:
> BTW, I'd like to see MD5/SHA-1 for BYTEA, not just TEXT, and with a
> BYTEA return value. Does pgcrypto provide that?

Yes.

--
marko

On 1/21/08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > MD5 is broken in the sense that you can create two or more meaningful
> > documents with the same hash.
>
> Note that this isn't actually very interesting for the purpose for
> which the md5() function was put into core: namely, hashing passwords
> before they are stored in pg_authid.

Note: this was bad idea. The function that should have been
added to core would be pg_password_hash(username, password).

Adding md5() lessens incentive to install pgcrypto or push/accept
digest() into core and gives impression there will be sha1(), etc
in the future.

Now users who want to store passwords in database (the most
popular usage) will probably go with md5() without bothering
with pgcrypto. They probably see "Postgres itself uses MD5 too",
without realizing their situation is totally different from
pg_authid one.

It's like we have solution that is ACID-compliant 99% of the time in core,
so why bother with 100% one.

--
marko

Martijn van Oosterhout wrote:
> On Sun, Jan 20, 2008 at 12:21:01AM -0800, Jon Hancock wrote:
>
>> I am trying to port a MySQL db to postgres 8.3rc1.
>> In MySQL I have a function SHA1, which is critical for storing and
>> authenticating passwords.
>> I see some old posts on how to add this function myself. see:
>> http://raveica.comdurav.com/blog/programming/how-do-i-add-sha1-to-postgresql/
>>
>
> It's in contrib in the pgcrypto module. If you use a distribution you
> can usually simply just install it.
>
> http://www.postgresql.org/docs/8.3/static/pgcrypto.html
>
> Have a nice day,
>

After you installed pgcrypto.so, just create a small function:

CREATE OR REPLACE FUNCTION sha1(text) RETURNS text AS '
SELECT
ENCODE(DIGEST($1, ''sha1"),"hex") AS result
' LANGUAGE 'SQL';

--
Regards,

Julio Cesar Sánchez González.

--
Ahora me he convertido en la muerte, destructora de mundos.
Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.

www.sistemasyconectividad.com.mx http://darkavngr.blogspot.com/

I am not thrilled about moving _some_ of pgcrypto into the backend ---
pgcrypto right now seems well designed and if we pull part of it out it
seems it will be less clear than what we have now. Perhaps we just need
to document that md5() isn't for general use and some function in
pgcrypto should be used instead?

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

Marko Kreen wrote:
> On 1/21/08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > MD5 is broken in the sense that you can create two or more meaningful
> > > documents with the same hash.
> >
> > Note that this isn't actually very interesting for the purpose for
> > which the md5() function was put into core: namely, hashing passwords
> > before they are stored in pg_authid.
>
> Note: this was bad idea. The function that should have been
> added to core would be pg_password_hash(username, password).
>
> Adding md5() lessens incentive to install pgcrypto or push/accept
> digest() into core and gives impression there will be sha1(), etc
> in the future.
>
> Now users who want to store passwords in database (the most
> popular usage) will probably go with md5() without bothering
> with pgcrypto. They probably see "Postgres itself uses MD5 too",
> without realizing their situation is totally different from
> pg_authid one.
>
> It's like we have solution that is ACID-compliant 99% of the time in core,
> so why bother with 100% one.
>
> --
> marko
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://postgres.enterprisedb.com

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

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

> I am not thrilled about moving _some_ of pgcrypto into the backend ---
> pgcrypto right now seems well designed and if we pull part of it out it
> seems it will be less clear than what we have now. Perhaps we just need
> to document that md5() isn't for general use and some function in
> pgcrypto should be used instead?

I think looking at this as putting some of pg_crypto into core is looking
at this the wrong way. We are never going to put the whole thing into
core given the current state of cryptography laws, as obviously the
current status of giving users md5() and nothing else is not ideal. What
we're looking for is a middle ground. It seems to me we've narrowed
it down to two questions:

1) Does sha1(), or other hashing algorithms risk running afoul of
cryptography regulations?

I'm 100% sure that sha1() itself is not a problem (it's even a PHP builtin,
and good luck finding a box these days wihout that monstrosity installed).
I highly doubt any of the rest (SHA*, HMAC, etc.) are a problem either:
we're doing a one-way hash, not encrypting data. But common sense and
cryptography have seldom been seen together since the start of the cold war,
so I'll hold my final judgement.

2) Which ones do we include?

Putting sha1() seems a no-brainer, but as Joe points out, why not add all
the rest in at the same time?

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200801281506
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFHnjeJvJuQZxSWSsgRA2fWAKCljvbj5BVaFQ5mEDvckNGhVz6rDgCg0DRc
zaIu/rT1vdDrL61JTsXdIZ8=
=7DKm
-----END PGP SIGNATURE-----

On 1/28/08, Greg Sabino Mullane <greg(at)turnstep(dot)com> wrote:
> > I am not thrilled about moving _some_ of pgcrypto into the backend ---
> > pgcrypto right now seems well designed and if we pull part of it out it
> > seems it will be less clear than what we have now. Perhaps we just need
> > to document that md5() isn't for general use and some function in
> > pgcrypto should be used instead?

I don't think docs will help much. The md5() has broken the
policy of "all crypto in external module" so I'm now thinking
we should just bite the bullet and add digest() to core.

Exctracting the part from pgcrypto is no-brainer, the hashes have
very thin wrapper around them, only thing common with rest of
pgcrypto is error handling, which hashes use the least.

Only thing that needs minor thought is that it would be nice
to access hashes from external module - that means exporting
the find_digest() function to it. Which should be no problem.

> I think looking at this as putting some of pg_crypto into core is looking
> at this the wrong way. We are never going to put the whole thing into
> core given the current state of cryptography laws, as obviously the
> current status of giving users md5() and nothing else is not ideal. What
> we're looking for is a middle ground. It seems to me we've narrowed
> it down to two questions:
>
> 1) Does sha1(), or other hashing algorithms risk running afoul of
> cryptography regulations?
>
> I'm 100% sure that sha1() itself is not a problem (it's even a PHP builtin,
> and good luck finding a box these days wihout that monstrosity installed).
> I highly doubt any of the rest (SHA*, HMAC, etc.) are a problem either:
> we're doing a one-way hash, not encrypting data. But common sense and
> cryptography have seldom been seen together since the start of the cold war,
> so I'll hold my final judgement.
>
> 2) Which ones do we include?
>
> Putting sha1() seems a no-brainer, but as Joe points out, why not add all
> the rest in at the same time?

Considering we have DES and MD5 already, then how can SHA2 be
a problem when SHA1 isnt?

--
marko

* Bruce Momjian:

> I am not thrilled about moving _some_ of pgcrypto into the backend ---
> pgcrypto right now seems well designed and if we pull part of it out it
> seems it will be less clear than what we have now. Perhaps we just need
> to document that md5() isn't for general use and some function in
> pgcrypto should be used instead?

Yes, that would probably help those folks doing checklist-based
security audits.

--
Florian Weimer <fweimer(at)bfk(dot)de>
BFK edv-consulting GmbH http://www.bfk.de/
Kriegsstraße 100 tel: +49-721-96201-1
D-76133 Karlsruhe fax: +49-721-96201-99

There isn't enough agreement to move some things from pgcrypto to the
core so this thread is being removed from the patch queue.

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

Bruce Momjian wrote:
>
> I am not thrilled about moving _some_ of pgcrypto into the backend ---
> pgcrypto right now seems well designed and if we pull part of it out it
> seems it will be less clear than what we have now. Perhaps we just need
> to document that md5() isn't for general use and some function in
> pgcrypto should be used instead?
>
> ---------------------------------------------------------------------------
>
> Marko Kreen wrote:
> > On 1/21/08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > > MD5 is broken in the sense that you can create two or more meaningful
> > > > documents with the same hash.
> > >
> > > Note that this isn't actually very interesting for the purpose for
> > > which the md5() function was put into core: namely, hashing passwords
> > > before they are stored in pg_authid.
> >
> > Note: this was bad idea. The function that should have been
> > added to core would be pg_password_hash(username, password).
> >
> > Adding md5() lessens incentive to install pgcrypto or push/accept
> > digest() into core and gives impression there will be sha1(), etc
> > in the future.
> >
> > Now users who want to store passwords in database (the most
> > popular usage) will probably go with md5() without bothering
> > with pgcrypto. They probably see "Postgres itself uses MD5 too",
> > without realizing their situation is totally different from
> > pg_authid one.
> >
> > It's like we have solution that is ACID-compliant 99% of the time in core,
> > so why bother with 100% one.
> >
> > --
> > marko
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://postgres.enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Was that really the conclusion? My memory of this thread showed that
most people who actually deal with hashes and cryptography *wanted* a
SHA based hash in core (because our users ask for it!) and the only
disagreement was in *what* should be included.

//Magnus

Bruce Momjian wrote:
>
> There isn't enough agreement to move some things from pgcrypto to the
> core so this thread is being removed from the patch queue.
>
> ---------------------------------------------------------------------------
>
> Bruce Momjian wrote:
> >
> > I am not thrilled about moving _some_ of pgcrypto into the backend
> > --- pgcrypto right now seems well designed and if we pull part of
> > it out it seems it will be less clear than what we have now.
> > Perhaps we just need to document that md5() isn't for general use
> > and some function in pgcrypto should be used instead?
> >
> > ---------------------------------------------------------------------------
> >
> > Marko Kreen wrote:
> > > On 1/21/08, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > > > > MD5 is broken in the sense that you can create two or more
> > > > > meaningful documents with the same hash.
> > > >
> > > > Note that this isn't actually very interesting for the purpose
> > > > for which the md5() function was put into core: namely, hashing
> > > > passwords before they are stored in pg_authid.
> > >
> > > Note: this was bad idea. The function that should have been
> > > added to core would be pg_password_hash(username, password).
> > >
> > > Adding md5() lessens incentive to install pgcrypto or push/accept
> > > digest() into core and gives impression there will be sha1(), etc
> > > in the future.
> > >
> > > Now users who want to store passwords in database (the most
> > > popular usage) will probably go with md5() without bothering
> > > with pgcrypto. They probably see "Postgres itself uses MD5 too",
> > > without realizing their situation is totally different from
> > > pg_authid one.
> > >
> > > It's like we have solution that is ACID-compliant 99% of the time
> > > in core, so why bother with 100% one.
> > >
> > > --
> > > marko
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 4: Have you searched
> > > our list archives?
> > >
> > > http://archives.postgresql.org
> >
> > --
> > Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> > EnterpriseDB
> > http://postgres.enterprisedb.com
> >
> > + If your life is a hard drive, Christ can be your backup. +
> >
> > ---------------------------(end of
> > broadcast)--------------------------- TIP 5: don't forget to
> > increase your free space map settings
>
> --
> Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
> EnterpriseDB http://enterprisedb.com
>
> + If your life is a hard drive, Christ can be your backup. +
>
> --
> Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-hackers

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

> There isn't enough agreement to move some things from pgcrypto
> to the core so this thread is being removed from the patch queue.

I don't agree that we should just close discussion. Nobody seems happy
with the status quo, which is that we provide md5 but not sha1, and
are thus encouraging people to use md5 everywhere. At the very least,
I think we need to add sha1. Adding sha* would be better, and adding
other hashes would be better still (and make PG a better product, in
my opinion: having things builtin vs. contrib is a huge distinction).

I'd also like to emphasize that this is not a pgcrypto issue: while
it provides the same functionality that this proposal does, so does
creating a Pl/Perl function, which is the route I usually go, as it
is much easier and portable. So I see this as adding missing features
to core. We will obviously never replace pgcrypto entirely, due to the
silly state of encryption legislation.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
End Point Corporation
PGP Key: 0x14964AC8 200804020906
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkfzhMQACgkQvJuQZxSWSsiTCwCgvauRh/hqOK0BEMEPoKXhzCf5
+GkAn0ma+cZy3fas1Y/uQ0Zf8WAqfLo8
=tTlK
-----END PGP SIGNATURE-----

"Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
> I don't agree that we should just close discussion. Nobody seems happy
> with the status quo, which is that we provide md5 but not sha1,

There may be a few people who are unhappy, but the above claim seems
vastly overblown. md5 is sufficient for the purpose it is intended
for in core postgres (namely, obscuring the true source text of
passwords), and if you have needs much beyond that you'll soon be
installing pgcrypto anyway.

regards, tom lane

Tom Lane wrote:
> "Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:
> > I don't agree that we should just close discussion. Nobody seems
> > happy with the status quo, which is that we provide md5 but not
> > sha1,
>
> There may be a few people who are unhappy, but the above claim seems
> vastly overblown. md5 is sufficient for the purpose it is intended
> for in core postgres (namely, obscuring the true source text of
> passwords), and if you have needs much beyond that you'll soon be
> installing pgcrypto anyway.

I think that claim is completely incorrect.

A lot of people use the md5() function in PostgreSQL today to hash
the passwords for the users of whatever webbapp they are running. It
only uses one account to connect to PostgreSQL and handles the rest of
the auth elsewhere in the app. These users would like to have sha1
(and/or other securer hashes). And they would like it in -core, because
their hosting company don't install the contrib modules.

//Magnus

On Wed, Apr 02, 2008 at 01:07:01PM -0000, Greg Sabino Mullane wrote:
>
> > There isn't enough agreement to move some things from pgcrypto to
> > the core so this thread is being removed from the patch queue.
>
> I don't agree that we should just close discussion. Nobody seems
> happy with the status quo, which is that we provide md5 but not
> sha1, and are thus encouraging people to use md5 everywhere. At the
> very least, I think we need to add sha1. Adding sha* would be
> better, and adding other hashes would be better still (and make PG a
> better product, in my opinion: having things builtin vs. contrib is
> a huge distinction).
>
> I'd also like to emphasize that this is not a pgcrypto issue: while
> it provides the same functionality that this proposal does, so does
> creating a Pl/Perl function, which is the route I usually go, as it
> is much easier and portable. So I see this as adding missing
> features to core. We will obviously never replace pgcrypto entirely,
> due to the silly state of encryption legislation.

Just exactly which encryption legislation are we talking about here?

I know there was some fuss about this issue back in the early 1990s,
but that was many, many law changes and court cases ago, world-wide.
It's far from clear to me that there's any reason other than inertia
not to roll the crypto stuff into the core functionality and have
done.

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

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

David Fetter <david(at)fetter(dot)org> writes:
> Just exactly which encryption legislation are we talking about here?

> I know there was some fuss about this issue back in the early 1990s,
> but that was many, many law changes and court cases ago, world-wide.
> It's far from clear to me that there's any reason other than inertia
> not to roll the crypto stuff into the core functionality and have
> done.

This seems a very USA-centric view of the problem. It's true that
the US export regulations no longer pose much of an issue for us
(but who's to say they might not become tighter again in future?);
the problem is there are lots of places where the laws are still
strict.

regards, tom lane

On Wed, Apr 02, 2008 at 12:27:15PM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > Just exactly which encryption legislation are we talking about
> > here?
>
> > I know there was some fuss about this issue back in the early
> > 1990s, but that was many, many law changes and court cases ago,
> > world-wide. It's far from clear to me that there's any reason
> > other than inertia not to roll the crypto stuff into the core
> > functionality and have done.
>
> This seems a very USA-centric view of the problem. It's true that
> the US export regulations no longer pose much of an issue for us
> (but who's to say they might not become tighter again in future?);
> the problem is there are lots of places where the laws are still
> strict.

Which places, and what laws?

Re: compulsively attempting to comply with every law everywhere, let's
recall that a certain Search! engine company managed to get itself in
a morally pretty reprehensible spot by "following the laws" of an
oppressive regime. We needn't do the same.

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

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

Hi Tom,

md5 is not being recommended anywhere because it contains hash collision. Therefore either it should be replaced with SHA1 or any other good hash algorithm or taken out of core completely. md5 in core is worthless now.I am not using it in my application. I am using SHA1 in client/web tier for password hashing.

Would replacing md5 with SHA1 in core involve much work?

Sanjay Sharma> To: greg(at)turnstep(dot)com> CC: pgsql-hackers(at)postgresql(dot)org> Subject: Re: [HACKERS] [GENERAL] SHA1 on postgres 8.3 > Date: Wed, 2 Apr 2008 11:38:31 -0400> From: tgl(at)sss(dot)pgh(dot)pa(dot)us> > "Greg Sabino Mullane" <greg(at)turnstep(dot)com> writes:> > I don't agree that we should just close discussion. Nobody seems happy> > with the status quo, which is that we provide md5 but not sha1,> > There may be a few people who are unhappy, but the above claim seems> vastly overblown. md5 is sufficient for the purpose it is intended> for in core postgres (namely, obscuring the true source text of> passwords), and if you have needs much beyond that you'll soon be> installing pgcrypto anyway.> > regards, tom lane> > -- > Sent via pgsql-hackers mailing list (pgsql-hackers(at)postgresql(dot)org)> To make changes to your subscription:> http://www.postgresql.org/mailpref/pgsql-hackers
_________________________________________________________________
Tried the new MSN Messenger? It’s cool! Download now.
http://messenger.msn.com/Download/Default.aspx?mkt=en-in

David Fetter wrote:
> On Wed, Apr 02, 2008 at 12:27:15PM -0400, Tom Lane wrote:
>
>> David Fetter <david(at)fetter(dot)org> writes:
>>
>>> Just exactly which encryption legislation are we talking about
>>> here?
>>>
>>> I know there was some fuss about this issue back in the early
>>> 1990s, but that was many, many law changes and court cases ago,
>>> world-wide. It's far from clear to me that there's any reason
>>> other than inertia not to roll the crypto stuff into the core
>>> functionality and have done.
>>>
>> This seems a very USA-centric view of the problem. It's true that
>> the US export regulations no longer pose much of an issue for us
>> (but who's to say they might not become tighter again in future?);
>> the problem is there are lots of places where the laws are still
>> strict.
>>
>
> Which places, and what laws?
>
http://rechten.uvt.nl/koops/cryptolaw/cls-sum.htm

(Info only - I have not spent time considering the issue at hand thus,
counter to net tradition, offer no opinion.)

Cheers,
Steve

David Fetter <david(at)fetter(dot)org> writes:
> Re: compulsively attempting to comply with every law everywhere, let's
> recall that a certain Search! engine company managed to get itself in
> a morally pretty reprehensible spot by "following the laws" of an
> oppressive regime. We needn't do the same.

We aren't actually doing any such thing. We are just making sure that
our distribution is split up in such a way that if someone *else* needs
to comply with such a law, it's not impractical to do so.

regards, tom lane

On Wed, Apr 02, 2008 at 12:49:38PM -0400, Tom Lane wrote:
> David Fetter <david(at)fetter(dot)org> writes:
> > Re: compulsively attempting to comply with every law everywhere,
> > let's recall that a certain Search! engine company managed to get
> > itself in a morally pretty reprehensible spot by "following the
> > laws" of an oppressive regime. We needn't do the same.
>
> We aren't actually doing any such thing. We are just making sure
> that our distribution is split up in such a way that if someone
> *else* needs to comply with such a law, it's not impractical to do
> so.

That cat is already out of the bag with every Linux distribution.
There's just no point in our going three sides around the square in
order to have someone, somewhere claim that our tarball isn't
violating their law.

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

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

David Fetter <david(at)fetter(dot)org> writes:
> That cat is already out of the bag with every Linux distribution.
> There's just no point in our going three sides around the square in
> order to have someone, somewhere claim that our tarball isn't
> violating their law.

[shrug...] It's not exactly terribly complicated to keep this stuff
in a contrib module.

regards, tom lane

sanjay sharma <sanksh(at)hotmail(dot)com> writes:
> md5 is not being recommended anywhere because it contains hash
> collision.

For the purposes we are using it for, that's just about 100% irrelevant.

> Would replacing md5 with SHA1 in core involve much work?

Yes, it would be a tremendous problem, because the use of md5 is part of
our password protocol. We'd have to change client-side code in sync
with the servers to do that.

regards, tom lane

Am Mittwoch, 2. April 2008 schrieb David Fetter:
> That cat is already out of the bag with every Linux distribution.

There are other operating systems besides Linux.

sanjay sharma wrote:
> Hi Tom,
>
> md5 is not being recommended anywhere because it contains hash
> collision. Therefore either it should be replaced with SHA1 or any
> other good hash algorithm or taken out of core completely. md5 in core
> is worthless now.I am not using it in my application. I am using SHA1
> in client/web tier for password hashing.
>
> Would replacing md5 with SHA1 in core involve much work?

sanjay - please do not top-answer, especially when others have put their
answers below.

MD5 is not broken for purposes that would require a pre-image attack,
AIUI. That means there is a whole series of uses for which it is still
quite OK, including password hashing.

That said, there might well be a reason for including a
collision-resistant hash function in core without including the whole of
pg_crypto.

cheers

andrew

On Wed, Apr 02, 2008 at 07:16:53PM +0200, Peter Eisentraut wrote:
> Am Mittwoch, 2. April 2008 schrieb David Fetter:
> > That cat is already out of the bag with every Linux distribution.
>
> There are other operating systems besides Linux.

And the cat's out of the bag there, too. Have you tried to get a
system that doesn't include strong crypto running any time recently?

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

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

Am Mittwoch, 2. April 2008 schrieb sanjay sharma:
> md5 is not being recommended anywhere because it contains hash collision.
> Therefore either it should be replaced with SHA1 or any other good hash
> algorithm or taken out of core completely. md5 in core is worthless now.I
> am not using it in my application. I am using SHA1 in client/web tier for
> password hashing.
> Would replacing md5 with SHA1 in core involve much work?

The vulnerabilities that exist for MD5 and SHA1 involve finding two random
input strings that create the same hash values. This is possible for MD5
*and* SHA1 now, so asking for SHA1 to replace MD5 is completely pointless.
What is not possible with either MD5 or SHA1 is finding an input string that
creates the same hash value as a given input string (except by googling, but
that affects all algorithms). So using MD5 for encrypting passwords or
digesting known data values or tarballs can be considered secure at the
moment.

If you are dealing with certificate infrastructures, where the hash collision
vulnerability described above might be relevant, you are certainly going to
use some library such as openssl, and those have already moved away from
using MD5 and SHA1 anyway.

David Fetter wrote:
> And the cat's out of the bag there, too. Have you tried to get a
> system that doesn't include strong crypto running any time recently?

Well yes, I have recently been involved in a large deployment of such systems.
The details are a bit too involved for this thread, but the notion expressed
elsewhere in this thread to make it easy to separate or rip out the different
parts are exactly what facilitated that project. Now I am not saying that we
must keep pgcrypto away from the core code forever for the benefit of these
specific and well-funded external requirements. But these requirements do
exist, and I haven't seen a good reason _for_ moving pgcrypto in this thread.
Note that the original post actually wanted an sha1 function for MySQL
compatibility.

Magnus Hagander wrote:
> I think that claim is completely incorrect.
>
> A lot of people use the md5() function in PostgreSQL today to hash
> the passwords for the users of whatever webbapp they are running. It
> only uses one account to connect to PostgreSQL and handles the rest of
> the auth elsewhere in the app. These users would like to have sha1
> (and/or other securer hashes). And they would like it in -core, because
> their hosting company don't install the contrib modules.
>

Hi Magnus:

I don't think this is a compelling argument, and I mostly agree with Tom.

PHP, Perl and Java are just three languages at the tip of my tongue that
have built in support for MD5 and SHA1, and in all cases I can think of
in a few seconds (I might be missing something?), it's far more
desirable to do the MD5 / SHA1 in the language. If the document being
encoded is large, doing it in the client is more efficient from a
network transport perspective, as well as allowing ensuring that
performance cost is on the web side, not the database side. If the text
to be encoded requires security, then transmitting the password in clear
text to the server only to be MD5 / SHA1 summed is not a great solution,
as it involves transmission of the password. In both cases, I would do
it client side, inside the web app. So, I believe your argument that web
apps need it is faulty.

I think a legitimate use would involve around using such a function in
pl/pgsql. I can't think of a case where I've ever needed to do that.

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>

Magnus Hagander <magnus(at)hagander(dot)net> writes:
> ... These users would like to have sha1
> (and/or other securer hashes). And they would like it in -core, because
> their hosting company don't install the contrib modules.

That line of argument could be used to justify putting anything and
everything in core. I think that our extensible architecture is an
important feature and one we should not hesitate to use to the fullest.

There is clearly work that could/should be done to improve it, such
as having better system support for packages or modules or whatever
you want to call them; and maybe we also need some marketing-type
work to encourage people such as hosting companies to allow access
to non-core packages. But allowing arguments such as the above to
drive our design is not the way to go.

regards, tom lane

Tom Lane wrote:
> There is clearly work that could/should be done to improve it, such
> as having better system support for packages or modules or whatever
> you want to call them; and maybe we also need some marketing-type
> work to encourage people such as hosting companies to allow access
> to non-core packages.
>
>

Funny you should mention that. I was just thinking the other day that I
needed to re-raise the question of getting rid of contrib, which has
long been a horrible misnomer, and calling its contents "The PostgreSQL
Standard Modules". Maybe we could rename the directory "modules". IIRC
that was the consensus name last time this topic was discussed.

We've already vastly improved their usefulness by adding documentation.
Let's take the next jump and start advertising them as exemplars of our
extensibility.

cheers

andrew

On Wed, Apr 02, 2008 at 05:09:14PM -0400, Andrew Dunstan wrote:
> Standard Modules". Maybe we could rename the directory "modules". IIRC

This seems like an easy and practical answer.

A

Magnus Hagander wrote:
> Was that really the conclusion? My memory of this thread showed that
> most people who actually deal with hashes and cryptography *wanted* a
> SHA based hash in core (because our users ask for it!) and the only
> disagreement was in *what* should be included.

Sorry if it looked like I was jumping to conclusions on this. I was
reporting the summary of the comments I had from the patch queue. Based
on the subsequent discussion, it seems it was accurate none-the-less.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

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

Andrew Dunstan wrote:
> Tom Lane wrote:
>> as having better system support for packages or modules or whatever
>> you want to call them; and maybe we also need some marketing-type....
>
> ...re-raise the question of getting rid of contrib...
> "The PostgreSQL Standard Modules".

While renaming, could we go one step further and come up with a
clear definition of what it takes for something to qualify as
a module? In particular I think standardizing the installation
would go a long way to letting packagers automate the installation
of modules from pgfoundry.

I think it'd be especially cool if one could one-day have a command

pg_install_module [modulename] -d [databasename]

and it would magically get (or verify that it had) the latest
version from pgfoundry; compile it (if needed) and install it
in the specified database.

The closest analogy to what I'm thinking is the perl CPAN or ruby gems.

Ron Mayer wrote:
> Andrew Dunstan wrote:
>> Tom Lane wrote:
>>> as having better system support for packages or modules or whatever
>>> you want to call them; and maybe we also need some marketing-type....
>>
>> ...re-raise the question of getting rid of contrib...
>> "The PostgreSQL Standard Modules".
>
> While renaming, could we go one step further and come up with a
> clear definition of what it takes for something to qualify as
> a module? In particular I think standardizing the installation
> would go a long way to letting packagers automate the installation
> of modules from pgfoundry.
>
> I think it'd be especially cool if one could one-day have a command
>
> pg_install_module [modulename] -d [databasename]
>
> and it would magically get (or verify that it had) the latest
> version from pgfoundry; compile it (if needed) and install it
> in the specified database.
>
> The closest analogy to what I'm thinking is the perl CPAN or ruby gems.
>

Yes, and the CPAN analogy that has been in several minds, but it only
goes so far. Perl and Ruby are languages - Postgres is a very different
animal.

We do in fact have some support for building / installing some modules
in a standard way. It's called pgxs and it is used by quite a number of
existing modules.

One thing that might be worth looking at is an install command at the
SQL level, so the "INSTALL foo" would run the install script for the foo
module in the current database, assuming it's in the standard location.

We don't have a central repository of non-standard modules, like CPAN,
and so of course no facility for fetching / building / installing them.

Not all modules fit a single pattern, either. There are addon languages,
types, and function libraries, as we all as utilities that are not
installed in the database at all.

Finally, setting up modules so they can be built for Windows, especially
using MSVC, will probably be quite a challenge.

So if someone wants to make a start on any of this I'm sure we would all
listen up.

cheers

andrew

Andrew Dunstan wrote:
>> I think it'd be especially cool if one could one-day have a command
>>
>> pg_install_module [modulename] -d [databasename]
>
> Yes, and the CPAN analogy that has been in several minds, but it only
> goes so far. Perl and Ruby are languages - Postgres is a very different
> animal.

Sure - but the benefits of standardizing installers for optional
components seems to apply the same for both.

> We do in fact have some support for building / installing some modules
> in a standard way. It's called pgxs and it is used by quite a number of
> existing modules.

Cool. Seems to handle at least quite a bit of the building part of
standardized modules.

> One thing that might be worth looking at is an install command at the
> SQL level, so the "INSTALL foo" would run the install script for the foo
> module in the current database, assuming it's in the standard location.

I'm guessing that this would be harder to add various
options (install/ignore dependancies ; specify a different source
web site) that a standard installer would like to have.

> We don't have a central repository of non-standard modules, like CPAN,
> and so of course no facility for fetching / building / installing them.

Seems that could easily be improved in a number of ways.

* The installer could specify the source. For example
pg_install_module postgis -source http://www.refractions.net
in exactly the same way ruby uses
gem install rails –source http://gems.rubyonrails.org

* pgfoundry could provide a repository of installable modules
for projects hosted there.

* perhaps pgfoundry could even have a section where it points
to installers on third party sites?

> Not all modules fit a single pattern, either. There are addon languages,
> types, and function libraries, as we all as utilities that are not
> installed in the database at all.

Agreed. Such a mechanism would only really apply for things
that are installed in the database. But from an end user's
point of view, installing functions, index types, languages,
data types, etc all see to fit the "pg_install postgis -d mydb",
"pg_install pl_ruby -d mydb", etc. pattern pretty well.

> Finally, setting up modules so they can be built for Windows, especially
> using MSVC, will probably be quite a challenge.

Indeed. Seems ruby gems give you the option of installing a "ruby"
version or a "windows" version that I'm guessing has pre-compiled
object files.

> So if someone wants to make a start on any of this I'm sure we would all
> listen up.
I'm happy to try, though might need pointing in the right directions.

On Wed, 02 Apr 2008 20:15:49 -0400
Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> > I think it'd be especially cool if one could one-day have a command
> >
> > pg_install_module [modulename] -d [databasename]
> >
> > and it would magically get (or verify that it had) the latest
> > version from pgfoundry; compile it (if needed) and install it
> > in the specified database.
> >
> > The closest analogy to what I'm thinking is the perl CPAN or ruby gems.

Check out NetBSD pkgsrc as a model. It is very flexible. One nice
thing would be the ability to specify where the packages are rather
than always insisting that they be on pgfoundry.

> Yes, and the CPAN analogy that has been in several minds, but it only
> goes so far. Perl and Ruby are languages - Postgres is a very different
> animal.

So the underlying struture needs to keep that in mind. Overall though
I don't think that what is being installed to changes much. The basics
remain the same - define the package with latest version, download if
necessary,check that the source package is the correct, tested one,
build, install, register.

There are some special considerations for PostgreSQL but I think that
the fact that there are unsolved problems shouldn't stop us from
solving them.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

Andrew Dunstan wrote:
>
>
> Ron Mayer wrote:
> > Andrew Dunstan wrote:
> >> Tom Lane wrote:
> >>> as having better system support for packages or modules or
> >>> whatever you want to call them; and maybe we also need some
> >>> marketing-type....
> >>
> >> ...re-raise the question of getting rid of contrib...
> >> "The PostgreSQL Standard Modules".
> >
> > While renaming, could we go one step further and come up with a
> > clear definition of what it takes for something to qualify as
> > a module? In particular I think standardizing the installation
> > would go a long way to letting packagers automate the installation
> > of modules from pgfoundry.
> >
> > I think it'd be especially cool if one could one-day have a command
> >
> > pg_install_module [modulename] -d [databasename]
> >
> > and it would magically get (or verify that it had) the latest
> > version from pgfoundry; compile it (if needed) and install it
> > in the specified database.
> >
> > The closest analogy to what I'm thinking is the perl CPAN or ruby
> > gems.
> >
>
> Yes, and the CPAN analogy that has been in several minds, but it only
> goes so far. Perl and Ruby are languages - Postgres is a very
> different animal.
>
> We do in fact have some support for building / installing some
> modules in a standard way. It's called pgxs and it is used by quite a
> number of existing modules.

On Windows we also have the StackBuilder application which is used for
installation of binary modules.

//Magnus

I had some thoughts about similar issues when looking at what it would
take to make pl/java yum-installable. The end goal was to be able to
say e.g. yum install pljava; echo "create language pljava;" | psql
mydb. Currently there's a non-trivial install process involving
running an sql script and java class.

My idea was to have a createlang_init kind of function that could be
called when installing a language to set up appropriate functions,
tables etc. There would be a similar function to clean up when
dropping the lang.

On Thu, Apr 3, 2008 at 6:12 AM, Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> wrote:
> Agreed. Such a mechanism would only really apply for things
> that are installed in the database. But from an end user's
> point of view, installing functions, index types, languages,
> data types, etc all see to fit the "pg_install postgis -d mydb",
> "pg_install pl_ruby -d mydb", etc. pattern pretty well.
>

Well, there are a couple of major differences. Firstly cpan, gem etc
are able to install all required dependencies themselves, at least
where no native compilation is required, because they are basically
their own platform. PG libs more or less require a build environment.

Secondly, and more importantly, module installation for those
environments happens once and is global; installation of native libs
for pgsql is different to instllation in a database. What happens in
the above scenario when the postgis libs are already installed? And
what about cleanup? Also, it would seem that such an install process
requires the server to be running - so much for packaging as
RPMs/debs/win32 installer etc.

I think a better solution would be to have a pg_install be a
distribution mechanism capable of installing binaries / scripts /
other resources, but have pgsql itself handle module installation into
a particular database. I'm thinking a "CREATE MODULE foo;" kind of
thing that would be capable of finding either a module install script
or a foo_init() function in libfoo.so/foo.dll. Similarly for cleanup,
so cleanup isn't dependent on pg_install lying around or the version
that was install still being the latest when pg_install looks for an
uninstall script.

This would allow modules to be installed site-wide but optionally
created / dropped from specific databases in a much saner manner, and
standard pgsql permissions could apply to installation of modules. It
would also allow creation of rpms etc that can be shipped by a
distribution, and then enabled by the user by calling the appropriate
command.

>> Finally, setting up modules so they can be built for Windows,
especially using MSVC, will probably be quite a challenge.
>>
>
> Indeed. Seems ruby gems give you the option of installing a "ruby"
> version or a "windows" version that I'm guessing has pre-compiled
> object files.

Yeah, setting up Cygwin to build postgres is a pain (or was when I
last did so). If we're serious about setting up a central repository,
we should consider having a virtualized windows machine capable of
building binaries for the modules that people upload.

Cheers

Tom

> > The closest analogy to what I'm thinking is the perl CPAN
> or ruby gems.

I think this is more a developer thing. I don't think an ISP would want
all that automagic (and certainly does not do that for joe user).

> One thing that might be worth looking at is an install command at the
> SQL level, so the "INSTALL foo" would run the install script for the
foo
> module in the current database, assuming it's in the standard
location.

Yes.

> We don't have a central repository of non-standard modules, like CPAN,

> and so of course no facility for fetching / building / installing
them.

I think that is not a problem, since the service providers would rather
want
readily fetched built and regression tested modules, not anything fancy
or magic.
The readily built modules would simply be part of their binary
distibution.

Andreas

Zeugswetter Andreas OSB SD wrote:
>>> The closest analogy to what I'm thinking is the perl CPAN
>>>
>> or ruby gems.
>>
>
> I think this is more a developer thing. I don't think an ISP would want
> all that automagic (and certainly does not do that for joe user).
>

I think you are missing an essential part of the vision. This is not
just targetted at developers. Binary distro authors typically include a
huge number of CPAN modules as well as core Perl, and ISPs typically
install them. We want to have something CPAN-like so we can get the same
effect.

At any rate, that's a bit blue sky right now. I haven't seen any
disagreement with our kissing "contrib" goodbye as a name, so let's work
on that. Unfortunately, that's going to involve a bit of pain, including
in the buildfarm, whose client relies on the name. I'll go to work on
fixing that, and we can get a new version out so when we make the switch
the buildfarm doesn't go dark.

cheers

andrew

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

> At any rate, that's a bit blue sky right now. I haven't seen any
> disagreement with our kissing "contrib" goodbye as a name, so let's work
> on that. Unfortunately, that's going to involve a bit of pain,

Yes, I'm not sure I see the point of it. It's got a bad name, but changing
it is just putting lipstick on a pig. End users don't know, and don't care,
about contrib. Sysadmins and casual DBAs only care what they can "yum install".
That only leaves packagers and hard-core developers, both of whom already
know how contrib works.

Not that I wouldn't want to see some of the good ideas raised in this thread
explored. In particular, I'd love to see some of the more standard contrib
things installable as simple as:

postgres=# INSTALL earthdistance;

Right now contrib is a real catch-all of various things; it would be nice to
categorize them somehow. And by categorize, I emphatically do NOT mean
move to pgfoundry, which is pretty much a kiss of death.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200804030953
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkf04VUACgkQvJuQZxSWSsjmPACeMoaDTXgjqXBKlthPad6D3sWV
qooAn2y0cwnafYwnGonGBEq/6IAbXzlF
=SO7r
-----END PGP SIGNATURE-----

* Greg Sabino Mullane <greg(at)turnstep(dot)com> [080403 09:54]:

> Right now contrib is a real catch-all of various things; it would be nice to
> categorize them somehow. And by categorize, I emphatically do NOT mean
> move to pgfoundry, which is pretty much a kiss of death.

But that begs the question of *why* it's a kiss of death?

For instance, in "perl land", having something in "CPAN" and not in
"perl core" is most certainly *not* a kiss of death? Why is it so
different for PostgreSQL?

Is it because the infrastructure behind CPAN is much better than that
behind pgfoundry?

Or is it because CPAN is better "vetted" and "organized" than pgfoundry?

Or is it because the projects that go into CPAN are better quality and
projects in pgroundry?

Or is it something else?

I'm pretty sure the answers to the above questions aren't all yes...

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

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

> That line of argument could be used to justify putting anything and
> everything in core. I think that our extensible architecture is an
> important feature and one we should not hesitate to use to the fullest.

I agree, but part of the problem here is that pgcrypto is extraordinary
overkill for people who just want a better hash function than md5.

Our extensible architecture is a feature, but our
contrib/packaging/gborg/pgfoundry situation is a mess. It's only the
efforts of the distro package maintainers that's kept things from
being even worse.

Here's what it boils down to for me:

1) Postgres has the md5() function, which is not ever getting removed.

2) Since it exists, people are using it.

3) Not having a builtin sha1() means we are less compatible with other
databases. Fair? Perhaps not. But requiring an installation of
pgcrypto, or plperl, is another hurdle to be cleared by people porting
and using applications with Postgres as a backend.

4) We're also encouraging the use of md5() by making it the only option.
Yes, we can talk about why people *shouldn't* use it for this purpose
or that, but they will.

5) It seems unwise to go through the trouble of just adding sha1(), when
we could easily add some better hashes, which has the nice side effect
of making us stand out more and push the envelope, rather than play follow
the leader, as was mentioned at PGCon East.

- --
Greg Sabino Mullane greg(at)turnstep(dot)com
PGP Key: 0x14964AC8 200804031020
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iEYEAREDAAYFAkf06AIACgkQvJuQZxSWSshJGACcDlE/sUBTJNx36zMW7C9G2FqE
n0QAoLOj50gGura/g2JCk+3sFxR0cLb1
=K8sl
-----END PGP SIGNATURE-----

Greg Sabino Mullane wrote:
>> At any rate, that's a bit blue sky right now. I haven't seen any
>> disagreement with our kissing "contrib" goodbye as a name, so let's work
>> on that. Unfortunately, that's going to involve a bit of pain,
>>
>
> Yes, I'm not sure I see the point of it. It's got a bad name, but changing
> it is just putting lipstick on a pig. End users don't know, and don't care,
> about contrib. Sysadmins and casual DBAs only care what they can "yum install".
> That only leaves packagers and hard-core developers, both of whom already
> know how contrib works.
>
>
>
If this were at all true we would not not have seen the complaints from
people along the lines of "My ISP won't install contrib". But we have,
and quite a number of times. We have concrete evidence that calling it
contrib actually works against us.

It's also worth pointing out that WE HAVE HAD THIS DISCUSSION BEFORE.
Sometimes I get rather frustrated by our habit of turning time into a
circle and running Groundhog Day.

cheers

andrew

Greg Sabino Mullane wrote:
> 4) We're also encouraging the use of md5() by making it the only option.
> Yes, we can talk about why people *shouldn't* use it for this purpose
> or that, but they will.
>

There is always the Java route - internal classes have package-scope
constructors to specifically prevent them from being accidentally used
(and relied on).

I prefer the "let them use it, but warn them not to have expectations"
route, which is what PostgreSQL is doing today. The above is not a
legitimate reason to provide additional functions in the core.

> 5) It seems unwise to go through the trouble of just adding sha1(), when
> we could easily add some better hashes, which has the nice side effect
> of making us stand out more and push the envelope, rather than play follow
> the leader, as was mentioned at PGCon East

This presumes that better hashes truly exist. It is basic math to show
that all hashes will include collisions. Ignoring the possibility that
one hash has theoretical better distribution for real documents, the
real "benefit" of SHA-1 over MD5, is that it has more bits. The
"ultimate" solution here, is to store the original using the "full copy"
hash technique, with 0 chance of collision. This extreme defeats the
purpose of a hash to start with.

Why does PostgreSQL need something better than md5 as part of core?
Bragging rights?

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>

On Thu, Apr 3, 2008 at 8:25 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:

> If this were at all true we would not not have seen the complaints from
> people along the lines of "My ISP won't install contrib". But we have, and
> quite a number of times. We have concrete evidence that calling it contrib
> actually works against us.

It's hard to see ISPs who won't install contrib from installing
${random module} from the big bad internet as has been discussed in
this thread, but who knows?

If we go with a solution that allows users to say "install mymodule;"
or whatever into their own database, is there any reason not to
install (as in make install) all modules currently called contrib by
default? Are there any security issues with modules in there? I seem
to remember something coming up involving dblink a while back...

Cheers

Tom

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 3 Apr 2008 21:03:05 +0530
"Tom Dunstan" <pgsql(at)tomd(dot)cc> wrote:

> On Thu, Apr 3, 2008 at 8:25 PM, Andrew Dunstan <andrew(at)dunslane(dot)net>
> wrote:
>
> > If this were at all true we would not not have seen the complaints
> > from people along the lines of "My ISP won't install contrib". But
> > we have, and quite a number of times. We have concrete evidence
> > that calling it contrib actually works against us.
>
> It's hard to see ISPs who won't install contrib from installing
> ${random module} from the big bad internet as has been discussed in
> this thread, but who knows?

Sure it is. The very word contrib brings about ideas of things like:

Unstable, Cooker, unofficial.

"modules" is completely different (from a perception perspective).

IMO the core modules should be compiled via configure with something
like:

./configure --enable-module=ALL

or

./configure --enable-module=pgcrypto --enable-module=cube

This would install all the modules but not enable them in the database
itself (of course). This could also be extended to the pls so that we
have exactly one mechanism to control those options as well.

./configure --enable-module=pgcrypto --enable-module=plperl

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH9PwCATb/zqfZUUQRAoWtAKCdbdcv4KdOIdiF8gcjebWTIrub1gCgg8RU
QaatCVhlETRkA6+5wyYNdRM=
=z1gI
-----END PGP SIGNATURE-----

Am Donnerstag, 3. April 2008 schrieb Andrew Dunstan:
> If this were at all true we would not not have seen the complaints from
> people along the lines of "My ISP won't install contrib". But we have,
> and quite a number of times. We have concrete evidence that calling it
> contrib actually works against us.

ISPs also won't install additional Perl modules, for example. Yet, CPAN does
exist successfully.

Mark Mielke wrote:
> This presumes that better hashes truly exist. It is basic math to show
> that all hashes will include collisions. Ignoring the possibility that
> one hash has theoretical better distribution for real documents, the
> real "benefit" of SHA-1 over MD5, is that it has more bits. The
> "ultimate" solution here, is to store the original using the "full
> copy" hash technique, with 0 chance of collision. This extreme defeats
> the purpose of a hash to start with.
>
> Why does PostgreSQL need something better than md5 as part of core?
> Bragging rights?
Having more than one hash algorithm significantly decreases the risk of
(common) collisions.

As a non-developer (who does track most messages on the list anyways), I
surely find the SHA* functions will add significantly value and they
should be easy to install (well-defined functions) with no maintainance
afterwards.
Hashes are an absolute minimum for keeping passwords stored somehat
safely in a database.

More two or even three different hashes with different collion-points
will strongly increase the security.

On Thu, Apr 3, 2008 at 9:17 PM, Joshua D. Drake <jd(at)commandprompt(dot)com> wrote:
> > It's hard to see ISPs who won't install contrib from installing
> > ${random module} from the big bad internet as has been discussed in
> > this thread, but who knows?
>
> Sure it is. The very word contrib brings about ideas of things like:
>
> Unstable, Cooker, unofficial.

Point taken, and I completely agree. Part of the problem is that we
have explicitly encouraged this perception, ie "it's in contrib so the
barrier to entry is lower". That may not be the case anymore, or it
may just be that the bar is really really high for non-contrib stuff
vs other projects. Whatever the actual case is, I agree that the name
is unfortunate.

When I wrote the above I was thinking about it from the other way
around: doing a cpan or gem install of some random module seems even
less safe to me, but maybe I'm just revealing confidence in pgsql or
fear of some cpan code etc that ISPs don't share.

> This would install all the modules but not enable them in the database
> itself (of course). This could also be extended to the pls so that we
> have exactly one mechanism to control those options as well.
>
> ./configure --enable-module=pgcrypto --enable-module=plperl

That's basically where I was heading, although I took it a step
further: why not build and install all possible modules by default, if
we think they're up to quality?

One answer is: what do you do if some required library isn't
available? Do you fail with an error message or just don't build that
module? I don't like the idea of e.g. accidentally and silently not
installing pl/perl just because the sysadmin hadn't installed their
perl-devel package or whatever.

--enable-module=ALL could be pretty good, though, especially if it
build pl/perl etc that most sysadmins will want to install but do so
in less configure args. :)

Cheers

Tom

Aidan Van Dyk wrote:
> * Greg Sabino Mullane <greg(at)turnstep(dot)com> [080403 09:54]:
>> I emphatically do NOT mean
>> move to pgfoundry, which is pretty much a kiss of death.
>
> But that begs the question of *why* it's a kiss of death?

> For instance, in "perl land", having something in "CPAN" and not in
> "perl core" is most certainly *not* a kiss of death? Why is it so
> different for PostgreSQL?
> Is it because the infrastructure behind CPAN is much better than that
> behind pgfoundry?

I wouldn't say one is better than the other. PGFoundry and CPAN have
totally disjoint feature sets. PgFoundry's like SoruceForge +
Bugtrackers + Discussion Forums + Surveys + Mailing Lists -- pretty
much everything except installable packages.

CPAN and RubyGems is very much focused on installable packages.

> Or is it because CPAN is better "vetted" and "organized" than pgfoundry?
>
> Or is it because the projects that go into CPAN are better quality and
> projects in pgroundry?

To simplify those two:
CPAN contains installers that mostly "just work".
PGFoundry contains mostly works-in-progress without installers.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 3 Apr 2008 21:45:52 +0530
"Tom Dunstan" <pgsql(at)tomd(dot)cc> wrote:

> > This would install all the modules but not enable them in the
> > database itself (of course). This could also be extended to the pls
> > so that we have exactly one mechanism to control those options as
> > well.
> >
> > ./configure --enable-module=pgcrypto --enable-module=plperl
>
> That's basically where I was heading, although I took it a step
> further: why not build and install all possible modules by default, if
> we think they're up to quality?

Good point.

>
> One answer is: what do you do if some required library isn't
> available?

If we build by default, then when a library isn't found the configure
output tells you:

Looking for Perl Development packages: No , disabling plperl build.
>
> --enable-module=ALL could be pretty good, though, especially if it
> build pl/perl etc that most sysadmins will want to install but do so
> in less configure args. :)

Right. I am using the Apache model here.

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH9QQ2ATb/zqfZUUQRAsD9AJ9b9/12ZtaJ/CpnQ3y0xH7U3a0EYACfVeUJ
FKUyEmuuw9nx3F+sk4mL2eQ=
=sA7I
-----END PGP SIGNATURE-----

Svenne Krap wrote:
> Mark Mielke wrote:
>> This presumes that better hashes truly exist. It is basic math to
>> show that all hashes will include collisions. Ignoring the
>> possibility that one hash has theoretical better distribution for
>> real documents, the real "benefit" of SHA-1 over MD5, is that it has
>> more bits. The "ultimate" solution here, is to store the original
>> using the "full copy" hash technique, with 0 chance of collision.
>> This extreme defeats the purpose of a hash to start with.
>>
>> Why does PostgreSQL need something better than md5 as part of core?
>> Bragging rights?
> Having more than one hash algorithm significantly decreases the risk
> of (common) collisions.

No it doesn't. More bits reduces risk of collisions. Additional
algorithms just muddy the waters.

> As a non-developer (who does track most messages on the list anyways),
> I surely find the SHA* functions will add significantly value and they
> should be easy to install (well-defined functions) with no
> maintainance afterwards.
> Hashes are an absolute minimum for keeping passwords stored somehat
> safely in a database.

It has yet to be proven that MD5 is insufficient for this purpose.
"Significant value" being what?

> More two or even three different hashes with different collion-points
> will strongly increase the security.

No it doesn't unless you are thinking about a security through obscurity
argument.

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>

D'Arcy J.M. Cain wrote:

> Check out NetBSD pkgsrc as a model. It is very flexible. One nice
> thing would be the ability to specify where the packages are rather
> than always insisting that they be on pgfoundry.

Yup - a feature shared by RubyGems:
gem install rails –source http://gems.rubyonrails.org

Many of the most popular modules seem to live outside of
pgfoundry anyway (postgis, the contrib ones, etc); so I'd
think even if we maintain a central repository we want to
make sure it can install from other sites.

>> Perl and Ruby are languages - Postgres is a very different animal.
>
> ...Overall though
> I don't think that what is being installed to changes much. The basics
> remain the same - define the package with latest version, download if
> necessary,check that the source package is the correct, tested one,
> build, install, register.

+1. From the end user I think he cares that the software is installed
with the required dependencies and passes any included regression tests.
Bonus points if it also registers itself in his database.

And in the ruby/gems world the Windows guys seem not to have liked
the "check...source packages...build" so they include precompiled
windows libraries for those guys in many Ruby Gems.

On Thu, 3 Apr 2008 13:54:11 -0000
"Greg Sabino Mullane" <greg(at)turnstep(dot)com> wrote:
> Right now contrib is a real catch-all of various things; it would be nice to
> categorize them somehow. And by categorize, I emphatically do NOT mean
> move to pgfoundry, which is pretty much a kiss of death.

Yes! I have plenty of FTP servers to put up my own open source
projects. It would annoy me if I was forced to use someone else's
development environment. Whatever we do should allow for packages to
be picked up from anywhere. We can use MD5 checksums to assure users
that no one has changed the file since it was tested and packaged.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 04/04/2008, Joshua D. Drake wrote:
> "Tom Dunstan" wrote:
> >
> > One answer is: what do you do if some required library isn't
> > available?
>
>
> If we build by default, then when a library isn't found the configure
> output tells you:
>
> Looking for Perl Development packages: No , disabling plperl build.
>

That might easily go unnoticed in amongst all the other configure
output. It would only be effective if the messages were repeated
again at the end of the configure, or configure somehow draws
attention to the fact that there was a problem.

Another approach I've come across is to fail with an error message like

"Perl development files not found, required to build module plperl.
Install these files or configure with --disable-module=plperl"

Cheers,
BJ
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: http://getfiregpg.org

iD8DBQFH9Qfu5YBsbHkuyV0RAmKIAJ9eBkAGaw5kBmahk4CzJ4JbrkmitACff9DB
eYYSl1SiANAaAyky/3QBSIs=
=Fg12
-----END PGP SIGNATURE-----

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 3 Apr 2008 12:35:31 -0400
"D'Arcy J.M. Cain" <darcy(at)druid(dot)net> wrote:

> On Thu, 3 Apr 2008 13:54:11 -0000
> "Greg Sabino Mullane" <greg(at)turnstep(dot)com> wrote:
> > Right now contrib is a real catch-all of various things; it would
> > be nice to categorize them somehow. And by categorize, I
> > emphatically do NOT mean move to pgfoundry, which is pretty much a
> > kiss of death.

Pgfoundry is not a kiss of death except that you spread falsehoods like
that. PGfoundry is a very alive project that is constantly adding
content and has continuing and very active projects.

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH9QjVATb/zqfZUUQRAmuMAKCR/+mgHqB9TTsdI0G3Ax2Y5ry4SQCfQMNt
d7+jcUa3pDirWo34n7dqg2o=
=p4Oq
-----END PGP SIGNATURE-----

On Thu, 3 Apr 2008 09:41:57 -0700
"Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On Thu, 3 Apr 2008 12:35:31 -0400
> "D'Arcy J.M. Cain" <darcy(at)druid(dot)net> wrote:
>
> > On Thu, 3 Apr 2008 13:54:11 -0000
> > "Greg Sabino Mullane" <greg(at)turnstep(dot)com> wrote:
> > > Right now contrib is a real catch-all of various things; it would
> > > be nice to categorize them somehow. And by categorize, I
> > > emphatically do NOT mean move to pgfoundry, which is pretty much a
> > > kiss of death.
>
> Pgfoundry is not a kiss of death except that you spread falsehoods like
> that. PGfoundry is a very alive project that is constantly adding
> content and has continuing and very active projects.

Eep! Careful with attributions. There is not a single word of mine in
what you included. I know it technically says that but since your
comments were directed at Greg you really should have replied to his
email and not to mine that included his.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

On Apr 3, 2008, at 7:01 AM, Aidan Van Dyk wrote:
> * Greg Sabino Mullane <greg(at)turnstep(dot)com> [080403 09:54]:
>
>> Right now contrib is a real catch-all of various things; it would
>> be nice to
>> categorize them somehow. And by categorize, I emphatically do NOT
>> mean
>> move to pgfoundry, which is pretty much a kiss of death.
>
> But that begs the question of *why* it's a kiss of death?
>
> For instance, in "perl land", having something in "CPAN" and not in
> "perl core" is most certainly *not* a kiss of death? Why is it so
> different for PostgreSQL?
>
> Is it because the infrastructure behind CPAN is much better than that
> behind pgfoundry?

Yes. I can install a package from a CPAN mirror with a one-line
incantation and be sufficiently sure it works that on the very rare
occasions it doesn't I'm really surprised.

On the Windows end of things I can usually get pre-built binaries
of those same packages installed, in the cases where a compiler
is needed to build them. The exact process is a bit different, but it's
consistent across most packages and uses the same namespace.

> Or is it because CPAN is better "vetted" and "organized" than
> pgfoundry?

Partly. "Vetted" is partly self-vetting - you're expected to pass your
self
tests and install cleanly before you publish to CPAN. The naming
hierarchy
helps with the CPAN organization, and makes it easier to use than the
trove approach, once you're familiar with the perl namespace habits.

Some of that is applicable to a postgresql package distribution method,
but the neat organization is a perl thing, not a CPAN thing, so that
idea
doesn't really transfer.

>
>
> Or is it because the projects that go into CPAN are better quality and
> projects in pgroundry?

Partly. There are some dubious packages on CPAN but they're finished,
and with extremely few exceptions download, pass their self tests and
do what it says on the box (the main flaws are packages going stale
and occasionally dependency problems).

Pgfoundry is a development site with a search engine and has projects
in various stages of completion from vaporware to production tested
usable code.

> Or is it something else?

Projects vs Packages sums up the differences.

Cheers,
Steve

On Thu, Apr 03, 2008 at 06:14:17PM +0200, Svenne Krap wrote:
> Hashes are an absolute minimum for keeping passwords stored somehat
> safely in a database.

> More two or even three different hashes with different collion-points
> will strongly increase the security.

Not only that, but they also increase the complexity of the system.
Increases in complexity tend to mean decreases in reliability and,
by implication, security. As an example, someone may do some fancy
cryptanalysis and discover that having lots of hashes will actually make
it easier. As another point, most passwords have significantly less
state than a 128bit hash allowing attacks like rainbow tables become
viable.

Sam

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 3 Apr 2008 12:46:30 -0400
"D'Arcy J.M. Cain" <darcy(at)druid(dot)net> wrote:

> On Thu, 3 Apr 2008 09:41:57 -0700
> "Joshua D. Drake" <jd(at)commandprompt(dot)com> wrote:
> > -----BEGIN PGP SIGNED MESSAGE-----
> > Hash: SHA1
> >
> > On Thu, 3 Apr 2008 12:35:31 -0400
> > "D'Arcy J.M. Cain" <darcy(at)druid(dot)net> wrote:
> >
> > > On Thu, 3 Apr 2008 13:54:11 -0000
> > > "Greg Sabino Mullane" <greg(at)turnstep(dot)com> wrote:
> > > > Right now contrib is a real catch-all of various things; it
> > > > would be nice to categorize them somehow. And by categorize, I
> > > > emphatically do NOT mean move to pgfoundry, which is pretty
> > > > much a kiss of death.
> >
> > Pgfoundry is not a kiss of death except that you spread falsehoods
> > like that. PGfoundry is a very alive project that is constantly
> > adding content and has continuing and very active projects.
>
> Eep! Careful with attributions. There is not a single word of mine
> in what you included. I know it technically says that but since your
> comments were directed at Greg you really should have replied to his
> email and not to mine that included his.

Sorry Darcy :).

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH9Qv2ATb/zqfZUUQRAtBCAJ4yRvm6IydAstjb06G2mM8XhkVfPACfdmCy
oa3KN6PmkXzZgFlFOSHseVk=
=T5sZ
-----END PGP SIGNATURE-----

On Thu, 03 Apr 2008 09:31:01 -0700
Ron Mayer <rm_pg(at)cheapcomplexdevices(dot)com> wrote:
> D'Arcy J.M. Cain wrote:
> > Check out NetBSD pkgsrc as a model. It is very flexible. One nice
> > thing would be the ability to specify where the packages are rather
> > than always insisting that they be on pgfoundry.
>
> Yup - a feature shared by RubyGems:
> gem install rails ?source http://gems.rubyonrails.org

Yes but what I am suggesting goes beyond that. My idea is that there
is a modules directory that contains a file for each installable
module. This file would contain all the information about the module
such as name, version, where to get the actual package, an MD5 checksum
of the package, minimum and maximum PostgreSQL versions required, etc.

Naturally we should allow for people to define their own local packages
as well.

In fact, this may be the way to deprecate contrib. Start building
modules and move the contrib packages to it one at a time. That way
people using contrib have some time to switch and we can point people
to modules if they are just starting out.

Is there support for this idea? I would like to start exploring this
if so.

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

D'Arcy J.M. Cain wrote:
>
> In fact, this may be the way to deprecate contrib. Start building
> modules and move the contrib packages to it one at a time. That way
> people using contrib have some time to switch and we can point people
> to modules if they are just starting out.
>
> Is there support for this idea? I would like to start exploring this
> if so.
>
>

No. I don't want to deprecate it, I want to get rid of it, lock, stock
and barrel. If you think that we need more than renaming then we can
discuss it, but I don't want a long death, I want one that is certain
and swift.

cheers

andrew

Mark Mielke wrote:
>> More two or even three different hashes with different collion-points
>> will strongly increase the security.
> No it doesn't unless you are thinking about a security through
> obscurity argument.
It is really the same argument on all your questions....

If I have a simple table now

ID serial
Username varchar
Password varchar

I currently save only md5(id || username || 'password')* into password,
if I had access to sha1 (for example) i would add another password
column so, having for example

ID serial
Username varchar
Password_md5 varchar
Password_sha1 varchar

No matter how you see it, I get more bits of hash to check against.

I would drop md5 totally and use sha1 and ripemd-160 if possible.. but
currently i use only md5 as it is the only available one.. Loading
pgcrypto is overkill for something as simple as hash-functions.

Svenne

* I prepend the id and the username to guard users with weak passwords
against known hashvalues (rainbow tables) should the box ever get
comprised ... if you are in doubt about the value of this, try google
for 40e94aa51dc5c0ccc5aad4e6aefdde2a and guess the secret password...

Svenne Krap wrote:
>
> If I have a simple table now
>
> ID serial
> Username varchar
> Password varchar
>
> I currently save only md5(id || username || 'password')* into
> password, if I had access to sha1 (for example) i would add another
> password column so, having for example
>
> ID serial
> Username varchar
> Password_md5 varchar
> Password_sha1 varchar
>
> No matter how you see it, I get more bits of hash to check against.
>

Really? Why stop at two, then? How many hash functions is enough?

cheers

andrew

On Thu, 03 Apr 2008 13:06:25 -0400
Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> D'Arcy J.M. Cain wrote:
> > In fact, this may be the way to deprecate contrib. Start building
> > modules and move the contrib packages to it one at a time. That way
> > people using contrib have some time to switch and we can point people
> > to modules if they are just starting out.
> >
> > Is there support for this idea? I would like to start exploring this
> > if so.
>
> No. I don't want to deprecate it, I want to get rid of it, lock, stock
> and barrel. If you think that we need more than renaming then we can
> discuss it, but I don't want a long death, I want one that is certain
> and swift.

Well, OK, but given that this is a huge public project with lots of
users expecting things to be in certain places, how fast do you think
we could make such a change. It seems to me that we are going to have
to make things look the same for some time at least otherwise we are
going to have lots of complaints. How swift is swift? To me, swift
means add the alternate functionality to the next release and remove
the old in the release after. Do you see things happening any faster?

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

On Thu, Apr 3, 2008 at 10:36 PM, Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
> No. I don't want to deprecate it, I want to get rid of it, lock, stock and
> barrel. If you think that we need more than renaming then we can discuss it,
> but I don't want a long death, I want one that is certain and swift.

I'll admit that I had thought that moving contrib modules over to a
modules dir as they were, uh, modularized would be the way forward.
Anything that doesn't fit the database-owner-installable pattern
(pgbench? start-scripts? others?) could end up in a utils dir, and
anything left in contrib shows us what's left to do before e.g. 8.4.
The end goal would be no more contrib dir by the next major release.

As a side note, how were you intending to rename contrib? Directory
shenanigans in CVS are horrible, particularly if you want all your
old branches to still work.

Cheers

Tom

Mark Mielke wrote:
> Svenne Krap wrote:
>> Mark Mielke wrote:
>>> Svenne Krap wrote:
>>>> More two or even three different hashes with different
>>>> collion-points will strongly increase the security.
>>> No it doesn't unless you are thinking about a security through
>>> obscurity argument
> Your logic is invalid - the best quality would be to not use a hash at
> all, and store in plain text, or ROT-13. Then you will have no
> collisions. If you truly believe more bits are better, don't use a
> hash to start with.
>

Ooops, went offlist by a wrong click. Putting it back onliste

I am aware that plain text (or any 1:1 mapping) has no chance of
collision, but on the other hand if the box is compromised it gives an
easy target for stealing passwords (and a lot of users use the same
passwords a lot of places).
I believe that hashing through one hash function is an acceptable
compromise between collisions (i.e. people get in with the wrong
password) and password safety (evil hacker cannot read passwords) given
you deploy anti rainbow table meassures.

I would still prefer two hash functions as they do add a better
safeguard towards collisions (the gentoo distribtion actually hashes the
files by three different algorithms SHA1, SHA256 and RMD160) - i would
be inclined to use three hashes too, if they were instantly available.

Svenne

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 3 Apr 2008 13:27:03 -0400
"D'Arcy J.M. Cain" <darcy(at)druid(dot)net> wrote:

> Well, OK, but given that this is a huge public project with lots of
> users expecting things to be in certain places, how fast do you think
> we could make such a change.

8.4.

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD4DBQFH9RY/ATb/zqfZUUQRAu0SAJ9+bnPyHmVIRb/QgbD8plEmGBRC2gCY0uS2
L+stcsM5h97QAzT23VD8zw==
=z+FW
-----END PGP SIGNATURE-----

D'Arcy J.M. Cain wrote:
> On Thu, 03 Apr 2008 13:06:25 -0400
> Andrew Dunstan <andrew(at)dunslane(dot)net> wrote:
>
>> D'Arcy J.M. Cain wrote:
>>
>>> In fact, this may be the way to deprecate contrib. Start building
>>> modules and move the contrib packages to it one at a time. That way
>>> people using contrib have some time to switch and we can point people
>>> to modules if they are just starting out.
>>>
>>> Is there support for this idea? I would like to start exploring this
>>> if so.
>>>
>> No. I don't want to deprecate it, I want to get rid of it, lock, stock
>> and barrel. If you think that we need more than renaming then we can
>> discuss it, but I don't want a long death, I want one that is certain
>> and swift.
>>
>
> Well, OK, but given that this is a huge public project with lots of
> users expecting things to be in certain places, how fast do you think
> we could make such a change. It seems to me that we are going to have
> to make things look the same for some time at least otherwise we are
> going to have lots of complaints. How swift is swift? To me, swift
> means add the alternate functionality to the next release and remove
> the old in the release after. Do you see things happening any faster?
>
>

I don't understand this at all. We are talking about directory and
package organisation here. How do you do that with transition
arrangements? I guess we could put in a symlink from contrib, but I just
don't see the point. I don't think we are under any obligation to
preserve the way we package or split packages between releases. And
doing this reorganisation now, fairly early in the release cycle, would
let us give people like packagers plenty of advance notice.

cheers

andrew

* Tom Dunstan <pgsql(at)tomd(dot)cc> [080403 13:30]:

> As a side note, how were you intending to rename contrib? Directory
> shenanigans in CVS are horrible, particularly if you want all your
> old branches to still work.

Well, please, anybody doing this, just simply copy and use cvs remove and
cvs add...

We're using CVS, so we live with disjoint history on renames... As long
as the commit comment is clear, the history isn't "lost", just another
command away.

a.
--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

On Thu, Apr 03, 2008 at 07:07:56PM +0200, Svenne Krap wrote:
> I currently save only md5(id || username || 'password')* into password,
> if I had access to sha1 (for example) i would add another password
> column so, having for example
>
> ID serial
> Username varchar
> Password_md5 varchar
> Password_sha1 varchar
>
> No matter how you see it, I get more bits of hash to check against.

Are you a cryptanalyst and are you sure that this doesn't actually make
things worse? I'm sure it gives you a warm fuzzy feeling that it's
*got* to be better, but unless someone has done some hard maths I'm not
sure how you can be so sure.

Why not just use SHA-512, you get many more quality bits that way.

> I would drop md5 totally and use sha1 and ripemd-160 if possible.. but
> currently i use only md5 as it is the only available one.. Loading
> pgcrypto is overkill for something as simple as hash-functions.

Sounds like a good reason for moving the current md5 function out into
pgcrypto as well! :)

> * I prepend the id and the username to guard users with weak passwords
> against known hashvalues (rainbow tables) should the box ever get
> comprised ...

I take it your threat model doesn't include the attacker logging
incoming queries to look for the clear-text password.

Sam

On Thursday 03 April 2008 08:47:12 Joshua D. Drake wrote:
> On Thu, 3 Apr 2008 21:03:05 +0530
>
> "Tom Dunstan" <pgsql(at)tomd(dot)cc> wrote:
> > On Thu, Apr 3, 2008 at 8:25 PM, Andrew Dunstan <andrew(at)dunslane(dot)net>
> >
> > wrote:
> > > If this were at all true we would not not have seen the complaints
> > > from people along the lines of "My ISP won't install contrib". But
> > > we have, and quite a number of times. We have concrete evidence
> > > that calling it contrib actually works against us.
> >
> > It's hard to see ISPs who won't install contrib from installing
> > ${random module} from the big bad internet as has been discussed in
> > this thread, but who knows?
>
> Sure it is. The very word contrib brings about ideas of things like:
>
> Unstable, Cooker, unofficial.
>
> "modules" is completely different (from a perception perspective).
>
> IMO the core modules should be compiled via configure with something
> like:
>
> ./configure --enable-module=ALL
>
> or
>
> ./configure --enable-module=pgcrypto --enable-module=cube
>
> This would install all the modules but not enable them in the database
> itself (of course). This could also be extended to the pls so that we
> have exactly one mechanism to control those options as well.
>
> ./configure --enable-module=pgcrypto --enable-module=plperl

I think --enable-module might be the wrong term here, since you specificaly
state we are not "enabling" them in the database.

I think --with-module=... might be a better way to go.

>
> 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

--
Darcy Buskermolen
Command Prompt, Inc.
+1.503.667.4564 X 102
http://www.commandprompt.com/
PostgreSQL solutions since 1997

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On Thu, 3 Apr 2008 12:03:43 -0700
Darcy Buskermolen <darcyb(at)commandprompt(dot)com> wrote:

> > This would install all the modules but not enable them in the
> > database itself (of course). This could also be extended to the pls
> > so that we have exactly one mechanism to control those options as
> > well.
> >
> > ./configure --enable-module=pgcrypto --enable-module=plperl
>
> I think --enable-module might be the wrong term here, since you
> specificaly state we are not "enabling" them in the database.
>
> I think --with-module=... might be a better way to go.
>

That would work.

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFH9S8HATb/zqfZUUQRAjAFAJ0dsH4Cwr3WuiLXVKw9tReOarhKSQCeNuKL
GkaxyLV8eC/YhUzgfd4YTEI=
=6C6r
-----END PGP SIGNATURE-----

Svenne Krap wrote:
> I would still prefer two hash functions as they do add a better
> safeguard towards collisions (the gentoo distribtion actually hashes
> the files by three different algorithms SHA1, SHA256 and RMD160) - i
> would be inclined to use three hashes too, if they were instantly
> available.
Technically MD5 (128 bits) + SHA1 (160 bits) gives better strength than
MD5 on its own, or SHA1 on its own, in that finding one collision is
likely to be insufficient to break in, however, I doubt you could
mathematically prove that it would perform equal to a 128 + 160 = 288
bit equivalent strength hash function. At the bare minimum, I refer you
to the fact that each component on its own represents a self-contained
hash of the entire document, and a small change in a real-life document
will presumedly affect both values (this is how hash functions are
designed), therefore, for real-life documents, there are bit patterns
that will never coincide, therefore, not all 288 bits are being used.
For real-life documents, it is highly likely that the MD5 and the SHA1
will always be a ~1:1 mapping (how many collision have truly been found
in practice?), and the effective bit strength of the total for real
documents approaches the highest of the two - which is 160 bits. I
suggest that MD5 + SHA1 is not 128 bits better than SHA1 on its own.

In any case, this is all irrelevant, because md5 passwords are still
very useful, and the argument that "more = better" is a never ending
infinite resource trap. More is not better. Better is better. If you can
prove md5 is insufficient for PostgreSQL passwords, the correct decision
would be to switch to something better, and deprecate md5 from the core.

Cheers,
mark

--
Mark Mielke <mark(at)mielke(dot)cc>

Mark Mielke wrote:
> In any case, this is all irrelevant, because md5 passwords are still
> very useful, and the argument that "more = better" is a never ending
> infinite resource trap. More is not better. Better is better. If you can
> prove md5 is insufficient for PostgreSQL passwords, the correct decision
> would be to switch to something better, and deprecate md5 from the core.

Agreed.

One must also remember that if you use two hashes, if *either* one of
them is broken in the future so that you can reconstruct the password
from the hash, you're screwed.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas wrote:
> Mark Mielke wrote:
> One must also remember that if you use two hashes, if *either* one of
> them is broken in the future so that you can reconstruct the password
> from the hash, you're screwed.
That is quite a good argument actually :)

Sam Mason wrote:
> Are you a cryptanalyst and are you sure that this doesn't actually make
> things worse? I'm sure it gives you a warm fuzzy feeling that it's
> *got* to be better, but unless someone has done some hard maths I'm not
> sure how you can be so sure.
>
No sadly I am no cryptoanalyst.
> Why not just use SHA-512, you get many more quality bits that way.
>
I would, if it was available in core.
>
>> I would drop md5 totally and use sha1 and ripemd-160 if possible.. but
>> currently i use only md5 as it is the only available one.. Loading
>> pgcrypto is overkill for something as simple as hash-functions.
>>
> Sounds like a good reason for moving the current md5 function out into
> pgcrypto as well! :)
>
I am not sure how I am to understand that comment. But again I am just a
user...
>> * I prepend the id and the username to guard users with weak passwords
>> against known hashvalues (rainbow tables) should the box ever get
>> comprised ...
>>
>
> I take it your threat model doesn't include the attacker logging
> incoming queries to look for the clear-text password.
>
No it doesn't, I am mostly concerned with the grab and run scenario.

I am still convinced having more (and better) hash-functions in core is
a gain for some users.

And it is fairly un-intrusive as the hash functions are well-defined and
never going to change (new ones can be added and old ones deleted, but
SHA256 for example will never change).

I think I will drop the issue as I cannot present formal proof of my
case, sorry to have wasted your time.

Svenne

Joshua D. Drake wrote:
>> On Thu, 3 Apr 2008 13:54:11 -0000
>> "Greg Sabino Mullane" <greg(at)turnstep(dot)com> wrote:
>>> Right now contrib is a real catch-all of various things; it would
>>> be nice to categorize them somehow. And by categorize, I
>>> emphatically do NOT mean move to pgfoundry, which is pretty much a
>>> kiss of death.
>
> Pgfoundry is not a kiss of death except that you spread falsehoods like
> that. PGfoundry is a very alive project that is constantly adding
> content and has continuing and very active projects.

+1.
This modules/packages concept seems 100% orthogonal to pgfoundry to me.

Pgfoundry seems like a bug-tracking / development infrastructure
somewhat like sourceforge. Some modules might want to use it,
some might not (no doubt postgis would stay with refractions, etc).

This hypothetical modules project is more focused on installers,
and perhaps ways to find and run the module installers whether
from pgfoundry or elsewhere.

Ron

PS: Regarding pgfoundry and credibility; it seems the stature
and image of pgfoundry would go up a lot if postgresql itself
were hosted there. But no, I'm not advocating that.

Sam Mason wrote:
> On Thu, Apr 03, 2008 at 07:07:56PM +0200, Svenne Krap wrote:
>>
>> ID serial
>> Username varchar
>> Password_md5 varchar
>> Password_sha1 varchar
> ...
> Why not just use SHA-512, you get many more quality bits that way.

Or if he just wanted to use builtin tools and reduce accidental
collisions almost exactly the same much as he propopses, he could use

password_md5_with_salt_xxx varchar
password_md5_with_salt_yyy varchar

but I also can't see the point. Won't he have more
collisions from cosmic rays turning the results of his comparisons
from false to true anyway?

>> I would drop md5 totally and use sha1 and ripemd-160 if possible.. but
>> currently i use only md5 as it is the only available one.. Loading
>> pgcrypto is overkill for something as simple as hash-functions.
>
> Sounds like a good reason for moving the current md5 function out into
> pgcrypto as well! :)

I'd rephrase it as saying "a good reason for making it less
intimidating to install modules". +1 to all the approaches
that make this less scary.

For Perl Digest-SHA1's in CPAN http://search.cpan.org/dist/Digest-SHA1/

On Fri, Apr 04, 2008 at 12:06:03AM +0200, Svenne Krap wrote:
> Sam Mason wrote:
> >Are you a cryptanalyst and are you sure that this doesn't actually make
> >things worse? I'm sure it gives you a warm fuzzy feeling that it's
> >*got* to be better, but unless someone has done some hard maths I'm not
> >sure how you can be so sure.
>
> No sadly I am no cryptoanalyst.

Neither am I. I'm not sure if my comment came across right though..

> >Why not just use SHA-512, you get many more quality bits that way.
> >
> I would, if it was available in core.

I'm still not sure why being in core is so special.

> >>I would drop md5 totally and use sha1 and ripemd-160 if possible.. but
> >>currently i use only md5 as it is the only available one.. Loading
> >>pgcrypto is overkill for something as simple as hash-functions.
> >>
> >Sounds like a good reason for moving the current md5 function out into
> >pgcrypto as well! :)
>
> I am not sure how I am to understand that comment. But again I am just a
> user...

Within (most? larger anyway) software projects there's a big tension
between adding features that are directly useful to users, versus
providing tools that can be used to do more powerful things. Modularity
and abstractions are about the only tools we have of taming complexity,
without them codebases become impossibly convoluted and difficult
to understand or modify. The module system in PG is part of this,
it allows various parts of the code to be separated off (reasonably
cleanly) allowing the rest of PG to be modified without too much concern
of unrelated breakage.

Lets see how much flack I get for that! :)

> >I take it your threat model doesn't include the attacker logging
> >incoming queries to look for the clear-text password.
>
> No it doesn't, I am mostly concerned with the grab and run scenario.

OK, this sort of thing is very application specific.

I have a feeling that my comments before came across a bit harshly,
sorry if they did.

> I am still convinced having more (and better) hash-functions in core is
> a gain for some users.

Yes, having more functionality in the code is always better for users!
But the down side is that the rest of the software changes more slowly.
I.e. a lot of the correctness/features/performance improvements that
have been happening recently probably wouldn't have happened if all the
code that's in the various modules was in core.

> And it is fairly un-intrusive as the hash functions are well-defined and
> never going to change (new ones can be added and old ones deleted, but
> SHA256 for example will never change).

That's the sort of engineering problem I'm very bad at dealing with, I'm
more interested in the computer science parts.

> I think I will drop the issue as I cannot present formal proof of my
> case, sorry to have wasted your time.

Sorry, that wasn't the intention of my mail---I just enjoy the formal
bits. I think they can be pretty beautiful, but most of the time
they're irrelevant.

Sam

On Thu, Apr 03, 2008 at 04:42:47PM -0700, Ron Mayer wrote:
> Sam Mason wrote:
> >On Thu, Apr 03, 2008 at 07:07:56PM +0200, Svenne Krap wrote:
> >>
> >>ID serial
> >>Username varchar
> >>Password_md5 varchar
> >>Password_sha1 varchar
> >...
> >Why not just use SHA-512, you get many more quality bits that way.
>
> Or if he just wanted to use builtin tools and reduce accidental
> collisions almost exactly the same much as he propopses, he could use
>
> password_md5_with_salt_xxx varchar
> password_md5_with_salt_yyy varchar
>
> but I also can't see the point. Won't he have more
> collisions from cosmic rays turning the results of his comparisons
> from false to true anyway?

There's a difference between random bit flips, which in large systems
happen surprisingly regularly, and a determined attacker. You'd be able
to get somewhere against the former by duplicating everything, and you'd
be able to get somewhere against the latter by using stronger hashes.

Or have I missed the point entirely.

> >Sounds like a good reason for moving the current md5 function out into
> >pgcrypto as well! :)
>
> I'd rephrase it as saying "a good reason for making it less
> intimidating to install modules". +1 to all the approaches
> that make this less scary.

That's a much nicer way of saying it!

I think that from an ISPs perspective (I can't remember the real use
case that was given recently) the main problem with modules is that
you have to build trust in each one individually. I.e. Doesn't the
code running in modules run as the postgres user? If so, what would
stop a malicious module from doing anything that the postgres user can?
I'd probably phrase this as saying the modules are within PG's trust
boundary, rather than outside. It's therefore the responsibility of
the person installing the module to verify (I think this will generally
be blind human trust) that the code isn't malicious. I'd guess that's
a reason why so few modules are installed, it basically opens your
code up to another source, with whom the installer has no prior trust
relationship.

There are operating systems that allow authority to be attenuated
arbitrarily finely (i.e. not the very coarse user/role based ACL systems
we have at the moment) which would allow mutually suspicious code to
function. I.e. PG would be able to assume that the module was malicious
and that the module wanted to crash PG, the module would assume the
reverse, and the ISP wouldn't have to worry at all. It'll be a few
years before we get there though.

Sam

On Thu, 3 Apr 2008, Peter Eisentraut wrote:

> Am Donnerstag, 3. April 2008 schrieb Andrew Dunstan:
> > If this were at all true we would not not have seen the complaints from
> > people along the lines of "My ISP won't install contrib". But we have,
> > and quite a number of times. We have concrete evidence that calling it
> > contrib actually works against us.
>
> ISPs also won't install additional Perl modules, for example. Yet, CPAN does
> exist successfully.

ISPs don't necessarily HAVE to install additional perl modules. If I have
my own home directory and shell access, I can run "perl Makefile.PL
PREFIX=/home/myuser/perlstuff", and just tweak PERL5LIB (or use lib) and I
can install modules without any superuser intervention.

This is where the CPAN comparison breaks down. I can install any perl
module I want (native perl or even XS/C modules) without superuser
privileges. With postgres, super user privileges are REQUIRED to install
any module, whatever it is called (contrib, modules, pgfoundry, gborg)...

IMHO, this is the Achilles heel of Postgres extensibility. Look at this
library of plugins out there that do all of these nifty things, and if you
can't find one that fits your needs, you can always write a little C code
to do the job exactly how you want. Too bad you can't use them if you
can't afford your own dedicated database server instance...

This was the most frustrating thing for me as a developer. I know that
there are all of these fine modules out there, and I even have a few of my
own. I have been spoiled by the extensibility of Postgres, only to have
it taken away when I want to move my databases from my own machine into
production on the hosting provider.

If I want to put geographical data in a database, I know PostGIS is out
there, but I can't install it. I could use cube/earthdistance, but I
can't install that either. So much for the geographical data. How about
text search? Nope, can't have that either, at least until 8.3 finds its
way into OpenBSD ports and the hosting provider gets around to installing
it. At least I have that to look forward to.

My opinion is, it doesn't matter what you call the modules/contrib stuff
if I can't use it, and I can't use it if it is not loaded in my
database, and I can't load it without superuser privileges.

--
Never put off till tomorrow what you can avoid all together.

On Fri, Apr 4, 2008 at 10:48 AM, Jeremy Drake <pgsql(at)jdrake(dot)com> wrote:

> My opinion is, it doesn't matter what you call the modules/contrib stuff
> if I can't use it, and I can't use it if it is not loaded in my
> database, and I can't load it without superuser privileges.

Right. Which is why some of us have been suggesting a model where all
modules currently in contrib are installed by default, but not enabled
until a database owner actually issues some sort of "Install module
foo" or whatever it looks like. Database owner privs are probably as
low as we can reasonably set the bar... is that sufficiently low to be
useful? If not, I suppose that we could add a specific "install /
uninstall module" privilege that could be granted to non-db-owner
users if that's the way the ISP prefers to work.

Regarding PostGIS etc, my hope is that if we standardize the
installation of postgresql modules in this manner, it will be much
easier for sysadmins to e.g. yum install postgis - they don't have to
run any SQL scripts by hand, they can get packages built for their
platform and distributed using the preferred platform distribution
method, and the modules will only be enabled for those users that
specifically enable them in their databases. We can't force sysadmins
to install random third party extensions to postgresql, but we can
make it a lot easer than it currently is.

Alternately, if that's still not enough, then if we do standardise the
interface it would be easier to bundle third party modules that live
outside the main source tree - just stick em in /modules when building
the tar files and they'll end up installed and ready-to-enable when
built.

Hmm. We could even do that for existing contrib modules if we want
them to live outside the core project - for example because their
maintainers don't need commit access to core. It would be easy enough
to have the buildfarm fetch blessed modules from their real location
(pgfoundry?) so that we maintain good test coverage.

Cheers

Tom

On Fri, Apr 04, 2008 at 02:23:31PM +0530, Tom Dunstan wrote:
> Right. Which is why some of us have been suggesting a model where all
> modules currently in contrib are installed by default, but not enabled
> until a database owner actually issues some sort of "Install module
> foo" or whatever it looks like.

Reading the message that starts this thread I note the problem is
underspecified. We have three properties of the provider:

a. You have superuser priveledges on your database
b. You have a shell where you can compile programs
c. Your provider has instlled the postgresql-contrib package

With either a&b or a&c you're home. However, without a you're stuffed.
What I want to know is: does the situation where you have only c but
not b or a happen often? Because that we *can* do something about. Or
are we dealing primarily with providers where you have none of the
above?

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

* Jeremy Drake <pgsql(at)jdrake(dot)com> [080404 01:27]:

> My opinion is, it doesn't matter what you call the modules/contrib stuff
> if I can't use it, and I can't use it if it is not loaded in my
> database, and I can't load it without superuser privileges.

Would it be possible to "change" the rules a bit to make this "more
friendly"?

Right now, the "superuser needed" part of installing a module (like
earthdistance or cube) is the "LANGUAGE C" functions, and that's the
part that allows the "user" to load random code that can gets run as the
postgres user. All the other function/domain stuff doesn't need
superuser privileges.

What if you didn't need super-user privileges to load "C" functions, on
the conditions that:
1) There is no / in the obj_file filename (or some other "sanitizing"
rules)
2) You're database owner

This follows the precedence of the CREATE LANGUAGE, which allows
database owners to install a language as long as it's "known" on the
system.

Doing this still wouldn't help the poor user who's ISP refuses to do
anything besides ./configure && make && make install of base PostgreSQL,
but it does allow distros/packages to make more packages available that
don't need to be "on" in every database, but where database owners can
easily enable any of the installed packages without further ISP admin
intervention.

This changes the game slightly from trying to get systems to come with
PostreSQL "modules" installed into PostgreSQL by default, to where
systems come with PostgreSQL "module" *packages* (rpms, debs, pkg, etc)
installed by default, and the DB owners can do the "PostgreSQL install"
part themselves.

Would this slight change of the game be of any value?

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

Aidan Van Dyk wrote:
> This changes the game slightly from trying to get systems to come with
> PostreSQL "modules" installed into PostgreSQL by default, to where
> systems come with PostgreSQL "module" *packages* (rpms, debs, pkg, etc)
> installed by default, and the DB owners can do the "PostgreSQL install"
> part themselves.
>
> Would this slight change of the game be of any value?
>
>
>

No. "packages" has another meaning in the database context.

I am going to point out AGAIN that we have already had a debate about
this subject, not that long ago, including the name by which we should
call these things. The consensus name then was "modules" and I think
that was right.

Those who do take cognizance of previous debates are doomed to repeat them.

cheers

andrew

* Andrew Dunstan <andrew(at)dunslane(dot)net> [080404 09:35]:
>
>
> Aidan Van Dyk wrote:
> >This changes the game slightly from trying to get systems to come with
> >PostreSQL "modules" installed into PostgreSQL by default, to where
> >systems come with PostgreSQL "module" *packages* (rpms, debs, pkg, etc)
> >installed by default, and the DB owners can do the "PostgreSQL install"
> >part themselves.
> >
> >Would this slight change of the game be of any value?

>
> No. "packages" has another meaning in the database context.
>
> I am going to point out AGAIN that we have already had a debate about
> this subject, not that long ago, including the name by which we should
> call these things. The consensus name then was "modules" and I think
> that was right.
>
> Those who do take cognizance of previous debates are doomed to repeat them.

Sorry - no, I'm not trying to debate the either the name or meaning of
modules in PostgreSQL - I understand that the concensus is "modules".
But I think you missed the whole point of my email.

Right now, PostgreSQL doesn't have a coherent "module" (or even package
for the specific database context) installation/infrastructure. It has
a flexible "use SQL to create domains/types/functions". This wasn't
about changing any of that.

This was simply about changing the user permissions needed to run CREATE
FUNCTION ... LANGUAGE "C" so that distros/packages could have whatever
module they want packaged (in system RPM/DEB/PKG context) and available
on the system in a way that databases owners could install them into
their PostgreSQL database (using the current psql < earthdistance.sql
methods) without getting ISP/superuser assistance.

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

Aidan Van Dyk wrote:
>
> This was simply about changing the user permissions needed to run CREATE
> FUNCTION ... LANGUAGE "C" so that distros/packages could have whatever
> module they want packaged (in system RPM/DEB/PKG context) and available
> on the system in a way that databases owners could install them into
> their PostgreSQL database (using the current psql < earthdistance.sql
> methods) without getting ISP/superuser assistance.
>
>
>

That's not going to happen, at least not like that - the security
implications are just horrible. We have recently relaxed the rules
relating to installation of trusted languages by database owners. But to
extend that to modules in general we'd need some way of designating
modules as safe or not.

cheers

andrew

* Andrew Dunstan <andrew(at)dunslane(dot)net> [080404 10:17]:

> Aidan Van Dyk wrote:

> >This was simply about changing the user permissions needed to run CREATE
> >FUNCTION ... LANGUAGE "C" so that distros/packages could have whatever
> >module they want packaged (in system RPM/DEB/PKG context) and available
> >on the system in a way that databases owners could install them into
> >their PostgreSQL database (using the current psql < earthdistance.sql
> >methods) without getting ISP/superuser assistance.

> That's not going to happen, at least not like that - the security
> implications are just horrible. We have recently relaxed the rules
> relating to installation of trusted languages by database owners. But to
> extend that to modules in general we'd need some way of designating
> modules as safe or not.

Excatly - this was just a one simple method of marking modules as
"safe"; if the object files are found in the "safe" place (possibly the
location of pg_config --pkglibdir, possibly elsewhere, specified by a
GUC, possibly something else), then they could be considered "safe" for
the database owner to use as a LANGUAGE "C" definition. The "safe"
location could be somewhere else.

Like I said earlier, this changes the game (that Jermery original said
was the problem - namely that a module is useless if it's not installed,
and he can't install it unless he's superuser) only slightly. It's just
a method for packagers/distros/sysadmins to mark modules as safe. If
the packages (rpm/deb/pkg context) are installed on the system, and the
files are in the location considered to mark them safe), then any DB
owner could install them into their DB.

It's just one possible way to mark modules as "safe". Just as another
method for used for languages was to use a system table like
pg_pltemplate. But it's something that I think *may* be better than
pg_pltemplate, because pg_pltemplate re-enforces the notion that only
things in core (and installed in core by default) can be considered
safe, because really, how is some dpkg installation of plruby or plperl
or pljava automatically going to get it's info into pl_pltemplate
without specific knowledge of the port your cluster (or all your
clusters) are on, superuser connection details, etc.

But maybe some system table *is* the way to make object files as safe,
so a super-user *is* required to add some row to some table to mark it
as safe.

I'm looking for *some* way to make a way for 3rd party modules to be
considered "safe" so somebody (non-superuser) can install them into a PG
database.

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

On Thu, 3 Apr 2008, Joshua D. Drake wrote:

> IMO the core modules should be compiled via configure with something
> like:
> ./configure --enable-module=ALL

If you really want to make the problems with using contrib modules go
away, so they are a) installed even by lazy ISPs who just do
compile/make/make install, and b) not viewed as second-class citizens when
people have to ask them to be installed, this won't do it. You should
default to installing all the modules and provide configure options to
turn them off instead. All PostgreSQL installations should have them all
available (but not installed in the database, as you point out) unless
someone goes out of their way to circumvent that.

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD

"Aidan Van Dyk" <aidan(at)highrise(dot)ca> writes:

> What if you didn't need super-user privileges to load "C" functions, on
> the conditions that:
> 1) There is no / in the obj_file filename (or some other "sanitizing"
> rules)
> 2) You're database owner

That's an interesting idea. It has the property that no super-user is required
to do any fiddling *inside* your database. That is, the ISP can just do CREATE
DATABASE and then leave you have at it without having to deal with installing
modules or granting any permissions inside your database.

It also opens the door to .deb packagers being able to put pgfoundry modules
in the same space. No other suggestion has offered any help to anything except
blessed contrib modules.

I would suggest a guc for the "safe" place and I would suggest it be a list of
places. And I would suggest that for OS packagers they really want two
locations on that list, something like:
/usr/lib/postgresql/modules;/usr/local/lib/postgresql/modules
That way users can compile and install their own modules into /usr/local
without interfering with modules which come from OS packages.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Get trained by Bruce Momjian - ask me about EnterpriseDB's PostgreSQL training!

Assuming others think something like this might be interesting, would
something to do this be an OK candidate for my first patch, if only to
start this ball rolling?

a.

* Gregory Stark <stark(at)enterprisedb(dot)com> [080404 14:57]:
> "Aidan Van Dyk" <aidan(at)highrise(dot)ca> writes:
>
> > What if you didn't need super-user privileges to load "C" functions, on
> > the conditions that:
> > 1) There is no / in the obj_file filename (or some other "sanitizing"
> > rules)
> > 2) You're database owner
>
> That's an interesting idea. It has the property that no super-user is required
> to do any fiddling *inside* your database. That is, the ISP can just do CREATE
> DATABASE and then leave you have at it without having to deal with installing
> modules or granting any permissions inside your database.
>
> It also opens the door to .deb packagers being able to put pgfoundry modules
> in the same space. No other suggestion has offered any help to anything except
> blessed contrib modules.
>
> I would suggest a guc for the "safe" place and I would suggest it be a list of
> places. And I would suggest that for OS packagers they really want two
> locations on that list, something like:
> /usr/lib/postgresql/modules;/usr/local/lib/postgresql/modules
> That way users can compile and install their own modules into /usr/local
> without interfering with modules which come from OS packages.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> "Aidan Van Dyk" <aidan(at)highrise(dot)ca> writes:
>> What if you didn't need super-user privileges to load "C" functions, on
>> the conditions that:
>> 1) There is no / in the obj_file filename (or some other "sanitizing"
>> rules)
>> 2) You're database owner

> That's an interesting idea.

And utterly, utterly insecure.

The fact that the referenced object file is a "trusted" Postgres module
isn't enough to make it safe --- the user can still play hob with the
system by creating functions with the wrong argument/result types,
pointing at exported symbols that weren't meant to be callable
functions, creating broken index opclasses from the functions, etc.

I think you'd need to move the security gating up a level, and somehow
see the SQL-language installation and deinstallation scripts as trusted.
This goes back to the question of what is a module anyway.

Like Andrew, I'm a bit disturbed that people feel free to propose to
implement this stuff when they evidently have read none of the prior
discussions.

regards, tom lane

On Sat, Apr 5, 2008 at 12:22 AM, Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> "Aidan Van Dyk" <aidan(at)highrise(dot)ca> writes:
>
> > What if you didn't need super-user privileges to load "C" functions, on
> > the conditions that:
> > 1) There is no / in the obj_file filename (or some other "sanitizing"
> > rules)
> > 2) You're database owner
>
> That's an interesting idea. It has the property that no super-user is required
> to do any fiddling *inside* your database. That is, the ISP can just do CREATE
> DATABASE and then leave you have at it without having to deal with installing
> modules or granting any permissions inside your database.

Maybe it didn't come across, but it's exactly what I've been
suggesting in this thread, albeit a slightly different solution.

My idea was to have an installed "module", and rather than allowing
$database_owner to create C language functions based on what are
really implementation details for the given extension, just allow them
to say e.g. "install module postgis;" or equivalent - this would then
run either some init function or an appropriately named and placed
install script that would take care of everything. A module would be
expected to provide an uninstall script, too, to allow "uninstall
module foo" or whatever.

Under this scenario end users don't need access to the install
scripts, don't need to know the exact library name, and aren't given
the ability to e.g. create C language functions in ways that weren't
intended, like declaring one taking the wrong variable types or
something. The sysadmin can trust the module to do the right thing -
they don't have to trust the user.

As far as getting sysadmins to install contrib, this then becomes
really easy - just install them all by default and let database owners
install them into their own dbs as they wish.

> It also opens the door to .deb packagers being able to put pgfoundry modules
> in the same space. No other suggestion has offered any help to anything except
> blessed contrib modules.

Well, I actually was approaching the problem from the point of view of
creating yum installable rpms - killing contrib was just a nice side
effect :)

> I would suggest a guc for the "safe" place and I would suggest it be a list of
> places. And I would suggest that for OS packagers they really want two
> locations on that list, something like:
> /usr/lib/postgresql/modules;/usr/local/lib/postgresql/modules
> That way users can compile and install their own modules into /usr/local
> without interfering with modules which come from OS packages.

+1.

Cheers

Tom

Greg Smith <gsmith(at)gregsmith(dot)com> writes:
> On Thu, 3 Apr 2008, Joshua D. Drake wrote:
>> IMO the core modules should be compiled via configure with something
>> like:
>> ./configure --enable-module=ALL

> If you really want to make the problems with using contrib modules go
> away, so they are a) installed even by lazy ISPs who just do
> compile/make/make install, and b) not viewed as second-class citizens when
> people have to ask them to be installed, this won't do it.

Indeed. If anything, this will make the problem worse by increasing the
"perception gap" between contrib modules and modules that don't come
with the core distribution.

IMHO, the ideal situation would be that the only stuff in contrib is
stuff that needs to be maintained together with the core code --- an
example is pg_controldata, because it looks at data structures that
we change on a frequent basis. We need to be looking for ways to
increase the status of stuff that doesn't come with the core distro,
not create an even stronger gap between the "ins" and the "outs".

regards, tom lane

Gregory Stark <stark(at)enterprisedb(dot)com> writes:
> I would suggest a guc for the "safe" place and I would suggest it be a list of
> places. And I would suggest that for OS packagers they really want two
> locations on that list, something like:
> /usr/lib/postgresql/modules;/usr/local/lib/postgresql/modules
> That way users can compile and install their own modules into /usr/local
> without interfering with modules which come from OS packages.

That seems like a great way to persuade people that "safe" isn't
so safe after all. If I were the kind of ISP that doesn't want
people to have database superuser, there's no way on earth that
I'd accept a setting like that.

regards, tom lane

"Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Gregory Stark <stark(at)enterprisedb(dot)com> writes:
>> I would suggest a guc for the "safe" place and I would suggest it be a list of
>> places. And I would suggest that for OS packagers they really want two
>> locations on that list, something like:
>> /usr/lib/postgresql/modules;/usr/local/lib/postgresql/modules
>> That way users can compile and install their own modules into /usr/local
>> without interfering with modules which come from OS packages.
>
> That seems like a great way to persuade people that "safe" isn't
> so safe after all. If I were the kind of ISP that doesn't want
> people to have database superuser, there's no way on earth that
> I'd accept a setting like that.

Huh? Nobody's forcing the sysadmin to *install* any modules. But if they do
they shouldn't have to overwrite files that came with their OS. All I'm saying
is that distribution packagers need two directories, one for files installed
as part of a distribution package and one for files the sysadmin wants to
install himself for his users.

That's exactly like, say, /usr/share/emacs/site-lisp and
/usr/local/share/emacs/site-lisp.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

Tom Lane wrote:
>
> IMHO, the ideal situation would be that the only stuff in contrib is
> stuff that needs to be maintained together with the core code --- an
> example is pg_controldata, because it looks at data structures that
> we change on a frequent basis. We need to be looking for ways to
> increase the status of stuff that doesn't come with the core distro,
> not create an even stronger gap between the "ins" and the "outs".
>
>
>

Well, I think we need some more than that, although possibly we don't
need everything that's in contrib now. I think it's important that we
keep a few in the core distribution as exemplars of the various module
types (broadly: PLs, types, function libraries).

The example I have in mind is Perl, as I have referred to before. It
comes with a number of useful modules (e.g. File::Find, and CGI) that
don't have to be in the perl core distribution but are very widely used
and so having them there makes some sense.

I do agree that we need to embark on some education to help make
non-core modules more acceptable to the world at large. A standard build
and install framework and a somewhat trustable repository would help a
lot with that. If people want to start building out stuff now on the "if
we build it they will come" theory, then that's where I'd personally
encourage them to put effort.

cheers

andrew

"Andrew Dunstan" <andrew(at)dunslane(dot)net> writes:

> The example I have in mind is Perl, as I have referred to before. It comes with
> a number of useful modules (e.g. File::Find, and CGI) that don't have to be in
> the perl core distribution but are very widely used and so having them there
> makes some sense.

What's different about those is that they are developed primarily as CPAN
modules. The ones in the perl source are just copies of the CPAN module
installed by default.

Would that help earn respect for pgfoundry? Modules there would have something
to aspire to if they grew to be considered indispensable rather than just be
doomed to being forgotten and ignored.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's 24x7 Postgres support!

* Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> [080404 16:12]:

> And utterly, utterly insecure.
>
> The fact that the referenced object file is a "trusted" Postgres module
> isn't enough to make it safe --- the user can still play hob with the
> system by creating functions with the wrong argument/result types,
> pointing at exported symbols that weren't meant to be callable
> functions, creating broken index opclasses from the functions, etc.

So those are all good reasons why we can't just assume a obj_file "safe"
to be used for a C language function with the current framework. Would
a stronger "contract" mechanism between PG and symbols loaded from
object files used for functions...

Today was the first time I actually ever looked at the create function
and create language code, and I'm actually suprised at how little
"contract" there seems to be between a object file and the created
function. But since it's always been guarded by super-user, it's
obviously not been an issue...

> I think you'd need to move the security gating up a level, and somehow
> see the SQL-language installation and deinstallation scripts as trusted.
> This goes back to the question of what is a module anyway.

And, unfortunately, I haven't seen any of those who have a thorough
enough knowledge and understanding of the whole system and security
issues talking a whole lot about that, well, beside shooting gaping
holes through lame ideas like mine ;-)

Maybe it's just because the problem really *is* that hard. But I've
seen some pretty amazing features come in to PG over the past years, so
I know hard problems are solved by you guys...

Unfortunately, the current state really does seem to mean that the
"feature of modularity" really is the kiss of death, since things are
actively pushed out from core to be modular projects, making them
unusable for most people...

> Like Andrew, I'm a bit disturbed that people feel free to propose to
> implement this stuff when they evidently have read none of the prior
> discussions.

Well, I'm happy to go back to lurking for now... Maybe after a few
years I'll have heard and seen more discussions and know better next
time ;-)

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

On Fri, 4 Apr 2008 20:22:51 -0400
Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:
> Unfortunately, the current state really does seem to mean that the
> "feature of modularity" really is the kiss of death, since things are
> actively pushed out from core to be modular projects, making them
> unusable for most people...

Is there anything but anecdotal evidence for this statement? I mean,
yes, some ISPs will make life hard for their users and some <SHAMELESS
PLUG "http://www.Vex.Net/" /> will work with their clients to deliver
what they need. Besides, most users of PostgreSQL are probably
enterprise users or users running off of their own systems who have all
the access they need. How big is this "problem" really?

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

"D'Arcy J.M. Cain" <darcy(at)druid(dot)net> writes:

> On Fri, 4 Apr 2008 20:22:51 -0400
> Aidan Van Dyk <aidan(at)highrise(dot)ca> wrote:
>> Unfortunately, the current state really does seem to mean that the
>> "feature of modularity" really is the kiss of death, since things are
>> actively pushed out from core to be modular projects, making them
>> unusable for most people...
>
> Is there anything but anecdotal evidence for this statement? I mean,
> yes, some ISPs will make life hard for their users and some <SHAMELESS
> PLUG "http://www.Vex.Net/" /> will work with their clients to deliver
> what they need. Besides, most users of PostgreSQL are probably
> enterprise users or users running off of their own systems who have all
> the access they need. How big is this "problem" really?

I was inclined to dismiss it myself but I think the point that's come up here
is interesting. The ISP has to not just install an RPM or type make install in
some source tree -- but actually log into each customer's database and run an
SQL script. That does seem like more work and more risk than a lot of ISPs
will be willing to take on.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's Slony Replication support!

Aidan Van Dyk wrote:
>
> Unfortunately, the current state really does seem to mean that the
> "feature of modularity" really is the kiss of death, since things are
> actively pushed out from core to be modular projects, making them
> unusable for most people...
>

Really? What have we pushed out that has died? The only thing I can
recall of any significance being pushed out is the client libraries, and
most or all of those are still alive and kicking.
>
>> Like Andrew, I'm a bit disturbed that people feel free to propose to
>> implement this stuff when they evidently have read none of the prior
>> discussions.
>>
>
> Well, I'm happy to go back to lurking for now... Maybe after a few
> years I'll have heard and seen more discussions and know better next
> time ;-)
>
>

Don't take it personally.

cheers

andrew

* Andrew Dunstan <andrew(at)dunslane(dot)net> [080404 21:54]:

> >Well, I'm happy to go back to lurking for now... Maybe after a few
> >years I'll have heard and seen more discussions and know better next
> >time ;-)

> Don't take it personally.

I don't, and for the record, I'm actually quite glad that the bar for
entry is so high. That's one of the things that keeps the quality of PG
so high. But that also means that some times, those more in the know
have to put up with incomplete thoughts from those of us who haven't
been immersed -hackers for years yet... ;-)

Apologies to people if my ill-thought and incomplete idea either put
anybody out, or got anybody's hopes up for not...

a.

--
Aidan Van Dyk Create like a god,
aidan(at)highrise(dot)ca command like a king,
http://www.highrise.ca/ work like a slave.

On Sat, 05 Apr 2008 02:17:10 +0100
Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
> I was inclined to dismiss it myself but I think the point that's come up here
> is interesting. The ISP has to not just install an RPM or type make install in
> some source tree -- but actually log into each customer's database and run an
> SQL script. That does seem like more work and more risk than a lot of ISPs
> will be willing to take on.

Why wouldn't you just run it against template1 so that it is available
in every database created after that?

--
D'Arcy J.M. Cain <darcy(at)druid(dot)net> | Democracy is three wolves
http://www.druid.net/darcy/ | and a sheep voting on
+1 416 425 1212 (DoD#0082) (eNTP) | what's for dinner.

> On Sat, 05 Apr 2008 02:17:10 +0100
> Gregory Stark <stark(at)enterprisedb(dot)com> wrote:
>> I was inclined to dismiss it myself but I think the point that's come
>> up here
>> is interesting. The ISP has to not just install an RPM or type make
>> install in
>> some source tree -- but actually log into each customer's database and
>> run an
>> SQL script. That does seem like more work and more risk than a lot of
>> ISPs
>> will be willing to take on.

On (k)Ubuntu you can apt-get install postgresql-contrib-8.3 which puts
everything in the right places, all you have to do then is to run the sql
scripts in /usr/share/postgresql/8.3/contrib/ as user postgres...
But of course you need the ISP to do it for you if you are not superuser.
Some will bother to run a few commands for a user, some won't...

"D'Arcy J.M. Cain" <darcy(at)druid(dot)net> writes:

> Why wouldn't you just run it against template1 so that it is available
> in every database created after that?

Well maybe you don't want *every* customer to have it or not every customer
wants it. And also, what do you do about modules you add after customers have
signed up?

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
Ask me about EnterpriseDB's PostGIS support!

On Fri, Apr 04, 2008 at 08:22:51PM -0400, Aidan Van Dyk wrote:
> Today was the first time I actually ever looked at the create function
> and create language code, and I'm actually suprised at how little
> "contract" there seems to be between a object file and the created
> function. But since it's always been guarded by super-user, it's
> obviously not been an issue...

There was once a discussion about allowing people to add declarations
to the C code indicating the types and returns type to avoid stupid
errors (you could just say CREATE FUNCTION foo FROM module.so and it
would get the types/volitility/etc from the module).

It even went so far that you could build the install/deinstall scripts
into the module itself, so all postgres had to do was dlopen() the
module it could access the install script. It fails due to the fact
that modules that don't require compilation are left out in the cold...

Have a nice day,
--
Martijn van Oosterhout <kleptog(at)svana(dot)org> http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On Sat, Apr 5, 2008 at 5:11 PM, Martijn van Oosterhout
<kleptog(at)svana(dot)org> wrote:
> It even went so far that you could build the install/deinstall scripts
> into the module itself, so all postgres had to do was dlopen() the
> module it could access the install script. It fails due to the fact
> that modules that don't require compilation are left out in the cold...

Well, there's no reason you can't allow both... look for
module_install() in the lib if it's there, or install.sql if it's not.

Cheers

Tom