Re: Client-side password encryption

Dave Page wrote:

-----Original Message----- From: pgadmin-hackers-owner@postgresql.org
on behalf of Peter Eisentraut Sent: Sun 12/18/2005 2:25 AM To:
pgadmin-hackers@postgresql.org Subject: [pgadmin-hackers] Client-side
password encryption

Commands like CREATE USER foo PASSWORD 'bar' transmit the password
in cleartext and possibly save the password in various client or
server log files. I have just fixed this for psql and createuser
to encrypt the password on the client side. A quick check of the
pgadmin3 source code shows that you are also affected by this
issue. I ask you to check where you paste cleartext passwords into
SQL commands and change those to encrypt the password before
sending or storing it anywhere. The required function
pg_md5_encrypt() is contained in libpq.

So did you just rip it from there into psql? I don't see it in the
list of libpq exports so if thats not the case, on Windows at least
we'll need to change the api, and possibly the dll name as well to
avoid any compatibility issues.

And a prototype in libpq-fe.h wouldn't hurt either... And a macro, to
enable distinguishing md5-enabled libpq versions from older versions.

Regards,
Andreas

Andreas Pflug wrote:

Dave Page wrote:

So did you just rip it from there into psql? I don't see it in the
list of libpq exports so if thats not the case, on Windows at least
we'll need to change the api, and possibly the dll name as well to
avoid any compatibility issues.

And a prototype in libpq-fe.h wouldn't hurt either... And a macro, to
enable distinguishing md5-enabled libpq versions from older versions.

So it appears that pg_md5_encrypt is not officially exported from libpq.
Does anyone see a problem with adding it to the export list and the
header file?

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

So it appears that pg_md5_encrypt is not officially exported from libpq.
Does anyone see a problem with adding it to the export list and the
header file?

Is it different to normal md5? How is this helpful to the phpPgAdmin
project?

Chris

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

So it appears that pg_md5_encrypt is not officially exported from libpq.
Does anyone see a problem with adding it to the export list and the
header file?

Is it different to normal md5? How is this helpful to the phpPgAdmin
project?

It would be better to export an API that is (a) less random (why one
input null-terminated and the other not?) and (b) less tightly tied
to MD5 --- the fact that the caller knows how long the result must be
is the main problem here.

Something like
char *pg_gen_encrypted_passwd(const char *passwd, const char *user)
with malloc'd result (or NULL on failure) seems more future-proof.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 19 December 2005 05:37
To: Christopher Kings-Lynne
Cc: Peter Eisentraut; pgsql-hackers@postgresql.org; Andreas
Pflug; Dave Page
Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password
encryption

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

So it appears that pg_md5_encrypt is not officially

exported from libpq.

Does anyone see a problem with adding it to the export

list and the

header file?

Is it different to normal md5? How is this helpful to the

phpPgAdmin

project?

It would be better to export an API that is (a) less random (why one
input null-terminated and the other not?) and (b) less tightly tied
to MD5 --- the fact that the caller knows how long the result must be
is the main problem here.

Something like
char *pg_gen_encrypted_passwd(const char *passwd, const
char *user)
with malloc'd result (or NULL on failure) seems more future-proof.

Changing the API is likely to cause fun on Windows for new apps that
find an old libpq.dll. Perhaps at this point it should become
libpq82.dll?

Regards, Dave.

On Mon, Dec 19, 2005 at 08:51:23AM -0000, Dave Page wrote:

Something like
char *pg_gen_encrypted_passwd(const char *passwd, const
char *user)
with malloc'd result (or NULL on failure) seems more future-proof.

Changing the API is likely to cause fun on Windows for new apps that
find an old libpq.dll. Perhaps at this point it should become
libpq82.dll?

Hmm? Libpq already has a version number, I beleive it's upto 4.1 right
now. So if any number is used, it should be that. And secondly, there
have already been new functions added to the API without changing the
library name so why should that happen here?

In windows the trend seems to be to upgrade a library if the one on the
system is too old. If programs are really worried about it, they should
lookup the function dynamically rather than statically...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: 19 December 2005 08:59
To: Dave Page
Cc: Tom Lane; Christopher Kings-Lynne; Peter Eisentraut;
pgsql-hackers@postgresql.org; Andreas Pflug
Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password
encryption

On Mon, Dec 19, 2005 at 08:51:23AM -0000, Dave Page wrote:

Something like
char *pg_gen_encrypted_passwd(const char *passwd, const
char *user)
with malloc'd result (or NULL on failure) seems more future-proof.

Changing the API is likely to cause fun on Windows for new apps that
find an old libpq.dll. Perhaps at this point it should become
libpq82.dll?

Hmm? Libpq already has a version number, I beleive it's upto 4.1 right
now. So if any number is used, it should be that.

Good point

And secondly, there
have already been new functions added to the API without changing the
library name so why should that happen here?

Because I suspect Tom hasn't suffered from 'dll hell' as a non-Windows
user, and because noone else noticed.

In windows the trend seems to be to upgrade a library if the
one on the
system is too old.

Yes, however it's possible that there might be multiple copies of a dll
on a single system. The search order for the DLLs has changed over the
years and over different Windows versions though, so it's not infeasible
for an app to upgrade one copy, but then load a different one when it
runs. It shouldn't affect pgAdmin, psqlODBC or pgInstaller because we
keep the DLLs local to the .exe's which is always first in the search
path, but other apps might suffer.

