As I’m trying to finish up one of my consulting projects (and enhance other active projects), I decided to take a nice long hard look a PostgreSQL permissions - the correct way of doing it. PostgreSQL applies the commonly accepted principle of assigning permissions to resources. They’re moving away from the concept of users and groups and more to roles. As of PostgreSQL 8.3, the primary command to create new users OR groups is
CREATE ROLE. The existing
CREATE USER / CREATE GROUP commands still exist, but are becoming non-existent in hopes of replacing the user / group permissions model with a strictly role-based model.
In an attempt to move my projects towards this model, I ended up running into several problems.
You assign “users” to “groups” by doing the following:
role_group is the name of the group role and
role_user is the name of the user role.
The assignment of “users” to roles plays a particular importance in permissions assignment. Typically, the webapp model has been such that it connects as one user to the database to perform
SELECT, INSERT, UPDATE, and DELETE queries. Thus, it was easy enough to simply:
With PostgreSQL, you have to execute the above command for each table, sequence, and other object type in order to grant access to your tables. If you have 50 tables, that’s not a tedious task, but it’s certainly annoying. Now envision you need to grant access to your developers and/or analysts to be able to perform the same queries. Share the username and password of the webapp? This violates a lot of industry practices and will be very difficult to trace back which user accidentally (or maliciously) mucked with the data in an inappropriate fashion.
So, you’re stuck executing the same 50 queries however many times you need to grant access to the database. Now it’s laborious and you start asking “What’s the better way to do this?”. Roles. If you executed the same 50 queries above on the “group” role, then to grant another user access to those same tables, you execute only the single query to add them to the role and BAM! They have access….or so you thought.
This is ultimately what this post was about. The PostgreSQL
GRANT documentation doesn’t include anything indicating that there’s a bit of a caveat with
GRANTing access to group roles.
When you implement the solution above, you may find yourself scratching your head when your user, a member of a group that has access to the schema and table you’re trying to query, receives the following error message:
cfegroup is the name of the schema; or, you get the following error message:
agency is the name of the table, then you have stumbled upon the same problem I had. Eventually, I found the answer still on the PostgreSQL documentation, but not in the
GRANT section. Rather, the problem is indeed a permissions problem because your user is not acknowledging the role permissions. According to the
CREATE ROLE documentation:
1 2 3 4 5
Thus, you simply need to make sure that your database “user” role has
INHERIT on it. Once you have verified this is the case, the user should not have a problem accessing your objects.