How to Create Database, User, and Access to PostgreSQL

In this article, I will explain how to access to our PostgreSQL server using psql via terminal. We should have psql command installed and defined as binary so that we can run these commands everywhere.

Once we install Postgres on our local environment, we should have a user postgres with all privileges. The first thing is to connect to our Postgres instance with all privileges. We should run the below command.

$ sudo -u postgres psql

After accessing the instance, we should first create the database:

CREATE DATABASE <database_name>;

Once the database is created, we can create our user with the desired password:

CREATE USER <user_name> WITH ENCRYPTED PASSWORD '<password>';

In the end, we should give all the privileges to our user on the database we created:

GRANT ALL PRIVILEGES ON DATABASE <database_name> TO <user_name>;

Since we created a new database and user with all privileges, we can now exit the current session and connect with those.

psql -d <database_name> -U <user_name>

We are going to be prompted to password. Once we enter our password <password>, we will be able to access the new database with the new user.

I hope it helps, thanks for reading :)

You may reach out via Linkedin and Github, all comments are appreciated 🕺