Postgresql User not connecting to Database (Nginx Django Gunicorn)

Question

For almost a month now I have been struggling with this issue. Whenever I try to access my Django Admin page on production I get the following error:

OperationalError at /admin/login/
FATAL:  password authentication failed for user "vpusr"
FATAL:  password authentication failed for user "vpusr"

My production.py settings file is as follows:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'vpdb',
        'USER': 'vpusr',
        'PASSWORD': os.environ["VP_DB_PASS"],
        'HOST': 'localhost',
    }
}

NOTE: the environment variable is working correctly. even if I put the normal password hard coded in there it doesn't work.

Here is the list of databases with their owner:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges   
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 vpdb      | vpusr    | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/vpusr            +
           |          |          |             |             | vpusr=CTc/vpusr

And here is the list of users:

                                   List of roles
 Role name |                         Attributes                         | Member of 
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 vpusr     | Superuser, Create DB                                       | {}

As you can see I have also tried adding the roles of Superuser and Create DB to the vpusr but that did not have any effect.

Even when I try to connect through the terminal like this I get the same error:

sudo -u postgres psql -U vpusr vpdb

I still get the error: psql: FATAL: Peer authentication failed for user "vpusr"

When I do this command:

psql -U vpusr -h localhost vpdb

I properly connect to psql as vpusr.

A few more notes: I did delete the database, and the user and re created them. I made sure the password was correct. I use Gunicorn, Nginx, Virtualenv, Django, Postgres on an Ubuntu Server from Digital Ocean.

Thank you in advance for taking the time to read this and helping me out!

EDIT: I have noticed that there are no migrations in my apps migration folder! Could it be that django or my user or postgres does not have permission to write the file?

EDIT: NOTE: I CHANGED THE USER TO TONY In my postgres log file the following errors are found:

    2017-09-09 18:09:55 UTC [29909-2] LOG:  received fast shutdown request
2017-09-09 18:09:55 UTC [29909-3] LOG:  aborting any active transactions
2017-09-09 18:09:55 UTC [29914-2] LOG:  autovacuum launcher shutting down
2017-09-09 18:09:55 UTC [29911-1] LOG:  shutting down
2017-09-09 18:09:55 UTC [29911-2] LOG:  database system is shut down
2017-09-09 18:09:56 UTC [2711-1] LOG:  database system was shut down at 2017-09-09 18:09:55 UTC
2017-09-09 18:09:56 UTC [2711-2] LOG:  MultiXact member wraparound protections are now enabled
2017-09-09 18:09:56 UTC [2710-1] LOG:  database system is ready to accept connections
2017-09-09 18:09:56 UTC [2715-1] LOG:  autovacuum launcher started
2017-09-09 18:09:57 UTC [2717-1] [unknown]@[unknown] LOG:  incomplete startup packet
2017-09-09 18:10:17 UTC [2740-1] tony@vpdb LOG:  provided user name (tony) and authenticated user name (postgres) do not match
2017-09-09 18:10:17 UTC [2740-2] tony@vpdb FATAL:  Peer authentication failed for user "tony"
2017-09-09 18:10:17 UTC [2740-3] tony@vpdb DETAIL:  Connection matched pg_hba.conf line 90: "local   all             all                                     peer"

EDIT:

pg_hba.conf file:

# 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            password
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            md5
#host    replication     postgres        ::1/128                 md5

what can you tell form this?

Answer 1

Your application is trying to connect to PostgreSQL using a password authentication method, but in your pg_hba.conf file, the connection type is matching the md5 method so it's expecting a md5 authentication. We can see this in your log messages

2017-09-01 11:42:17 UTC [16320-1] vpusr@vpdb FATAL:  password authentication failed for user "vpusr"
2017-09-01 11:42:17 UTC [16320-2] vpusr@vpdb DETAIL:  Connection matched pg_hba.conf line 92: "host    all             all             127.0.0.1/32            md5"

Locate your pg_hba.conf file inside your PostgreSQL data directory, vim the pg_hba.conf file and update the line

host    all             all             127.0.0.1/32            md5

and change it to

host    all             all             127.0.0.1/32            password

and then restart your PostgreSQL service

[root@server] service postgresql restart

and then try to authenticate again

To expand on the other messages you are seeing, when you run the command: sudo -u postgres psql -U vpusr vpdb you are not passing the -h <host> parameter, so the connection will attempt to match the line

local    all             all             127.0.0.1/32            <method>

so you will need to check which method of authentication it expects for local connections and authenticate that way, or else pass the -h <host> parameter, and then it will match your line

host    all             all             127.0.0.1/32            password

which means you can then enter your password when prompted, or else change your connection string to

sudo -u postgres -c "PGPASSWORD=<password>;psql -h localhost -U vpusr vpdb"

Answer 2

From the documentation:

db_user_namespace (boolean)

This parameter enables per-database user names. It is off by default. This parameter can only be set in the postgresql.conf file or on the server command line.

If this is on, you should create users as username@dbname. When username is passed by a connecting client, @ and the database name are appended to the user name and that database-specific user name is looked up by the server. Note that when you create users with names containing @ within the SQL environment, you will need to quote the user name.

With this parameter enabled, you can still create ordinary global users. Simply append @ when specifying the user name in the client, e.g. joe@. The @ will be stripped off before the user name is looked up by the server.

db_user_namespace causes the client's and server's user name representation to differ. Authentication checks are always done with the server's user name so authentication methods must be configured for the server's user name, not the client's. Because md5 uses the user name as salt on both the client and server, md5 cannot be used with db_user_namespace.

Although this doesn't explain why psql does the right thing, it's worth looking into.

Another possibility is that psycopg2 links with a different libpq, that links with a different and FIPS compliant OpenSSL. It would have no way to do md5 hashing as that OpenSSL doesn't contain the md5 algorithm. I would expect a different error message, but this bug is all but obvious.

UPDATE: This looks like a red herring. Apparently psycopg2 brings it's own crypto version.

Last thing to check would be character encoding. Test with a password that only contains ascii characters, like abcdefghijkl. If Django works then, look into LANG_* and LC_* variables in the environment.

Answer 3

fox fix password authentication failed for user "vpusr" try add password as is to the settings and the test for os.environ["VP_DB_PASS"],

change Engine

'ENGINE': 'django.db.backends.postgresql_psycopg2'

install if need:

pip install psycopg2

for fix psql: FATAL: Peer authentication failed for user "vpusr" try simple add host

psql -h localhost -U vpusr vpdb
#    ^^^^^^^^^^^^