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 PostgreSQLSUPERUSER
/NOSUPERUSER
: allow (or not) superuser permissions. A database superuser will bypass other permission checks, except forLOGIN
(it must be granted separately).CREATEDB
/NOCREATEDB
: allow (or not) the ability to create new databasesCREATEROLE
/NOCREATEROLE
: allow (or not) the ability to create new rolesCREATEUSER
/NOCREATEUSER
: allow (or not) the ability to create new usersINHERIT
/NOINHERIT
: allow (or not) the ability to make the privileges inheritableREPLICATION
/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;
→ I wrote 17 books to help you become a better developer, download them all at $0 cost by joining my newsletter
→ JOIN MY CODING BOOTCAMP, an amazing cohort course that will be a huge step up in your coding career - covering React, Next.js - next edition February 2025