PostgreSQL User Permissions

In this tutorial I will explain how to managing users and permissions in PostgreSQL.

In PostgreSQL, all is built around the concept of role.

When first installing PostgreSQL on macOS, the script created a role with your macOS username, with a list of permissions granted.

There are no users in PostgreSQL, just roles.

By running psql postgres in your terminal, you’ll automatically login with your macOS username to PostgreSQL, therefore accessing the role created.

In my case the flaviocopes role was created, and I can see it by using the \du command:

See? I have the following roles attributes by default:

• Superuser
• Create role
• Create DB
• Replication
• Bypass RLS

and I’m not a member of any other role (more on this later)

Creating a new role

A new role is created using the CREATE ROLE command:

CREATE ROLE <role>;

For example:

CREATE ROLE testing;

We got a new role, with the Cannot login role attribute. Our newly created user will not be able to login.

You can try by typing the \q command, and then psql postgres -U testing, but you’ll see this error:

To fix this problem we must add the LOGIN role attribute at creation:

CREATE ROLE <role> WITH LOGIN;

If we remove that role using:

DROP ROLE <role>;

and add WITH LOGIN this time:

DROP ROLE testing;
CREATE ROLE testing WITH LOGIN;

We can see that the testing role can login, because we don’t have the Cannot login role attribute this time:

Try by adding the command \q to quit, and then psql postgres -U testing:

Notice that the prompt changed from =# to => because we don’t have the Superuser role attribute now.

Adding a password to a role

In the previous CREATE ROLE command we created a role without password. Of course it’s very important to have (secure) passwords. You can add a password by using the PASSWORD keyword:

CREATE ROLE <role> WITH LOGIN PASSWORD '<password>';

CREATE USER

An alternative way to define roles with the LOGIN attribute automatically added (effectively creating users that can login) is to use CREATE USER:

CREATE USER <role> PASSWORD '<password>';

Adding a role attribute to a role

A role attribute can be added later on to a role using the ALTER ROLE command.

Let’s suppose we created a role without the LOGIN attribute:

CREATE ROLE <username> PASSWORD '<password>';

We can add it using:

ALTER ROLE <role> WITH LOGIN;

Built-in role attributes

We saw the LOGIN role attribute already, to allow a role to login.

But what are other built-in role attributes we can use?

• LOGIN / NOLOGIN: allow (or not) to login to PostgreSQL
• SUPERUSER / NOSUPERUSER: allow (or not) superuser permissions. A database superuser will bypass other permission checks, except for LOGIN (it must be granted separately).
• CREATEDB / NOCREATEDB: allow (or not) the ability to create new databases
• CREATEROLE / NOCREATEROLE: allow (or not) the ability to create new roles
• CREATEUSER / NOCREATEUSER: allow (or not) the ability to create new users
• INHERIT / NOINHERIT: allow (or not) the ability to make the privileges inheritable
• REPLICATION / NOREPLICATION: grant (or not) replication permissions (an advanced topic we’ll not cover)

Group roles

In PostgreSQL, there are no groups of users.

Instead you can create roles with certain permissions, and then grant those roles to other roles.

Roles will inherit the permissions of roles granted to them, if those roles have the INHERIT attribute.

Create a group role

To create a group role, type

CREATE ROLE <groupname>;

The syntax is the same as creating a role.

Once the group role is created, you can add roles to the group role using GRANT:

GRANT <groupname> TO <role>

For example, we can create a flavio user role, a “employee” group role, and assign the user to the group role:

CREATE USER flavio PASSWORD 'superSecret123$';
CREATE ROLE employee;
GRANT employee TO flavio;

You can remove a role from a group role using:

REVOKE <groupname> FROM <username>

Example:

REVOKE employee FROM flavio;

Group role attributes

By default, adding a role to a group role will not make the role inherit attributes (permissions) from the group role.

You need to create the group role with the INHERIT attribute.

Suppose you create the employee group role, and assign it the CREATEDB attribute:

CREATE ROLE employee WITH CREATEDB INHERIT;

Now create a new role using INHERIT:

CREATE ROLE flavio;
GRANT employee TO flavio;