PostgreSQL User Permissions

By

Learn how PostgreSQL manages users and permissions through roles, from role attributes like Superuser and Create DB to group roles and inheritance.

~~~

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. A role with the LOGIN attribute behaves like a user account. A role without it can still own objects and hold permissions, which makes non-login roles useful as groups and as object owners that are not credentials.

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:

PostgreSQL \du command output showing flaviocopes role with Superuser, Create role, Create DB, Replication, Bypass RLS attributes

See? I have the following roles attributes by default:

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

That is a fully privileged role, fine for your own laptop. Application roles should get much less, as we’ll see.

Creating a new role

A new role is created using the CREATE ROLE command:

CREATE ROLE <role>;

For example:

CREATE ROLE testing;

Terminal showing CREATE ROLE testing command and \du output displaying testing role with Cannot login attribute

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:

Terminal error message: psql error could not connect to server FATAL role testing is not permitted to log in

The FATAL: role "testing" is not permitted to log in message is worth remembering: it means the server is running and reachable, and the only problem is the missing LOGIN attribute.

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:

Terminal showing DROP ROLE and CREATE ROLE testing WITH LOGIN commands, \du output shows testing role without Cannot login

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

Terminal showing successful login as testing user with postgres=> prompt indicating non-superuser status

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

One caveat: the cleartext password ends up in your terminal history and possibly in server logs. For real credentials, prefer setting it interactively with the \password <role> command in psql, which prompts for it instead.

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

CREATE USER and CREATE ROLE ... WITH LOGIN are exactly equivalent. There is no separate user object.

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?

Keep SUPERUSER for administration only. An application should connect with a role that has none of these attributes.

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.

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;

What membership passes along, and what it doesn’t

Members inherit the group’s object privileges — grants like SELECT on a table or USAGE on a schema — as long as the member role has the INHERIT attribute, which is the default. Grant table access to employee once, and every member gets it.

Role attributes are different. Attributes like CREATEDB, CREATEROLE, and LOGIN are never inherited through membership. If you give the employee group the CREATEDB attribute:

CREATE ROLE employee WITH CREATEDB;
CREATE ROLE flavio;
GRANT employee TO flavio;

flavio still cannot create a database directly. To use the group’s attributes, a member has to switch into the group role explicitly:

SET ROLE employee;
CREATE DATABASE reports;
RESET ROLE;

You can check memberships at any time with \du, which lists each role and the groups it belongs to:

Terminal showing employee role creation and grant commands, \du output displays flavio role as member of employee group

This split is a feature. Object privileges flow to members so day-to-day queries just work, while powerful attributes stay behind a deliberate SET ROLE, so nobody creates databases or roles by accident.

Tagged: Database · All topics
~~~

Related posts about database: