FATAL: password authentication failed for user "root" postgresql

Question

I use PostgreSQL and Django (format Heroku) and have error

FATAL: password authentication failed for user "root"

Traceback (most recent call last):
      File "manage.py", line 10, in <module>
        execute_from_command_line(sys.argv)
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 367, in execute_from_command_line
        utility.execute()
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/core/management/__init__.py", line 359, in execute
        self.fetch_command(subcommand).run_from_argv(self.argv)
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/core/management/base.py", line 294, in run_from_argv
        self.execute(*args, **cmd_options)
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/core/management/base.py", line 345, in execute
        output = self.handle(*args, **options)
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/core/management/commands/makemigrations.py", line 109, in handle
        loader.check_consistent_history(connection)
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/db/migrations/loader.py", line 276, in check_consistent_history
        applied = recorder.applied_migrations()
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/db/migrations/recorder.py", line 65, in applied_migrations
        self.ensure_schema()
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/db/migrations/recorder.py", line 52, in ensure_schema
        if self.Migration._meta.db_table in self.connection.introspection.table_names(self.connection.cursor()):
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 233, in cursor
        cursor = self.make_cursor(self._cursor())
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 204, in _cursor
        self.ensure_connection()
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 199, in ensure_connection
        self.connect()
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/db/utils.py", line 94, in __exit__
        six.reraise(dj_exc_type, dj_exc_value, traceback)
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 199, in ensure_connection
        self.connect()
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/db/backends/base/base.py", line 171, in connect
        self.connection = self.get_new_connection(conn_params)
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/django/db/backends/postgresql/base.py", line 176, in get_new_connection
        connection = Database.connect(**conn_params)
      File "/home/jet/Desktop/DJango/chatbot/local/lib/python2.7/site-packages/psycopg2/__init__.py", line 164, in connect
        conn = _connect(dsn, connection_factory=connection_factory, async=async)
    django.db.utils.OperationalError: FATAL:  password authentication failed for user "root"

pg_hba.conf

# Database administrative login by Unix domain socket
local   all             postgres                                md5

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                trust
#host    replication     postgres        127.0.0.1/32            trust
#host    replication     postgres        ::1/128                 trust

"Setting.py"

Django settings for aideptraihontao project on Heroku. For more info, see: https://github.com/heroku/heroku-django-template

For more information on this file, see
https://docs.djangoproject.com/en/1.9/topics/settings/

