How To Use PostgreSQL with your Django Application on Ubuntu 20.04

Introduction

Django is a flexible framework for quickly creating Python applications. By default, Django applications are configured to store data into a lightweight SQLite database file. While this works well under some loads, a more traditional database management system can improve performance in production.

In this guide, you will install and configure PostgreSQL (often referred to as Postgres) to use with your Django applications. You will install the necessary software, create database credentials for our application, and then start and configure a new Django project to use this backend.

Prerequisites

• You will need a clean Ubuntu 20.04 server instance with a non-root user configured with sudo privileges. Learn how to set this up by following our initial server setup guide.

When you are ready to continue, log in as your sudo user.

Step 1 – Installing the Components from the Ubuntu Repositories

First you will install the essential components. This includes pip, the Python package manager for installing and managing Python components, and also the database software with its associated libraries.

You will be using Python 3, which ships with Ubuntu 20.04. Start the installation by typing:

sudo apt update
sudo apt install python3-pip python3-dev libpq-dev postgresql postgresql-contrib

With the installation out of the way, you can move on to the database.

Step 2 – Creating a Database and Database User

By default, Postgres uses an authentication scheme called “peer authentication” for local connections. Basically, this means that if the user’s operating system username matches a valid Postgres username, that user can login with no further authentication.

During the Postgres installation, an operating system user named postgres was created to correspond to the postgres PostgreSQL administrative user. You need to use this user to perform administrative tasks. You can use sudo and pass in the username with the -u option.

Log into an interactive Postgres session by typing:

sudo -u postgres psql

First, you will create a database for the Django project. Each project should have its own isolated database for security reasons. We will call the database myproject in this guide, but it’s always better to select something more descriptive:

CREATE DATABASE myproject;

Next, you will create a database user which you will use to connect to and interact with the database. Set the password to something strong and secure:

CREATE USER myprojectuser WITH PASSWORD 'password';

Afterwards, you will modify a few of the connection parameters for the user you just created. This will speed up database operations so that the correct values do not have to be queried and set each time a connection is established.

ALTER ROLE myprojectuser SET client_encoding TO 'utf8';
ALTER ROLE myprojectuser SET default_transaction_isolation TO 'read committed';
ALTER ROLE myprojectuser SET timezone TO 'UTC';

You are setting the default encoding to UTF-8, which Django expects. You are also setting the default transaction isolation scheme to “read committed”, which blocks reads from uncommitted transactions. Lastly, you are setting the timezone. By default, your Django projects will be set to use UTC. These are all recommendations from the Django project itself.

Now, all you need to do is give your database user access rights to the database you created:

GRANT ALL PRIVILEGES ON DATABASE myproject TO myprojectuser;

Exit the SQL prompt to get back to the postgres user’s shell session:

\q

Install Django within a Virtual Environment

Now that your database is set up, you can install Django. For better flexibility, you will install Django and all of its dependencies within a Python virtual environment. The virtualenv package allows you to create these environments easily.

To install virtualenv, type:

sudo pip3 install virtualenv

Make and move into a directory to hold your Django project:

mkdir ~/myproject
cd ~/myproject

You can create a virtual environment to store your Django project’s Python requirements by typing:

python3 -m virtualenv myprojectenv

This will install a local copy of Python and a local pip command into a directory called myprojectenv within your project directory.

Before you install applications within the virtual environment, you need to activate it. You can do so by typing:

source myprojectenv/bin/activate

Your prompt will change to indicate that you are now operating within the virtual environment. It will look something like this (myprojectenv)user@host:~/myproject$.

Once your virtual environment is active, you can install the official release of Django with pip. You will also install the psycopg2 package that will allow us to use the Postgres database you configured:

pip install Django psycopg2

You can now start a Django project within the myproject directory. This will create a child directory of the same name to hold the code itself, and will create a management script within the current directory. Make sure to add the dot at the end of the command so that this is set up correctly:

django-admin startproject myproject .

Configure the Django Database Settings

Now that you have a project, you need to configure it to use the database you created.

