Create a Database and a User

This guide explains how to set up a new database and a dedicated user with the correct permissions in PostgreSQL.

NOTE

This guide assumes that the local connection for the postgres user is set to peer in the pg_hba.conf file. By default, the postgres role uses peer authentication for local connections. It is safest to keep it this way for the main postgres user and require passwords for others. Changing it to trust is dangerous, as it allows anyone logged into the server to access the database as a superuser without a password.

Steps

First, connect to your PostgreSQL database server via SSH. Open the command line tool as the postgres user using the following command.

sudo -u postgres psql -d postgres

Create the User

Once you are in the shell, run this command to create a new user. Replace your_username and your_password with your chosen credentials.

CREATE USER your_username ENCRYPTED PASSWORD 'your_password';

Create the Database

Next, create the database and set the new user as the owner. This ensures the user has all the necessary permissions for that database. Replace your_database_name with your preferred name.

CREATE DATABASE your_database_name WITH OWNER=your_username;

Confirm Changes

Exit the PostgreSQL shell.

exit

Test the new credentials by logging into the database terminal:

psql -U your_username -d your_database_name

Enter your password when prompted. If you log in successfully, the setup is complete.