create user in postgresql

How to Create User in PostgreSQL

Many times you may need to create database users in PostgreSQL. Here’s how to create user in PostgreSQL


How to Create User in PostgreSQL

Here are the steps to create user in PostgreSQL.


1. Log into PostgreSQL

Open terminal and run the following command as root user

$ su - postgres

Bonus Read : How to Create PostgreSQL Index


2. Create User in PostgreSQL

You can create user in interactive mode, or normal mode. We will look at both these methods.

Interactive mode

You will be logged into PostgreSQL as superuser. Run the following command

createuser --interactive --pwprompt


Postgres will next ask you to enter new user details one by one, as shown below

  • Enter name of role to add – enter new user name
  • Enter password for new role – enter password for new user
  • Enter it again – enter password again
  • Shall the new role be a superuser- Enter Y if you want to create user with superuser privileges. Else enter N
  • Shall the new role be allowed to create databases- Enter Y if you want new user to be able to create databases, else enter N.
  • Shall the new role be allowed to create new roles- Enter Y if you want new user to be able to create new users, else enter N.


PostgreSQL will create your user.

Bonus Read : How to Get Row Number in PostgreSQL


Normal mode or non-interactive mode

In this mode PostgreSQL will directly create new user without prompting you for any information.

If you want to create user in a non-interactive manner, just run the following command after Step 1 above.

postgres=# create user user_name with encrypted password 'mypassword';

Replace user_name and mypassword with new user’s username and password respectively.

If you want to grant access to new user to your database sample_db, run the following command

postgres=# grant all privileges on database sample_db to user_name;

Bonus Read : PostgreSQL DROP View


3. List all Users in PostgreSQL

Since you are logged in as superuser, run the \du or \du+ command to list all users in PostgreSQL.





Hopefully, now you can easily create user in PostgreSQL.

Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!