Обсуждение: Updated TODO list
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
> 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
[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
[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
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
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: > 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. > > > > > >
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: > 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. > > > > >
[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
> > 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
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 > > >
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.
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 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 > >
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.
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.
> 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
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 */
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.
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
[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
>
> 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) #
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.
[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
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
> 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
>
> > 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) #
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
> 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 ======================================================
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 > ====================================================== > > >
> 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.
> > 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
> > 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
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
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
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.
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. > > > >
> 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
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
==========================================================================
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 > > >
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
> 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
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.
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) #
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