Published Dec 30 2019
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.
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
See? I have the following roles attributes by default:
and I’m not a member of any other role (more on this later)
A new role is created using the
CREATE ROLE command:
CREATE ROLE <role>;
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>;
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
=> because we don’t have the
Superuser role attribute now.
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
CREATE ROLE <role> WITH LOGIN PASSWORD '<password>';
An alternative way to define roles with the
LOGIN attribute automatically added (effectively creating users that can login) is to use
CREATE USER <role> PASSWORD '<password>';
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;
We saw the
LOGIN role attribute already, to allow a role to login.
But what are other built-in role attributes we can use?
NOLOGIN: allow (or not) to login to PostgreSQL
NOSUPERUSER: allow (or not) superuser permissions. A database superuser will bypass other permission checks, except for
LOGIN(it must be granted separately).
NOCREATEDB: allow (or not) the ability to create new databases
NOCREATEROLE: allow (or not) the ability to create new roles
NOCREATEUSER: allow (or not) the ability to create new users
NOINHERIT: allow (or not) the ability to make the privileges inheritable
NOREPLICATION: grant (or not) replication permissions (an advanced topic we’ll not cover)
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.
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 <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>
REVOKE employee FROM flavio;
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
Suppose you create the employee group role, and assign it the
CREATE ROLE employee WITH CREATEDB INHERIT;
Now create a new role using
CREATE ROLE flavio; GRANT employee TO flavio;