Guidance on User Management and Row-Level Security with PostgREST

[vdufloth]

Hi everyone,

I’m considering using PostgREST as the backend API for a new product, especially since I plan to use PostgreSQL anyway. PostgREST seems like it could significantly reduce the time to market for new features. However, after reading through the documentation, there are still a few things I’m uncertain about, and I’d really appreciate some guidance from those with more experience.

To illustrate, let’s consider the following business model:

Imagine I have a table TODOS(todo_id, user_id, description, due_date, done).

In this scenario, users can have one of three permissions: ‘dashboard’, ‘users’, or ‘managers’.

• ‘Dashboard’ users have SELECT permissions on the TODOS table.
• ‘Users’ have SELECT permissions on the TODOS table and full permissions on TODOS rows that they own (TODOS.user_id = (logged) USER.id).
• ‘Managers’ have full permissions on the TODOS table.

Q1.
What’s the recommended way to handle application users in general? If I had different apps connecting to the same database, it’s clear that using roles and JWT would be the way to go.

But does this approach also apply to ‘normal’ application users, as in this example?

For instance, if I have 1,000 app users, should I also create 1,000 PostgreSQL users with roles applied? Or do you store the user’s role as a string in a basic_auth table, as suggested in the documentation and have 1,000 rows in this table returning the appropriate role in the JWT?

For reference, I’m accustomed to traditional backend applications, where you’d adjust the query string based on the user making the request, like this:

// Pseudocode
if (user.permission == 'users') {
   query = query + ' AND todo.user_id = ' + connectedUserId;
}

Of course, I could achieve the same result by altering the client-side URL request to PostgREST, but that would make the client application responsible for always modifying the URL correctly. How can I enforce these rules for future external applications, integrations, or even new front-end developers who might not fully understand the business model?

I assume the solution involves PostgreSQL’s Row-Level Security Policies, but I’m unclear on how user roles would fit into this approach.
Should each user have their database user and role granted?
Should I check their role as string in basic_auth?

What’s the most common and proper way to handle this?

Q2.
Let’s say a new table is introduced: TODO_DETAILS(todo_detail_id, todo_id, detail_description, done).

How would I apply the same row-level security policy to this table, given that it doesn’t have a user_id field? If the solution is to create a row-level security policy using a subquery that joins the TODO and TODO_DETAILS tables, would that provide good performance? Is it considered a best practice?

Q3.
Considering the new table, does PostgREST automatically generate some form of API PUT request that allows inserting data like this:

{
  "user_id": 123,
  "description": "one two three",
  "due_date": "2024-10-10",
  "done": false,
  "todo_details": [
    {
      "detail_description": "one and two",
      "done": true
    },
    {
      "detail_description": "three",
      "done": false
    }
  ]
}

And then automatically perform all the necessary inserts in PostgreSQL?

Thank you in advance! These might be basic questions, but after reading through the documentation and running some tests in a Docker container, I’m still unsure of the “proper” way to address these issues.

[wolfgangwalther]

Considering the new table, does PostgREST automatically generate some form of API PUT request that allows inserting data like this:
[...]
And then automatically perform all the necessary inserts in PostgreSQL?

You want #818, which we don't support, yet.

[vdufloth]

Is there some roadmap for #818 ?

And if that does not exist yet, how are people handling this issue with PostgREST?

To me the main benefit of PostgREST would be to make changes in the schema and have them available instantaneously to the APIs. However, without this feature, for transactional inserts of related tables wich are very common to appear (order + order_items etc...) either a RPC would have to be created for each relational insert, or another application would have to connect to the database and provide new sets of insert API's. Both require additional work beyond only the database schema, wich results in taking the traditional development time (for relational inserts).

Are people mostly using PostgREST as a READ API only? What are the current main usages of it in production?

[wolfgangwalther]

Is there some roadmap for #818 ?

The same as for all other issues: If somebody finds the time to do it...

There has been a first try here: #3226

And if that does not exist yet, how are people handling this issue with PostgREST?

