User privilege and privilege access management is a crucial security concept for any database type. PostgreSQL handles access control through roles and privileges. For example, adding the LOGIN permission to a role yields a user. Postgres offers several ways to create and manage user accounts.
This tutorial shows how to create and set up different user types for a PostgreSQL database.
- Postgres installed (follow our guide for Ubuntu or Windows).
- Access to the terminal with sudo privileges.
- Access to the postgres user.
Create a New User in PostgreSQL
There are two ways to make a new user in PostgreSQL, and both cases require access to the postgres user.
Note: The postgres user is the PostgreSQL superadmin created during the installation process.
Method 1: Using The createuser Client Utility
The first way to create a new user is with the
createuser client utility. This method avoids connecting to the PSQL command-line interface.
To create the user, run the following command in the terminal:
sudo -u postgres createuser <name>
The terminal does not output a message. To echo the server message, add the
sudo -u postgres createuser -e <name>
sudo -u postgres createuser -e john
Alternatively, split the command into two parts:
1. Switch to the postgres user:
sudo su - postgres
2. Run the
Postgres automatically creates the user (role with login permissions) in both cases.
Method 2: Using PSQL
The second way to create a new user in PostgreSQL is through the interactive PSQL shell.
1. Switch to the postgres user and start the interactive terminal with:
sudo -u postgres psql
The terminal session changes to
postgres=#, indicating a successful connection to the Postgres shell.
2. Use the following statement to create a user:
CREATE USER <name>;
CREATE USER mary;
Running the command prints
CREATE ROLE to the console. The reason is that the
CREATE USER query is an alias for the following command:
CREATE ROLE <name> WITH LOGIN;
Both queries yield the same result.
Create a Superuser in PostgreSQL
To create a superuser in PostgreSQL, you must have the superuser role.
Warning: A database superuser bypasses all checks, which is dangerous from a security aspect. Use this action with care and avoid working with a superuser account unless absolutely necessary.
There are two ways to make a superuser in PostgreSQL:
1. Create a superuser role through the client utility by adding the
sudo -u postgres createuser --superuser <name>
Or use the shorthand tag
-s instead of
sudo -u postgres createuser -s <name>
The terminal outputs a message in case of an error or if the user already exists. If successful, no message appears.
2. Alternatively, use the
CREATE USER PSQL statement:
CREATE USER <name> SUPERUSER;
CREATE USER statement is an alias for the following statement:
CREATE ROLE <name> LOGIN SUPERUSER;
CREATE ROLE statement requires adding the
LOGIN permission to emulate a user.
Create a Password for the User
Every database user must have a strong password to prevent brute force attacks. PostgreSQL offers two methods to create a user with a password.
Warning: Out of the two methods, the first is preferred and more secure.
1. Use the
createuser client utility and add the
--pwprompt option to invoke a password creation prompt automatically:
sudo -u postgres createuser <name> --pwprompt
The shorthand version is the
sudo -u postgres createuser <name> -P
The terminal prompts to enter the password twice. The password itself or the length is encrypted and hidden when communicating with the server.
2. Use PSQL to create a user with a password:
CREATE USER <name> WITH PASSWORD '<password>';
If the user already exists, add the password by using
ALTER USER <name> WITH PASSWORD '<password>';
Password management via PSQL comes with three security vulnerabilities:
- The password is visible on the screen.
- Viewing the command history exposes the password.
- The information transmits as clear text without any encryption.
Use this method with caution.
Note: Refer to our post to learn more about what a brute force attack is and how it works.
Grant Privileges to the User
By default, new users do not have any privileges except for login. To add privileges when creating a user, run the
createuser client utility in the following format:
createuser <option> <name>
To do the same in PSQL, run:
CREATE USER <name> WITH <option>;
Below is a table with commonly used options for both methods.
|Add the superuser privilege.|
|No superuser privilege (default).|
|Allows the user to create databases.|
|Not allowed to create databases (default).|
|Allows the user to make new roles.|
|Not allowed to create roles (default).|
|Automatically inherit the privileges of roles (default).|
|Do not inherit privileges of roles.|
|Allows the user to log into a session with the role name (default).|
|Not allowed to log into a session with the role name.|
|Allows initiating streaming replication and activating/deactivating backup mode.|
|Not allowed to initiate streaming replication or backup mode (default).|
|Initiates password creation prompt or adds provided password to the user. Avoid using this option to create a passwordless user.|
|/||Specifically sets the password to null. Every password authentication fails for this user.|
|Sets the maximum number of connections for user. Default is without limit.|
For example, create a user with create role and database privileges and add the
-e tag to echo the results:
sudo -u postgres createuser -d -r -e <name>
Or use the PSQL equivalent:
CREATE USER <name> WITH CREATEROLE CREATEDB;
In both cases, the stated privileges are granted automatically to the new user.
Create a PostgreSQL User Interactively
The interactive user creation is a practical option available only for the client utility. To create a user interactively, run the following command:
sudo -u postgres createuser --interactive
The command automatically prompts a question series, asking the following:
- Name of the role.
- Whether the role is a superuser.
- If the user is allowed to create databases.
- If the user is allowed to create other roles.
Answer yes (
y) to the superuser question automatically to add the "create database" and "create role" privileges and end the prompt.
List All Users in PostgreSQL
An essential tool for user management in databases is listing all the users with their respective roles and privileges.
To list all users in PostgreSQL, do the following:
1. Connect to the PSQL prompt as the postgres user:
sudo -u postgres psql
2. List all the users with the following command:
The output shows a table with the role names, attributes (privileges), and the user's groups. To display the description for each role, add the plus (
The output shows an additional column with the role description where applicable.
After going through the examples in this guide, you should know how user and role management works in PostgreSQL.
For further reading, learn about how Database-as-a-Service can help improve database management productivity.