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.
#\du
or
#\du+
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!