Because of schema isolation, I have the todos and todo_details table normalized in a data schema. In the exposed / public / api schema (however, you call it), I have a todos view, which does the JOIN to todo_details already, and exposes it as an array. I then have INSTEAD OF triggers on that view to manage the insert. Those triggers take care of inserting this array into the todo_details table via MERGE. I posted an example of that in some other discussion a while ago, but I can't find it right now.

To me the main benefit of PostgREST would be to make changes in the schema and have them available instantaneously to the APIs. [...] Both require additional work beyond only the database schema, wich results in taking the traditional development time (for relational inserts).

Exposing the data schema directly is discouraged. See Schema Isolation mentioned above.

Writing code for the data schema + the api schema is still a lot faster than "traditional" methods (I disagree with traditional here, I'm pretty sure that there was a time where "business logic in the database" was much more common than nowadays, so I'd say what we do here with PostgREST is more traditional than the other way around).

For me, a major benefit is, that I can make schema changes much more aggressively.. because those changes happen in the same transaction (most of the time) than the changes to my api. So I can keep my API consistent while the data schema below it evolves - without considering that multiple applications using this database schema need to be updated in sync with those schema migrations...

Are people mostly using PostgREST as a READ API only?

Certainly not.

[wolfgangwalther]

Or to put it differently: If you're looking for a "no code" solution to writing APIs - IMHO PostgREST is not the answer.

[vdufloth]

If you're looking for a "no code" solution to writing APIs - IMHO PostgREST is not the answer

Not at all. All "no code" solutions I looked are very very limited, and I think there is no way out of it. If it were more complex then it would end up being some form of coding again.

My thinking was more like: if the relational inserts on PostgREST would not work 'out of the box' and thus require another coded server (or codded triggers RPCs) then maybe it would be better off to code the entire API on this other server and centralize the codebase.

Writing code for the data schema + the api schema is still a lot faster than "traditional" methods [...] "business logic in the database" was much more common than nowadays

Having worked with a 15+ years old application that did exactly that, had a lot of business logic on the PostgreSQL server, I totally agree that it was already done.
"Commom" I guess would be a better word. Much more tooling for versioning, deploying and unit testing code in Java / Python / Javascript (Node) etc.. then with triggers and functions.

But I agree that it still is faster, with the potencial lack of standard tooling. As always in IT, each tool has its pros and cons.

Using your provided solution with VIEWS and INSTEAD OF triggers although would not provide an "elegant" JSON, would solve the issue so I will mark this as resolved as it addresses my question on how to do it with what PostgREST offers today.

[wolfgangwalther]

Much more tooling for versioning, deploying and unit testing code in Java / Python / Javascript (Node) etc.. then with triggers and functions.

Yes, fully agree. Tooling has been my only pain-point, so far. I've built some project-specific tooling, which makes it work-able, but there is still a lot to desire.

[wolfgangwalther]

Let’s say a new table is introduced: TODO_DETAILS(todo_detail_id, todo_id, detail_description, done).

How would I apply the same row-level security policy to this table, given that it doesn’t have a user_id field? If the solution is to create a row-level security policy using a subquery that joins the TODO and TODO_DETAILS tables, would that provide good performance?

I have done both:

• denormalized my schema and added the user_id column to todo_details, too. Added additional FKs to make sure the user_id column always matches the same column in todos.
• created RLS with subqueries, just like you mentioned.

Both work and depending on the exact case might make more sense. Imho, both patterns are fine. Denormalized is faster, but at the cost of more storage (you'll need the additional columns, and possibly some more, redundant, unique indexes on todo to be able to create the FK).

[wolfgangwalther]

Should each user have their database user and role granted?

I have a big production instance running with this. We currently have ~ 70k users. Because of a more complex role system, with scopes for groups etc., we have around ~ 140k pg roles with that.

This works fine so far, except for one limitation: We had to make the authenticator role SUPERUSER, because otherwise it would have taken ~ 6 minutes for the SET ROLE command on each request. We are running pg 15, this could have been improved in v17. There was some discussion and improvements around a nearby area, but I haven't tested the performance with that, yet.