How to Create a Postgres User

February 8, 2022

Introduction

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.

How to Create a Postgres User

Prerequisites

  • 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 -e tag:

sudo -u postgres createuser -e <name>

For example:

sudo -u postgres createuser -e john
postgres create user echo query

Alternatively, split the command into two parts:

1. Switch to the postgres user:

sudo su - postgres

2. Run the createuser command:

createuser <name>

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
sudo -u postgres psql terminal output

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>;

For example:

CREATE USER mary;
create user create role output

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 --superuser tag:

sudo -u postgres createuser --superuser <name>

Or use the shorthand tag -s instead of --superuser:

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 superuser psql

The CREATE USER statement is an alias for the following statement:

CREATE ROLE <name> LOGIN SUPERUSER;

The 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 -P tag:

sudo -u postgres createuser <name> -P
postgres createuser password prompt

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>';
create user with password psql

If the user already exists, add the password by using ALTER USER:

ALTER USER <name> WITH PASSWORD '<password>';
alter user with password psql

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.

Option SyntaxPSQLExplanation
-s
--superuser
SUPERUSERAdd the superuser privilege.
-S
--no-superuser
NOSUPERUSERNo superuser privilege (default).
-d
--createdb
CREATEDBAllows the user to create databases.
-D
--no-createdb
NOCREATEDBNot allowed to create databases (default).
-r
--createrole
CREATEROLEAllows the user to make new roles.
-R
--no-createrole
NOCREATEROLENot allowed to create roles (default).
-i
--inherit
INHERITAutomatically inherit the privileges of roles (default).
-I
--no-inherit
NOINHERITDo not inherit privileges of roles.
-l
--login
LOGINAllows the user to log into a session with the role name (default).
-L
--no-login
NOLOGINNot allowed to log into a session with the role name.
--replicationREPLICATIONAllows initiating streaming replication and activating/deactivating backup mode.
--no-replicationNOREPLICATIONNot allowed to initiate streaming replication or backup mode (default).
-P
--pwprompt
PASSWORD '<password>'Initiates password creation prompt or adds provided password to the user. Avoid using this option to create a passwordless user.
/PASSWORD NULLSpecifically sets the password to null. Every password authentication fails for this user.
-c <number>
--connection-limit=<number>
CONNECTION LIMIT <number>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>
create user grant permissions

Or use the PSQL equivalent:

CREATE USER <name> WITH CREATEROLE CREATEDB;
create user grant permissions psql

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
create user 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:

\du
du command postgres

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 (+) sign:

\du+
du command extended postgres

The output shows an additional column with the role description where applicable.

Conclusion

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.

Was this article helpful?
YesNo
Milica Dancuk
Milica Dancuk is an aspiring technical writer at phoenixNAP and a data nerd. Her background in Electrical Engineering and Computing and her teaching experience give her a unique set of skills - being able to easily explain complex technical concepts through her content.
Next you should read
How to Create a Database in PostgreSQL
May 26, 2021

This tutorial shows three easy ways to create a database in PostgreSQL. Whether you prefer using...
Read more
How to List All Databases in PostgreSQL
June 9, 2021

Follow this simple tutorial to learn three different ways to list all databases in PostgreSQL.
Read more
PostgreSQL Drop Database with Examples
January 18, 2022

This tutorial teaches you how to remove (drop) a database in PostgreSQL using two different...
Read more
PostgreSQL Data Types
July 29, 2021

As a relational database management system, PostgreSQL offers a variety of data types for users to work...
Read more
  • © 2022 Copyright phoenixNAP | Global IT Services. All Rights Reserved.