If programs are really worried about it,
they should
lookup the function dynamically rather than statically...

For the sake of a simple name change?

Regards, Dave.

On Mon, Dec 19, 2005 at 09:16:19AM -0000, Dave Page wrote:

Something like
char *pg_gen_encrypted_passwd(const char *passwd, const
char *user)
with malloc'd result (or NULL on failure) seems more future-proof.

If programs are really worried about it, they should lookup the
function dynamically rather than statically...

For the sake of a simple name change?

The function as stated above doesn't exist yet, so we're adding a new
function, not changing the name of one. The function that started the
thread is not even exported by libpq so changing that shouldn't affect
anybody. Besides, this whole discussion is moot until someone writes
such a function.

As for Windows DLL hell, I don't know a lot about that, but if that's
such a problem, why didn't the original creators of the windows port
stick the version number in there from the start. On UNIX, libpq is
half versioned (the library is, but not the symbols) so I would have
thought copying that idea would have been obvious.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: 19 December 2005 09:38
To: Dave Page
Cc: Tom Lane; Christopher Kings-Lynne; Peter Eisentraut;
pgsql-hackers@postgresql.org; Andreas Pflug
Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password
encryption

On Mon, Dec 19, 2005 at 09:16:19AM -0000, Dave Page wrote:

Something like
char *pg_gen_encrypted_passwd(const char *passwd, const
char *user)
with malloc'd result (or NULL on failure) seems more

future-proof.

If programs are really worried about it, they should lookup the
function dynamically rather than statically...

For the sake of a simple name change?

The function as stated above doesn't exist yet, so we're adding a new
function, not changing the name of one. The function that started the
thread is not even exported by libpq so changing that shouldn't affect
anybody. Besides, this whole discussion is moot until someone writes
such a function.

You missunderstand me - we were asked to start using the function in
third party apps and I pointed out that it wasn't exported so we
couldn't. Tom suggested exporting an API friendly version.

As for the name, I meant the DLL name, not the function name.

As for Windows DLL hell, I don't know a lot about that, but if that's
such a problem, why didn't the original creators of the windows port
stick the version number in there from the start. On UNIX, libpq is
half versioned (the library is, but not the symbols) so I would have
thought copying that idea would have been obvious.

Because we simply didn't think of it at the time, and it's something
that has irked me ever since.

Regards, Dave.

On Mon, Dec 19, 2005 at 10:32:03AM -0000, Dave Page wrote:

As for Windows DLL hell, I don't know a lot about that, but if that's
such a problem, why didn't the original creators of the windows port
stick the version number in there from the start. On UNIX, libpq is
half versioned (the library is, but not the symbols) so I would have
thought copying that idea would have been obvious.

Because we simply didn't think of it at the time, and it's something
that has irked me ever since.

In that case, I agree. I've always thought a lot of problem in windows
could be solved if they systematically added a version number to every
library (like in UNIX).

Are there any reasons why we shouldn't change the libname with every
release like for UNIX? I can't think of any, but you never know...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

-----Original Message-----
From: Martijn van Oosterhout [mailto:kleptog@svana.org]
Sent: 19 December 2005 10:42
To: Dave Page
Cc: Tom Lane; Christopher Kings-Lynne; Peter Eisentraut;
pgsql-hackers@postgresql.org; Andreas Pflug
Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password
encryption

In that case, I agree. I've always thought a lot of problem in windows
could be solved if they systematically added a version number to every
library (like in UNIX).

Are there any reasons why we shouldn't change the libname with every
release like for UNIX? I can't think of any, but you never know...

Not that I can think of.

Regards, Dave

As for Windows DLL hell, I don't know a lot about that, but if
that's such a problem, why didn't the original creators of the
windows port stick the version number in there from the start. On
UNIX, libpq is half versioned (the library is, but not

the symbols)

so I would have thought copying that idea would have been obvious.

Because we simply didn't think of it at the time, and it's

something

that has irked me ever since.

In that case, I agree. I've always thought a lot of problem
in windows could be solved if they systematically added a
version number to every library (like in UNIX).

Are there any reasons why we shouldn't change the libname
with every release like for UNIX? I can't think of any, but
you never know...

Yes.
If FooApp is compiled against 8.0, it will then be unable to run if you
upgrade libpq to 8.1. IIRC on Unix it will "fall forward" to the new
version if it's just a minor version upgrade (correct me if I'm wrong).
On windows, it will break with an ugly dialog box. Which is why DLL
renames are usually only done for backwards incompatible changes.

//Magnus

On Mon, Dec 19, 2005 at 01:07:26PM +0100, Magnus Hagander wrote:

If FooApp is compiled against 8.0, it will then be unable to run if you
upgrade libpq to 8.1. IIRC on Unix it will "fall forward" to the new
version if it's just a minor version upgrade (correct me if I'm wrong).
On windows, it will break with an ugly dialog box. Which is why DLL
renames are usually only done for backwards incompatible changes.

Not quite, in UNIX you have a SONAME which is the file you search for
at runtime. This might end up being symlinked to a different version
than the one you linked against.

The argument for the name change is that then you can have both the old
version and the new versions installed at the same time. So when you
"upgrade" to 8.1, you don't actually remove the old libpq but keep both
around. Then programs using either will continue to work. On UNIX you
don't actually waste any diskspace because you can symlink them
together.

So it's only an issue if you have a policy of removing old versions of
libpq on upgrades... I'm not sure what's "best practice" on windows in
this area.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout wrote:

So it's only an issue if you have a policy of removing old versions of
libpq on upgrades... I'm not sure what's "best practice" on windows in
this area.

When removing the application (in this case: pgsql), you'd remove that
old lib as well if it's the only app using it. If you have another
application installed, the deinstaller should observe this, and keep the
version.

I'm voting +1 for lib name versions.

Regards,
Andreas

-----Original Message-----
From: Magnus Hagander [mailto:mha@sollentuna.net]
Sent: 19 December 2005 12:07
To: Martijn van Oosterhout; Dave Page
Cc: Tom Lane; Christopher Kings-Lynne; Peter Eisentraut;
pgsql-hackers@postgresql.org; Andreas Pflug
Subject: RE: [HACKERS] [pgadmin-hackers] Client-side password
encryption

Yes.
If FooApp is compiled against 8.0, it will then be unable to
run if you
upgrade libpq to 8.1. IIRC on Unix it will "fall forward" to the new
version if it's just a minor version upgrade (correct me if
I'm wrong).
On windows, it will break with an ugly dialog box. Which is why DLL
renames are usually only done for backwards incompatible changes.

So each app ships with it's required version of libpq, thus preventing
any issues, including problems caused by finding an older dll with a
different API.

Regards, Dave.

Yes.
If FooApp is compiled against 8.0, it will then be unable to run if
you upgrade libpq to 8.1. IIRC on Unix it will "fall

forward" to the

new version if it's just a minor version upgrade (correct me if I'm
wrong).
On windows, it will break with an ugly dialog box. Which is why DLL
renames are usually only done for backwards incompatible changes.

So each app ships with it's required version of libpq, thus
preventing any issues, including problems caused by finding
an older dll with a different API.

It makes life easier for us. Only then we can be almost certain that all
apps will ship with 8.0.0, 8.1.0 etc, and nobody will get any minor
version upgrades.

But yeah, the easiest for us is certainly to push that out to the app
vendor. Not really a problem for me, just wanted to point out the
scenario.

//Magnus

-----Original Message-----
From: Magnus Hagander [mailto:mha@sollentuna.net]
Sent: 19 December 2005 14:50
To: Dave Page; Martijn van Oosterhout
Cc: Tom Lane; Christopher Kings-Lynne; Peter Eisentraut;
pgsql-hackers@postgresql.org; Andreas Pflug
Subject: RE: [HACKERS] [pgadmin-hackers] Client-side password
encryption

Yes.
If FooApp is compiled against 8.0, it will then be unable

to run if

you upgrade libpq to 8.1. IIRC on Unix it will "fall

forward" to the

new version if it's just a minor version upgrade (correct

me if I'm

wrong).
On windows, it will break with an ugly dialog box. Which

is why DLL

renames are usually only done for backwards incompatible changes.

So each app ships with it's required version of libpq, thus
preventing any issues, including problems caused by finding
an older dll with a different API.

It makes life easier for us. Only then we can be almost
certain that all
apps will ship with 8.0.0, 8.1.0 etc, and nobody will get any minor
version upgrades.

Why? I'm not advocating that the dll name change with revisions, only
major or minor version changes or if the API changes (which should never
happen from revision to revision (yes, I know...)), depending on which
numbering scheme is used.

Regards, Dave

Martijn van Oosterhout <kleptog@svana.org> writes:

Are there any reasons why we shouldn't change the libname with every
release like for UNIX? I can't think of any, but you never know...

Surely that cure is far worse than the disease. You'd be trading a
might-break risk (app using new function will fail if used with old
library) for a guaranteed-to-break risk (*every* app fails if used
with *any* library version other than what it was built against).

The Unix version of the idea is considerably more flexible than
what would happen on Windows.

regards, tom lane

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 19 December 2005 15:00
To: Martijn van Oosterhout
Cc: Dave Page; Christopher Kings-Lynne; Peter Eisentraut;
pgsql-hackers@postgresql.org; Andreas Pflug
Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password
encryption

Martijn van Oosterhout <kleptog@svana.org> writes:

Are there any reasons why we shouldn't change the libname with every
release like for UNIX? I can't think of any, but you never know...

Surely that cure is far worse than the disease. You'd be trading a
might-break risk (app using new function will fail if used with old
library) for a guaranteed-to-break risk (*every* app fails if used
with *any* library version other than what it was built against).

If it's changed to include the so version, or PG version in the filename
(eg. Libpq41.dll, or libpq82.dll) then all we require is that a vendor
ship the appropriate version with his app. If it installs in a shared
location, it's guaranteed to only be upgraded by a point release because
the windows installers have no convention for including version numbers
in the filenames and will only upgrade a file of the name name, and with
an older version number (from the version resource).

Regards, Dave.

Peter Eisentraut said:

So it appears that pg_md5_encrypt is not officially exported from
libpq. Does anyone see a problem with adding it to the export list
and the header file?

