This article will show you how to set up user roles in PostgreSQL, and provide code examples. User roles are one of the best features of the PostgreSQL database system. This feature lets you group users and assign them permissions, making managing your database permissions and security much, much easier.
What are PostgreSQL’s User Roles?
In most database management systems, permission (such as being able to read or write to a database or table within a database) are assigned directly to user accounts in the database system. While this works, it’s not really convenient if you are dealing with a lot of users and a lot of databases.
User roles in PostgreSQL lets you assign permissions to roles rather than directly to users, and then assign those roles to users. This abstraction makes it easier to manage your databases, and improves security as it is less likely that you will accidentally grant the wrong permissions to the wrong user.
The best example of this is being able to update the permission for a role, with those permissions then being applied to all users in that role, rather than having to update (potentially tens or hundreds) of user accounts individually.
Users can have many roles, and the permissions from each will be applicable, as well as any permissions granted to users individually. As PostgreSQL’s permissions are restricted by default, and you are only granting permissions to allow something, there are no conflicts.
How to Create User Roles in PostgreSQL
The CREATE_ROLE statement is used to create new user roles in PostgreSQL:
CREATE ROLE my_role WITH LOGIN;
Note the use of WITH LOGIN – this grants the LOGIN permission to the role from the outset, otherwise, users with that role would not be able to log in unless they had been granted that permission individually.
Granting Additional Permissions to a Role
The GRANT statement is use to grant permissions to a role once it has been created:
GRANT CONNECT, SELECT ON DATABASE my_database TO my_role;
Above, the role my_role is granted permission to CONNECT to the database my_database and SELECT data from it.
A list of available PostgreSQL database permissions can be found here.
Assigning Roles to Users
The GRANT command is also used to assign roles to users:
GRANT my_role TO my_user;
Removing Roles from Users
The REVOKE statement is used to remove a user from a role:
REVOKE my_role FROM my_user;
