Implement Role-based Permissions with PostgreSQL
Introduction
In 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.
Prerequisites
To complete this tutorial, you need:
1. Create a Sample Database and a Table
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 astore_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 theorders
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 anorders
table. In the next step, you'll define some roles and permissions.
2. Create PostgreSQL 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
, andDELETE
permissions to theSTORE_ADMIN
role. In other words, any member of theSTORE_ADMIN
role can execute all CRUD operations against theorders
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 thecurrval
andnextval
functions which are responsible for sequence manipulation in theauto-increment/SERIAL
columns. Theorder_id
is aSERIAL
column in theorders
table in this tutorial.store_db=# GRANT USAGE, SELECT ON SEQUENCE orders_order_id_seq TO STORE_ADMIN;
Next, assign
INSERT
andSELECT
permissions to theORDER_SPECIALIST
role. In simple terms, members of theORDER_SPECIALIST
role can create and view orders, but if they want to update or delete the orders, they've to escalate the tasks to theSTORE_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 theCUSTOMER_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.
3. Create Users and Link them to Roles
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
, andcarol
. ReplaceEXAMPLE_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 usersjohn
andmary
.store_db=# GRANT STORE_ADMIN TO john, mary;
Then, associate user
isaac
,jane
, andjacob
to theORDER_SPECIALIST
role.store_db=# GRANT ORDER_SPECIALIST TO isaac, jane, jacob;
Then link user
carol
to theCUSTOMER_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.
4 - Create Users and Link them to Roles
Log in to the PostgreSQL server either as user
john
ormary
. Remember, both of these accounts have theSTORE_ADMIN
privileges:INSERT
,SELECT
,UPDATE
, andDELETE
.$ 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
ormary
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
, orjacob
. Remember, these users have theORDER_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
, orjacob
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
andSELECT
statements succeeded. Exit from the PostgreSQL database server.store_db=# \q
Next, log in as user
carol
. This user has theCUSTOMER_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 usercarol
. Your PostgreSQL database roles and permissions are now working as expected. You can merge the appropriate permissions to the respective users through roles.
Conclusion
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.