Well, these changes have broken the windows build, so something needs to
change.I don't see a reason in principle not to expose our routine, given
that its name means it is unlikely to conflict with anything else.

cheers

andrew

Tom Lane wrote:

Martijn van Oosterhout <kleptog@svana.org> writes:

Are there any reasons why we shouldn't change the libname with every
release like for UNIX? I can't think of any, but you never know...

Surely that cure is far worse than the disease. You'd be trading a
might-break risk (app using new function will fail if used with old
library) for a guaranteed-to-break risk (*every* app fails if used
with *any* library version other than what it was built against).

The Unix version of the idea is considerably more flexible than
what would happen on Windows.

Different from Unix distros, win32 apps will always bring all their
required libraries with them, so it's totally under control of the
developer/packager. There's no such thing as prerequisite packages for
win32 installs, new lib names will *not* break other apps when installed
because older ones stay untouched.

Regards,
Andreas

By the way,

I've already implemented this in phpPgAdmin trivially using the md5()
function. I can't be bothered using a C library function :D

Chris

Dave Page wrote:

Christopher Kings-Lynne wrote:

By the way,

I've already implemented this in phpPgAdmin trivially using the md5()
function. I can't be bothered using a C library function :D

IIRC the whole point of this exercise was to avoid passing the password
to the server in the first place. Unless you are talking about a PHP
md5() password of course ...

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

I've already implemented this in phpPgAdmin trivially using the md5()
function. I can't be bothered using a C library function :D

IIRC the whole point of this exercise was to avoid passing the password
to the server in the first place. Unless you are talking about a PHP
md5() password of course ...

Yes...

However of course in phpPgAdmin the password has already been sent
cleartext to the webserver from your browser, and the database
connection password parameter is still sent in the clear so...

Chris

-----Original Message-----
From: Christopher Kings-Lynne [mailto:chriskl@familyhealth.com.au]
Sent: 20 December 2005 01:33
To: Dave Page
Cc: Tom Lane; Peter Eisentraut; pgsql-hackers@postgresql.org;
Andreas Pflug
Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password
encryption

By the way,

I've already implemented this in phpPgAdmin trivially using the md5()
function. I can't be bothered using a C library function :D

Yeah, figured you probably would :-)

/D

Andreas Pflug wrote:

Martijn van Oosterhout wrote:

So it's only an issue if you have a policy of removing old versions of
libpq on upgrades... I'm not sure what's "best practice" on windows in
this area.

When removing the application (in this case: pgsql), you'd remove that
old lib as well if it's the only app using it. If you have another
application installed, the deinstaller should observe this, and keep the
version.

I'm voting +1 for lib name versions.

If you add a version number to the Win32 libpq name, you have to update
any command-line compile tools that mention libpq after an upgrade. The
Unix linker knows about version numbers, but the Win32 linker doesn't,
so adding version numbers does add quite a bit of chaos to the Win32
compile world.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

On 12/21/05, Bruce Momjian <pgman@candle.pha.pa.us> wrote:

Andreas Pflug wrote:

Martijn van Oosterhout wrote:

So it's only an issue if you have a policy of removing old versions of
libpq on upgrades... I'm not sure what's "best practice" on windows in
this area.

When removing the application (in this case: pgsql), you'd remove that
old lib as well if it's the only app using it. If you have another
application installed, the deinstaller should observe this, and keep the
version.

I'm voting +1 for lib name versions.

If you add a version number to the Win32 libpq name, you have to update
any command-line compile tools that mention libpq after an upgrade. The
Unix linker knows about version numbers, but the Win32 linker doesn't,
so adding version numbers does add quite a bit of chaos to the Win32
compile world.

win32 compile world *is* a chaos... it's very frustating when you try
to run a program and it fails because a library (when you actually has
the library, at least _a_ version of the library)...

IMHO, adding version numbers to the name of library for windows is a
the cleanest thing you can do...

--
Bruce Momjian                        |  http://candle.pha.pa.us
pgman@candle.pha.pa.us               |  (610) 359-1001
+  If your life is a hard drive,     |  13 Roberts Road
+  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)

On Wed, Dec 21, 2005 at 02:51:46PM -0500, Bruce Momjian wrote:

If you add a version number to the Win32 libpq name, you have to update
any command-line compile tools that mention libpq after an upgrade. The
Unix linker knows about version numbers, but the Win32 linker doesn't,
so adding version numbers does add quite a bit of chaos to the Win32
compile world.

The funny thing about it is that the UNIX linker doesn't know about
version numbers at all. It just looks for a lib<libname>.so (no
version) which is symlinked to the actual version to use. Thus just by
changing a few symlinks you can control which library version is linked
in. Delete the .so and the linker won't find the library anymore (and
fall back to the .a lib) though runtime users still will find it
because they *do* have the version number, which is extracted from the
library itself.

I'm often impressed by the way UNIX is highly configurable yet
trivially transparant at the same time. The chances that anything
remotely similar would work on windws seems slim at best.
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

Martijn van Oosterhout wrote:
-- Start of PGP signed section.

On Wed, Dec 21, 2005 at 02:51:46PM -0500, Bruce Momjian wrote:

