django test app error - Got an error creating the test database: permission denied to create database
PythonSqlDjangoPostgresqlDjango TestingPython Problem Overview
When I try to test any app with command (I noticed it when I tried to deploy myproject using fabric, which uses this command):
python manage.py test appname
I get this error:
Creating test database for alias 'default'...
Got an error creating the test database: permission denied to create database
Type 'yes' if you would like to try deleting the test database 'test_finance', or 'no' to cancel
syncdb command seems to work. My database settings in settings.py:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
'NAME': 'finance', # Or path to database file if using sqlite3.
'USER': 'django', # Not used with sqlite3.
'PASSWORD': 'mydb123', # Not used with sqlite3.
'HOST': '127.0.0.1', # Set to empty string for localhost. Not used with sqlite3.
'PORT': '', # Set to empty string for default. Not used with sqlite3.
}
}
Python Solutions
Solution 1 - Python
When Django runs the test suite, it creates a new database, in your case test_finance. The postgres user with username django does not have permission to create a database, hence the error message.
When you run migrate or syncdb, Django does not try to create the finance database, so you don't get any errors.
You can add the createdb permission to the django user by running the following command in the postgres shell as a superuser (hat tip to this stack overflow answer).
=> ALTER USER django CREATEDB;
Note: The username used in the ALTER USER <username> CREATEDB; command needs to match the database user in your Django settings files. In this case, the original poster, had the user as django the above answer.
Solution 2 - Python
I have found interesting solution to your problem.
In fact for MySQL you can grant privileges for non-existing database.
So you can add name 'test_finance' for your test database in your settings:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2', # Add 'postgresql_psycopg2', 'mysql', 'sqlite3' or 'oracle'.
'NAME': 'finance', # Or path to database file if using sqlite3.
'USER': 'django', # Not used with sqlite3.
'PASSWORD': 'mydb123', # Not used with sqlite3.
'HOST': '127.0.0.1', # Set to empty string for localhost. Not used with sqlite3.
'PORT': '', # Set to empty string for default. Not used with sqlite3.
'TEST': {
'NAME': 'test_finance',
},
}
}
start MySQL shell as the root user:
mysql -u root -p
and now grant all privileges to this non-existing database in MySQL:
GRANT ALL PRIVILEGES ON test_finance.* TO 'django'@'localhost';
Now Django will start tests without any problems.
Solution 3 - Python
In the case of Postgres, the user must have createdb permission.
ALTER ROLE miriam CREATEDB;
See this documentation: https://docs.djangoproject.com/en/2.0/topics/testing/overview/#the-test-database
Solution 4 - Python
If database is mysql then these two changes will get the things done.
1.Open mysite/mysite/settings.py
Your database settings should have an additional TEST block as shown with projectname_test.
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'myproject',
'USER': 'chandan',
'PASSWORD': 'root',
'HOST': 'localhost',
'PORT': '3306',
'TEST': {
'NAME': 'myproject_test',
},
}
}
2.Type the below command using mysql command prompt or mysql workbench to give all privilages to the user specified in settings.py
GRANT ALL PRIVILEGES ON myproject_test.* TO 'chandan'@'localhost';
Now you can run python manage.py test polls.
Solution 5 - Python
In my case, GRANT PRIVILEGES solutions didn't work with Python 3.7.2, Django 2.1.7 and MySQL 5.6.23... I don't know why.
So I decided to use SQLite as a TEST database...
DATABASES = {
'default': {
'NAME': 'productiondb',
'ENGINE': 'mysql.connector.django', # 'django.db.backends.mysql'
'USER': '<user>',
'PASSWORD': '<pass>',
'HOST': 'localhost',
'PORT': 3306,
'OPTIONS': {
'autocommit': True,
},
'TEST': {
'ENGINE': 'django.db.backends.sqlite3',
'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
},
}
}
After that, TESTS car run without troubles:
$ python manage.py test
Creating test database for alias 'default'...
System check identified no issues (0 silenced).
Destroying test database for alias 'default'...
----------------------------------------------------------------------
Ran 0 tests in 0.000s
OK
Process finished with exit code 0
Solution 6 - Python
Wow so combining all of the answers here with a little tweaking finally got me to a working solution for docker-compose, django, and postgres...
First the postgres command given by noufal valapra is not correct (or maybe just not current), it should be:
ALTER USER docker WITH CREATEDB;
In the case of a docker-compose setup, this will go in the init.sql file, this is what mine looks like:
CREATE USER docker;
ALTER USER docker WITH CREATEDB;
CREATE DATABASE djangodb;
GRANT ALL PRIVILEGES ON DATABASE djangodb TO docker;
Then the Dockerfile for postgres looks like this:
FROM postgres:10.1-alpine
COPY init.sql /docker-entrypoint-initdb.d/
Then the Django settings.py has this entry:
if 'RDS_DB_NAME' in os.environ:
INTERNAL_DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': os.environ['RDS_DB_NAME'],
'USER': os.environ['RDS_USERNAME'],
'PASSWORD': os.environ['RDS_PASSWORD'],
'HOST': os.environ['RDS_HOSTNAME'],
'PORT': os.environ['RDS_PORT'],
}
}
and the docker-compose looks like this:
version: '3.6'
services:
postgresdb:
build:
context: ./
dockerfile: ./Dockerfile-postgresdb
volumes:
- postgresdata:/var/lib/postgresql/data/
django:
build:
context: ../
dockerfile: ./docker/Dockerfile
environment:
- RDS_DB_NAME=djangodb
- RDS_USERNAME=docker
- RDS_PASSWORD=docker
- RDS_HOSTNAME=postgresdb
- RDS_PORT=5432
stdin_open: true
tty: true
depends_on:
- postgresdb
volumes:
postgresdata:
Solution 7 - Python
If you are using docker-compose what worked for me was the following:
ALTER ROLE username CREATEDB;
GRANT ALL PRIVILEGES ON test_database_name.* TO 'username';
or
ALTER ROLE username CREATEDB;
GRANT ALL PRIVILEGES ON *.* TO 'username'@'%';
My settings looks like this:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.mysql',
'NAME': 'database_name',
'USER': 'username',
'PASSWORD': 'password',
'HOST': 'db',
'PORT': '3306',
}
}
and my docker-compose.yml looks as follows:
version: '3'
services:
web:
build: .
command: './wait_for_db_and_start_server.sh'
env_file: env_web
working_dir: /project_name
links:
- db
volumes:
- .:/volume_name
ports:
- "8000:8000"
depends_on:
- db
db:
image: mysql:5.7
restart: always
env_file: env_db
working_dir: /db
volumes:
- ./Dump.sql:/db/Dump.sql
ports:
- "3306:3306"
Solution 8 - Python
Switch over to the postgres account on your server by typing:
sudo -i -u postgres
You can now access a Postgres prompt immediately by typing:
psql
Now type
ALTER USER username CREATEDB;
Solution 9 - Python
A superuser account is the easiest way to guarantee smooth testing. so a simpler way of making the django user su is to do ALTER django WITH SUPERUSER .
for more information https://www.postgresql.org/docs/current/sql-alteruser.html
Solution 10 - Python
Check the the actions in runtime and switch your database
import sys
TESTING = sys.argv[1:2] == ['test']
if TESTING==False:
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': config('DB_NAME'),
'USER': config('DB_USER'),
'PASSWORD': config('DB_PASSWORD'),
'HOST': config('DB_HOST'),
'PORT': ''
}
}
else:
DATABASES = {
'default': {
"ENGINE": "django.db.backends.sqlite3",
"TEST": {
"NAME": os.path.join(BASE_DIR, "test_db.sqlite3"),
}
}}
Solution 11 - Python
Maybe you put your test in suspended mode or as a backgrounded job. Try with fg command in bash shell.
Solution 12 - Python
You can also just create a test database manually but putting the TEST key in your settings.py
Then fill pass the MIRROR key with value 'default' so you can test off of an exact replica of your default db, or whichever db you like.
DATABASES = {
'default': {
'ENGINE': 'django.db.backends.postgresql_psycopg2',
'NAME': 'finance',
'USER': 'django',
'TEST': {
'MIRROR': 'default',
},
'PASSWORD': 'mydb123',
'HOST': '127.0.0.1',
'PORT': '',
}
}
You can also check the advanced django postgres docs
Solution 13 - Python
I am facing the same issue for
> pytest
I altered the django user permission so that it can create a test database while running python test cases.
ALTER USER USERNAME CREATEDB;