3

I want to know is it advisable to directly use the database user and passwaord as the user of web application generally i have seen everywhere they use a separate table inside database for managing (add/edit/delete) the users .

My question why NOT use the default database user name and password for my web application ?

enter image description here

Note: I want to reuse the user and role system which postgres already provide instead of making a new table of users. I think this will be good because

  • reduce the replication of user tables
  • not to worry about security password mgmt
  • can use roles of postgres to limit the data access
  • many other application can directly connect the database (in my case this is important as a desktop application will also access the database still the data will be secured by roles )
  • Just to make sure I'm following: do you mean, give all users the user name and password to access your database, instead of having individual user accounts in the application? Or, creating an individual database username and password for all users accessing the web application, and having them use that to log into the application? Or, something else? – RDFozz Apr 1 '17 at 7:09
2

You have to consider, basically two tradeoffs:

PROs

  1. Using the "PostgreSQL users" instead of your own table(s) allows for very fine-tuning security privileges without having to add all this logic in your application. Consider whether you actually need all this finesse or not.

CONs

  1. If you want to add extra information about a user (such as an email account to which you can send a restore password link or something similar) you still need to create a secondary table to store all of it [or use something like COMMENT ON ROLE role IS '{"email":"a@b.com", "comment:""structured text where you store all kinds of information"}', which I think denormalizes too much how you use your database].

  2. Login roles are common to all databases within a PostgreSQL cluster, not just for a specific one. If you're hosting several of them simultaneously, this can artificially limit the usernames you can give to your users (because, maybe, you're already using the same username (=login role) for another DB for another purpose).

Alternative (having the best of both worlds)

You can have a mix: create one login role (or group role and login role) per each different role within your application, which allows for fine-tuning security; have one users table with a login role assigned to each. Your application should start with a role that can read this table, and after validation of credentials, disconnect from the database and reconnect using the proper login role. The login roles are internal to the application, and not visible to the users.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.