If you add a version number to the Win32 libpq name, you have to update
any command-line compile tools that mention libpq after an upgrade. The
Unix linker knows about version numbers, but the Win32 linker doesn't,
so adding version numbers does add quite a bit of chaos to the Win32
compile world.

The funny thing about it is that the UNIX linker doesn't know about
version numbers at all. It just looks for a lib<libname>.so (no
version) which is symlinked to the actual version to use. Thus just by
changing a few symlinks you can control which library version is linked
in. Delete the .so and the linker won't find the library anymore (and
fall back to the .a lib) though runtime users still will find it
because they *do* have the version number, which is extracted from the
library itself.

Yes, important distinction. Thanks for the clarification.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Tom Lane wrote:

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

So it appears that pg_md5_encrypt is not officially exported from libpq.
Does anyone see a problem with adding it to the export list and the
header file?

Is it different to normal md5? How is this helpful to the phpPgAdmin
project?

It would be better to export an API that is (a) less random (why one
input null-terminated and the other not?) and (b) less tightly tied
to MD5 --- the fact that the caller knows how long the result must be
is the main problem here.

Something like
char *pg_gen_encrypted_passwd(const char *passwd, const char *user)
with malloc'd result (or NULL on failure) seems more future-proof.

Where are we on this? In general I agree with Tom, but I have no time to
do the work. Unless someone has an immediate implementation, I suggest
that pro tem we add pg_md5_encrypt to src/interfaces/libpq/exports.txt,
which is the minimum needed to unbreak Windows builds, while this gets
sorted out properly.

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:

Where are we on this? In general I agree with Tom, but I have no time to
do the work. Unless someone has an immediate implementation, I suggest
that pro tem we add pg_md5_encrypt to src/interfaces/libpq/exports.txt,
which is the minimum needed to unbreak Windows builds, while this gets
sorted out properly.

I had forgotten that the Windows build is broken. I'll see what I can
do with throwing together the cleaner-API function.

regards, tom lane

I wrote:

I had forgotten that the Windows build is broken. I'll see what I can
do with throwing together the cleaner-API function.

Done, but I noticed that the change to createuser has arguably broken
it; at least we need to change the docs. To wit, the docs say

-E
--encrypted
Encrypts the user's password stored in the database. If not
specified, the default password behavior is used.

-N
--unencrypted
Does not encrypt the user's password stored in the database. If not
specified, the default password behavior is used.

As currently coded, however, the behavior when neither switch is given
is to force the password to be encrypted --- the database's
password_encryption setting is overridden.

I'm not sure we can do much about this --- certainly we don't want the
default behavior of createuser to still be to send an unencrypted
password. But if we leave the code as-is the docs need a change.

regards, tom lane

Where are we on this? In general I agree with Tom, but I have no time to
do the work. Unless someone has an immediate implementation, I suggest
that pro tem we add pg_md5_encrypt to src/interfaces/libpq/exports.txt,
which is the minimum needed to unbreak Windows builds, while this gets
sorted out properly.

I had forgotten that the Windows build is broken. I'll see what I can
do with throwing together the cleaner-API function.

Another question about these encrypted passwords. phpPgAdmin needs to
connect to databases that are sometimes on other servers.

I use the pg_connect() function to do this. This is passed down to
PQconenct() I presume.

So, can I specify the password to pg_connect() as
'md5127349123742342344234'?

ie. Can I CONNECT using an md5'd password?

Also, does this work for non-md5 host lines on the server, and how can I
avoid doing it on older (pre-7.2) PostgreSQL??

Chris

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

So, can I specify the password to pg_connect() as
'md5127349123742342344234'?

Certainly not. We'd hardly be worrying about obscuring the original
password if the encrypted version were enough to get in with.

regards, tom lane

So, can I specify the password to pg_connect() as
'md5127349123742342344234'?

Certainly not. We'd hardly be worrying about obscuring the original
password if the encrypted version were enough to get in with.

AndrewSN can't post at the moment, but asked me to post this for him:

"Knowing the md5 hash is enough to authenticate via the 'md5' method in
pg_hba.conf, even if you don't know the original password. Admittedly
you have to modify libpq to do this, but this isn't going to stop an
attacker for more than 5 seconds."

I'll add my own note that never sending the cleartext password does not
necessarily improve PostgreSQL security, but certainly stops someone who
sniffs the password from then using that cleartext password to get into
other applications. If all they can get is the md5 hash, then all they
can get into is PostgreSQL.

Chris

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

AndrewSN can't post at the moment, but asked me to post this for him:
"Knowing the md5 hash is enough to authenticate via the 'md5' method in
pg_hba.conf, even if you don't know the original password.

If you know the md5 hash, you know everything the postmaster does, so
it's hard to see where such an attacker is going to be stopped. The
entire point here is not to expose the cleartext password, and that
really has nothing to do with whether you're going to break into the
PG database. It's about protecting users who are foolish enough to
use the same cleartext password for multiple services.

regards, tom lane

Tom Lane <tgl@sss.pgh.pa.us> writes:

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

AndrewSN can't post at the moment, but asked me to post this for him:
"Knowing the md5 hash is enough to authenticate via the 'md5' method in
pg_hba.conf, even if you don't know the original password.

If you know the md5 hash, you know everything the postmaster does, so
it's hard to see where such an attacker is going to be stopped.

