Updated TODO list

ADMIN

* Better interface for adding to pg_group
* More access control over who can create tables and access the database
* Add syslog functionality
* Allow elog() to return error codes, not just messages
* Allow international error message support and add error codes
* Generate postmaster pid file and remove flock/fcntl lock code
* Add ability to specifiy location of lock/socket files

How about:
* Not storing passwords in plain text

ADMIN

* Allow international error message support and add error codes

The JDBC driver already has the international error message support. All
it needs are some translations of the errors.properties file.

It would be easy to add error codes to it.

TYPES

* Large objects
o Fix large object mapping scheme, own typeid or reltype(Peter)
o Allow large text type to use large objects(Peter)

I hope to these two done for 6.6

o Not to stuff everything as files in a single directory, hash
dirs
o Allow large object vacuuming

Do you mean vacuuming within a large objects table, or removing orphaned
ones?
A solution for orphaning is in contrib, although it still needs some
work.

On the JDBC front, I'm planning for 6.6:

* Array support
* Large Object Stream support
* More JDBC2 API implemented

But the big one will be to get the driver to use the current protocol.
Up until now, it's been using the 6.3 backend protocol. Now that the
current driver supports JDBC2, I want to get it up to date.

This will mean that the 6.6 driver will not be backward compatible with
earlier backends (I can't remember which one started using the current
protocol). The 6.5 driver does support 6.3 and 6.4.x backends.

Peter

--
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.

[Charset iso-8859-1 unsupported, filtering to ASCII...]

ADMIN

* Better interface for adding to pg_group
* More access control over who can create tables and access the database
* Add syslog functionality
* Allow elog() to return error codes, not just messages
* Allow international error message support and add error codes
* Generate postmaster pid file and remove flock/fcntl lock code
* Add ability to specifiy location of lock/socket files

How about:
* Not storing passwords in plain text

But we don't, do we? I thougth they were hashed.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

[Charset iso-8859-1 unsupported, filtering to ASCII...]

ADMIN

* Better interface for adding to pg_group
* More access control over who can create tables and access the database
* Add syslog functionality
* Allow elog() to return error codes, not just messages
* Allow international error message support and add error codes
* Generate postmaster pid file and remove flock/fcntl lock code
* Add ability to specifiy location of lock/socket files

How about:
* Not storing passwords in plain text

I have just added this item:

* Make postgres user have a password by default

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

ADMIN

* Allow international error message support and add error codes

The JDBC driver already has the international error message support. All
it needs are some translations of the errors.properties file.

It would be easy to add error codes to it.

Good. I think Vadim wasn't codes from the backend for scripts and
stuff.

TYPES

* Large objects
o Fix large object mapping scheme, own typeid or reltype(Peter)
o Allow large text type to use large objects(Peter)

I hope to these two done for 6.6

Good. I can help too.

o Not to stuff everything as files in a single directory, hash
dirs
o Allow large object vacuuming

Do you mean vacuuming within a large objects table, or removing orphaned
ones?

Tom Lane says you can get multiple versions of a large object in the
file, and there is no way to remove them.

A solution for orphaning is in contrib, although it still needs some
work.

On the JDBC front, I'm planning for 6.6:

* Array support
* Large Object Stream support
* More JDBC2 API implemented

But the big one will be to get the driver to use the current protocol.
Up until now, it's been using the 6.3 backend protocol. Now that the
current driver supports JDBC2, I want to get it up to date.

Cool.

This will mean that the 6.6 driver will not be backward compatible with
earlier backends (I can't remember which one started using the current
protocol). The 6.5 driver does support 6.3 and 6.4.x backends.

That is not a problem. We have given them enough releases that will
work with the new driver.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Bruce Momjian wrote:

[Charset iso-8859-1 unsupported, filtering to ASCII...]

ADMIN

