Thread: Updated TODO list

Updated TODO list

From
Bruce Momjian
Date:
Attached is the current TODO list.  It has all the current things I
have.

This is also updated in the cvs tree every time I make a change.

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

TODO list for PostgreSQL
========================
Last updated:        Wed Jul  7 23:33:17 EDT 1999

Current maintainer:    Bruce Momjian (maillist@candle.pha.pa.us)

The most recent version of this document can be viewed at
the PostgreSQL web site, http://www.postgreSQL.org.

A dash(-) marks changes that will appear in the next release.


RELIABILITY
-----------

RESOURCES

* Elog() does not free all its memory(Jan)
* spinlock stuck problem when elog(FATAL) and elog(ERROR) inside bufmgr
* Recover or force failure when disk space is exhausted

PARSER

* Disallow inherited columns with the same name as new columns
* INSERT INTO ... SELECT with AS columns matching result columns problem
* SELECT pg_class FROM pg_class generates strange error
* Alter TABLE ADD COLUMN does not honor DEFAULT, add CONSTRAINT
* Do not allow bpchar column creation without length
* Select a[1] FROM test fails, it needs test.a[1]
* Array index references without table name cause problems
* Update table SET table.value = 3 fails
* Creating index of TIMESTAMP & RELTIME fails, rename to DATETIME(Thomas)
* SELECT foo UNION SELECT foo is incorrectly simplified to SELECT foo
* INSERT ... SELECT ... GROUP BY groups by target columns not source columns
* CREATE TABLE test (a char(5) DEFAULT text '', b int4) fails on INSERT
* UNION with LIMIT fails

VIEWS

* Views containing aggregates sometimes fail(Jan)
* Views with spaces in view name fail when referenced

MISC

* User who can create databases can modify pg_database table
* Plpgsql does not handle quoted mixed-case identifiers

ENHANCEMENTS
------------

URGENT

* Add referential integrity
* Add OUTER joins, left and right(Thomas)
* Allow long tuples by chaining or auto-storing outside db (chaining,large objs)
* Eliminate limits on query length
* Fix memory leak for expressions?, aggregates?

EXOTIC FEATURES

* Add sql3 recursive unions
* Add the concept of dataspaces
* Add replication of distributed databases
* Allow queries across multiple databases

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

TYPES

* Add BIT, BIT VARYING
* Nchar (as distinguished from ordinary varchar),
* Domain capability
* Add STDDEV/VARIANCE() function for standard deviation computation/variance
* Allow compression of large fields or a compressed field type
* Large objectso Fix large object mapping scheme, own typeid or reltype(Peter)o Allow large text type to use large
objects(Peter)oNot to stuff everything as files in a single directory, hash dirso Allow large object vacuuming
 
* Allow pg_descriptions when creating types, tables, columns, and functions
* Add IPv6 capability to INET/CIDR types
* Make a separate SERIAL type?
* Store binary-compatible type information in the system
* Allow user to define char1 column
* Add support for & operator
* Allow LOCALE on a per-column basis, default to ASCII
* Allow array on int8[]
* Remove Money type, add money formatting for decimal type
* Declare typein/out functions in pg_proc with a special "C string" data type
* Add non-large-object binary field
* Add index on NUMERIC type

VIEWS

* Allow DISTINCT on views
* Allow views of aggregate columns
* Allow views with subselects

INDEXES

* Allow CREATE INDEX zman_index ON test (date_trunc( 'day', zman ) datetime_ops) fails index can't store constant
parameters
* Allow creation of functional indexes to use default types
* Permissions on indexes - prevent them?
* Allow SQL function indexes
* Add FILLFACTOR to index creation
* Allow indexing of LIKE with localle character sets
* Allow indexing of more than eight columns

COMMANDS

* ALTER TABLE ADD COLUMN to inherited table put column in wrong place
* Add ALTER TABLE DROP/ALTER COLUMN feature
* Allow CLUSTER on all tables at once, and improve CLUSTER
* Generate error on CREATE OPERATOR of ~~, ~ and and ~*
* Add SIMILAR TO to allow character classes, 'pg_[a-c]%'
* Auto-destroy sequence on DROP of table with SERIAL(Ryan)
* Allow LOCK TABLE tab1, tab2, tab3 so all tables locked in unison
* Allow INSERT/UPDATE of system-generated oid value for a row
* Allow ESCAPE '\' at the end of LIKE for ANSI compliance
* Rewrite the LIKE handling by rewriting the user string with the  supplied ESCAPE
* Move LIKE index optimization handling to the optimizer 
CLIENTS

* Make NULL's come out at the beginning or end depending on the  ORDER BY direction
* Allow flag to control COPY input/output of NULLs
* Update reltuples from COPY command
* Allow psql \copy to allow delimiters
* Add a function to return the last inserted oid, for use in psql scripts
* Allow psql to print nulls as distinct from ""(?)
* PQrequestCancel() be able to terminate backend waiting for lock

MISC

* Increase identifier length(NAMEDATALEN) if small performance hit
* Allow row re-use without vacuum(Vadim)
* Create a background process for each database that runs while database is idle, finding superceeded rows, gathering
statsand vacuuming
 
