Database says NOPE

I joined Virtual Coffee last week and they have this awesome zoom meeting that members occasionally spin up for pairing and coworking. A great dev named Travis Martin was adapting an existing project that had an app bundled with a Postgres v9 DB in a docker context, and he was trying to redeploy it in a different context with a newer version of Postgres. At the point I joined the zoom meeting, the app was having trouble authenticating to Postgres.

I've worked with a few different databases before, and I'd contributed to the TAU project in the past which uses Django and Postgres. As I tried to make suggestions, I referred to a few of the bootstrapping scripts I encountered on that project, and they helped to some degree of making sure all the pieces were in place in the database server (pasted below):

• check if user exists: SELECT COUNT(*) AS count FROM pg_catalog.pg_user WHERE usename={db_user}
• check if database exists: SELECT COUNT(*) AS count FROM pg_database WHERE datname={db_name}
• create the database if needed: CREATE DATABASE {db_name};
• create the user if needed: CREATE USER {db_user} WITH ENCRYPTED PASSWORD '{db_pw}';
• assign privileges: GRANT ALL PRIVILEGES ON DATABASE {db_name} TO {db_user}; # use with care
• update user password if needed: ALTER USER {db_user} WITH ENCRYPTED PASSWORD '{db_pw}'

However, after using statements like these to make sure the DB server was setup correctly, we still were getting the same error message. Travis verified all sorts of things, like whether the app had access to the environment variables he wanted. We had a big clue when he attempted to authenticate to the Postgres over the psql command with the app's credentials, and he didn't get an opportunity to enter a password. The trick turned out to be that he was logged into the OS with the same username, configured earlier in the deployment process. As we got to reading further in the Postgres docs, we found that the Postgres configuration file pg_hba.conf had the authentication method set to "ident", which relies on a separate
"ident" service, and in order to get things working, Travis set the authentication method to different option more appropriate for clients leveraging usernames and encrypted passwords.

This was a pretty specific use case, but maybe it'll help somebody!