PostgreSQL provides a CREATE USER command that assists us in creating the users. Using CREATE USER command, we can create an ordinary or a superuser. While creating a user, we can specify the privileges to that particular user, such as login, CREATEDB, CREATEROLE, etc. In PostgreSQL, you can also create a user via pgAdmin.
Through practical examples, this blog post will show you how to create a user in Postgres.
How to Use CREATE USER Command in PostgreSQL?
To create a user in Postgres, specify the “CREATE USER” command followed by the user name, and after that, assign the privileges to the user using the “WITH” clause:
CREATE USER user_name WITH option;
In place of the option, you can assign any privileges of your choice, such as SUPERUSER, PASSWORD, VALID UNTIL, LOG-IN, CONNECTION LIMIT, CREATEDB, CREATEROLE, etc.
Note: You can specify multiple options for a single user using the space-separated syntax.
Example 1: How to Create a User in Postgres?
Let’s learn how to create an ordinary user with the “CREATEDB” and “CREATE ROLE” privileges:
CREATE USER tsep WITH CREATEDB CREATEROLE;
In the above snippet:
- We created a user named “tsep” using the CREATE USER statement.
- Next, we utilized the WITH clause to assign multiple options to the user named “tsep”.
- The user named tsep will have CREATEDB and CREATEROLE privileges.
In the above snippet, the “CREATE ROLE” message appears as a output when we execute the CREATE USER statement. It proves that the specified user has been created. To verify the user creation, you can execute the “\du” command as follows:
\du;
The output authenticates that the user named “tsep” has been created with the specified attributes.
Example 2: How to Create a Superuser in Postgres?
To create a superuser in Postgres, execute the CREATE USER statement and assign it the SUPERUSER attribute with the assistance of the WITH clause:
CREATE USER TSEP_LTD WITH SUPERUSER;
The output snippet authenticates that the user named TSEP_LTD has been created. Let’s verify the user’s creation using the “\du” command:
\du;
The above snippet verifies that a superuser named “tsep_ltd” has been created successfully.
How to Create a User/Role in Postgres Using pgAdmin?
To create a user via pgAdmin, firstly, launch the pgAdmin, expand the “Servers” tree and follow the below-listed steps:
Step 1: Select Login/Group Roles
In pgAdmin, search for the “Login/Group Roles” under the “Servers” tree. Once you find the “Login/Group roles” option, right-click on it, then hover over the “Create” option and click on the “Login/Group Roles”:
Clicking on the “Login/Group roles” option will pop up a new window.
Step 2: Specify User’s Name
In the “Create-Login/Group roles” window, specify the name of the role:
Step 3: Select Definition Tab
Now switch to the definition tab, and provide the password and its validity date:
In the above snippet, we specified a password that will expire on “2022-12-31 11:59:00 +05:00”.
Step 4: Assign Privileges
Select the "Privileges" tab and select the attributes that you want to assign to the role:
The above snippet indicates that the specified role is a superuser.
Step 5: Check SQL Query
To see the respective SQL query for the specified role, select the “SQL” tab:
Click on the Save button to create a user named “example_role” with superuser privileges.
Step 5: Verify User’s Creation
Expand the “Login/Group roles” and search for the newly created user:
The output snippet authenticates that the user named “example_role” has been created successfully.
Conclusion
In PostgreSQL, a user can be created using the CREATE USER statement. To do so, specify the “CREATE USER” command followed by the user name, and after that, assign the privileges to the user using the “WITH” clause. Postgres allows us to create a user via pgAdmin. To do that, firstly right-click on the “Login/ roles” option, then hover over the “Create” option and click on the “Login/Group Roles”. After that, specify the login details and privileges. Finally, hit the save button to create a user. This blog post explained how to create a user via pgAdmin and SQL SHELL.
