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

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" <chriskl@familyhealth.com.au>
To: "Rudi" <rudi@oasis.net.au>; <pgsql-sql@postgresql.org>
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.

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

I can't find the changelog for 7.2.  Did nested transactions make it in?
-- 
John Hasler
john@dhh.gt.org
Dancing Horse Hill
Elmwood, Wisconsin

John Hasler wrote:
> I can't find the changelog for 7.2.  Did nested transactions make it in?

No, sorry.

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