Search Postgresql Archives

Re: Approach to creating users in Database

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Greetings,

* Sanjay Minni (sanjay.minni@xxxxxxxxx) 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

Attachment: signature.asc
Description: PGP signature


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux