PostgreSQL: Create and List User

PostgreSQL is one of the popular open source relational SQL databases. If you don't have a PostgreSQL databases, you can configure one on your Windows Subsystem for Linux easily: Install PostgreSQL on WSL. The steps to install it on UNIX-alike systems are similar.

Create user

The following code snippets shows you how to create a database user.

Create user without password:

CREATE USER kontext;

Drop user:

drop user kontext;

Create user with password:

CREATE USER kontext WITH PASSWORD 'kontext';

Create user with password and also expiry date:

CREATE USER kontext WITH PASSWORD 'kontext' VALID UNTIL '2021-12-01';

Create user with password and roles:

CREATE USER kontext WITH PASSWORD 'kontext' VALID UNTIL '2021-12-01' CREATEDB CREATEROLE;

The above statement creates a user with two default roles: create database and create role.

Show or list users

\du

Example output:

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 kontext   | Create role, Create DB                                    +| {}
           | Password valid until 2021-12-01 00:00:00+11                |
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}