* Better interface for adding to pg_group
* More access control over who can create tables and access the database
* Add syslog functionality
* Allow elog() to return error codes, not just messages
* Allow international error message support and add error codes
* Generate postmaster pid file and remove flock/fcntl lock code
* Add ability to specifiy location of lock/socket files

How about:
* Not storing passwords in plain text

But we don't, do we? I thougth they were hashed.

do
select * from pg_shadow;

I think that it was agreed that it is better when they can't bw snatched
from
network than to have them hashed in db.
Using currently known technologies we must either either know the
original password
and use challenge-response on net, or else use plaintext (or equivalent)
on the wire.

-------------------
Hannu

From: Bruce Momjian <maillist@candle.pha.pa.us>

ADMIN

How about:
* Not storing passwords in plain text

But we don't, do we? I thougth they were hashed.

maybe I miss something but it does not look so to me:

[PostgreSQL 6.5.0 on i386-unknown-freebsd3.2, compiled by gcc 2.7.2.1]

test1=> select * from pg_shadow;
usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd|valuntil
--------+--------+-----------+--------+--------+---------+------+-----------
-----------------
postgres| 2000|t |t |t |t | |Sat Jan 31
09:00:00 2037 MSK
afmmgr | 2001|f |t |f |t |mgrpwd|
afmusr | 2002|f |t |f |t |usrpwd|
(3 rows)

From: Hannu Krosing <hannu@trust.ee>

How about:
* Not storing passwords in plain text

But we don't, do we? I thougth they were hashed.

do
select * from pg_shadow;

I think that it was agreed that it is better when they can't bw snatched
from
network than to have them hashed in db.
Using currently known technologies we must either either know the
original password
and use challenge-response on net, or else use plaintext (or equivalent)
on the wire.

I would be happier even with storing passwords at the server as a reversible
hash. For example, xor all user passwords with some value (for example
"PostgreSQL") and store base64(xor) strings instead of plain text.

Challenge-response authentication based on MD5 or SHA hashing would be
better, of course. A scheme like this would be reasonably secure:

1. Client initiates connection.
2. Server generates a long (16 byte) random value and passes it to the
client.
3. Client generates a one way hash of the user ID, SHA(password), and the
random number:
hash := SHA(uid [+] SHA(password) [+] randomval)
and sends openly uid and the hash back to the server
4. Server reconstructs the hash using stored SHA(password) and compares it
with the received hash.

Even more secure: don't store SHA(password) at the server but store
SHA(password) XOR <mastervalue>.

Gene Sokolov.

It would be nice if the password scheme you finally settle on can be
optionally replaced (compile-time) by the password hash available native
on the OS. In the case of OpenBSD, the Blowfish-based replacement for the
DES or MD5 based crypt(3) is better suited to resisting dictionary and
other offline attacks by fast processors.

This suggestion is useful in case the shadow password file is compromised.
It is independent of any challenge-response protocol you apply upstream.

Ciao
--Louis <louis@bertrandtech.on.ca>

Louis Bertrand http://www.bertrandtech.on.ca
Bertrand Technical Services, Bowmanville, ON, Canada

OpenBSD: Because security matters. http://www.openbsd.org/

On Fri, 9 Jul 1999, Gene Sokolov wrote:

Gene Sokolov wrote:

From: Hannu Krosing <hannu@trust.ee>

I think that it was agreed that it is better when they can't bw snatched
from
network than to have them hashed in db.
Using currently known technologies we must either either know the
original password
and use challenge-response on net, or else use plaintext (or equivalent)
on the wire.

This seems correct to me.

[snip]
Challenge-response authentication based on MD5 or SHA hashing would be
better, of course. A scheme like this would be reasonably secure:

1. Client initiates connection.
2. Server generates a long (16 byte) random value and passes it to the
client.
3. Client generates a one way hash of the user ID, SHA(password), and the
random number:
hash := SHA(uid [+] SHA(password) [+] randomval)
and sends openly uid and the hash back to the server
4. Server reconstructs the hash using stored SHA(password) and compares it
with the received hash.

