How to create new user in PostgreSQL

gcptutorials.com PostgreSQL

CREATE USER is an alias for CREATE ROLE. The only difference is that when the CREATE USER command is used , LOGIN is assumed by default, whereas NOLOGIN is assumed when the CREATE ROLE command is used. Below are the steps for creating new user/role in postgres with password.

Switch to postrges user

sudo su - postgres

Switch to psql — PostgreSQL interactive terminal

psql

Create new user/role with password

CREATE USER test_user WITH PASSWORD 'test_pwd';

Create new database and list databases

CREATE DATABASE test_db;

Connect postgres shell with below command and provide password

psql -U 'username' 'database name'

psql -U test_user test_db

if you get error "psql: error: could not connect to server: FATAL: Peer authentication failed for user "test_user" than follow below steps.
1. navigate to "pg_hba.conf".
2. edit pg_hba.conf with entires show in updated entries below(path may be different according to pgsql version)

sudo vi /var/lib/pgsql/12/data/pg_hba.conf

Initial entry(this was the default setting, change this to as shown in update entry):

local all all peer

Updated entry:

local all postgres peer
local all test_user md5

3. Restart the postgresql server.

sudo systemctl start postgresql-12

Category: PostgreSQL