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 recipe, 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...
