December 12, 2023
Summary: in this tutorial, you will learn how to perform authorization using roles and privileges in PostgreSQL.
Table of Contents
Introduction
It is quite common to see a user being given more than the required privileges in a database. Sometimes, the default postgres user is used as an application user and a backup user. This can be dangerous as the superuser has the highest level of privileges. A user with a superuser role can drop a database along with its data. At the same time, it may be difficult to grant privileges on each database object to every user. For this purpose, we could use the concept of roles and privileges. In this tutorial, we shall discuss how roles and privileges can be used to perform a better authorization mechanism in PostgreSQL.
Getting ready
Before attempting to segregate privileges using roles and privileges, it is important to distinguish the access control you wish to implement in your database. For example, if the database is serving two application modules that connect to different schemas or tables, it is better to have two or multiple roles that serve the purpose of granting read or write access to each of the schemas or each set of objects serving an application module. This way, we may grant read_only access to a user that only grants read access to objects serving a specific application module but not all.
How to do it…
We can tweak authorization privileges by using the following steps:
1. Create a read_only and read_write role that has the NOLOGIN privilege by default:
$ psql -d rockdb "CREATE ROLE scott_read_only"
$ psql -d rockdb "CREATE ROLE scott_read_write"
2. Grant read access on all tables to the read_only role and write access on all tables to the read_write role:
$ psql -d rockdb "GRANT USAGE, SELECT ON ALL TABLES IN SCHEMA scott TO scott_read_only"
$ psql -d rockdb "GRANT USAGE, SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA scott TO scott_read_write"
3. Now, grant the read_only role to the developer and the read_write role to the application user:
$ psql -d "GRANT scott_read_only TO dev_user"
$ psql -d "GRANT scott_read_write TO app_user"
How it works…
In the steps discussed in this tutorial, we are assuming that the objects being accessed by the application are all in the scott schema. To properly segregate read and write access, we see the commands using which two roles are created in step 1. Once the roles are created, the roles can be granted read and write access appropriately as seen in step 2. Once the privileges are granted, the read_only role can be assigned to a user needing read-only access (developer) and the read_write access can be granted to the user who should have both read and write access, as seen in step 3.