* Add UNIQUE capability to non-btree indexes
* Certain indexes will not shrink, i.e. oid indexes with many inserts
* Restore unused oid's on backend exit if no one else has gotten oids
* Have UPDATE/DELETE clean out indexes
* Allow WHERE restriction on ctid
* Allow cursors to be DECLAREd/OPENed/CLOSEed outside transactions
* Allow PQrequestCancel() to terminate when in waiting-for-lock state
* Transaction log, so re-do log can be on a separate disk by with after-row images
* Populate backend status area and write program to dump status data
* Make oid use unsigned int more reliably, pg_atoi()
* Allow subqueries in target list
* Put sort files, large objects in their on directory
* Do autocommit so always in a transaction block
* Show location of syntax error in query
* Redesign the function call interface to handle NULLs better(Jan)
* Document/trigger/rule so changes to pg_shadow create pg_pwd
* Missing optimizer selectivities for date, r-tree, etc.
* Overhaul mdmgr/smgr to fix double unlinking and double opens, cleanup
* Overhaul bufmgr/lockmgr/transaction manager
* Tables that start with xinv confused to be large objects
* Add PL/Perl(Mark Hollomon)


PERFORMANCE
-----------

FSYNC

* Allow transaction commits with rollback with no-fsync performance
* Prevent fsync in SELECT-only queries

INDEXES

* Use indexes in ORDER BY for restrictive data sets, min(), max()
* Pull requested data directly from indexes, bypassing heap data
* Use index to restrict rows returned by multi-key index when used with non-consecutive keys or OR clauses, so fewer
heapaccesses
 
* Convert function(constant) into a constant for index use
* Allow LIMIT ability on single-table queries that have no ORDER BY to use a matching index
* Improve LIMIT processing by using index to limit rows processed
* Have optimizer take LIMIT into account when considering index scans
* Make index creation use psort code, because it is now faster(Vadim)
* Allow creation of sort temp tables > 1 Gig
* Create more system table indexes for faster cache lookups
* fix indexscan() so it does leak memory by not requiring caller to free
* Improve _bt_binsrch() to handle equal keys better, remove _bt_firsteq()(Tom)

CACHE

* Cache most recent query plan(s?)
* Shared catalog cache, reduce lseek()'s by caching table size in shared area
* elog() flushes cache, try invalidating just entries from current xact, perhaps using invalidation cache


MISC

* Allow compression of log and meta data
* Update pg_statistic table to remove operator column
* Allow char() not to use variable-sized header to reduce disk size
* Do async I/O to do better read-ahead of data
* Fix memory exhaustion when using many OR's
* Get faster regex() code from Henry Spencer <henry@zoo.utoronto.ca> when it is available
* Use mmap() rather than SYSV shared memory(?)
* Process const = const parts of OR clause in separate pass
* Make oid use oidin/oidout not int4in/int4out in pg_type.h
* Improve Subplan list handling
* Allow Subplans to use efficient joins(hash, merge) with upper variable
* use fmgr_info()/fmgr_faddr() instead of fmgr() calls in high-traffic places, like GROUP BY, UNIQUE, index processing,
etc.
* improve dynamic memory allocation by introducing tuple-context memory allocation
* fix memory leak in cache code when non-existant table is referenced
* In WHERE x=3 AND x=y, add y=3
* pass atttypmod through parser in more cases(Bruce)
* remove duplicate type in/out functions for disk and net

SOURCE CODE
-----------
* Add use of 'const' for varibles in source tree
* Fix C optimizer problem where fmgr_ptr calls return different types


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


Developers who have claimed items are:
--------------------------------------* Billy is Billy G. Allie <Bill.Allie@mug.org>* Brook is Brook Milligan
<brook@trillium.NMSU.Edu>*Bruce is Bruce Momjian<maillist@candle.pha.pa.us>* Bryan is Bryan
Henderson<bryanh@giraffe.netgate.net>*D'Arcy is D'Arcy J.M. Cain <darcy@druid.net>* David is David Hartwig
<daveh@insightdist.com>*Edmund is Edmund Mergl <E.Mergl@bawue.de>* Goran is Goran Thyni <goran@kyla.kiruna.se>* Hiroshi
isHiroshi Inoue<Inoue@tpf.co.jp>* Jan is Jan Wieck <wieck@sapserv.debis.de>    * Marc is Marc Fournier
<scrappy@hub.org>*Massimo Dal Zotto <dz@cs.unitn.it>* Michael is Michael Meskes <meskes@postgresql.org>* Oleg is Oleg
Bartunov<oleg@sai.msu.su>* Peter is Peter T Mount <peter@retep.org.uk>* Ryan is Ryan Bradetich
<rbrad@hpb50023.boi.hp.com>*Stefan Simkovics <ssimkovi@rainbow.studorg.tuwien.ac.at>* Tatsuo is Tatsuo Ishii
<t-ishii@sra.co.jp>*Tom is Tom Lane <tgl@sss.pgh.pa.us>* Thomas is Thomas Lockhart <lockhart@alumni.caltech.edu>* TomH
isTom I Helbekkmo <tih@Hamartun.Priv.NO>* Vadim is "Vadim B. Mikheev" <vadim@krs.ru>
 



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


Re: [HACKERS] Updated TODO list

From
"Gene Sokolov"
Date:
> 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



Re: [HACKERS] Updated TODO list

From
Bruce Momjian
Date:
[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,
Pennsylvania19026
 


Re: [HACKERS] Updated TODO list

From
Bruce Momjian
Date:
[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,
Pennsylvania19026
 


Re: [HACKERS] Updated TODO list

From
Hannu Krosing
Date:
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.

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



Hashing passwords (was Updated TODO list)

From
"Gene Sokolov"
Date:
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)






Hashing passwords (was Updated TODO list)

From
"Gene Sokolov"
Date:
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.





Re: [HACKERS] Hashing passwords (was Updated TODO list)

From
Louis Bertrand
Date:
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:

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




Re: [HACKERS] Hashing passwords (was Updated TODO list)

From
"Mark Hollomon"
Date:
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


Re: [HACKERS] Hashing passwords (was Updated TODO list)

From
"Gene Sokolov"
Date:
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.





Re: [HACKERS] Hashing passwords (was Updated TODO list)

From
"Gene Sokolov"
Date:
> 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.




Re: [HACKERS] Hashing passwords (was Updated TODO list)

From
"Mark Hollomon"
Date:
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


Re: [HACKERS] Hashing passwords (was Updated TODO list)

From
Louis Bertrand
Date:
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:

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




Re: Hashing passwords (was Updated TODO list)

From
Bruce Momjian
Date:
[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,
Pennsylvania19026
 


Re: [HACKERS] Updated TODO list

From
Bruce Momjian
Date:
> > 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,
Pennsylvania19026
 


Re: [HACKERS] Re: Hashing passwords (was Updated TODO list)

From
Louis Bertrand
Date:
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:

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




Re: [HACKERS] Re: Hashing passwords (was Updated TODO list)

From
"Gene Sokolov"
Date:
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>
> 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.
>
> > > From: Bruce Momjian <maillist@candle.pha.pa.us>
> > 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.




Re: [HACKERS] Hashing passwords (was Updated TODO list)

From
"Gene Sokolov"
Date:
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.




RE: [HACKERS] Updated TODO list

From
"John Ridout"
Date:
I can "select * from pgshadow" as the database owner.

> -----Original Message-----
> From: owner-pgsql-hackers@postgreSQL.org
> [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Bruce Momjian
> Sent: 09 July 1999 17:41
> To: Hannu Krosing
> Cc: Gene Sokolov; PostgreSQL-development
> Subject: Re: [HACKERS] Updated TODO list
> 
> 
> > > 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
> 
> 


Re: [HACKERS] Re: Hashing passwords (was Updated TODO list)

From
Mattias Kregert
Date:
I found this at freshmeat.net:
------------------------------
Secure Remote Password (SRP) is a password-based authentication and  key exchange mechanism where no information about
thepassword is  leaked during the authentication process. It does not require any
 
public  key cryptography, yet even if one were to eavesdrop on the  authentication process, no information which would
aidin guessing
 
the  password can be obtained (in theory). There are some reworked Telnet  and FTP clients and servers available
already.
http://srp.stanford.edu/srp/

It stores encrypted passwords on the server (not simple XOR), sends
different
data over the wire every time, it's is impossible to listen on the wire
and
compute the password (even with the simplest passwords).

see http://srp.stanford.edu/srp/design.html

/* m */

Gene Sokolov wrote:
> 
> 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>
> > 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.
> >
> > > > From: Bruce Momjian <maillist@candle.pha.pa.us>
> > > 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.


Re: [HACKERS] Re: Hashing passwords (was Updated TODO list)

From
Mattias Kregert
Date:
Another nice thing with SRP is that it is a mutual authentication. A
third party cannot say "hey i'm the server, please connect to me. Sure,
your password is correct, start sending queries... INSERT? ok, sure,
INSERT 1 1782136. go on..." and steal a lot of data... the SRP client
always knows if it is talking to the real thing. No more third party
attacks...
http://srp.stanford.edu/srp/others.html

/* m */


Gene Sokolov wrote:
> 
> 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>
> > 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.
> >
> > > > From: Bruce Momjian <maillist@candle.pha.pa.us>
> > > 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.


Re: [HACKERS] Updated TODO list

From
Bruce Momjian
Date:
> I can "select * from pgshadow" as the database owner.

Are you saying you can do this as a database owner, not the postgres
user?  I just tried it, and was not able to see the table contents:
xx=> select * from pg_shadow;ERROR:  pg_shadow: Permission denied.

Yes, only the installation owner can do that.  No way to do password stuff
unless the 'postgres' user can access the passwords, righ?  Is that a
problem?


> > -----Original Message-----
> > From: owner-pgsql-hackers@postgreSQL.org
> > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Bruce Momjian
> > Sent: 09 July 1999 17:41
> > To: Hannu Krosing
> > Cc: Gene Sokolov; PostgreSQL-development
> > Subject: Re: [HACKERS] Updated TODO list
> > 
> > 
> > > > 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
> > 
> > 
> 
> 


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


Re: [HACKERS] Re: Hashing passwords (was Updated TODO list)

From
"Gene Sokolov"
Date:
I looked it up.
One problem with this protocol imho is extensive use of modular
exponentiation. This operation is heavy. The login procedure would be
cpu-intensive.
Second - the protocol covers secure authentication. Data is sent unencrypted
anyway. I think it is not wise to spending a lot of effort on secure login
without securing the data channel. "Building secure PgSQL" would be an
interesting subject of discussion though.

Gene Sokolov.

From: Mattias Kregert <matti@algonet.se>
> Another nice thing with SRP is that it is a mutual authentication. A
> third party cannot say "hey i'm the server, please connect to me. Sure,
> your password is correct, start sending queries... INSERT? ok, sure,
> INSERT 1 1782136. go on..." and steal a lot of data... the SRP client
> always knows if it is talking to the real thing. No more third party
> attacks...
> http://srp.stanford.edu/srp/others.html
>
> /* m */




Re: [HACKERS] Hashing passwords (was Updated TODO list)

From
Peter Eisentraut
Date:
On Fri, 9 Jul 1999, Louis Bertrand wrote:

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

Perhaps one could also allow the use of PAM where available. That would
make things infinitely easier for administrators.

-- 
Peter Eisentraut
PathWay Computing, Inc.



RE: [HACKERS] Updated TODO list

From
"John Ridout"
Date:
I'm using 6.4 so you may want to ignore everything I say.
I created a new user with create db and create user permission.
With said new user I "select * from pg_shadow".  Is that right?

John.

> 
> > I can "select * from pgshadow" as the database owner.
> 
> Are you saying you can do this as a database owner, not the postgres
> user?  I just tried it, and was not able to see the table contents:
> 
>     xx=> select * from pg_shadow;
>     ERROR:  pg_shadow: Permission denied.
> 
> Yes, only the installation owner can do that.  No way to do 
> password stuff
> unless the 'postgres' user can access the passwords, righ?  Is that a
> problem?
> 
> 
> > > -----Original Message-----
> > > From: owner-pgsql-hackers@postgreSQL.org
> > > [mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of 
> Bruce Momjian
> > > Sent: 09 July 1999 17:41
> > > To: Hannu Krosing
> > > Cc: Gene Sokolov; PostgreSQL-development
> > > Subject: Re: [HACKERS] Updated TODO list
> > > 
> > > 
> > > > > 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
> > 
> > 
> 
> 


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



Re: [HACKERS] Updated TODO list

From
Bruce Momjian
Date:
[Charset iso-8859-1 unsupported, filtering to ASCII...]
> 
> I'm using 6.4 so you may want to ignore everything I say.
> I created a new user with create db and create user permission.
> With said new user I "select * from pg_shadow".  Is that right?

6.4 and 6.5 should be the same in this area.  If you say the user is a
'super-user' in createdb, he will be able to access pg_shadow.  If not,
no access.

The ability to create databases is unrelated to that.

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


Re: [HACKERS] Updated TODO list

From
wieck@debis.com (Jan Wieck)
Date:
>
> I can "select * from pgshadow" as the database owner.
>

    You  must  be  a  database superuser or a superuser must have
    granted SELECT right for pg_shadow to you.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

Re: [HACKERS] Updated TODO list

From
"Gene Sokolov"
Date:
From: Jan Wieck <wieck@debis.com>
> >
> > I can "select * from pgshadow" as the database owner.
> >
>
>     You  must  be  a  database superuser or a superuser must have
>     granted SELECT right for pg_shadow to you.
>
>
> Jan

DB admin has no business knowing other's passwords. The current security
scheme is seriously flawed.

Gene Sokolov.




Re: [HACKERS] Updated TODO list

From
Bruce Momjian
Date:
[Charset koi8-r unsupported, filtering to ASCII...]
> From: Jan Wieck <wieck@debis.com>
> > >
> > > I can "select * from pgshadow" as the database owner.
> > >
> >
> >     You  must  be  a  database superuser or a superuser must have
> >     granted SELECT right for pg_shadow to you.
> >
> >
> > Jan
> 
> DB admin has no business knowing other's passwords. The current security
> scheme is seriously flawed.
> 

But it is the db passwords, not the Unix passwords.  How are we supposed
to make this work if the db doesn't know the passwords, AND use random
salt over the wire?

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


Re: [HACKERS] Updated TODO list

From
Tom Lane
Date:
Bruce Momjian <maillist@candle.pha.pa.us> writes:
>> DB admin has no business knowing other's passwords. The current security
>> scheme is seriously flawed.

> But it is the db passwords, not the Unix passwords.

I think the original point was that some people use the same or related
passwords for psql as for their login password.

Nonetheless, since we have no equivalent of "passwd" that would let a
db user change his db password for himself, it's a little silly to
talk about hiding db passwords from the admin who puts them in.

If this is a concern, we'd need to add both encrypted storage of
passwords and a remote-password-change feature.
        regards, tom lane


Re: [HACKERS] Updated TODO list

From
Bruce Momjian
Date:
> Bruce Momjian <maillist@candle.pha.pa.us> writes:
> >> DB admin has no business knowing other's passwords. The current security
> >> scheme is seriously flawed.
> 
> > But it is the db passwords, not the Unix passwords.
> 
> I think the original point was that some people use the same or related
> passwords for psql as for their login password.
> 
> Nonetheless, since we have no equivalent of "passwd" that would let a
> db user change his db password for himself, it's a little silly to
> talk about hiding db passwords from the admin who puts them in.
> 
> If this is a concern, we'd need to add both encrypted storage of
> passwords and a remote-password-change feature.

Doing the random salt over the wire would still be a problem.

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


Re: [HACKERS] Updated TODO list

From
wieck@debis.com (Jan Wieck)
Date:
>
> > Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > >> DB admin has no business knowing other's passwords. The current security
> > >> scheme is seriously flawed.
> >
> > > But it is the db passwords, not the Unix passwords.
> >
> > I think the original point was that some people use the same or related
> > passwords for psql as for their login password.
> >
> > Nonetheless, since we have no equivalent of "passwd" that would let a
> > db user change his db password for himself, it's a little silly to
> > talk about hiding db passwords from the admin who puts them in.
> >
> > If this is a concern, we'd need to add both encrypted storage of
> > passwords and a remote-password-change feature.
>
> Doing the random salt over the wire would still be a problem.

    And  I don't like password's at all. Well, up to now the bare
    PostgreSQL doesn't need anything else. But  would  it  really
    hurt  to  use ssl in the case someone needs security? I don't
    know exactly, but the authorized keys might reside in  a  new
    system catalog. So such a secure installation can live with a
    wide open hba.conf and  who  can  be  who  is  controlled  by
    pg_authorizedkeys then.

    As  a  side effect, all communication between the backend and
    the client would be crypted, so no wire  listener  could  see
    anything :-)


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

Re: [HACKERS] Updated TODO list

From
"Gene Sokolov"
Date:
From: Bruce Momjian <maillist@candle.pha.pa.us>
> > Bruce Momjian <maillist@candle.pha.pa.us> writes:
> > >> DB admin has no business knowing other's passwords. The current
security
> > >> scheme is seriously flawed.
> >
> > > But it is the db passwords, not the Unix passwords.
> >
> > I think the original point was that some people use the same or related
> > passwords for psql as for their login password.
> >
> > Nonetheless, since we have no equivalent of "passwd" that would let a
> > db user change his db password for himself, it's a little silly to
> > talk about hiding db passwords from the admin who puts them in.
> >
> > If this is a concern, we'd need to add both encrypted storage of
> > passwords and a remote-password-change feature.
>
> Doing the random salt over the wire would still be a problem.

There is absolutely no technical problem with storing hashed passwords and
still sending salted hash over the wire. It was recently discussed in detail
in "Hashing passwords" thread in pgsql-hackers list.

Gene Sokolov



Re: [HACKERS] Updated TODO list

From
Jeff MacDonald
Date:
> I think the original point was that some people use the same or related
> passwords for psql as for their login password.

This may sound cold, but isn't that their own problem. I can remmeber
being told the first time i needed a passwd "don't reuse this" .
There should come a tiem when people take their own security a little
more into their own hands, but hey that's just me :)

jeff

======================================================
Jeff MacDonaldjeff@hub.org    webpage: http://hub.org/~jeffjeff@pgsql.com    irc: bignose on EFnet
======================================================



RE: [HACKERS] Updated TODO list

From
"John Ridout"
Date:
I like MS SQL Server on NT, it has integerated security. ;-)
Asking ordinary users to remember extra passwords
is not going to work.  Either they won't do it or they
will use lots of easy ones like 'mouse', child's name, etc.

John.

> > I think the original point was that some people use the 
> same or related
> > passwords for psql as for their login password.
> 
> This may sound cold, but isn't that their own problem. I can remmeber
> being told the first time i needed a passwd "don't reuse this" .
> There should come a tiem when people take their own security a little
> more into their own hands, but hey that's just me :)
> 
> jeff
> 
> ======================================================
> Jeff MacDonald
>     jeff@hub.org    webpage: http://hub.org/~jeff
>     jeff@pgsql.com    irc: bignose on EFnet
> ======================================================
> 
> 
> 


Re: [HACKERS] Updated TODO list

From
Peter Eisentraut
Date:
> I think the original point was that some people use the same or related
> passwords for psql as for their login password.

Well, you can't expect the pedestrians out here to remember to different
passwords. The fact that pgsql passwords are all lowercase makes this kind
of tough though. So, then you have the option of storing passwords in
plain readable to the db admin, which is unacceptable, or storing no
password at all which leaves you with ident.

Also, when you use things like PHP or run scripts/programs from cron, you
can't really have people enter a password. Hardcoding passwords seems to
be suggested by a lot of people, but that's ridiculous.

I think what many people discussed about separating the authentication
method into a compile-time option would be a good idea. Then the admin can
decide whether to use the current system, SSL, ssh(?), PAM, whatever.
Perhaps that would also take some load of the developers who would
probably much rather develop a DBMS than authentication systems.

I've posted this a while ago on one of the general lists, about whether
there is a PAM-enabling patch available, but evidently I got the answer
here. :(

-- 
Peter Eisentraut
PathWay Computing, Inc.



Re: [HACKERS] Updated TODO list

From
Bruce Momjian
Date:
> > Doing the random salt over the wire would still be a problem.
> 
> There is absolutely no technical problem with storing hashed passwords and
> still sending salted hash over the wire. It was recently discussed in detail
> in "Hashing passwords" thread in pgsql-hackers list.

But you are hashing it with a secret known by the database adminstrator,
and someone knows any password, like their own, can guess the secret by
looking at the hashed version, no?

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


Re: [HACKERS] Updated TODO list

From
Bruce Momjian
Date:
> > I think the original point was that some people use the same or related
> > passwords for psql as for their login password.
> 
> This may sound cold, but isn't that their own problem. I can remmeber
> being told the first time i needed a passwd "don't reuse this" .
> There should come a tiem when people take their own security a little
> more into their own hands, but hey that's just me :)

This may be the issue.  If we decided the postgres user has to be able
to know the password, we are stuck requiring people to use a different
password for the database if the postgres user is not trusted as much as
the system owner.

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


Password redux (was:Re: [HACKERS] Updated TODO list)

From
Lamar Owen
Date:
Bruce Momjian wrote:
> 
> > > Doing the random salt over the wire would still be a problem.
> >
> > There is absolutely no technical problem with storing hashed passwords and
> > still sending salted hash over the wire. It was recently discussed in detail
> > in "Hashing passwords" thread in pgsql-hackers list.
> 
> But you are hashing it with a secret known by the database adminstrator,
> and someone knows any password, like their own, can guess the secret by
> looking at the hashed version, no?

If the algorithm used for Unix passwords is used, it shouldn't be a
problem, as the "secret" is the password itself, which the client uses
to encrypt a 64 bit block of zeros, then it encrypts the resulting
64-bits of cyphertext with the password -- this process is repeated
until the 64-bit block of zeros has been encrypted (usually with DES) 25
times, using the salt to randomize things inside the DES algorithm
itself -- DES hardware chips that don't know about salt can't deal with
it. The encrypted zeros are then sent down the wire and compared to the
stored "hash". 

Using this algorithm, the only thing that goes over the wire is the
salted 64-bit cyphertext -- whose plaintext is a 64-bit block of zeros.

So, the only commonality between the stored "passwords" is the 64-bit
block of zeros -- which is why dictionary attacks and brute force
methods have to be used.  Of course, you could always use another crypt
algorithm instead of DES.  However, the standard unix crypt() call is
exportable -- it's only 56-bits.  MD5 and RSA have been used by various
vendors to do the same job. 
As an administrator, while I know the plaintext of the hash (64-bit
block of zeros), and I have the salted cyphertext of the hash (13
characters, the first two of which are the salt), and I have the
algorithm -- but none of that helps me find out the secret used to
encrypt the plaintext.

This makes it as secure as the Unix password system itself -- which,
AFAIK, no one has ever been able to DIRECTLY extract the password for
the cyphertext (brute forcing the password doesn't count).

However, there is a security risk with doing the crypt() in the client
-- if I know someone else's hashed password, I can bypass the client's
crypt() call (by hacking the client), and get authentication without
ever knowing the user's password.  To solve this, you don't transmit the
final hash or the plaintext password from the client to the server --
you wrap the password transmittal in SSL (session keys and all that),
and the backend does the crypt() and compare.

To summarize -- to use hashed passwords in a client-server system in a
relatively secure fashion:
1.)    Use a one-way algorithm, like Unix crypt(), where a known plaintext
is heavily encrypted by the password;
2.)    The password is never sent from the client to the server in
plaintext form;
3.)    An SSL-like mechanism is used to encrypt the transmission of the
password to the backend, which does the crypt() and compares it against
the stored hash -- this prevents us from using a known hash attack with
a hacked client.

There are two layers to this security cake -- network, and database.


Lamar Owen
WGCR Internet Radio


Re: [HACKERS] Updated TODO list

From
"Gene Sokolov"
Date:
From: Bruce Momjian <maillist@candle.pha.pa.us>
> > > I think the original point was that some people use the same or
related
> > > passwords for psql as for their login password.
> >
> > This may sound cold, but isn't that their own problem. I can remmeber
> > being told the first time i needed a passwd "don't reuse this" .
> > There should come a tiem when people take their own security a little
> > more into their own hands, but hey that's just me :)
>
> This may be the issue.  If we decided the postgres user has to be able
> to know the password, we are stuck requiring people to use a different
> password for the database if the postgres user is not trusted as much as
> the system owner.

Assuming that people have limited memory, they really have only two
choices - reuse passwords, possibly with some modifications, or write
passwords down. I think the first choice is the lesser evil.   There are perfect solutions to the authentication
problem.It's just a
 
matter of accepting one of these solutions.

Gene Sokolov



Re: [HACKERS] Updated TODO list

From
"Gene Sokolov"
Date:
From: Bruce Momjian <maillist@candle.pha.pa.us>
> > > Doing the random salt over the wire would still be a problem.
> >
> > There is absolutely no technical problem with storing hashed passwords
and
> > still sending salted hash over the wire. It was recently discussed in
detail
> > in "Hashing passwords" thread in pgsql-hackers list.
>
> But you are hashing it with a secret known by the database adminstrator,
Yes, DB admin can gain useable info.

> and someone knows any password, like their own, can guess the secret by
> looking at the hashed version, no?

No. Not any password, <master value> only. SHA or MD5 hash is one-way. There
are many schemes. What I proposed is just one solution. Others may propose
something better.

Here are my thoughts:
1. Yes, database admin can compromise security of the whole installation, no
matter what security scheme is selected.
2. Even if database admin can compromise security, I would rather opt for a
better security scheme, rather then give up completely.
3. When you enter your password at any login prompt, the password either
appears as *** or does not appear at all. Why do you think it is done this
way? Same applies to select * from pg_shadow.
4. Storing hashes instead of plain text passwords would divert all casual
and "peek over the shoulder" hackers. It's two really different tasks -
memorizing a password or memorizing 24 random-looking bytes of a base64 hash
presentation.
6. People tend to reuse passwords. Getting one password helps to get other
passwords too.
7. I do not understand why it's so important to keep passwords in plain
text. Just a simple hash would help a lot.

Gene Sokolov.



Re: [HACKERS] Updated TODO list

From
Louis Bertrand
Date:
I agree with Gene. Following this discussion, I sense a reluctance to
implement stronger password schemes because of the extra development
hassles (compatibility, crypto prohibitions, maintenance).

1) Divide and conquer: the developers are concerned about both "over the
wire" and server passwords. I suggest you focus on the server side and
leave the over the wire security to the DB admin/sys.admin as an
installation issue. If they choose to use SSL, SSH, IPsec or a home-grown
authentication handshake, that's of no concern to pgsql. Just think of it
as a telnet session into the server.

