Author: Francis Ndungu
Last Updated: Fri, Jan 28, 2022In a PostgreSQL server, a role is a collection of permissions that you can assign to one or more users. Roles simplify assigning bundled privileges to users in a multi-user environment using a single statement.
One great example of a scenario where you can use database roles is an e-commerce application to process orders. In this scenario, you have three roles: system administrators, order specialists, and customer support.
System administrators can create, view, update, and delete orders.
Order specialists can create and view the orders, but they can not delete or update them.
Customer support staff can only view the order details, but they can not change or create new orders.
In the above example, assume you have three store administrators, seven order specialists, and 15 customer support staff in your company. Without roles, you would have to manually assign the permissions to each user. However, with the role-based model, you only need to create three roles to assign privileges to the users.
In this guide, you'll implement role-based permissions on the PostgreSQL database on your Ubuntu 20.04 server.
To complete this tutorial, you need:
SSH to your server and follow the steps below to create a sample database and table.
Then, log in to your PostgreSQL database server as postgres.
$ sudo -u postgres psql
Enter your postgres user password and press ENTER to proceed. Then, execute the SQL command below to create a store_db database.
postgres=# CREATE DATABASE store_db;
Switch to the new store_db database.
postgres=# \c store_db;
Next, create an orders table.
store_db=# CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
customer_name VARCHAR (50),
product_name VARCHAR (50),
amount NUMERIC(5,2)
);
Insert the following sample records into the orders table.
store_db=# INSERT INTO orders (customer_name, product_name, amount)
VALUES ('JOHN DOE', 'BASIC MEMBERSHIP', 5.25);
INSERT INTO orders (customer_name, product_name, amount)
VALUES ('PETER ERIC', 'ELITE MEMBERSHIP', 25.25);
INSERT INTO orders (customer_name, product_name, amount)
VALUES ('MARY SMITH', 'PREMIUM MEMBERSHIP', 75.25);
Ensure the records are in place by executing a SELECT statement against the orders table.
store_db=# SELECT
order_id,
customer_name,
product_name,
amount
FROM orders;
You should get the following output.
order_id | customer_name | product_name | amount
----------+---------------+--------------------+--------
1 | JOHN DOE | BASIC MEMBERSHIP | 5.25
2 | PETER ERIC | ELITE MEMBERSHIP | 25.25
3 | MARY SMITH | PREMIUM MEMBERSHIP | 75.25
(3 rows)
You've successfully set up a store_db database and created an orders table. In the next step, you'll define some roles and permissions.
The PostgreSQL server treats roles as entities that can own database objects and permissions. Therefore, every role you create in the PostgreSQL database server is valid across all databases.
The following is the basic syntax for defining database roles in a PostgreSQL server.
postgres=# CREATE ROLE EXAMPLE_ROLE_NAME WITH SAMPLE_OPTIONS
There are many options that you can define when creating the roles, but they're beyond the scope of this guide.
In this tutorial, you'll create sample roles and later associate them to different users depending on the privileges you want them to inherit.
Create the three roles.
store_db=# CREATE ROLE STORE_ADMIN;
store_db=# CREATE ROLE ORDER_SPECIALIST;
store_db=# CREATE ROLE CUSTOMER_SUPPORT;
After executing each command, you'll get the following output.
...
CREATE ROLE
Ensure the roles are in place by listing them.
store_db=# \du
You should get the following output. Please note the postgres role is a default system role.
Role name | Attributes | Member of
------------------+------------------------------------------------------------+-----------
customer_support | Cannot login | {}
order_specialist | Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
store_admin | Cannot login | {}
With the system roles created, you'll grant permissions Using the following syntax.
postgres=# GRANT SAMPLE_LIST_OF_PERMISSIONS ON SAMPLE_TABLE_NAME TO EXAMPLE_ROLE_NAME;
Start by assigning INSERT, SELECT, UPDATE, and DELETE permissions to the STORE_ADMIN role. In other words, any member of the STORE_ADMIN role can execute all CRUD operations against the orders table.
store_db=# GRANT INSERT, SELECT, UPDATE, DELETE ON orders TO STORE_ADMIN;
Also, for the INSERT command to work, you have to grant privileges to the currval and nextval functions which are responsible for sequence manipulation in the auto-increment/SERIAL columns. The order_id is a SERIAL column in the orders table in this tutorial.
store_db=# GRANT USAGE, SELECT ON SEQUENCE orders_order_id_seq TO STORE_ADMIN;
Next, assign INSERT and SELECT permissions to the ORDER_SPECIALIST role. In simple terms, members of the ORDER_SPECIALIST role can create and view orders, but if they want to update or delete the orders, they've to escalate the tasks to the STORE_ADMIN users.
store_db=# GRANT INSERT, SELECT ON orders TO ORDER_SPECIALIST;
GRANT USAGE, SELECT ON SEQUENCE orders_order_id_seq TO ORDER_SPECIALIST;
Then assign the SELECT permission to the CUSTOMER_SUPPORT role. Members under this group can only list the orders, but you'll not allow them to change any records.
store_db=# GRANT SELECT ON orders TO CUSTOMER_SUPPORT;
After each GRANT command, you should get the following output to confirm the new change.
...
GRANT
Read the grants table to ensure you've set up the permissions correctly.
store_db=# \z
The following output shows the roles and the associated permissions denoted with a(INSERT/APPEND), r(SELECT/READ), w(UPDATE/WRITE), d(DELETE), and U(USAGE).
Access privileges
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+---------------------+----------+------------------------------+-------------------+----------
public | orders | table | postgres=arwdDxt/postgres +| |
| | | store_admin=arwd/postgres +| |
| | | order_specialist=ar/postgres+| |
| | | customer_support=r/postgres | |
public | orders_order_id_seq | sequence | postgres=rwU/postgres +| |
| | | store_admin=rU/postgres +| |
| | | order_specialist=rU/postgres | |
(2 rows)
You've now set the appropriate roles and assigned the correct privileges. You'll create users and associate them with the roles in the next step.
You'll log in and interact with the database using user accounts and not roles in your database. Therefore, you need to create users to make operations to the orders table.
To associate users with privileges, you'll assign them to the different roles you've already defined.
Create 6 user accounts named john, mary, isaac, jane, jacob, and carol. Replace EXAMPLE_PASSWORD with a strong password for each user.
store_db=# CREATE USER john with encrypted password 'EXAMPLE_PASSWORD';
CREATE USER mary with encrypted password 'EXAMPLE_PASSWORD';
CREATE USER isaac with encrypted password 'EXAMPLE_PASSWORD';
CREATE USER jane with encrypted password 'EXAMPLE_PASSWORD';
CREATE USER jacob with encrypted password 'EXAMPLE_PASSWORD';
CREATE USER carol with encrypted password 'EXAMPLE_PASSWORD';
Next, assign the STORE_ADMIN role to users john and mary.
store_db=# GRANT STORE_ADMIN TO john, mary;
Then, associate user isaac, jane, and jacob to the ORDER_SPECIALIST role.
store_db=# GRANT ORDER_SPECIALIST TO isaac, jane, jacob;
Then link user carol to the CUSTOMER_SUPPORT role.
store_db=# GRANT CUSTOMER_SUPPORT TO carol;
Log out from the PostgreSQL database.
store_db=# \q
You've now created users and assigned them appropriate permissions through roles. In the next step, you'll test whether everything is working as expected.
Log in to the PostgreSQL server either as user john or mary. Remember, both of these accounts have the STORE_ADMIN privileges: INSERT, SELECT, UPDATE, and DELETE.
$ psql -U john -h 127.0.0.1 -d store_db -W
or.
$ psql -U mary -h 127.0.0.1 -d store_db -W
Enter the password for user john or mary and press ENTER to proceed. Then, execute the following statement.
INSERT statement:
store_db=> INSERT INTO orders (customer_name, product_name, amount)
VALUES ('PETER DAVID', 'ELITE MEMBERSHIP', 25.25);
Output.
INSERT 0 1
UPDATE statement:
store_db=> UPDATE orders SET
customer_name = 'PETER ERICSON'
WHERE order_id = 2;
Output.
UPDATE 1
DELETE statement:
store_db=# DELETE FROM orders
WHERE order_id = 4;
Output.
DELETE 1
SELECT statement:
store_db=# SELECT * FROM orders;
Output.
order_id | customer_name | product_name | amount
----------+---------------+--------------------+--------
1 | JOHN DOE | BASIC MEMBERSHIP | 5.25
3 | MARY SMITH | PREMIUM MEMBERSHIP | 75.25
2 | PETER ERICSON | ELITE MEMBERSHIP | 25.25
(3 rows)
The privileges for the STORE_ADMIN role are working without any problems. Exit from the PostgreSQL database.
store_db=# \q
Next, log in as user isaac, jane, or jacob. Remember, these users have the ORDER_SPECIALIST permissions.
$ psql -U isaac -h 127.0.0.1 -d store_db -W
or.
$ psql -U jane -h 127.0.0.1 -d store_db -W
or.
$ psql -U jacob -h 127.0.0.1 -d store_db -W
Enter the password for user isaac, jane, or jacob and press ENTER to proceed. Then, try executing the following commands.
INSERT statement:
store_db=> INSERT INTO orders (customer_name, product_name, amount)
VALUES ('JANE DERICK', 'PREMIUM MEMBERSHIP', 75.25);
Output.
INSERT 0 1
UPDATE statement:
store_db=# UPDATE orders SET
customer_name = 'JOHN ROE'
WHERE order_id = 1;
Output.
ERROR: permission denied for table orders
DELETE statement:
store_db=# DELETE FROM orders
WHERE order_id = 3;
Output.
ERROR: permission denied for table orders
SELECT statement:
store_db=# SELECT * FROM orders;
Output.
order_id | customer_name | product_name | amount
----------+---------------+--------------------+--------
1 | JOHN DOE | BASIC MEMBERSHIP | 5.25
3 | MARY SMITH | PREMIUM MEMBERSHIP | 75.25
2 | PETER ERICSON | ELITE MEMBERSHIP | 25.25
5 | JANE DERICK | PREMIUM MEMBERSHIP | 75.25
(4 rows)
As you can see from the above outputs, only the INSERT and SELECT statements succeeded. Exit from the PostgreSQL database server.
store_db=# \q
Next, log in as user carol. This user has the CUSTOMER_SUPPORT privileges.
$ psql -U carol -h 127.0.0.1 -d store_db -W
Then, enter the password for user carol and press ENTER to proceed.
Try executing the following statement against the orders table.
INSERT statement:
store_db=> INSERT INTO orders (customer_name, product_name, amount)
VALUES ('JANE DERICK', 'PREMIUM MEMBERSHIP', 75.25);
Output.
ERROR: permission denied for table orders
UPDATE statement:
store_db=> UPDATE orders SET
customer_name = 'JOHN ROE'
WHERE order_id = 1;
Output.
ERROR: permission denied for table orders
DELETE statement:
store_db=> DELETE FROM orders
WHERE order_id = 3;
Output.
ERROR: permission denied for table orders
SELECT statement:
store_db=> SELECT * FROM orders;
Output.
order_id | customer_name | product_name | amount
----------+---------------+--------------------+--------
1 | JOHN DOE | BASIC MEMBERSHIP | 5.25
3 | MARY SMITH | PREMIUM MEMBERSHIP | 75.25
2 | PETER ERICSON | ELITE MEMBERSHIP | 25.25
5 | JANE DERICK | PREMIUM MEMBERSHIP | 75.25
(4 rows)
From the database responses above, only the SELECT statement worked for user carol. Your PostgreSQL database roles and permissions are now working as expected. You can merge the appropriate permissions to the respective users through roles.
You've set up a sample database and a table in this guide. You've also created database users, roles, and permissions on your PostgreSQL database.
Follow the links below to read more guides about the PostgreSQL database.