postgresql: FATAL: password authentication failed for user "douglas"

Question

I am trying to migrate a DB from sqlite to postgresql...so I typed:

sudo -u postgres psql
postgres=# ALTER USER postgres WITH PASSWORD 'newpassword';

and the output returns ALTER ROLE

but when I type python manage.py migrate I receive always the same error:

django.db.utils.OperationalError: FATAL: password authentication failed for user "douglas"

This is the database sections of my settings.py.

# Old, using mysqlite
"""
DATABASES = {
    #'default': {
    #    'ENGINE': 'django.db.backends.sqlite3',
    #    'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    #}
    'default': dj_database_url.config(default='postgres://localhost:5432/postgres_db_name'),
}
"""

# New, using postgres
DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'douglas_db',
        'USER': 'douglas',
        'PASSWORD': 'vamointer',
        'HOST': '127.0.0.1',
        'PORT': '5432',
    }
}

Note: When I run the 'ALTER USER postgres WITH PASSWORD' I put in the same password defined in the settings.py.

The error mentions the user account `user` but your alter statement is changing the password of the user `postgres`. Lastly, your settings.py references yet another user; `douglas`. Which user do you actually want to use, and are you running commands that reference just this user? — Shadow, Mar 28 '17 at 04:58
Fair enough. What about the SQL query? You should be running `ALTER USER douglas WITH PASSWORD 'vamointer';` — Shadow, Mar 28 '17 at 05:28
Sorry, I don't understand what you mean by that. Did you run the the query I wrote on the postgres database before attempting to run migrations? — Shadow, Mar 28 '17 at 05:32
I just try migrate the DB of a Django application from sqlite to postgrsql...I was following this steps http://stackoverflow.com/questions/3476606/django-what-are-the-best-practices-to-migrate-a-project-from-sqlite-to-posgresq — Douglas da Dias Silva, Mar 28 '17 at 05:33

Shadow · Accepted Answer · 2019-09-24T01:44:59.587

26

The SQL you are running does not match the user you are attempting to use.

You will need to create the user if it does not exist:

CREATE USER douglas WITH PASSWORD 'vamointer';

or if it does exist, change that user's password instead.

ALTER USER douglas WITH PASSWORD 'vamointer';

Once you have done that you should have more luck. You may need to assign permissions to that user as well.

edited Sep 24 '19 at 01:44

answered Mar 28 '17 at 05:36

Shadow

8,749
4
47
57

where should I put it. I tried this at my postgres container and it errored `CREATE: not found` – Farhang Amaji Aug 05 '22 at 08:16
@FarhangAmaji sounds like you might have just tried to paste this the shell... you need to do it in psql. – Shadow Aug 05 '22 at 13:56

score 13 · Answer 2 · answered Aug 21 '19 at 21:28

13

If you are bone-headed like me and have used 'USERNAME' instead of 'USER' in your Django database configs in settings.py, make sure you change it to 'USER' else you will see this same error. Hope this helps someone like me down the road.

answered Aug 21 '19 at 21:28

Harlin

1,059
14
18

3

This solved my problem and it looks like we're the same haha. – Steven Oct 25 '19 at 06:53

score 9 · Answer 3 · edited Sep 23 '19 at 19:13

Special characters in postgresql are converted to different characters while execution. Make sure you do not have special characters (#,$,etc..) in your password.

If you do, change the postgresql password as follows:

sudo -u postgresql psql
postgresql=#ALTER USER yourusername WITH PASSWORD 
'set_new_password_without_special_character';

Make sure you do not forget the ; at the end of postgresql command. Then run python manage.py and it should work!

score 3 · Answer 4 · edited Apr 05 '23 at 01:28

3

You can try this:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'telusko',
        'USER': 'postgres', # not USERNAME, that will cause an error.
        'PASSWORD': '1234',
        'HOST':'localhost',
    }
}

edited Apr 05 '23 at 01:28