2) On the server side, use the native crypt(3) by default (or the NT
equivalent) and store the password hash. The strength of the crypt will
vary depending on the installation, but that's really up to the choice of
OS and installation. If someone wants to patch for PAM, Kerberos or
whatever, that's fine too, as long as you can always revert back to the
plain old crypt(3).

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

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

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

On Thu, 15 Jul 1999, Gene Sokolov wrote:

> From: Bruce Momjian <maillist@candle.pha.pa.us>
> > > > Doing the random salt over the wire would still be a problem.
> > >
> > > There is absolutely no technical problem with storing hashed passwords
> and
> > > still sending salted hash over the wire. It was recently discussed in
> detail
> > > in "Hashing passwords" thread in pgsql-hackers list.
> >
> > But you are hashing it with a secret known by the database adminstrator,
> Yes, DB admin can gain useable info.
> 
> > and someone knows any password, like their own, can guess the secret by
> > looking at the hashed version, no?
> 
> No. Not any password, <master value> only. SHA or MD5 hash is one-way. There
> are many schemes. What I proposed is just one solution. Others may propose
> something better.
> 
> Here are my thoughts:
> 1. Yes, database admin can compromise security of the whole installation, no
> matter what security scheme is selected.
> 2. Even if database admin can compromise security, I would rather opt for a
> better security scheme, rather then give up completely.
> 3. When you enter your password at any login prompt, the password either
> appears as *** or does not appear at all. Why do you think it is done this
> way? Same applies to select * from pg_shadow.
> 4. Storing hashes instead of plain text passwords would divert all casual
> and "peek over the shoulder" hackers. It's two really different tasks -
> memorizing a password or memorizing 24 random-looking bytes of a base64 hash
> presentation.
> 6. People tend to reuse passwords. Getting one password helps to get other
> passwords too.
> 7. I do not understand why it's so important to keep passwords in plain
> text. Just a simple hash would help a lot.
> 
> Gene Sokolov.
> 
> 
> 
> 




