PostgreSQL Case Sensitivity Pitfall

A little while back I was working on Dockerizing my PostgreSQL instances, and I ran into a headache caused by the case sensitiveness of PostgreSQL. This isn't the first time that PostgreSQL has caught me off guard with being case sensitive where I didn't expect so I wanted to document this before I forget what happened. It was a bit more difficult to troubleshoot this time since I was erroneously blaming the issue on being related to running inside a Docker container, which turned out to not be a problem at all.

Creating User and Logging In

I have a local Jenkins instance that takes backups of a number of PostgreSQL databases. Since we all know that an untested backup is not a backup at all, I decided to have Jenkins spool up a test PostgreSQL container and restore the recent backups for me. In doing this I decided to create a new super user account for Jenkins to use in these cases.

I created the user from psql at command line using this command.

$ psql -U admin_user -h 127.0.0.1 -p 5432 postgres -c "CREATE ROLE jenkins_TEST_SU LOGIN PASSWORD 'reallysecurepassword' SUPERUSER"

I then tried to log in using the new Super User account I created above and received an unexpected error.

$ psql -U jenkins_TEST_SU -h 127.0.0.1 -p 5432 -d postgres

Password for user jenkins_TEST_SU:

psql: FATAL:  password authentication failed for user "jenkins_TEST_SU"
FATAL:  no pg_hba.conf entry for host "192.168.0.100", user "jenkins_TEST_SU", database "postgres", SSL off

Uh oh, it didn't work!

What Happened?

I was initially suspicious of my Docker configuration and how I was loading the pg_hba.conf file referenced in the error above, but that wasn't the issue. The problem was how PostgreSQL created the user above. While I explicitly entered the user name jenkins_TEST_SU, PostgreSQL created the user as jenkins_test_su in all lowercase characters.
Changing the command to connect to the following string worked without issue. In case you're wondering, this has been documented for quite some time with no resolution.

$ psql -U jenkins_test_su -h 127.0.0.1 -p 5432 -d postgres

Conclusion

With this issue in mind, I've decided to always ensure that my PostgreSQL user names are entirely lowercase.

Remember, PostgreSQL is normally case sensitive! See this page for more details.

It is worth noting that for data comparisons, PostgreSQL is case sensitive and there is no option to change this behaviour. You will have to force data to upper or lower on both sides of text comparisons if case is not important to the operation and there is a chance of it being different. This conversion might be a good time to force data used in joins and comparisons to all upper or lower case. You will also need to look at the application for code that does comparisons of user-entered information taking advantage of MS SQL Server's typical case insensitivity.