Eh? Just because you know everything the postmaster does doesn't mean you
can't be stopped. In the traditional unix password file scheme the crypt
string is public knowledge but it's not enough to log in. You need the
original password that crypts to that value.

The entire point here is not to expose the cleartext password, and that
really has nothing to do with whether you're going to break into the PG
database. It's about protecting users who are foolish enough to use the same
cleartext password for multiple services.

Well that's a fine goal but it's not as good as an authentication scheme that
doesn't store a password equivalent in the database.

--
greg

On Fri, Dec 23, 2005 at 09:12:52AM -0500, Greg Stark wrote:

Eh? Just because you know everything the postmaster does doesn't mean you
can't be stopped. In the traditional unix password file scheme the crypt
string is public knowledge but it's not enough to log in. You need the
original password that crypts to that value.

This isn't the first time this has been explained, but:

With password encryption you essentially have two options:

- Server knows password, use challenge-response authentication so
password is not visible on wire.
- Server only knows hash of password, password must be sent in clear
over wire.

These exist in the real world as PAP or CHAP, but there are many other
examples. The reason it works in UNIX login is that the "in-the-clear"
transit of the password is from the keyboard, via the kernel to a
single process, not over a network, so it is considered secure. The
login protocol for SMB has a similar flaw. If you can read the password
file on an SMB server, you can login as any user. You may have to hack
a client to make it work, but it is possible.

PostgreSQL uses a variation where the cleartext password sent is just
the md5 hash of the real password. It just stops the admin guessing it
to see if the user is using it elsewhere. You really don't need the
original password to login, just the hash.

The solution is obvious, public-key authentication which doesn't have
these problems. eg SSH, SSL, etc... Or a trusted third party (ident).

Have a nice day,

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

* Martijn van Oosterhout (kleptog@svana.org) wrote:

On Fri, Dec 23, 2005 at 09:12:52AM -0500, Greg Stark wrote:

Eh? Just because you know everything the postmaster does doesn't mean you
can't be stopped. In the traditional unix password file scheme the crypt
string is public knowledge but it's not enough to log in. You need the
original password that crypts to that value.

This isn't the first time this has been explained, but:

With password encryption you essentially have two options:

- Server knows password, use challenge-response authentication so
password is not visible on wire.
- Server only knows hash of password, password must be sent in clear
over wire.

Erm, Postgres isn't doing either of these...? You even talk about what
Postgres does below so I'm kind of bemused that you don't mention it in
your list... :)

These exist in the real world as PAP or CHAP, but there are many other
examples. The reason it works in UNIX login is that the "in-the-clear"
transit of the password is from the keyboard, via the kernel to a
single process, not over a network, so it is considered secure. The
login protocol for SMB has a similar flaw. If you can read the password
file on an SMB server, you can login as any user. You may have to hack
a client to make it work, but it is possible.

Well, and these days quite often the network connection is encrypted.

PostgreSQL uses a variation where the cleartext password sent is just
the md5 hash of the real password. It just stops the admin guessing it
to see if the user is using it elsewhere. You really don't need the
original password to login, just the hash.

Stops the admin from guessing the password, but makes the text on the
disk *the* authentication token, meaning someone who manages to get a
copy of the password file gets full access to the system.

The solution is obvious, public-key authentication which doesn't have
these problems. eg SSH, SSL, etc... Or a trusted third party (ident).

There's also Kerberos, which I'm happy to say seems to be getting more
and more use. I'd really like to get ODBC Kerberos working, at least
with MIT kerberos and then maybe someday (if I can manage to get it
working...) setup some cross-realm stuff with the Windows AD and SSPI
(iirc) things and have ODBC use that to authenticate against my
Linux-based PostgreSQL server.

I guess to do that we'd have to make libpq under Windows have the option
of using the Windows SSPI layer. Anyone looked into this at all?
Anyone know if it'd have a chance of getting accepted?

Thanks,

Stephen

On 23 Dec 2005 09:12:52 -0500, Greg Stark <gsstark@mit.edu> wrote:

Tom Lane <tgl@sss.pgh.pa.us> writes:

Christopher Kings-Lynne <chriskl@familyhealth.com.au> writes:

AndrewSN can't post at the moment, but asked me to post this for him:
"Knowing the md5 hash is enough to authenticate via the 'md5' method in
pg_hba.conf, even if you don't know the original password.

If you know the md5 hash, you know everything the postmaster does, so
it's hard to see where such an attacker is going to be stopped.

Eh? Just because you know everything the postmaster does doesn't mean you
can't be stopped. In the traditional unix password file scheme the crypt
string is public knowledge but it's not enough to log in. You need the
original password that crypts to that value.

In unix scheme the cleartext password is send over wire and server
stores hash. So indeed even if you know the hash, it wont get you in.

But current postgres case the md5 is sent over wire and also stored
in server. So knowing md5 is enough to get in. The md5 is only used
to obfuscate the cleartext password from administrators, in case
the user uses it somewhere else too.

(And the reason for current md5 hacking is to avoid the cleartext
password from appearing from logs, thus overall rounding the goal.)

The entire point here is not to expose the cleartext password, and that
really has nothing to do with whether you're going to break into the PG
database. It's about protecting users who are foolish enough to use the same
cleartext password for multiple services.

