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"