Re: [HACKERS] Updated TODO list

From
Bruce Momjian
Date:
> I agree with Gene. Following this discussion, I sense a reluctance to
> implement stronger password schemes because of the extra development
> hassles (compatibility, crypto prohibitions, maintenance).
> 
> 1) Divide and conquer: the developers are concerned about both "over the
> wire" and server passwords. I suggest you focus on the server side and
> leave the over the wire security to the DB admin/sys.admin as an
> installation issue. If they choose to use SSL, SSH, IPsec or a home-grown
> authentication handshake, that's of no concern to pgsql. Just think of it
> as a telnet session into the server.
> 
> 2) On the server side, use the native crypt(3) by default (or the NT
> equivalent) and store the password hash. The strength of the crypt will
> vary depending on the installation, but that's really up to the choice of
> OS and installation. If someone wants to patch for PAM, Kerberos or
> whatever, that's fine too, as long as you can always revert back to the
> plain old crypt(3).
> 

I disagree.  Over the wire seems more important than protecting the
passwords from the eyes of the database administrator, which in _most_
cases is the system owner anyway.

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


Re: [HACKERS] Updated TODO list

From
Vince Vielhaber
Date:
On Thu, 15 Jul 1999, Bruce Momjian wrote:

> > 1) Divide and conquer: the developers are concerned about both "over the
> > wire" and server passwords. I suggest you focus on the server side and
> > leave the over the wire security to the DB admin/sys.admin as an
> > installation issue. If they choose to use SSL, SSH, IPsec or a home-grown
> > authentication handshake, that's of no concern to pgsql. Just think of it
> > as a telnet session into the server.
> > 
> > 2) On the server side, use the native crypt(3) by default (or the NT
> > equivalent) and store the password hash. The strength of the crypt will
> > vary depending on the installation, but that's really up to the choice of
> > OS and installation. If someone wants to patch for PAM, Kerberos or
> > whatever, that's fine too, as long as you can always revert back to the
> > plain old crypt(3).
> > 
> 
> I disagree.  Over the wire seems more important than protecting the
> passwords from the eyes of the database administrator, which in _most_
> cases is the system owner anyway.