Shadow

8,749
4
47
57

answered Oct 16 '19 at 17:41

user12228212

31
1

score 2 · Answer 5 · answered Apr 19 '20 at 14:04

2

In my case, I had to change

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

to

'ENGINE': 'django.db.backends.postgresql',

Hope it helps someone!

answered Apr 19 '20 at 14:04

Sarmad Gulzar

136
2
9

That was the old "engine" for postgresql. I kept trying to use that one for some time before I figured out now it was just simply 'postgresql'. Thanks for the post! – Harlin Nov 18 '20 at 17:41

score 2 · Answer 6 · answered Jul 14 '20 at 20:50

2

It's also possible that your PostgreSQL server is not running. Please run next command, to check if postgres is running:

sudo service postgresql status

If not please run it, using:

sudo service postgresql start

Also, you can have wrong port in your settings. To check where Postgres is running use:

sudo netstat -plunt |grep postgres

And after update PORT in DATABASE config in Django settings

answered Jul 14 '20 at 20:50

Roksolanka Fedkovych

117
4

The missing PORT in setting.py solved my issue. Thanks. – pe.kne Jan 07 '21 at 19:49

score 1 · Answer 7 · answered Apr 22 '22 at 20:34

For me it was as simple as using capital letters in my db and user name.

It seems that postgres automatically ignores case:

postgres=# CREATE USER MyProjectUser WITH PASSWORD 'password';

is then stored as:

postgres=# \du+
                                          List of roles
 Role name |                         Attributes                         | Member of | Description
-----------+------------------------------------------------------------+-----------+-------------
 myprojectuser |                                                        | {}        |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}        |

So in my database, this:

DATABASES = {
    'default': {
        .
        .
        'USER': 'MyProjectUser',
        'PASSWORD': 'password',}}

wasn't recognized & threw password auth failure error.

score 0 · Answer 8 · answered Jul 16 '20 at 03:34

Another reason why this happens is if you have a conflicting version of postgres running. I had one running in docker and another on my system.

systemctl stop postgresql to stop the system version and purge it if you'd rather use only the docker version.

score 0 · Answer 9 · edited Mar 24 '22 at 21:47

Try this for creating the user for Postgres

postgres=# create user username with encrypted password 'password';

Add permissions

postgres=# grant all on database db_name to username;

other useful commands might help

sudo -u postgres psql

\du+ #list of user

\l+ # list of DB

Credits to this article

Note: the database name and the username are always in lowercase, even if you create them with capitals.

score 0 · Answer 10 · answered Oct 24 '22 at 02:52

Wanna do my part as a fellow developer here who got this issue too

You might be following a sometimes dubious "by the book guide" to set up PGSQL with Django. Some of the guides do not require us to write down the HOST and PORT in the DATABASES section. Like this :

DATABASES = {
'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'NAME': 'urdb',
    'USER': 'your_username',
    'PASSWORD': '1234',
    'HOST':'localhost',
    'PORT':''
}}

I fixed this by :

Check my actual host and port, in my case, the default port is 5433
Put 5433 in the PORT

DATABASES = {
'default': {
    'ENGINE': 'django.db.backends.postgresql',
    'NAME': 'urdb',
    'USER': 'your_username',
    'PASSWORD': '1234',
    'HOST':'localhost',
    'PORT':'5433'
}}

Et voila! No more authentication issues!

Don't expect your environment nor codes to know everything! :)

Farhang Amaji · Answer 11 · 2022-08-08T07:50:50.500

I had not exactly the same problem cause mine was with docker-compose, django but mainly with postgres in which in steps of building django project (note deletion of database wasnt an issue) I couldnt migrate my database so by deleting data\db folder I attempted to solve it. for more information on my problem Django Postgres docker-compose connection error mainly failed: FATAL: password authentication failed for user foobar ERROR: 2

postgresql: FATAL: password authentication failed for user "douglas"

11 Answers11

Linked