Well that's a fine goal but it's not as good as an authentication scheme that
doesn't store a password equivalent in the database.

http://srp.stanford.edu/whatisit.html

--
marko

On Fri, Dec 23, 2005 at 09:42:44AM -0500, Stephen Frost wrote:

* Martijn van Oosterhout (kleptog@svana.org) wrote:

This isn't the first time this has been explained, but:

With password encryption you essentially have two options:

- Server knows password, use challenge-response authentication so
password is not visible on wire.
- Server only knows hash of password, password must be sent in clear
over wire.

Erm, Postgres isn't doing either of these...? You even talk about what
Postgres does below so I'm kind of bemused that you don't mention it in
your list... :)

Postgres *is* using one of these, the first one, where the server knows
the authentication token (the md5 hash of the password). UNIX login
uses the latter. Perhaps if you substitute "authentication token" for
"password" above it makes it clearer?

Well, and these days quite often the network connection is encrypted.

If you use SSL or SSH? Sure. I think in that case you can setup
pg_hba.conf to require "password" in which case the server will only
accept an unhashed password.

Stops the admin from guessing the password, but makes the text on the
disk *the* authentication token, meaning someone who manages to get a
copy of the password file gets full access to the system.

If md5 auth is setup, yes.

There's also Kerberos, which I'm happy to say seems to be getting more
and more use. I'd really like to get ODBC Kerberos working, at least
with MIT kerberos and then maybe someday (if I can manage to get it
working...) setup some cross-realm stuff with the Windows AD and SSPI
(iirc) things and have ODBC use that to authenticate against my
Linux-based PostgreSQL server.

Yeah, I was counting kerberos under "trust a third party". It shouldn't
be too hard to add other such systems, like PAM has been...

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

* Martijn van Oosterhout (kleptog@svana.org) wrote:

On Fri, Dec 23, 2005 at 09:42:44AM -0500, Stephen Frost wrote:

* Martijn van Oosterhout (kleptog@svana.org) wrote:

This isn't the first time this has been explained, but:

With password encryption you essentially have two options:

- Server knows password, use challenge-response authentication so
password is not visible on wire.
- Server only knows hash of password, password must be sent in clear
over wire.

Erm, Postgres isn't doing either of these...? You even talk about what
Postgres does below so I'm kind of bemused that you don't mention it in
your list... :)

Postgres *is* using one of these, the first one, where the server knows
the authentication token (the md5 hash of the password). UNIX login
uses the latter. Perhaps if you substitute "authentication token" for
"password" above it makes it clearer?

Is it actually doing challenge-response where the challenge is different
each time? I thought it just used the user's username or some such.
Point being- can an attacker use what's passed around on the network to
authenticate to the system directly? If it's a random
challenge/response setup then they shouldn't be able to unless they
manage to convince the server to give it the same challenge (which
should be very difficult).

Well, and these days quite often the network connection is encrypted.

If you use SSL or SSH? Sure. I think in that case you can setup
pg_hba.conf to require "password" in which case the server will only
accept an unhashed password.

Yeah, but you can't do both, which is the real annoyence. You can't get
it to do the same as unix-based auth.

Stops the admin from guessing the password, but makes the text on the
disk *the* authentication token, meaning someone who manages to get a
copy of the password file gets full access to the system.

If md5 auth is setup, yes.

Yeah, but the other alternative is clear-text passwords on the disk, not
something I really care for either, honestly.

There's also Kerberos, which I'm happy to say seems to be getting more
and more use. I'd really like to get ODBC Kerberos working, at least
with MIT kerberos and then maybe someday (if I can manage to get it
working...) setup some cross-realm stuff with the Windows AD and SSPI
(iirc) things and have ODBC use that to authenticate against my
Linux-based PostgreSQL server.

Yeah, I was counting kerberos under "trust a third party". It shouldn't
be too hard to add other such systems, like PAM has been...

I don't know that I'd consider PAM under Postgres as really being PAM.
It's *only* password-checking, and there's the issue that Postgres
doesn't have a root process to do the PAM work under so the postgres
user has to be in shadow (which means all the Postgres processes can
read /etc/shadow, not exactly a nice setup) to have PAM work. Perhaps
SASL and saslauthd could be an option for Postgres. I'm not sure though
as I don't think the Postgres protocol currently supports the kind of
back-and-forth that both PAM and SASL want to be able to do for things
like password-expring, forced-password-changes, etc.

Thanks,

Stephen

Stephen Frost wrote:

Is it actually doing challenge-response where the challenge is different
each time?

The docs say:

AuthenticationMD5Password

The frontend must now send a PasswordMessage containing the password
encrypted via MD5, using the 4-character salt specified in the
AuthenticationMD5Password message. If this is the correct password,
the server responds with an AuthenticationOk, otherwise it responds
with an ErrorResponse.

A little investigation reveals that this is port->md5salt which is 4
random bytes set up fresh per connection (see src/backend/libpq/auth.c
and src/backend/postmaster/postmaster.c). So it seems indeed to be a
true (small) one time challenge token, unless I've missed something.

cheers

andrew