But Hannu's point was that you can guard against network sniffing or you
can guard
against the evil 'select * pg_shadow', but not both.

While you're scheme _does_ secure against packet sniffing, it doesn't do
anything
against the select. So, you might as well just store 'password' and pass
back

hash := SHA(uid [+] password [+] randomval).

But I fully admit that cryptography is not my strong suit.

Even more secure: don't store SHA(password) at the server but store
SHA(password) XOR <mastervalue>.

I don't see how. I certainly know _my_ password. So I can compute
SHA(password). So,
if I can see what the database is storing, figuring out <mastervalue> is
a no brainer.
--

Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008

From: Mark Hollomon <mhh@nortelnetworks.com>

Challenge-response authentication based on MD5 or SHA hashing would be
better, of course. A scheme like this would be reasonably secure:

1. Client initiates connection.
2. Server generates a long (16 byte) random value and passes it to the
client.
3. Client generates a one way hash of the user ID, SHA(password), and

the

random number:
hash := SHA(uid [+] SHA(password) [+] randomval)
and sends openly uid and the hash back to the server
4. Server reconstructs the hash using stored SHA(password) and compares

it

with the received hash.

[snip]

While you're scheme _does_ secure against packet sniffing, it doesn't do
anything
against the select. So, you might as well just store 'password' and pass
back

hash := SHA(uid [+] password [+] randomval).

But I fully admit that cryptography is not my strong suit.

I cannot fully agree:
1. Select in this case would give you a value, which you have to use from a
*custom* modified client. Any standard client would not be able to use it.
2. Many people use the same or similar passwords for all logins. Just
obfuscating the passwords would be beneficial for them.
3. See below.

Even more secure: don't store SHA(password) at the server but store
SHA(password) XOR <mastervalue>.

I don't see how. I certainly know _my_ password. So I can compute
SHA(password). So,
if I can see what the database is storing, figuring out <mastervalue> is
a no brainer.

Ok, make it SHA(pass) XOR SHA(mastervalue [+] uid). This way you can't get a
useful info without knowing the master value.

It would be nice if the password scheme you finally settle on can be
optionally replaced (compile-time) by the password hash available native
on the OS. In the case of OpenBSD, the Blowfish-based replacement for the
DES or MD5 based crypt(3) is better suited to resisting dictionary and
other offline attacks by fast processors.

Once you say "strong encryption", you also say "export controls", "wasenaar"
and "avoid it if you can". It means PgSQL team would have to maintain two
distributions - one for the US and one for the rest of the world. It's not
like it cannot be done. I just see no benefit in using encryption instead of
hashing. There is no need for DES or Blowfish to justify the pain.

Gene Sokolov.

Gene Sokolov wrote:

From: Mark Hollomon <mhh@nortelnetworks.com>

While you're scheme _does_ secure against packet sniffing, it doesn't do
anything
against the select. So, you might as well just store 'password' and pass
back

hash := SHA(uid [+] password [+] randomval).

But I fully admit that cryptography is not my strong suit.

I cannot fully agree:
1. Select in this case would give you a value, which you have to use from a
*custom* modified client. Any standard client would not be able to use it.
2. Many people use the same or similar passwords for all logins. Just
obfuscating the passwords would be beneficial for them.
3. See below.

Okay, so only the 'casual cracker' is involved. I don't have a problem
as long
as long as that is stated.

Even more secure: don't store SHA(password) at the server but store
SHA(password) XOR <mastervalue>.

I don't see how. I certainly know _my_ password. So I can compute
SHA(password). So,
if I can see what the database is storing, figuring out <mastervalue> is
a no brainer.

Ok, make it SHA(pass) XOR SHA(mastervalue [+] uid). This way you can't get a
useful info without knowing the master value.

That is better. But, under the 'casual cracker' senario, I don't think
this is necessary.

Also, the unspoken assumption is that mastervalue is site specific, say
randomly
generated at initdb time. But ouch, that sure makes upgrades dicey. And
we would
have to think carefully about where to store it.

