Thread: Postgresql security checks
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.
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
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
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
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
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
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. +
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
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. +
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
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
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>