For the full list of settings and their values, see
https://docs.djangoproject.com/en/1.9/ref/settings/
"""

import os
import dj_database_url

# Build paths inside the project like this: os.path.join(BASE_DIR, ...)
BASE_DIR = os.path.dirname(os.path.dirname(os.path.abspath(__file__)))
PROJECT_ROOT = os.path.dirname(os.path.abspath(__file__))


# Quick-start development settings - unsuitable for production
# See https://docs.djangoproject.com/en/1.9/howto/deployment/checklist/

# SECURITY WARNING: keep the secret key used in production secret!
SECRET_KEY = "bjjx34#zc)=sm^4a*zkt5r@$k_%#@x#&llw%@8gtv95^hl1#7="

# SECURITY WARNING: don't run with debug turned on in production!
DEBUG = False

# Application definition

INSTALLED_APPS = [
    'django.contrib.admin',
    'django.contrib.auth',
    'django.contrib.contenttypes',
    'django.contrib.sessions',
    'django.contrib.messages',
    # Disable Django's own staticfiles handling in favour of WhiteNoise, for
    # greater consistency between gunicorn and `./manage.py runserver`. See:
    # http://whitenoise.evans.io/en/stable/django.html#using-whitenoise-in-development
    'whitenoise.runserver_nostatic',
    'django.contrib.staticfiles',
    'jetgoogle',
]

MIDDLEWARE_CLASSES = [
    'django.middleware.security.SecurityMiddleware',
    'whitenoise.middleware.WhiteNoiseMiddleware',
    'django.contrib.sessions.middleware.SessionMiddleware',
    'django.middleware.common.CommonMiddleware',
    'django.middleware.csrf.CsrfViewMiddleware',
    'django.contrib.auth.middleware.AuthenticationMiddleware',
    'django.contrib.auth.middleware.SessionAuthenticationMiddleware',
    'django.contrib.messages.middleware.MessageMiddleware',
    'django.middleware.clickjacking.XFrameOptionsMiddleware',
]

ROOT_URLCONF = 'aideptraihontao.urls'

TEMPLATES = [
    {
        'BACKEND': 'django.template.backends.django.DjangoTemplates',
        'DIRS': [],
        'APP_DIRS': True,
        'OPTIONS': {
            'context_processors': [
                'django.template.context_processors.debug',
                'django.template.context_processors.request',
                'django.contrib.auth.context_processors.auth',
                'django.contrib.messages.context_processors.messages',
            ],
            'debug': DEBUG,
        },
    },
]

WSGI_APPLICATION = 'aideptraihontao.wsgi.application'


# Database
# https://docs.djangoproject.com/en/1.9/ref/settings/#databases

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'comadicui',
        'User':'postgres',
        'PASSWORD':'123456',
        'Host' : '127.0.0.1',
        'Post' : '5432', 
    }
}

AUTH_PASSWORD_VALIDATORS = [
    {
        'NAME': 'django.contrib.auth.password_validation.UserAttributeSimilarityValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.MinimumLengthValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.CommonPasswordValidator',
    },
    {
        'NAME': 'django.contrib.auth.password_validation.NumericPasswordValidator',
    },
]

# Internationalization
# https://docs.djangoproject.com/en/1.9/topics/i18n/

LANGUAGE_CODE = 'en-us'
TIME_ZONE = 'UTC'
USE_I18N = True
USE_L10N = True
USE_TZ = True

# Update database configuration with $DATABASE_URL.
db_from_env = dj_database_url.config(conn_max_age=500)
DATABASES['default'].update(db_from_env)

# Honor the 'X-Forwarded-Proto' header for request.is_secure()
SECURE_PROXY_SSL_HEADER = ('HTTP_X_FORWARDED_PROTO', 'https')

# Allow all host headers
ALLOWED_HOSTS = ['*']

# Static files (CSS, JavaScript, Images)
# https://docs.djangoproject.com/en/1.9/howto/static-files/

STATIC_ROOT = os.path.join(PROJECT_ROOT, 'staticfiles')
STATIC_URL = '/static/'

# Extra places for collectstatic to find static files.
STATICFILES_DIRS = [
    os.path.join(PROJECT_ROOT, 'static'),
]

# Simplified static file serving.
# https://warehouse.python.org/project/whitenoise/
STATICFILES_STORAGE = 'whitenoise.storage.CompressedManifestStaticFilesStorage'
LOGGING = {
    'version': 1,
    'disable_existing_loggers': False,
    'formatters': {
        'verbose': {
            'format' : "[%(asctime)s] %(levelname)s [%(name)s:%(lineno)s] %(message)s",
            'datefmt' : "%d/%b/%Y %H:%M:%S"
        },
        'simple': {
            'format': '%(levelname)s %(message)s'
        },
    },
    'handlers': {
        'file': {
            'level': 'DEBUG',
            'class': 'logging.FileHandler',
            'filename': 'mysite.log',
            'formatter': 'verbose'
        },
    },
    'loggers': {
        'django': {
            'handlers':['file'],
            'propagate': True,
            'level':'DEBUG',
        },
        'MYAPP': {
            'handlers': ['file'],
            'level': 'DEBUG',
        },
    }
}

"psql -U postgres -h localhost" it ok!!

Answer 1

I know this is an old question but this is the thing that worked for me

pip install python-dotenv

Then in manage.py add

from dotenv import load_dotenv

load_dotenv()

In .env file

export DB_NAME=databasename
export DB_USER=db_use
export DB_USER_PASSWORD=password
export DB_HOST=host

Now restart the server to run.

Answer 2

you have wrong database settings

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'comadicui',
        'User':'postgres',
        'PASSWORD':'123456',
        'Host' : '127.0.0.1',
        'Post' : '5432', 
    }
}

try it

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'comadicui',
        'USER':'postgres',
        'PASSWORD':'123456',
        'HOST' : '127.0.0.1',
        'PORT' : '5432', 
    }
}

when you don't have USER in database configuration, then django getting root user as default user

Answer 3

1. Connect as any user. Connect as the superuser postgres (note, the superuser name may be different in your installation. In some systems it is called pgsql, for example.)

psql -U postgres

or

psql -h 127.0.0.1 -U postgres

(note that with the first command you will not always be connected with local host)

2. Reset password

ALTER USER my_user_name with password 'my_secure_password';

3. Restore the old pg_hba.conf as it is very dangerous to keep around

cp pg_hba.conf-backup pg_hba.conf

4. restart the server, in order to run with the safe pg_hba.conf

sudo /etc/init.d/postgresql restart