Thread: Postgresql security checks

Postgresql security checks

From
Machiel Richards
Date:
Good day all....

     I hope that someone can help me out with this one question quickly....

      I am busy setting up a security compliance report for one of our clients and one of the things to check is the following:

- Check that no password is equal to the user name or some "initial standard
password" that your company uses.

    Can someone perhaps assist me on how to check this?


    I would really appreciate help on this as this is the only method I have not figured out yet.


Regards
Machiel

Re: Postgresql security checks

From
Thom Brown
Date:
On 1 September 2010 09:54, Machiel Richards <machielr@rdc.co.za> wrote:
> Good day all....
>
>      I hope that someone can help me out with this one question quickly....
>
>       I am busy setting up a security compliance report for one of our
> clients and one of the things to check is the following:
>
> - Check that no password is equal to the user name or some "initial standard
> password" that your company uses.
>
>     Can someone perhaps assist me on how to check this?
>
>
>     I would really appreciate help on this as this is the only method I have
> not figured out yet.

You can find out if this rule is already violated by running:

SELECT usename
FROM pg_shadow
WHERE passwd = 'md5' || md5(usename)
OR passwd = 'md5' || md5('company_password');

I don't think password checks are available until 9.0.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Re: Postgresql security checks

From
Josh Kupershmidt
Date:
On Wed, Sep 1, 2010 at 5:02 AM, Thom Brown <thom@linux.com> wrote:

> SELECT usename
> FROM pg_shadow
> WHERE passwd = 'md5' || md5(usename)
> OR passwd = 'md5' || md5('company_password');

I think this query should be:

  SELECT usename
    FROM pg_shadow
    WHERE passwd = 'md5' || md5(usename || usename) OR
                   passwd = 'md5' || md5('company_password' || usename);

Since the md5 passwords in pg_shadow (and pg_authid) are created as:
  MD5(password || username)

By the way, the documentation pages for pg_authid and pg_shadow don't
mention that md5 passwords are stored in this fashion, perhaps they
should? Or is this fact documented somewhere else I'm not seeing?

Josh

Re: Postgresql security checks

From
Thom Brown
Date:
On 1 September 2010 16:17, Josh Kupershmidt <schmiddy@gmail.com> wrote:
> On Wed, Sep 1, 2010 at 5:02 AM, Thom Brown <thom@linux.com> wrote:
>
>> SELECT usename
>> FROM pg_shadow
>> WHERE passwd = 'md5' || md5(usename)
>> OR passwd = 'md5' || md5('company_password');
>
> I think this query should be:
>
>  SELECT usename
>    FROM pg_shadow
>    WHERE passwd = 'md5' || md5(usename || usename) OR
>                   passwd = 'md5' || md5('company_password' || usename);
>
> Since the md5 passwords in pg_shadow (and pg_authid) are created as:
>  MD5(password || username)
>
> By the way, the documentation pages for pg_authid and pg_shadow don't
> mention that md5 passwords are stored in this fashion, perhaps they
> should? Or is this fact documented somewhere else I'm not seeing?

Yes, I had discovered this previously but obviously I've got a memory
leak.  Actually, doesn't this apply to roles too?

 SELECT rolname
   FROM pg_authid
   WHERE rolpassword = 'md5' || md5(rolname || rolname) OR
                  rolpassword = 'md5' || md5('company_password' || rolname);

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Re: Postgresql security checks

From
Josh Kupershmidt
Date:
On Wed, Sep 1, 2010 at 11:32 AM, Thom Brown <thom@linux.com> wrote:
> Yes, I had discovered this previously but obviously I've got a memory
> leak.  Actually, doesn't this apply to roles too?

Yeah, pg_shadow is just a view on top of pg_authid, declared as:

 SELECT ...
   FROM pg_authid
   WHERE pg_authid.rolcanlogin;

So I think either query would work, with pg_shadow only showing you
roles which have login enabled.

Josh

Re: Postgresql security checks

