Creating a new database in postgres

The very simple steps of creating a new database in postgres.

Make sure that postgres is using scram-sha-256 for the passwords.

This is configuration option in /var/lib/pgsql/13/data/postgresql.conf.

password_encryption = scram-sha-256

Now we can login to postgres using the psql user.

> su -c "psql" - postgres

Next, we need to a create a new user in postgres.

postgres=# CREATE ROLE username WITH LOGIN PASSWORD 'username';

Now that we have a user, we can create a database that will be owned by this user.

postgres=# CREATE DATABASE somedb WITH OWNER username TEMPLATE template0 ENCODING UTF8 LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8';

We then need to update /var/lib/pgsql/13/data/pg_hba.conf to allow our new user access to postgres.

You can also find the file by doing the following:

postgres=# SHOW hba_file;
              hba_file
              ------------------------------------
               /var/lib/pgsql/13/data/pg_hba.conf

We can then add the below line to the pg_hba file.

local    somedb    username    scram-sha-256

Next we restart postgres.

> sudo service postgresql-13 restart

We can then login to the database with our new user.

>  /usr/pgsql-13/bin/psql -U username -d somedb

This should ask for your password, and voila! we have a new database ready to use.