-----Original Message-----
From: Stephen Frost [mailto:sfrost@snowman.net]
Sent: Fri 12/23/2005 2:42 PM
To: Martijn van Oosterhout
Cc: Greg Stark; Tom Lane; Christopher Kings-Lynne; Andrew Dunstan; Peter Eisentraut; pgsql-hackers@postgresql.org; Andreas Pflug; Dave Page
Subject: Re: [HACKERS] [pgadmin-hackers] Client-side password encryption

There's also Kerberos, which I'm happy to say seems to be getting more
and more use. I'd really like to get ODBC Kerberos working, at least
with MIT kerberos and then maybe someday (if I can manage to get it
working...) setup some cross-realm stuff with the Windows AD and SSPI
(iirc) things and have ODBC use that to authenticate against my
Linux-based PostgreSQL server.

psqlODBC & libpq already work well with MIT Kerberos on Windows as well as *nix. I believe Magnus has production systems running with a Linux based server authenticating against the AD.

AFAICR, the bit that doesn't work yet is server-side Kerberos on Windows, which just means you have to have the server running on *nix.

Regards, Dave

There's also Kerberos, which I'm happy to say seems to be
getting more and more use. I'd really like to get ODBC
Kerberos working, at least with MIT kerberos and then maybe
someday (if I can manage to get it
working...) setup some cross-realm stuff with the Windows AD and SSPI
(iirc) things and have ODBC use that to authenticate against
my Linux-based PostgreSQL server.

ODBC and Kerberos works just fine, if you use the 8.1 ODBC driver. I use
it all the time :)
Haven't tried any cross-realm work, though, but I use it to authenticate
Windows users in AD to a postgresql server running on Linux.
(It's not SSPI, btw, it's plain Kerberos)

(it works with libpq and OLEDB in 8.0.2 (I think, it could be .3), but
it's much better in 8.1)

I guess to do that we'd have to make libpq under Windows have
the option of using the Windows SSPI layer. Anyone looked
into this at all?
Anyone know if it'd have a chance of getting accepted?

That is another thing alltogether, which would allow us to work with NT4
domains (not really interesting, IMHO) and local windows accounts (which
might be interesting).

In general, I'm not sure it's worth it considering we can do AD with
Kerberos. It might be interesting to be able to use windows accounts and
passwords to do authentication that's *not* integrated (meaning we take
the password from the user and just use the windows SAM instead of a
passwd file). That's a completely different thing, though.

//Magnus

* Magnus Hagander (mha@sollentuna.net) wrote:

ODBC and Kerberos works just fine, if you use the 8.1 ODBC driver. I use
it all the time :)

That's what I had heard, I just havn't gotten it working yet myself. :)
Believe me when I say that I *really* want to have it working though;
this postgres->pam->libpam-krb5 nonsense is really a huge pain...

Haven't tried any cross-realm work, though, but I use it to authenticate
Windows users in AD to a postgresql server running on Linux.
(It's not SSPI, btw, it's plain Kerberos)

The windows users still have to be running leash and kinit'ing against
your unix-based KDC, don't they? If we had SSPI then the credentials
your users use to log into the Windows AD could be used to authenticate
them to the database as well. It'd need to be cross-realm though and
that can be difficult due to having to find a common encryption key that
doesn't suck (does one exist with the more recent versions of AD? I
don't know and I had real difficulty getting cross-realm working before,
which is very frustrating :( ).

(it works with libpq and OLEDB in 8.0.2 (I think, it could be .3), but
it's much better in 8.1)

I'll definitely give it a shot. I think I'm going to upgrade our main
databases next week when it's quiet to 8.1 and see how that goes. I'll
definitely also try out MIT leash and 8.1 ODBC from Access to Postgres.

I guess to do that we'd have to make libpq under Windows have
the option of using the Windows SSPI layer. Anyone looked
into this at all?
Anyone know if it'd have a chance of getting accepted?

That is another thing alltogether, which would allow us to work with NT4
domains (not really interesting, IMHO) and local windows accounts (which
might be interesting).

Yeah, the ability to use the credentials the users get when they log in
to authenticate them to Postgres would be *really* nice. Get that
working for Apache2, SSH, POP3/IMAP, etc, and you get the
'single-sign-on' golden crown... :)

In general, I'm not sure it's worth it considering we can do AD with
Kerberos. It might be interesting to be able to use windows accounts and
passwords to do authentication that's *not* integrated (meaning we take
the password from the user and just use the windows SAM instead of a
passwd file). That's a completely different thing, though.

I'm not really sure I follow what you mean by 'AD with Kerberos'. I
thought there were only two different options: MIT Kerberos on Windows
(which isn't AD and you have to use leash to kinit seperately with) or
SSPI and Windows AD (which means you have to implement against SSPI in
the client code). Those are the two options the PuTTY Kerberos folks
provide and they do it using two different .dll files (where you rename
whichever one to 'krbauth.dll' or some such). I suppose another option
would be to have the Windows systems authenticate against Samba and a
Unix-based KDC but I didn't think that worked all that well yet and
unfortunately isn't an option for me anyway (can't just change people
from using the corporate domain server to using one I set up, wouldn't
really want to anyway).

I'd love to discuss this further and I'm interested enough in SSPI
support (assuming it's necessary to do what I want) that I'd be willing
to spend some time looking into implementing it. I don't think it'd be
too difficult, aiui it's quite similar to the Kerberos calls...

Thanks!

Stephen