Обсуждение: 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>