From rudi@oasis.net.au Thu Feb 7 01:44:26 2002 Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g171iPE52526 for ; Wed, 6 Feb 2002 20:44:26 -0500 (EST) (envelope-from rudi@oasis.net.au) Received: from sun (palm.oasis.net.au [210.8.139.3]) by water.oasis.net.au (8.9.3/100.1) with SMTP id MAA09116 for ; Thu, 7 Feb 2002 12:03:16 +1000 X-Authentication-Warning: water.oasis.net.au: Host palm.oasis.net.au [210.8.139.3] claimed to be sun Message-ID: <000801c1af79$f270d8f0$0c00a8c0@sun> From: "Rudi" To: Subject: pg_shadow / pg_user Date: Thu, 7 Feb 2002 11:51:23 +1000 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0005_01C1AFCD.C40C2010" X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200202/77 X-Sequence-Number: 6507 This is a multi-part message in MIME format. ------=_NextPart_000_0005_01C1AFCD.C40C2010 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi friends, I've been learning about security using Pg lately. Up until last night I thought system user passwords were stored safely away= in pg_user.=20 So far I haven't been able to get any passwords out only '*******'. Then last night was observing each system table and found that pg_shadow st= ores user passwords in clear text. ?? pg_shadow =3D clear text password pg_user =3D hidden password I guess this means if an intruder gets an appropriate account on the box th= e can view all passwords. I had assumed that system passwords were stored hidden from all eye balls. Sort of like apache storing http passwords in binary form in a db. Is this how it is ? If so I was thinking I like to know if someone tries or succeeds in queryin= g the pg_shadow table. I thought maybe to increase the postmaster debug level so that all sql quer= ies are logged. Then write a cron job to check this log and email me if it is detected that= a user is attempted or did query=20 the pg_shadow table. How does this sound ? Am I totaly on track ? Thank for your time and attention Kind regards Rudi. ------=_NextPart_000_0005_01C1AFCD.C40C2010 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi friends,
 
I've been learning about securit= y using=20 Pg lately.
Up until last night I thought system user = passwords=20 were stored safely away in pg_user.
So far I haven't been able to get any pass= words out=20 only '*******'.
Then last night was observing each system = table and=20 found that pg_shadow stores user passwords in clear text.
??
pg_shadow =3D clear text password
pg_user =3D hidden password
 
I guess this means if an intruder gets an= =20 appropriate account on the box the can view all passwords.
I had assumed that system passwords were s= tored=20 hidden from all eye balls.
Sort of like apache storing http passwords= in=20 binary form in a db.
 
Is this how it is ?
 
If so I was thinking I like to know if som= eone=20 tries or succeeds in querying the pg_shadow table.
I thought maybe to increase the postmaster= debug=20 level so that all sql queries are logged.
Then write a cron job to check this log an= d email=20 me if it is detected that a user is attempted or did query
the pg_shadow table.
 
How does this sound ?
Am I totaly on track ?
 
