First of all, I would like to say that I will not compare a licensed product (Oracle) vs. a freely distributed product (Postgres). This article is written to mention how password security is managed in both products, not to tell Oracle (or postgres) is the best product or vice versa.
Ok, that said, I have analyzed and worked on how password security is managed based on 4 topics: Password hashes, Salt used, password length and case sensitivity.
During all these tests, I used an Oracle 12.2 database (EE) and the last postgres server version 9.6.2 I built from sources.
HASHES
In Oracle and in Postgres, both rdbms use hash algorithms to store encrypted passwords.
Oracle
In Oracle 12.2, depending on the value of SQLNET.ALLOWED_LOGON_VERSION_SERVER in sqlnet.ora, the password can be stored using different hash algorithms.
To sum up:
- Before Oracle 11gR1: an algorithm based on DES algorithm is used to generate password hash. This algorithm is weak due to the key length which is too short, and because the password generated is not case sensitive. The password hash is store in USER$.PASSWORD in the dictionnary.
- From Oracle 11gR1: Oracle uses SHA1 algorithm. Nowdays, SHA1 has been broken and collisions are possible. SHA1 password hash is stored in USER$.SPARE4 (the hash is the string that starts with S:) .
- Starting with Oracle 12c, Oracle started to use SHA2(512) combined with PBKDF2 function (See. https://en.wikipedia.org/wiki/PBKDF2). At the moment I wrote this blogpost, these algorithms haven’t been broken yet. SHA2(512) password hash is stored in USER$.SPARE4 (the hash is the string that starts with T:) .
Please note, that in Oracle 12.1, an HTML digest has been used but has been now removed in Oracle 12.2. By default, Oracle 12c stores SHA1 and SHA2(512) hashes:
SQL> create user laurent identified by laurent;
User created.
SQL> select name,spare4 from user$ where name='LAURENT';
NAME SPARE4
-------------------- ------------------------------------------------------------
LAURENT S:5F0BAF84F6E102D0B1B7AEA78A5C1234E39F131EEC9C5C5E6B07CF3482
AF;T:B340F958041950CD2977301A15A2781D5EA3F71D51E25E9432EBC78
D369F13D9D568AC230422FDD2E28F1360D90F360E0A52B76B6F32522EF2B
3C1625489A5FBC93D710F50C4F0DDE7BFB8EB4CD4C729
Postgres
In postgres, password hashes are generated with the md5 algorithm. The hashed password is store in pg_authid table (in pg_catalog), the 3 first bytes are set to ‘md5’ followed by the hash:
postgres=# create role test with encrypted password 'test' login;
postgres=# select rolname,rolpassword from pg_authid where rolname='test';
rolname | rolpassword
---------+-------------------------------------
test | md505a671c66aefea124cc08b76ea6d30bb
Please note that, even if it’s not the default, password can be stored without encryption in the catalog.
postgres=# alter role test with unencrypted password 'passwd';
ALTER ROLE
postgres=# select rolname,rolsuper,rolpassword from pg_authid where rolname='test';
rolname | rolsuper | rolpassword
---------+----------+-------------
test | f | passwd
Match score
Oracle wins the hashes game because Oracle 12 is the only version that offer a strong hash algorithm (SHA2(512) + PBKDF2) that hasn’t been broken yet (SHA1 and MD5 are both weak algorithms).
SALT
As a remind, a salt is a random data that is used as an additional input to a function that hashes a password. As a result, the hashed password will be different each time it will be modified, even if the same password is used.
Oracle
- Before Oracle 11g, Oracle don’t use a real salt. the algorithm (you can find it easily on the internet) uses DES encryption and a initial key “0x0123456789ABCDEF”.
- From Oracle 11gR1, password is concatenated with a (supposed) random 10 bytes string and hashes with the SHA1 algorithm
- With Oracle 12c onwards, a (supposed) random salt is used too.
As a result, if we set the same password than previous one, the password hash changes:
SQL> alter user laurent identified by passwd;
User altered.
SQL> select name,spare4 from user$ where name='LAURENT';
NAME SPARE4
------------------------------ ------------------------------------------------------------------------------------------------------------------------
LAURENT S:3F2A7144E07CE3631F88D00C9851703D107A4230B3C8A820BAC49372BFE6;T:47A8D24FFC2465100B8C5097278DDDF012EFD24303680DB8C7151ED
54D3DE0E2BD649A3788FDB50316B843903D686034BEA6E5FC51AF914F2C30547DCF89BF507CDEB5D9B14BA70B8572C35251DCF861
SQL> alter user laurent identified by passwd;
User altered.
SQL> select name,spare4 from user$ where name='LAURENT';
NAME SPARE4
------------------------------ ------------------------------------------------------------------------------------------------------------------------
LAURENT S:6073B325C321F6CAFD97297ECC5CD7C423F3F381A1E0111400AC8BFBA87F;T:003F805AA6CA9E86393D84FB09E0B66BEBDEC3CBBA0DCC4EE59E83F
85E980BF86AB262D05BC4C424392383EE6FA4A0B88A5E0C7D1A76A6A4EBE1900CF5B37C5F20ED1D42F0D2FA912DD222E72E716391
Postgres
If you have a look into the postgres C code, password is hashed in the src/backend/commands/user.c file inside a function called CreateRole or AlterRole depending on what you are doing on your role.
Let’s take the AlterRole function for example. In this function, a call is made to pg_md5_encrypt function. This function code is available in the src/backend/libpq/md5.c file.
By having a look to the pg_md5_encrypt function call and how it’s designed, it’s easy to concluded that role name is used as salt.
Postgres adds a string to the password before hashing it, but it’s not random. As a consequence, if you do the same operation as I did previously for Oracle, we can see below that the password hash remains the same which is … in my opinion, weak.
postgres=# alter role test with encrypted password 'passwd';
ALTER ROLE
postgres=# select rolname,rolsuper,rolpassword from pg_authid where rolname='test';
rolname | rolsuper | rolpassword
---------+----------+-------------------------------------
test | f | md50104560cc42670b87acd04a5bba33344
(1 row)
postgres=# alter role test with encrypted password 'passwd';
ALTER ROLE
postgres=# select rolname,rolsuper,rolpassword from pg_authid where rolname='test';
rolname | rolsuper | rolpassword
---------+----------+-------------------------------------
test | f | md50104560cc42670b87acd04a5bba33344
Match score
Oracle wins the salt match because it uses what is really defined as a salt … random data.
PASSWORD LENGTH
As we all know, the more the password is long the more it is difficult to guess (specially through a brute force attack).
Oracle
Oracle have a hard limit for password length which is set to 30 characters.
Above 30 characters length, an error is thrown:
SQL> alter user laurent identified by abcdefghijklmnopqrstuvwxyz0123;
User altered.
SQL> alter user laurent identified by abcdefghijklmnopqrstuvwxyz01234;
alter user laurent identified by abcdefghijklmnopqrstuvwxyz01234
*
ERROR at line 1:
ORA-00972: identifier is too long
Postgres
For postgres, I was able to set a password with unlimited length (ALTER ROLE Statement ends correctly) but I could only login the database by using a password up to 995 characters, and only by using PGPASSWORD environment variable (see below). Above 995 characters, message “LOG: invalid message length” is logged in the server logfile (I didn’t find any reference to this limit in the code, and but it deserves another analysis to go further):
Please note that, with the psql binary, this one uses the C function named “simple_prompt” to read the password entered in the standard input file, and this function call is made with a limit of 100 characters. So if you use psql to login, you cannot directly type a password with more than 100 characters. (See. src/bin/psql/startup.c, src/port/sprompt.c).
postgres=# alter role test with encrypted password 'abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123';
ALTER ROLE
postgres=# \q
laurent@pg:~/pgsrc/postgresql-9.6.2$ export PGPASSWORD=abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123abcdefghijklmnopqrstuvwxyz0123
laurent@pg:~/pgsrc/postgresql-9.6.2$ psql -d laurent -U test
psql (9.6.2)
Type "help" for help.
laurent=>
Match score
On the field of password length, Postgres wins the game. Indeed, with PG you can set very long password and hacking a password can become a very long task to do (but be careful with the client tools you use). But, above 100 characters length, login to a postgres becomes painful depending on the client you use.
With Oracle or with postgres, the best thing to do is to use long password that contain all kind of characters (upper and lower case, number, special characters).
CASE SENSITIVITY
In this field, (modern) Oracle and Postgres are using case sensitive password.
But with Oracle
- 10g
- 11g with sec_case_sensitive_logon parameter set to FALSE
- 12c with SQLNET.ALLOWED_LOGON_VERSION_SERVER set 8, 9, 10 or 11.
Passwords become case unsensitive which is not very secured.
Data ... as usual 