Open the main Django project settings file located within the child project directory:

nano ~/myproject/myproject/settings.py

Towards the bottom of the file, you will see a DATABASES section that looks like this:

. . .

DATABASES = {
    'default': {
            'ENGINE': 'django.db.backends.sqlite3',
            'NAME': os.path.join(BASE_DIR, 'db.sqlite3'),
    }
}

. . .

This is currently configured to use SQLite as a database. You need to change this so that your PostgreSQL database is used instead.

First, change the engine so that it uses the postgresql adaptor instead of the sqlite3 adaptor. For the NAME, use the name of your database (myproject in this example). You also need to add login credentials. You need the username, password, and host to connect to. You will add and leave blank the port option so that the default is selected:

. . .

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql',
        'NAME': 'myproject',
        'USER': 'myprojectuser',
        'PASSWORD': 'password',
        'HOST': 'localhost',
        'PORT': '',
    }
}

. . .

While you are here, you will also need to adjust the ALLOWED_HOSTS directive. This defines a whitelist of addresses or domain names allowed to connect to the Django instance. Any incoming requests with a Host header that is not in this list will raise an exception. Django requires that you set this to prevent a certain class of security vulnerability.

In the snippet below, there are a few commented out examples used to demonstrate:

. . .
# The simplest case: just add the domain name(s) and IP addresses of your Django server
# ALLOWED_HOSTS = [ 'example.com', '203.0.113.5']
# To respond to 'example.com' and any subdomains, start the domain with a dot
# ALLOWED_HOSTS = ['.example.com', '203.0.113.5']
ALLOWED_HOSTS = ['your_server_domain_or_IP']

In the square brackets, list the IP addresses or domain names that are associated with your Django server. Each item should be listed in quotations with entries separated by a comma. If you wish requests for an entire domain and any subdomains, prepend a period to the beginning of the entry.

When you are finished, save and close the file.

Migrate the Database and Test your Project

Now that the Django settings are configured, you can migrate your data structures to your database and test out the server.

You can begin by creating and applying migrations to your database. Since you don’t have any actual data yet, this will simply set up the initial database structure:

cd ~/myproject
python manage.py makemigrations
python manage.py migrate

After creating the database structure, you can create an administrative account by typing:

python manage.py createsuperuser

You will be asked to select a username, provide an email address, and choose and confirm a password for the account.

If you followed the initial server setup guide, you should have a UFW firewall in place. Before you can access the Django development server to test your database, you need to open the port in your firewall.

Allow external connections to the port by typing:

sudo ufw allow 8000

Once you have the port open, you can test that your database is performing correctly by starting up the Django development server:

python manage.py runserver 0.0.0.0:8000

In your web browser, visit your server’s domain name or IP address followed by :8000 to reach default Django root page:

http://server_domain_or_IP:8000

You should see the default index page:

Append /admin to the end of the URL and you should be able to access the login screen to the admin interface:

Enter the username and password you just created with the createsuperuser command. You will then be taken to the admin interface:

When you’re done investigating, you can stop the development server by hitting CTRL-C in your terminal window.

By accessing the admin interface, you have confirmed that you database has stored your user account information and that it can be appropriately accessed.

Conclusion

In this guide, you have demonstrated how to install and configure PostgreSQL as the backend database for a Django project. While SQLite can easily handle the load during development and light production use, most projects benefit from implementing a more full-featured database management system.

To take your project even further, see our guide on How To Set Up Django with Postgres, Nginx, and Gunicorn on Ubuntu 20.04.

because the migrate command will fail as the user will not have access to the public schema. 

As per my experience, following steps should be done as well

-- Switch to the new database
\c {dbname}

-- Grant usage and create privileges on the public schema to the new user
GRANT USAGE ON SCHEMA public TO {username};
GRANT CREATE ON SCHEMA public TO {username};

-- Grant all privileges on all tables in the public schema to the new user
GRANT ALL PRIVILEGES ON DATABASE {dbname} TO {username};

-- Grant default privileges to ensure future tables are accessible
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO {username};