1

PostgresSQL 10.4 is our backend database for a consumer-facing app with millions of users. When implementing row-level security so each user can only access their own data, is it best practice to use a separate Postgres role for each user or to have a shared role with a user table?

The documentation always uses examples with separate Postgres roles, whereas e.g. this blog describes how to do it using a user table.

closed as primarily opinion-based by mustaccio, Md Haidar Ali Khan, hot2use, Max Vernon, Marco Sep 18 '18 at 12:51

Many good questions generate some degree of opinion based on expert experience, but answers to this question will tend to be almost entirely based on opinions, rather than facts, references, or specific expertise. If this question can be reworded to fit the rules in the help center, please edit the question.

2

With that many users, it's probably only practical to manage it using a shared role with your own user table. In fact, I know of very few consumer-facing applications that manage their users using database roles. The thing is, you almost certainly need to store more information per user than what Postgres stores for roles. So if you just manage your users as data in your own table, you're done. But if you have to create a role for each user too, then you're increasing the work you need to do. So I'd stick with a user table.

Not the answer you're looking for? Browse other questions tagged or ask your own question.