Thank for your time and attention
Kind regards
Rudi.
 
 
 
 
 
 
------=_NextPart_000_0005_01C1AFCD.C40C2010-- From chriskl@familyhealth.com.au Thu Feb 7 02:04:44 2002 Received: from mail.iinet.net.au (symphony-06.iinet.net.au [203.59.3.38]) by postgresql.org (8.11.3/8.11.4) with SMTP id g1724hE58394 for ; Wed, 6 Feb 2002 21:04:44 -0500 (EST) (envelope-from chriskl@familyhealth.com.au) Received: (qmail 12732 invoked by uid 666); 7 Feb 2002 02:04:41 -0000 Received: from unknown (HELO houston.familyhealth.com.au) (203.59.231.6) by mail.iinet.net.au with SMTP; 7 Feb 2002 02:04:41 -0000 Received: (from root@localhost) by houston.familyhealth.com.au (8.11.6/8.11.6) id g1724fQ10451 for pgsql-sql@postgresql.org; Thu, 7 Feb 2002 10:04:41 +0800 (WST) (envelope-from chriskl@familyhealth.com.au) Received: from mariner (mariner.internal [192.168.0.101]) by houston.familyhealth.com.au (8.11.6/8.9.3) with SMTP id g1724cq10354; Thu, 7 Feb 2002 10:04:38 +0800 (WST) From: "Christopher Kings-Lynne" To: "Rudi" , Subject: Re: pg_shadow / pg_user Date: Thu, 7 Feb 2002 10:05:37 +0800 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0) X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Importance: Normal In-Reply-To: <000801c1af79$f270d8f0$0c00a8c0@sun> X-scanner: scanned by Inflex 0.1.5c - (http://www.inflex.co.za/) X-Archive-Number: 200202/78 X-Sequence-Number: 6508 Hi Rudi, In the newly-released Postgres 7.2, the passwords are now MD5 encrypted (IIRC). I highly suggest upgrading to the new version. Chris -----Original Message----- From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Rudi Sent: Thursday, 7 February 2002 9:51 AM To: pgsql-sql@postgresql.org Subject: [SQL] pg_shadow / pg_user Hi friends, I've been learning about security using Pg lately. Up until last night I thought system user passwords were stored safely away in pg_user. So far I haven't been able to get any passwords out only '*******'. Then last night was observing each system table and found that pg_shadow stores user passwords in clear text. ?? pg_shadow = clear text password pg_user = hidden password I guess this means if an intruder gets an appropriate account on the box the can view all passwords. I had assumed that system passwords were stored hidden from all eye balls. Sort of like apache storing http passwords in binary form in a db. Is this how it is ? If so I was thinking I like to know if someone tries or succeeds in querying the pg_shadow table. I thought maybe to increase the postmaster debug level so that all sql queries are logged. Then write a cron job to check this log and email me if it is detected that a user is attempted or did query the pg_shadow table. How does this sound ? Am I totaly on track ? Thank for your time and attention Kind regards Rudi. From rudi@oasis.net.au Thu Feb 7 02:08:59 2002 Received: from water.oasis.net.au (water.oasis.net.au [210.8.139.2]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g1728wE58655 for ; Wed, 6 Feb 2002 21:08:59 -0500 (EST) (envelope-from rudi@oasis.net.au) Received: from sun (palm.oasis.net.au [210.8.139.3]) by water.oasis.net.au (8.9.3/100.1) with SMTP id MAA09506 for ; Thu, 7 Feb 2002 12:27:51 +1000 X-Authentication-Warning: water.oasis.net.au: Host palm.oasis.net.au [210.8.139.3] claimed to be sun Message-ID: <000501c1af7d$6135c090$0c00a8c0@sun> From: "Rudi" To: References: Subject: Re: pg_shadow / pg_user Date: Thu, 7 Feb 2002 12:15:57 +1000 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.50.4133.2400 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4133.2400 X-Archive-Number: 200202/79 X-Sequence-Number: 6508 Hi Chris, Thanks for your reply. That's very good news. I am using Pg 7.0 at home for research and dev. It's an old box. At work we are using 7.1.3 Cheers Rudi. ----- Original Message ----- From: "Christopher Kings-Lynne" To: "Rudi" ; Sent: Thursday, February 07, 2002 12:05 PM Subject: RE: [SQL] pg_shadow / pg_user > Hi Rudi, > > In the newly-released Postgres 7.2, the passwords are now MD5 encrypted > (IIRC). I highly suggest upgrading to the new version. > > Chris > > -----Original Message----- > From: pgsql-sql-owner@postgresql.org > [mailto:pgsql-sql-owner@postgresql.org]On Behalf Of Rudi > Sent: Thursday, 7 February 2002 9:51 AM > To: pgsql-sql@postgresql.org > Subject: [SQL] pg_shadow / pg_user > > > Hi friends, > > I've been learning about security using Pg lately. > Up until last night I thought system user passwords were stored safely away > in pg_user. > So far I haven't been able to get any passwords out only '*******'. > Then last night was observing each system table and found that pg_shadow > stores user passwords in clear text. > ?? > pg_shadow = clear text password > pg_user = hidden password > > I guess this means if an intruder gets an appropriate account on the box the > can view all passwords. > I had assumed that system passwords were stored hidden from all eye balls. > Sort of like apache storing http passwords in binary form in a db. > > Is this how it is ? > > If so I was thinking I like to know if someone tries or succeeds in querying > the pg_shadow table. > I thought maybe to increase the postmaster debug level so that all sql > queries are logged. > Then write a cron job to check this log and email me if it is detected that > a user is attempted or did query > the pg_shadow table. > > How does this sound ? > Am I totaly on track ? > > Thank for your time and attention > Kind regards > Rudi. From pgman@candle.pha.pa.us Thu Feb 7 02:31:35 2002 Received: from candle.pha.pa.us (216-55-132-35.dsl.san-diego.abac.net [216.55.132.35]) by postgresql.org (8.11.3/8.11.4) with ESMTP id g172VZE66219 for ; Wed, 6 Feb 2002 21:31:35 -0500 (EST) (envelope-from pgman@candle.pha.pa.us) Received: (from pgman@localhost) by candle.pha.pa.us (8.11.6/8.10.1) id g172VUF27995; Wed, 6 Feb 2002 21:31:30 -0500 (EST) From: Bruce Momjian Message-Id: <200202070231.g172VUF27995@candle.pha.pa.us> Subject: Re: pg_shadow / pg_user In-Reply-To: <000801c1af79$f270d8f0$0c00a8c0@sun> To: Rudi Date: Wed, 6 Feb 2002 21:31:30 -0500 (EST) CC: pgsql-sql@postgresql.org X-Mailer: ELM [version 2.4ME+ PL96 (25)] MIME-Version: 1.0 Content-Transfer-Encoding: 7bit Content-Type: text/plain; charset=US-ASCII X-Archive-Number: 200202/80 X-Sequence-Number: 6509 You are correct. Pre-7.2 required this because of the way passwords from the client were comparied. 7.2 has a postgresql.conf setting "password_encryption" which controls whether passwords are stored on the server encrypted or plaintext. The only reason this is false in 7.2 by default is that once it is true, you can't communicate with pre-7.2 clients. There is also an ENCRYPTED option to the PASSWORD clause of CREATE USER that forces the password to be stored encrypted. Again, works fine with 7.2 clients but not with 7.1 and earlier. --------------------------------------------------------------------------- Rudi wrote: > Hi friends, > > I've been learning about security using Pg lately. > Up until last night I thought system user passwords were stored safely away in pg_user. > So far I haven't been able to get any passwords out only '*******'. > Then last night was observing each system table and found that pg_shadow stores user passwords in clear text. > ?? > pg_shadow = clear text password > pg_user = hidden password > > I guess this means if an intruder gets an appropriate account on the box the can view all passwords. > I had assumed that system passwords were stored hidden from all eye balls. > Sort of like apache storing http passwords in binary form in a db. > > Is this how it is ? > > If so I was thinking I like to know if someone tries or succeeds in querying the pg_shadow table. > I thought maybe to increase the postmaster debug level so that all sql queries are logged. > Then write a cron job to check this log and email me if it is detected that a user is attempted or did query > the pg_shadow table. > > How does this sound ? > Am I totaly on track ? > > Thank for your time and attention > Kind regards > Rudi. > > > > > > -- Bruce Momjian | http://candle.pha.pa.us pgman@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