Should I use Postgres's roles system for a web app's user management?

Question

Postgres already has fully featured user management system. Why should I duplicate this functionality and use another one on top of that? And I think this is the right place to manage users & groups as it allows fine-grained control. Am I wrong? Is there some php libraries that already have done that?
I should add that the app in question is not a public web-site, but a corporate app working in the private network.

Answer 1

I strongly advocate that application designers make use of PostgreSQL's users and role system ... but for a number of reasons having a 1:1 mapping of app users to database users is not usually practical.

• PostgreSQL roles are shared across all databases (though they don't have to be granted rights on anything except one)

• You can't have a foreign key reference from a normal application table to a PostgreSQL user table

• There's no function or other interface to authenticate a user by password. You have to make a new connection to authenticate by password. This breaks connection pooling.

Instead, I advise that you use a couple of roles in the database:

• A database owner role. This user/role owns the database and the tables within it. Scripts to change the database structure ("migrations" and so on) run as this user.

• A webapp role. This is the role the app connects as when establishing pooled connections. This is GRANTed only the access the app needs when running day to day. It can't change table structure, drop tables, etc. If a table is supposed to be append-only you don't grant UPDATE rights to this role.

• (possibly) some maintenance roles for scripts, etc, which have limited access to just what they need for their task.

You manage your application users with normal tables.

Sometimes you also want additional database roles for particular categories of user. This can be handy if you're dealing with apps with different privilege levels, departments, etc. The webapp can SET ROLE to switch roles, so if "joe" connects and you know "joe" is in accounts, you "SET ROLE accounts" before running queries for joe. This is more advanced, and most people don't need it.

The main time I think using PostgreSQL user management directly makes sense is when the app has quite complex access requirements and doesn't need a huge profusion of different users (thousands, rather than millions). For webapps I'd stick with normal database tables and just separate a "db admin"role from the webapp connection pool role.

Answer 2

AFAIK this is not done, although possible.

Your application would not be portable to other databases, and mysql is quite popular for web apps.

Instead you normally design your users table to your own needs, and write groups and access management yourself, or use one of the many libraries.

(an upvote for noticing the PG role system)