How to implement complex permission based data access in Postgres with Postgraphile or alternatives

Question

For a new project, we're currently designing a database and an API to access this. We've already established we'll be using PostgresQL for the database, and want to access it via a GraphQL API.

To ease with maintainability, we looked at several intermediaries between client/API/database, mainly Prisma, PostGraphile and Hasura. PostGraphile stood out, because of ease of use and the focus of handling stuff "in database" as opposed to in your backend code. However, we ran into issues when figuring out how to implement this.

Allow me to expand on what we designed thus far:

---

Provisional database design:

• users table
• groups table
• roles table:
• u_g_r table: A user can be part of multiple groups, and can have multiple roles in each group. This table represents foreign keys for users, groups and roles, as many-to-many relations can exist in virtually all combinations.

Data Permissions:

We want users to grant others access to their personal data in several steps, preferably for each group. For example:

• level 3: Yourself and only absolutely necesary people, such as account manager
• level 2: Only people in group X, Y, etc
• level 1: Everybody

It would be awesome if it was possible to set this for various types of data, for example grant level 2 for your phone number, but only level 1 for your physical address. So, these levels (1, 2, 3) would accompany data in the database, like phone_number and phone_number_access_level for example. Then, in the u_g_r junction table, each combination of user/group/role would have an allowed level attached to it, which must be higher than the required level for the relevant data. Thus, if your role allowed access to data on level 2, you would be able to view data on level 1 and 2, but not level 3.

---

Postgres allows both column- and row level security, to let users access certain data. The PostGraphile wiki goes into some detail (here and here) how you would make this work with JWT claims instead of PostGres roles. Our problem arrives when we want to implement the above features. It seems we want a kind of 'field level security' that does not exist, but I can't imagine others not having had the same issues.

What would you advive us to do? Please let me know if there are options we've missed, or whether there are other options that are better for us!

Implementing this outside the database, in backend code might might be the easiest way in and of itself, but it greatly impacts maintainability for us, as the main luxury of things like PostGraphile for us is removing the need to write GraphQL schema's and resolvers ourselves.

Answer 1

It seems that you want all users to see all table rows, but only certain columns.

You probably cannot use column permissions, because these can only allow or deny access to the column as a whole and do not respect who “owns” a certain table row.

So perhaps views can do what you want, for example:

CREATE VIEW users_view
WITH (security_barrier = true, check_option = local) AS
SELECT /* accessible to everyone */
       username,
       /* accessible only to certain groups */
       CASE WHEN pg_has_role('x', 'USAGE') OR pg_has_role('y', 'USAGE')
            THEN level2_col
            ELSE NULL
       END AS level2_col,
       /* accessible only to admins and owner */
       CASE WHEN username = current_user OR pg_has_role('admin', 'USAGE')
            THEN level3_col
            ELSE NULL
       END AS level3_col
FROM users;

security_barrier makes sure that nobody can use functoins with side effects to subvert security, and check_option ascertains that nobody can INSERT a row that is not visible to themselves.

You can allow DML operations on the views if you define INSTEAD OF triggers.

Answer 2

Based on the answer of Laurenz Albe, I created an immense view for all kinds of columns. It worked, certainly, and even with several thousands of entries of mock data it was still relatively quick.

When I got back to it last week, a cleaner solution (arguably) dawned on me. Instead of using custom views like this, I'm now using separate tables with the sensitive data, link them with foreign keys and enable Row Level Security on these rows.

I haven't done any benchmarks, but it should be faster as this data isn't always requested anyways. It at least saves complicated views with a lot of boilerplate!