1

PostgreSQL supports multiple authentication methods, and I think it supports SSL and plaintext connections. How do I find out which authentication method a server uses? I do not have access to the server configuration. Can psql tell we which algorithm it uses to transmit the password, and whether the connection is encrypted? Is there a PostgreSQL security scanner for this purpose?

2

If you don't have access to read the server configuration, the easiest way to check is to require the use of TLS on your client and see if you're able to connect successfully. This is done through the sslmode parameter key word. For example:

$ psql "host=12.34.56.78 port=5432 dbname=mydb sslmode=require"

After you have verified that TLS is available, you can also check to see if plaintext mode has been disabled by setting sslmode to disable.

Sidenote: The $ is not part of the command; it's to show that the line should be ran from a terminal, and does not need to be a superuser account.

2
  • Another security-relevant option is gssencmode=require. – Sjoerd Jan 20 at 8:43
  • channel_binding=require can also divulge information on the security of the connection. Channel binding only works with SCRAM over SSL, and it gives different errors depening on whether the server does not support SCRAM or SSL. – Sjoerd Jan 20 at 10:09
0

I changed the source of libpq to print the authentication request type.

  1. Get the source code.
  2. Run ./configure in the newly created postgresql directory.
  3. Change src/interfaces/libpq/fe-connect.c to print the areq variable. Valid values are in src/include/libpq/pqcomm.h.
  4. Compile and run. To compile only libpq, navigate to the src/interfaces/libpq directory and run make.
  5. Run psql. To load the modified library from the current directory, use LD_LIBRARY_PATH=. on Linux or DYLD_LIBRARY_PATH=. on MacOS.

To print the authentication type, I added the printf here:

/* It is an authentication request. */ 
conn->auth_req_received = true; 
 
/* Get the type of request. */ 
if (pqGetInt((int *) &areq, 4, conn)) 
{       
        /* We'll come back when there are more data */ 
        return PGRES_POLLING_READING; 
}       
msgLength -= 4; 
 
printf("Auth method: %u\n", areq);

Then, when connecting:

$ DYLD_LIBRARY_PATH=. psql "host=my.precious.host"
Auth method: 5
Password for user Sjoerd: 

And auth method 5 corresponds with AUTH_REQ_MD5 according to pqcomm.h.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.