MD5 Hashed passwords: where does it get hashed?

Question

I am connecting to a local PostgreSQL database with an md5 hashed password.

It works but I want to understand what is happening under the hood.
Does pq hash the password before it is sent over the network? How would it know whether to hash it or leave it as plain text? The server (in pg_hba.conf) is the one who specifies the authentication method of how the password is sent over the connection.

Is there a handshake that goes on between pq and psql before the connection string with the password is sent over?

  user := "foo_user"
  password := "test"
  dbname := "bar"
  connectionString := fmt.Sprintf(
      "user=%s password=%s dbname=%s", 
      user, 
      password, 
      dbname)
  db, err := sql.Open("postgres", connectionString)

The user was created with a password via:

ALTER USER foo_user WITH PASSWORD 'test';

And verified that the password is being stored as a hash:

postgres=# SELECT rolname, rolpassword from pg_authid;
      rolname      |             rolpassword             
-------------------+-------------------------------------
 postgres          | 
 pg_signal_backend | 
 foo_user          | md51083525553eab8f4090ada980d2b86e7
(3 rows)

And pg_hba.conf is completely unmodified:

# maintenance (custom daily cronjobs, replication, and similar tasks).
#
# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5

Time to migrate off it. http://passlib.readthedocs.io/en/stable/lib/passlib.hash.postgres_md5.html "This algorithm is not considered secure by modern standards. It should only be used when verifying existing hashes, or when interacting with applications that require this format. For new code, see the list of recommended hashes." — ceejayoz, Dec 22 '17 at 17:49

score 1 · Accepted Answer · answered Dec 22 '17 at 21:32

There are two places where password hashing takes place, and they should not be confused.

When a password is set for a role, it is concatenated with the user name and then hashed. This is the password stored in pg_authid, and that is the actual password used by PostgreSQL. PostgreSQL does not use the password you entered, but a hashed version of it, to keep attackers form stealing your password and then trying it outside of PostgreSQL (in case the same password is used in several places).

So to break into a database, you do not actually need to know the clear text password, the “actual” hashed password is enough.

There are two places where the hashing can take place:
- On the server side, if you use
```
CREATE/ALTER ROLE ... PASSWORD 'mypassword';
```
  This is not so good, because the password is sent over the wire in clear text and it can show up in the database log.
- On the client side, if you use
```
CREATE/ALTER ROLE ... PASSWORD 'hashedpassword';
```
  This is better, and it is what psql uses internally if you use the \password command.
During session authentication, if the authentication method specified for the database and user demands it. The server will then respond to the connection request with a AuthenticationMD5Password message (see the documentation).

The client then hashes the clear text password to obtain the actual password, then hashes it again with a random “salt” provided by the server.

The server hashes the password from pg_authid in the same fashion and compares the result.

MD5 Hashed passwords: where does it get hashed?

1 Answers1