And when it's not?  People have a tendency to use passwords in more than
one place so they won't forget what they used (they can keep it narrowed
down to a couple passwords).  Why would you want to make it visible to
anyone?  

Vince.
-- 
==========================================================================
Vince Vielhaber -- KA8CSH   email: vev@michvhf.com   flame-mail: /dev/null      # include <std/disclaimers.h>
       TEAM-OS2       Online Campground Directory    http://www.camping-usa.com      Online Giftshop Superstore
http://www.cloudninegifts.com
==========================================================================





Password thread (was: Re: [HACKERS] Updated TODO list)

From
Louis Bertrand
Date:
Agreed: over the wire is _very_ important. The question remains: does the
pgsql development team want to take on the responsibility of implementing
such a scheme at the application layer? There are plenty of very good
link/network/transport layer schemes in existence already. You'd just 
be reinventing.

In a benign environment you don't worry about securing the wire (as with
protocols like telnet/rlogin, ftp and POP/IMAP). In a more cautious
environment, you apply well-known and understood encryption/authentication
schemes at a layer below the application (IPsec, SSH, SSL, Java security,
Kerberos, and there are plenty in the Microsoft world too).

But above all: do not store passwords in cleartext. It makes it
ridiculously easy for an attacker to take over user accounts.  Let's say
they're kiddies who cracked root just by using a widely available exploit,
then, for free, they get the rest of the passwords in clear. Bonus!

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 Thu, 15 Jul 1999, Bruce Momjian wrote:

> > I agree with Gene. Following this discussion, I sense a reluctance to
> > implement stronger password schemes because of the extra development
> > hassles (compatibility, crypto prohibitions, maintenance).
> > 
> > 1) Divide and conquer: the developers are concerned about both "over the
> > wire" and server passwords. I suggest you focus on the server side and
> > leave the over the wire security to the DB admin/sys.admin as an
> > installation issue. If they choose to use SSL, SSH, IPsec or a home-grown
> > authentication handshake, that's of no concern to pgsql. Just think of it
> > as a telnet session into the server.
> > 
> > 2) On the server side, use the native crypt(3) by default (or the NT
> > equivalent) and store the password hash. The strength of the crypt will
> > vary depending on the installation, but that's really up to the choice of
> > OS and installation. If someone wants to patch for PAM, Kerberos or
> > whatever, that's fine too, as long as you can always revert back to the
> > plain old crypt(3).
> > 
> 
> I disagree.  Over the wire seems more important than protecting the
> passwords from the eyes of the database administrator, which in _most_
> cases is the system owner anyway.
> 
> -- 
>   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
> 
> 
> 




Re: Password thread (was: Re: [HACKERS] Updated TODO list)

From
"Henry B. Hotz"
Date:
At 10:45 AM -0700 7/15/99, Louis Bertrand wrote:
>Agreed: over the wire is _very_ important. The question remains: does the