From
Bruce Momjian
Date:
Josh Kupershmidt wrote:
> On Wed, Sep 1, 2010 at 5:02 AM, Thom Brown <thom@linux.com> wrote:
>
> > SELECT usename
> > FROM pg_shadow
> > WHERE passwd = 'md5' || md5(usename)
> > OR passwd = 'md5' || md5('company_password');
>
> I think this query should be:
>
>   SELECT usename
>     FROM pg_shadow
>     WHERE passwd = 'md5' || md5(usename || usename) OR
>                    passwd = 'md5' || md5('company_password' || usename);
>
> Since the md5 passwords in pg_shadow (and pg_authid) are created as:
>   MD5(password || username)
>
> By the way, the documentation pages for pg_authid and pg_shadow don't
> mention that md5 passwords are stored in this fashion, perhaps they
> should? Or is this fact documented somewhere else I'm not seeing?

It is documented here:

    http://www.postgresql.org/docs/9.0/static/encryption-options.html
    17.7. Encryption Options
    Encrypting Passwords Across A Network

        The MD5 authentication method double-encrypts the password on the
    client before sending it to the server. It first MD5-encrypts it based
    on the user name, and then encrypts it based on a random salt sent by
    the server when the database connection was made. It is this
    double-encrypted value that is sent over the network to the server.
    Double-encryption not only prevents the password from being discovered,
    it also prevents another connection from using the same encrypted
    password to connect to the database server at a later time.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Re: Postgresql security checks

From
Thom Brown
Date:
On 8 September 2010 00:10, Bruce Momjian <bruce@momjian.us> wrote:
> Josh Kupershmidt wrote:
>> On Wed, Sep 1, 2010 at 5:02 AM, Thom Brown <thom@linux.com> wrote:
>>
>> > SELECT usename
>> > FROM pg_shadow
>> > WHERE passwd = 'md5' || md5(usename)
>> > OR passwd = 'md5' || md5('company_password');
>>
>> I think this query should be:
>>
>>   SELECT usename
>>     FROM pg_shadow
>>     WHERE passwd = 'md5' || md5(usename || usename) OR
>>                    passwd = 'md5' || md5('company_password' || usename);
>>
>> Since the md5 passwords in pg_shadow (and pg_authid) are created as:
>>   MD5(password || username)
>>
>> By the way, the documentation pages for pg_authid and pg_shadow don't
>> mention that md5 passwords are stored in this fashion, perhaps they
>> should? Or is this fact documented somewhere else I'm not seeing?
>
> It is documented here:
>
>        http://www.postgresql.org/docs/9.0/static/encryption-options.html
>        17.7. Encryption Options
>        Encrypting Passwords Across A Network
>
>            The MD5 authentication method double-encrypts the password on the
>        client before sending it to the server. It first MD5-encrypts it based
>        on the user name, and then encrypts it based on a random salt sent by
>        the server when the database connection was made. It is this
>        double-encrypted value that is sent over the network to the server.
>        Double-encryption not only prevents the password from being discovered,
>        it also prevents another connection from using the same encrypted
>        password to connect to the database server at a later time.

The difference with that is that it's talking about how passwords are
protected by a form of encryption when sent across a connection rather
than how they're stored in a database.

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Re: Postgresql security checks

From
Bruce Momjian
Date:
Thom Brown wrote:
> > It is documented here:
> >
> > ? ? ? ?http://www.postgresql.org/docs/9.0/static/encryption-options.html
> > ? ? ? ?17.7. Encryption Options
> > ? ? ? ?Encrypting Passwords Across A Network
> >
> > ? ? ? ? ? ?The MD5 authentication method double-encrypts the password on the
> > ? ? ? ?client before sending it to the server. It first MD5-encrypts it based
> > ? ? ? ?on the user name, and then encrypts it based on a random salt sent by
> > ? ? ? ?the server when the database connection was made. It is this
> > ? ? ? ?double-encrypted value that is sent over the network to the server.
> > ? ? ? ?Double-encryption not only prevents the password from being discovered,
> > ? ? ? ?it also prevents another connection from using the same encrypted
> > ? ? ? ?password to connect to the database server at a later time.
>
> The difference with that is that it's talking about how passwords are
> protected by a form of encryption when sent across a connection rather
> than how they're stored in a database.

Yes, you are right.  Should this be documented?  Where?

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +

Re: Postgresql security checks

