Creating a user in PostgreSQL

In order to connect to a PostgreSQL database, we need to have a username. A PostgreSQL user is a role that has the CONNECT privilege. Both CREATE USER and CREATE ROLE work well to create a PostgreSQL user. The only difference between the two is that the LOGIN role is not assigned when we use CREATE ROLE to create a user. In this recipe, we shall see how a user can be created in PostgreSQL.

Getting ready

It requires a superuser or a CREATEROLE privilege for the database user to create a user or a role. So, we must use a user who has either of these privileges to create a user.

How to do it...

A user with a LOGIN role can be created using any of the following three methods:

1. Method 1: We can create a user using the CREATE USER command:

CREATE USER percuser WITH ENCRYPTED PASSWORD 'secret';

2. Method 2: We can create a user using the CREATE ROLE .. WITH LOGIN command:

CREATE ROLE percuser WITH LOGIN ENCRYPTED PASSWORD 'secret';

3. Method 3: We can create a user using the CREATE ROLE command and then assign the LOGIN privilege to that user:

CREATE ROLE percuser;
ALTER ROLE percuser WITH LOGIN ENCRYPTED PASSWORD 'secret';

The three aforementioned methods demonstrate the three different ways in which a user, percuser, can be created.

How it works

When you use CREATE USER with any of these commands, PostgreSQL automatically translates them internally with the following:

CREATE ROLE percuser
 WITH NOSUPERUSER INHERIT NOCREATEROLE NOCREATEDB LOGIN NOREPLICATION NOBYPASSRLS
 ENCRYPTED PASSWORD 'secret';

In order to validate whether the user can log in (has the CONNECT privilege) or not, we may use the following query and substitute the username appropriately:

postgres=# select rolcanlogin from pg_roles where rolname = 'percuser';
 rolcanlogin
 -------------
 t
 (1 row)

There's more...

In order to list the users and roles created in a PostgreSQL server, we could either query the view (pg_roles) or use the shortcut "\du":

We can use the shortcut "\du" to get the list of users:

$ psql -c "\du"
 List of roles
 Role name | Attributes | Member of
 ------------------+------------------------------------------------------------+-----------
 app_user | | {}
 dev_user | | {}
 percuser | | {}
 postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
 read_only_scott | Cannot login | {}
 read_write_scott | Cannot login | {}

We can also use the pg_roles query to get the list of users:

 $ psql -c "SELECT rolname, rolcanlogin FROM pg_roles where rolname NOT LIKE 'pg_%'"
 rolname | rolcanlogin
 ------------------+-------------
 postgres | t
 percuser | t
 read_write_scott | f
 read_only_scott | f
 app_user | t
 dev_user | t
 (6 rows)

Thus, we have learned how to create users with various privileges in PostgreSQL.