>But above all: do not store passwords in cleartext. It makes it
>ridiculously easy for an attacker to take over user accounts.  Let's say

There is a fundamental conflict here:  If you want to encyrpt the stored
passwords then they have to go over the wire in the clear.  If you want the
passwords encrypted over the wire then they need to be stored in the clear
on the machine.  If you encrypt the channel (so you can encrypt the stored
passwords and still protect the wire) then the conflict applies to how you
set up the channel.

I walked in in the middle of this discussion, but if we are creating a
PG-unique authentication scheme I would hope that the PG passwords are not
those of the other unix user accounts.

Currently PG has a real grab-bag of authentication methods.  This is nice,
but many of them are not very secure.  If we can tie into something like
SSH, IPsec, or SSL then that is definitely to be prefered to doing it all
ourselves.

I wish I could recommend kerberos (which we already claim to support), but
the implementations I've seen seem buggy.  NetBSD and Solaris both have it
built in, but there are subroutine name conflicts between the kerberos
libraries and some standard libraries on both platforms (different
conflicts).  I think it's an example of good US technology being destroyed
by the ITAR restrictions.  The overseas NetBSD developers, and a large
fraction of the US ones, don't touch the kerberos stuff, so it suffers
bitrot.  Excuse the rant.

Signature failed Preliminary Design Review.
Feasibility of a new signature is currently being evaluated.
h.b.hotz@jpl.nasa.gov, or hbhotz@oxy.edu


Re: Password thread (was: Re: [HACKERS] Updated TODO list)

From
Bruce Momjian
Date:
> I wish I could recommend kerberos (which we already claim to support), but
> the implementations I've seen seem buggy.  NetBSD and Solaris both have it
> built in, but there are subroutine name conflicts between the kerberos
> libraries and some standard libraries on both platforms (different
> conflicts).  I think it's an example of good US technology being destroyed
> by the ITAR restrictions.  The overseas NetBSD developers, and a large
> fraction of the US ones, don't touch the kerberos stuff, so it suffers
> bitrot.  Excuse the rant.
> 

Totally agree kerberos is a great way to go, if we can.

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


Re: Password thread (was: Re: [HACKERS] Updated TODO list)

From
"Gene Sokolov"
Date:
From: Henry B. Hotz <hotz@jpl.nasa.gov>
> >Agreed: over the wire is _very_ important. The question remains: does the
>
> >But above all: do not store passwords in cleartext. It makes it
> >ridiculously easy for an attacker to take over user accounts.  Let's say
>
> There is a fundamental conflict here:  If you want to encyrpt the stored
> passwords then they have to go over the wire in the clear.  If you want
the

I have repeated it several times already: there is NO conflict. The conflict
is due to the present security scheme only. It's purely technical, nothing
more.

Yes, in any security scheme (short of full blown RSA) you still have to
store something at the server which can be used to gain access to the
database if stolen. But that does not have to be the cleartext password
itself.

Gene Sokolov.





Re: [HACKERS] Updated TODO list

From
wieck@debis.com (Jan Wieck)
Date:
Bruce Momjian wrote:

> I disagree.  Over the wire seems more important than protecting the
> passwords from the eyes of the database administrator, which in _most_
> cases is the system owner anyway.

No,

    both  are  equally important. There is a good reason why even
    root cannot see cleartext unix passwords. And there's a  good
    reason  for doing something different over the net (why do we
    use ssh when accessing hub.org?).

    Well, the sysadmin could run some  password  cracker  against
    shadow  files.   But  if I ever notice that Marc uses a brute
    force method to crack my ones, I'll take a trip and break his
    neck (after breaking every single finger, one by one, hour by
    hour - you'll hear him over there).

    Hosts I consider trusted ones are hosts where I trust the  OS
    and  the admin.  It's O.K. if an admin takes a look into some
    files.  And if he then finds some of my private xxx pics,  so
    be  it  -  as long as he doesn't pin them onto the blackboard
    under "Jan's private pics".  But it's not O.K.  if that  look
    means  he'll  see  cleartext passwords without having to take
    extra cracking steps.

    To store really crypted passwords in the  database,  I  think
    it's  required to send cleartext over the wire. So we have to
    protect that at least until  the  authentication  is  done  -
    optionally until disconnect.

    I  haven't  found  much documentation yet how to use OpenSSL,
    and I even don't know if it really is what we  need.  But  it
    has  an  Apache like license (free for private and commercial
    use).

    If it is what I think so far, it should be possible to enable
    ssl  during  configure  and  then  tell  in  the  hba.conf if
    password auth has to be ssl protected. Then we  could  easily
    send  cleartext  passwords  over  a  protected channel. Thus,
    local traffic could  be  high  speed  while  net  traffic  is
    securely  crypted.  But the admin decides what "local" means,
    so traffic on the backbone net (web-server->db-server)  might
    be considered secure.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck@debis.com (Jan Wieck) #

Re: [HACKERS] Updated TODO list

From
Hannu Krosing
Date:
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.

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