Like many other databases, create a user in PostgreSQL is pretty easy. In this short tutorial, we will be looking at 3 easy ways to create users in PostgreSQL with examples.
The first option in our tutorial “3 easy ways to create users in PostgreSQL” is to create a user using the pgAdmin Graphical User interface. Using the graphical user interface is pretty straight forward and easy.
- Connect to your server
- Extend the server to view the “Databases”, “Login/Group Roles” and “Tablespaces” options
- Right click on “Login/Group Roles” > Create > Login/Group Roles
- Enter the necessary parameters in the tabs in the new window shown.
For a working example of this, please see the animated GIF below.
The second option in our tutorial “3 easy ways to create users in PostgreSQL” is to create a user using the Linux command-line.
First, log on to your operating system and connect as Postgres user.
sudo su postgres
Once connected, run the command below and follow the instructions on the screen to create the user:
createuser --interactive --pwprompt
Output to expect:
Enter name of role to add: testuser1 Enter password for new role: Enter it again: Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) y Shall the new role be allowed to create more new roles? (y/n) y
The third option is our tutorial “3 easy ways to create users in PostgreSQL” is to create a user using the query tool. This can be done using the query tool in pgAdmin and the command-line query tool (psql)
Create user with no password:
CREATE USER testuser;
Create user with a password:
CREATE USER testuser WITH PASSWORD 'testpassword';
Create a user that expires on a given date:
CREATE USER testuser WITH PASSWORD 'testpassword' VALID UNTIL '2020-01-01';
Create a user that can create databases:
CREATE USER testuser WITH PASSWORD 'testpassword' CREATEDB;
Create a User with more options:
CREATE ROLE "QueryUser" WITH LOGIN NOSUPERUSER CREATEDB CREATEROLE INHERIT NOREPLICATION CONNECTION LIMIT -1 VALID UNTIL '2020-08-03T10:20:20-05:00' PASSWORD 'test';
For more information on how to create users in PostgreSQL please refer to the Official PostgreSQL Online Manual. Check out our other post on 20 PostgreSQL basic administrative commands you need to know.