Approach to creating users in Database

Greetings,

* Sanjay Minni (sanjay.minni@gmail.com) wrote:

what is the usual approach in creating users / role in a postgresql
database serving as the data repository to a hosted multi-tenanted
application with a large number of users.

the 2 approaches I can think of is
A. The user stays and is controlled only in the application level, the
application in turn connects with the postgresql database as a single user
name. Any userid to be stored as part of the data is injected by the
application
B. Each user is also created in the postgresql database with grant or
required rights to the tables.

what is the usual practice ?
I am unable to see any benefit in (B) above - i.e. recreating each user in
the postgres database

Both approaches are used pretty commonly. The advantages of B include:

- The permissions can be managed using GRANT/REVOKE at the database
level and you remove the risk, for the most part, of things like SQL
injection attacks because the user you're connected to the database as
has only the rights that they should have, unlike in approach A where
the user connected to the database has rights to basically everything.

- Auditing is able to be done of who did what using the database rather
than having to trust to application logs and auditing.

There's other up-sides and down-sides, of course.

Thanks,

Stephen