PostgreSQL, commonly known as Postgres, is an open-source relational database management system (RDBMS) that offers robust features, extensibility, and industry-standard compliance.
User management is a critical set of Postgres capabilities admins use to create new users and assign or revoke permissions for existing users. This is crucial for the overall smooth operations and security of the database and the larger application infrastructure.
In this comprehensive tutorial, we’ll discuss several ways of create users in PostgreSQL. We’ll also go into the details of setting passwords and listing users.
Let’s start with the prerequisites and then go into the details of creating users in PostgreSQL.
Table Of Content
- Prerequisites to Creating Users in PostgreSQL
- Create a New User in PostgreSQL
- Create a Superuser in PostgreSQL
- User Management in PostgreSQL
- Conclusion
- FAQs
Prerequisites to Creating Users in PostgreSQL
Before creating a user in PostgreSQL, make sure you have:
- A working installation of PostgreSQL.
- Command-line or terminal access to the server.
- Working knowledge of SQL.
Create a New User in PostgreSQL
You need access to the postgres user for creating a new user in PostgreSQL. Once you have verified access to this user, let’s see the two methods of creating a new user in PostgreSQL.
Method #1: Use the createuser Utility
PostgreSQL offers createuser, a dedicated utility for creating new users. The only catch is that you need to be logged in as a superuser to use this utility. You also don’t need to connect to the PSQL command line for this method.
Launch the terminal and run the following command to switch to the postgres user:
# sudo su - postgres
Next, execute the createuser command:
# createuser -e <name>
You can combine these two commands into a single command as follows:
# sudo -u postgres createuser -e <name>
For instance, the following command uses the createuser utility to create a new user named steve:
# sudo -u postgres createuser -e steve
Use the createuser Interactive Mode
In addition to these commands, you can also use the createuser interactive mode that simplifies the process of creating users in PostgreSQL.
Enter the following command to engage the interactive mode:
# sudo -u postgres createuser --interactive
During the session, you answer the following list of questions:
- Name of the role.
- Whether the role should possess superuser privileges.
- Permission to create databases.
- Authorization to create additional roles.
Method #2: Use the PostgreSQL Shell (PSQL)
PSQL offers an alternative method of creating a new user in PostgreSQL.
Launch the terminal and enter the following command to switch to postgres user and initiate the PSQL shell:
# sudo -u postgres psql
You can see that the prompt changes to postgres=# to indicate that we are now in the PSQL instead of the terminal.
Now, create a new user with the following command:
postgres=# CREATE USER <name>;
Essentially, this command is a shorthand of the longer CREATE ROLE <name> WITH LOGIN; command.
Let’s use the command to create a new user with the name mark:
postgres=# CREATE USER mark;
Create a Superuser in PostgreSQL
Now that you know how to create a user in PostgreSQL, the next step is to create a superuser role.
A superuser role has the most privileges in the PostgreSQL environment. Superuser roles have complete access to all data and users, and they can bypass most of the user-permission checks during command execution.
Note that only a superuser can grant superuser privileges to other users.
Let’s discuss the two ways of creating a superuser role in PostgreSQL:
Method #1: Use the createuser Utility
Launch the terminal and use the following command to switch to the postgres user and use the createuser utility with the –superuser flag to create a superuser.
# sudo -u postgres createuser --superuser <name>
Alternatively, use the shorthand tag -s instead of –superuser:
# sudo -u postgres createuser -s <name>
You will get an error message if an account already exists with the username you mentioned in the command. In case of success, you won’t see any status message.
Method #2: Use PSQL
Launch PSQL and enter the following statement:
postgre=# CREATE ROLE <name> LOGIN SUPERUSER;
You will see CREATE ROLE as the output of the successful execution.
Instead of this lengthy statement, you can also use the following alias:
postgres=# CREATE USER <name> SUPERUSER;
Did you know that installing PostgreSQL on Ubuntu is a simple process. Read our comprehensive tutorial and get your RDBMS up and running in minutes!
User Management in PostgreSQL
Now that you have a clear idea of creating users in PostgreSQL, let’s see how you can manage these users.
User management involves changing or updating user passwords and assigning and revoking privileges.
Create a Password for the User
You can use either of the following methods for creating a password for a new user.
Method #1: Use the createuser Utility
When creating a new user with the createuser utility, use the — pwprompt flag. You will see a prompt for entering a password for the user:
# sudo -u postgres createuser <name> --pwprompt
You can also use the -P flag as a shorthand for the –pwprompt flag. For instance, we used the command sudo -u postgres createuser steve -P to create a new user named steve and asked the createuser utility to prompt for the user’s password.
During the command execution, Postgres will encrypt and store the user’s password.
Method #2: Use PSQL
Important: We don’t recommend this method because the user password is visible on the screen and is transmitted in cleartext.
You can also use the CREATE USER statement to create a user and set their password. The statement’s syntax is:
postgres=# CREATE USER <name> WITH PASSWORD '<password>';
If the user already exists, you can add a password with the ALTER USER:
postgres=# ALTER USER <name> WITH PASSWORD '<password>';
Setting robust passwords is just one of the several recommended practices for secure PostgreSQL operations.
Grant User Privileges
Postgres only allows login privileges for the new user accounts. By default, newly created users in PostgreSQL have only login privileges.
You can assign specific privileges to new users when you create their accounts by mentioning them in the command. This applies to both the createuser utility and the PSQL.
The syntax for the createuser utility is
# createuser <option> <name>
While in PSQL:
postgres=# CREATE USER <name> WITH <option>;
For instance, use the following command in the terminal if you wish to create a user and set a connection limit of 10 for the account (by default, PostgreSQL does not place any limits on the number of connections a user can create)
# sudo -u postgres createuser -c 10 -e <username>
Alternatively, if you’re using PSQL, run the following statement:
postgres=# CREATE USER <username> WITH CONNECTION LIMIT 10;
List All Users in PostgreSQL
Database admins often need to list down the users and their assigned roles and privileges. This is a common activity in database security checks to review user privileges.
Follow these steps to list all users in your PostgreSQL installation.
Start by launching PSQL as the postgres user.
# sudo -u postgres psql
Next, in PSQL, enter the following command to list all users:
postgres=# \du
The output should look something similar to the following, where you can see the names, a list of attributes (that defines the privileges), and the groups the user belongs to:
You can get further details with the \du+ command:
postgres=# \du+
You can see an additional column that shows a description of the roles.
Here’s a table that outlines some common user roles in PostgreSQL:
Also Read: How to Delete a Postgres User (Drop User) [5 Methods Inside!]
Conclusion
Managing users is an essential aspect of PostgreSQL database administration. This guide provides multiple methods to create users, ensuring flexibility and control over your user management process.
For top-notch PostgreSQL hosting, choose bare metal server hosting by RedSwitches – your reliable and high-performance solution. Contact us for tailored solutions for dedicated server hosting, instant dedicated server, and 10 Gbps dedicated server.
FAQs
Q. What is a superuser in PostgreSQL?
A superuser in PostgreSQL has all the privileges and can perform any operation in the database.
Q. Is it essential to set a password for PostgreSQL users?
Yes, for security reasons, it’s highly recommended to set a strong password for your PostgreSQL users.
Q. How can I remove a user in PostgreSQL?
You can eliminate a user by executing the command DROP USER username; in the PSQLshell. It’s essential to prioritize secure user management to safeguard the security and integrity of your data.
Q. How do I create a new user in PostgreSQL?
To create a new user in PostgreSQL, you can use the CREATE USER command at the command line or an SQL client such as pgAdmin. Here is an example of the command:
CREATE USER username WITH PASSWORD 'password';
Q. How can I create a new database for a user in PostgreSQL?
To generate a new database for a user in PostgreSQL, use the following command:
CREATE DATABASE database_name OWNER username;
Q. How do I grant privileges to a user in PostgreSQL?
To grant privileges to a user in PostgreSQL, use the GRANT command. Here is an example:
GRANT privilege_name ON database_name TO username;
Q. How do I change the password for a user in PostgreSQL?
To modify a user’s password in PostgreSQL, use the ALTER USER command. Here’s an example:
ALTER USER username WITH PASSWORD 'new_password';
Q. What is pgAdmin, and how can I use it to create and manage users in PostgreSQL?
pgAdmin is an open-source tool for managing PostgreSQL databases. It has a user-friendly interface for creating databases, managing users, and other tasks. To begin using this tool, download and install pgAdmin from the official website. Then, connect it to your PostgreSQL server to manage users and databases efficiently.
Q. How can I create a user in PostgreSQL using pgAdmin?
To create a user in PostgreSQL using pgAdmin, follow these steps:
- Open pgAdmin and connect to your PostgreSQL server.
- Expand the Server Group and select the server you want to work with.
- Right-click Login/Group Roles and select New Login/Group Role.
- In the General tab, enter the preferred username and password.
- Specify the user’s privileges and settings in the other tabs if needed.
- Click OK to create the user.
Q. What are the default privileges assigned to a new user in PostgreSQL?
By default, when a new user is created in PostgreSQL, they do not receive any privileges on databases or objects. To provide the user with the necessary permissions, you must explicitly use the GRANT command.
Q. How to list users in PostgreSQL?
To see the list of users in PostgreSQL, you can run the following SQL command in the command line or a SQL client:
SELECT username FROM pg_user;
Q. What is the difference between a user and a role in PostgreSQL?
In PostgreSQL, a user and a role are functionally identical. They both represent unique individuals or entities with their own permissions. While newer versions of PostgreSQL prefer the term “role,” the term “user” is still widely used and supported for backward compatibility.