--

Mark Hollomon
mhh@nortelnetworks.com
ESN 451-9008 (302)454-9008

No, I am not suggesting PostgreSQL bundle any strong crypto: simply take
advantage of what's available native on the host OS.

BTW, the US export controls only apply to code written in the US. The
Wassenaar Arrangement specifically excludes free/open software.

Ciao
--Louis <louis@bertrandtech.on.ca>

Louis Bertrand http://www.bertrandtech.on.ca
Bertrand Technical Services, Bowmanville, ON, Canada
Tel: +1.905.623.8925 Fax: +1.905.623.3852

OpenBSD: Secure by default. http://www.openbsd.org/

On Fri, 9 Jul 1999, Gene Sokolov wrote:

But we don't, do we? I thougth they were hashed.

do
select * from pg_shadow;

I think that it was agreed that it is better when they can't bw snatched
from
network than to have them hashed in db.
Using currently known technologies we must either either know the
original password
and use challenge-response on net, or else use plaintext (or equivalent)
on the wire.

Yes, I remember now, we hash them with random salt before sending them
to the client, and they are only visible to the postgres user.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

[Charset iso-8859-1 unsupported, filtering to ASCII...]

From: Bruce Momjian <maillist@candle.pha.pa.us>

ADMIN

How about:
* Not storing passwords in plain text

But we don't, do we? I thougth they were hashed.

maybe I miss something but it does not look so to me:

[PostgreSQL 6.5.0 on i386-unknown-freebsd3.2, compiled by gcc 2.7.2.1]

test1=> select * from pg_shadow;
usename |usesysid|usecreatedb|usetrace|usesuper|usecatupd|passwd|valuntil
--------+--------+-----------+--------+--------+---------+------+-----------
-----------------
postgres| 2000|t |t |t |t | |Sat Jan 31
09:00:00 2037 MSK
afmmgr | 2001|f |t |f |t |mgrpwd|
afmusr | 2002|f |t |f |t |usrpwd|
(3 rows)

Yes, I remember now. We keep them in clear, because we send random
salt-encrypted versions over the wire. Only Postgresql can read this
table.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Why should anyone be able to read cleartext passwords, or even need to?
People have a habit of reusing the same password for logins elsewhere.
Hash the password as it's entered and compare hashes. This way, even if
the password file (PostgreSQL's or the system's) is compromised, the
attacker gains no extra information.

Ciao
--Louis <louis@bertrandtech.on.ca>

Louis Bertrand http://www.bertrandtech.on.ca
Bertrand Technical Services, Bowmanville, ON, Canada
Tel: +1.905.623.8925 Fax: +1.905.623.3852

OpenBSD: Secure by default. http://www.openbsd.org/

On Fri, 9 Jul 1999, Bruce Momjian wrote:

From: Louis Bertrand <louis@bertrandtech.on.ca>

No, I am not suggesting PostgreSQL bundle any strong crypto: simply take
advantage of what's available native on the host OS.

BTW, the US export controls only apply to code written in the US. The
Wassenaar Arrangement specifically excludes free/open software.

Not every OS has strong encryption built-in. And that means PgSQL would have
to maintain a crypto package for the OSes without the strong crypto. Thus
the benefits of using the OS encryption would be less significant.

Gene Sokolov.

I completely agree with Louis. It's not just the hacker: there is no need
for sysadmin to know passwords as well. I believe the security scheme where
sysadmin or anyone has to take action in order *not* to see passwords is
flawed.

I think the following solution would be satisfactory:
Store SHA(password) XOR SHA(mastervalue [+] uid). In case it's difficult to
alter the wire protocol, store password XOR SHA(mastervalue [+] uid). Either
way no one can get useful info without knowing the master value. Even simple
password XOR <mastervalue> would be helpful.

Gene Sokolov.

From: Louis Bertrand <louis@bertrandtech.on.ca>