Use PostgreSQL users for all application users



  • Up to now I always used one database user, and handled permission checking, and corresponding filtering in my application.

    This pattern has the drawback that the programming language which was chosen when the application "was born" is mandatory.

    All the permission checking stuff gets handled in this programming language.

    Since I currently start an application from scratch I re-think this pattern.

    Why not use a corresponding PG-user for every user which exists in the application?

    So authentication still happens in my application, but as soon as I know that Bob is Bob (and not somebody else) I use https://www.postgresql.org/docs/current/sql-set-role.html so that the following SQL statements get executed by the PG-user Bob.

    Question: Why not use one PostgreSQL users for every application users?

    Background: If I do the permission handling in PG ( https://www.postgresql.org/docs/current/ddl-rowsecurity.html ), then I can give the users (readonly) SQL access. Hint: My users are scientist, and I think they able to learn SQL.

    I guess this kind of application development is unusual. I googled but could not find talks or article. Please leave a comment if you know a article/talk about this topic.



  • Indeed, why not? Even if it is unusual, there is nothing wrong with managing your application users with database roles. As you say, it would make using row level security straightforward. Here are two potential problems to be aware of:

    • You won't be able to use a connection pool effectively, since connections can only be reused for the same database user.

    • Make sure to handle object permissions and policies via group roles. If you directly grant permissions to the end user, creating new users and dropping old ones becomes unnecessarily complicated.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2