From
Thom Brown
Date:
On 8 September 2010 00:26, Bruce Momjian <bruce@momjian.us> wrote:
> Thom Brown wrote:
>> > It is documented here:
>> >
>> > ? ? ? ?http://www.postgresql.org/docs/9.0/static/encryption-options.html
>> > ? ? ? ?17.7. Encryption Options
>> > ? ? ? ?Encrypting Passwords Across A Network
>> >
>> > ? ? ? ? ? ?The MD5 authentication method double-encrypts the password on the
>> > ? ? ? ?client before sending it to the server. It first MD5-encrypts it based
>> > ? ? ? ?on the user name, and then encrypts it based on a random salt sent by
>> > ? ? ? ?the server when the database connection was made. It is this
>> > ? ? ? ?double-encrypted value that is sent over the network to the server.
>> > ? ? ? ?Double-encryption not only prevents the password from being discovered,
>> > ? ? ? ?it also prevents another connection from using the same encrypted
>> > ? ? ? ?password to connect to the database server at a later time.
>>
>> The difference with that is that it's talking about how passwords are
>> protected by a form of encryption when sent across a connection rather
>> than how they're stored in a database.
>
> Yes, you are right.  Should this be documented?  Where?

Whether it needs documenting, I'm not sure, but if it were to go
anywhere, I believe it would be here:
http://www.postgresql.org/docs/current/static/catalog-pg-authid.html

--
Thom Brown
Twitter: @darkixion
IRC (freenode): dark_ixion
Registered Linux user: #516935

Re: Postgresql security checks

From
Josh Kupershmidt
Date:
On Wed, Sep 8, 2010 at 4:11 AM, Thom Brown <thom@linux.com> wrote:
> On 8 September 2010 00:26, Bruce Momjian <bruce@momjian.us> wrote:
>> Yes, you are right.  Should this be documented?  Where?
>
> Whether it needs documenting, I'm not sure, but if it were to go
> anywhere, I believe it would be here:
> http://www.postgresql.org/docs/current/static/catalog-pg-authid.html

I sent in a suggested doc change to that pg_authid page a few days ago:
http://archives.postgresql.org/pgsql-docs/2010-09/msg00031.php

Josh

Re: Postgresql security checks

From
Bruce Momjian
Date:
Josh Kupershmidt wrote:
> On Wed, Sep 8, 2010 at 4:11 AM, Thom Brown <thom@linux.com> wrote:
> > On 8 September 2010 00:26, Bruce Momjian <bruce@momjian.us> wrote:
> >> Yes, you are right. ?Should this be documented? ?Where?
> >
> > Whether it needs documenting, I'm not sure, but if it were to go
> > anywhere, I believe it would be here:
> > http://www.postgresql.org/docs/current/static/catalog-pg-authid.html
>
> I sent in a suggested doc change to that pg_authid page a few days ago:
> http://archives.postgresql.org/pgsql-docs/2010-09/msg00031.php

I have updated the docs to be a little clearer about unencrypted
passwords.

--
  Bruce Momjian  <bruce@momjian.us>        http://momjian.us
  EnterpriseDB                             http://enterprisedb.com

  + It's impossible for everything to be true. +
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index ecedacc..09152e6 100644
*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
***************
*** 1264,1275 ****
        <entry><structfield>rolpassword</structfield></entry>
        <entry><type>text</type></entry>
        <entry>
!        Password (possibly encrypted); null if none.  If the password is
!        encrypted, this column will contain the string <literal>md5</> followed by a
!        32-character hexadecimal MD5 hash.  The MD5 hash will be of the
!        user's password concatenated to their username (for example, if
!        user joe has password xyzzy, <productname>PostgreSQL</> will store
!        the md5 hash of xyzzyjoe).
        </entry>
       </row>

--- 1264,1277 ----
        <entry><structfield>rolpassword</structfield></entry>
        <entry><type>text</type></entry>
        <entry>
!        Password (possibly encrypted); null if none.  If the password
!        is encrypted, this column will begin with the string <literal>md5</>
!        followed by a 32-character hexadecimal MD5 hash.  The MD5 hash
!        will be of the user's password concatenated to their username.
!        For example, if user <literal>joe</> has password <literal>xyzzy</>,
!        <productname>PostgreSQL</> will store the md5 hash of
!        <literal>xyzzyjoe</>.  A password that does not follow that
!        format is assumed to be unencrypted.
        </entry>
       </row>