How to create new user in 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
  • Switch to psql — PostgreSQL interactive terminal
  • Create new user/role with password
  • Create new database and list databases
  • Connect postgres shell with below command and provide password
  • 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):
  • Updated entry:
  • 3. Restart the postgresql server.

    Category: PostgreSQL