Re: [HACKERS] USER Profiles for PostgreSQL

John,

* John R Pierce (pierce(at)hogranch(dot)com) wrote:
> On 9/19/2017 3:32 PM, chiru r wrote:
> >How those application accounts get recognized in database?
> >
> >Let say  App_user1 authenticated through application ,after that
> >how the App_user1 get access to DB?
> >
> >can you please provide more information ,how the app users are
> >accessing database ?
>
> the user isn't accessing the database, the application itself is
> accessing the database.   a web app might have 10s of 1000s of
> unique users, but a web app typically only uses a single application
> account to access the database.

While that's one approach, it's certainly not the only one (and not
necessairly considered a good approach either..).

If AD is in the mix here, then there's no need to have things happening
at the database level when it comes to passwords- configure PG to use
Kerberos and create a princ in AD and put that on the database server
and then users can authenticate that way.

Further, applications can be configured to accept and proxy Kerberos
credentials from the user to authenticate with to the database, allowing
the application to actually be logged in as the user for that session,
with only those rights the user has.

Group/Role membership does still have to be sync'd between the AD/LDAP
directory and the database for those permissions to be set up, but that
isn't all that hard to do using a cronjob and ldap_fdw, or similar,
people just have to realize that there's a bit of lag. The same goes
for creating accounts in the first place in the database, of course.

Thanks!

Stephen