Postgres User Authentication on web app through LoginRoles Vs Table

Question

I have a postgres database and a web app.

The web app allows the existance of users and they do some stuff on the webapp.

I am new to postgres but what we used to do in SQL Server was ActiveDirectory - CreateLogins - CreateRoles etc..

Now in this database is it possible to create a login for each user? or is a table with users (username password) better? or worse?

I was thinking about having 1 login user in the database that can only do specific procedures and see views and just authenticate the user through the table.

Which is better solution?

Answer 1

I think it's better to use the database's built-in role management, as it keeps it DRY. Despite myths, you can still use connection pooling. It makes it much easier to audit, and to use row-level security later if you need it.

1. Postgres can authenticate users using built-in roles, or using Kerberos, GSSAPI, SSPI or LDAP.

2. You can sync LDAP users/groups/roles with Postgres:

   https://github.com/larskanis/pg-ldap-sync

3. You can delegate authentication to the web server. Login as the web server role, and then change the user's role, to act as that user, using SET ROLE X. This lets you use connection pooling.