A colleague asked me today for my opinion on database security and the best way to grant a certain database privileges to a few users in a postgreSQL database. I will share my thoughts here and I welcome your thoughts as well. These basic database security concepts here apply to any relational database including Oracle, SQL Server, MySQL, or any database that implements roles for security. They also apply to application security roles where the access control is managed in the application rather than the database, as is often the case.
My colleague needed to give certain users the ability to kill other processes. He was struggling with deciding how to structure the privilege. In PostgreSQL, the privilege to instruct another process to terminate is granted by virtue of the default role called pg_signal_backend. He was deciding between granting that role directly to the users in question, or to create a role called something like Manage_Other_Processes that would be granted to the users in question.
Here is how I think about using roles.
A role is really a business role
Basically, one should grant a privilege to a role rather than directly to a user when that privilege is to be granted to a group of users, instead of just one, specifically, a group of users that perform the same business function. One benefit of this approach is that this simplifies replication of one user’s privilege to another user, as in the case of one user leaving the company and being replaced by another user.
A privilege should also be granted to a role when that privilege enables the user to perform a certain function, and when it is likely that other privileges will also be required in order for a user to perform that same function.
These considerations really get to the whole idea of roles in the first place. A role really refers to the role that the individual receiving the privilege plays in the organization. I think it’s original intent was not really to be considered a database construct, but that is how many think of it now, this misalignment is particularly reflected in the naming of the pg_signal_backend role in postgreSQL, more on that later.
Database Privileges, Security Best Practices, Keeping it Organized
A key benefit of using roles is organization. A given user may have many privileges. Update, delete, insert, select, each on tables, views, stored procedures etc. Add in system privs and a typical user has lots of privileges. Managing privileges on that many objects is a challenge. The best way to manage a large number of things is to categorize and label them. This is accomplished with roles.
For example, I can group together all the privileges on stored procedures, tables, views, and other database objects required to manage client records, and grant them to a role called manage_client_records. And I can group together all of the privileges required to manage employee records, and grant them to a role called manage_employee_records.
Database Security and adding new users
Rather than remembering that I need to grant execute permissions on 2 stored procedures and 10 tables for managing the employee records, and on 3 procedures, and 15 tables for managing customer records, I can simply grant all of those privileges to the appropriate roles once, and grant those roles to the proper users in one simple statement.
Ease of removing or changing user access
Perhaps most importantly, I can revoke all those privileges by simply revoking the roles, enhancing security by reducing the possibility of human error resulting in dangling privileges when someone changes roles in the company.
Ease of managing application enhancements and changes
If the developers add functionality to the application, resulting in new tables, views, or other database objects that will require access by certain application users, these new privileges can be granted to the appropriate roles, and all users that have that role will receive that privilege. No need to individually grant the privileges to individual users.
Discovery and User Access reporting
When we do database security assessments, we often generate reports that show which users have privilege to access tables, execute stored procedures, and change system configuration.
What management really wants to know, however, is not what table a user can access, they want to know what business functions each user can perform and what data they can read or edit in that capacity. Here is where using roles really shines.
A report showing the set of users that can view or manage client accounts is much more useful to management then a report that shows a set of users that have select or edit privilege on the client table, and the client address table, and the client account table, and the client transaction table, etc. Management needs to quickly be able to see what capability users have. Roles make it much easier for them to see that. Imagine a report showing 10 users that have been granted the manage_client_data role, and 15 that have been granted the view_client_data role. Twenty five lines that tell the complete story. Contrast that to a report with hundreds of lines showing all tables and stored procedures that all users have access to. Of course a detail report will be useful as well for deep analysis, and that can be generated when using roles as well.
Database Privileges and System Roles
I used application related roles as examples in this article, but the same concepts apply to system roles and application-owner roles like those that my colleague asked about, and that motivated me to write this article. And this deserves a little more discussion and some readers may disagree with my thoughts on this and I was definitely on the fence about it. Please comment and add your thoughts if you think differently.
The privilege that he asked about was actually already a role, not a privilege. Pg_signal_backend is a role that enables the user to terminate processes owned by other users (except super-users). While this is already a role, I feel like it is so narrowly defined that it does not satisfy the real intent of role as I discussed it above. I feel like it would not be surprising if other similar privileges (roles) of this nature are likely to be needed by the same user, given that it needs to control other processes. And I would rather see a better defined (and named) role, like Manage_Other_Processes, that includes this role and any others that will end up being necessary. And then that role can be applied to any other users that need this capability.
Similar to my discussion about user access reporting above, a role with a name like Manage_Other_Processes will tell much more during a user access report than one with the name pg_signal_backend.
To Role or not to Role
So at the end of the day, when designing a security scheme, I try to use roles wherever it is likely that the same business function requires multiple privileges, or where the same privileges are likely to be assigned to multiple users. Please share your thoughts and contact us for more information.
