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 trust in the pg_hba.conf file.

Steps

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

psql -U 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.