Postgresql – Login using database user accounts

database-designdatabase-recommendationpostgresql-9.5

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 ?

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 )

Best Answer

You have to consider, basically two tradeoffs:

PROs

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

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].
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.

Related Solutions

Database Design – Model with Users, Roles, and Rights

First of all, what type of security model do you plan to implement? Role-based Access Control (RBAC) or Discretionary Access Control (DAC)?

RBAC in the Role-Based Access Control (RBAC) model, access to resources is based on the role assigned to a user. In this model, an administrator assigns a user to a role that has certain predetermined right and privileges. Because of the user's association with the role, the user can access certain resources and perform specific tasks. RBAC is also known as Non-Discretionary Access Control. The roles assigned to users are centrally administered.

DAC In the Discretionary Access Control (DAC) model, access to resources is based on user's identity. A user is granted permissions to a resource by being placed on an access control list (ACL) associated with resource. An entry on a resource's ACL is known as an Access Control Entry (ACE). When a user (or group) is the owner of an object in the DAC model, the user can grant permission to other users and groups. The DAC model is based on resource ownership.

see source

1) In RBAC: you need ElementType table to assign rights to role (users are assigned to role(s)). RBAC defines: "What can this role/user do". Administrator assigns rights for roles and permissions to roles, assigns users to role(s) to access resources. 2) In DAC: users and roles have rights to elements via access control list (ownership). DAC defines: "who has access to my data". User (owner) grants permissions to owned resource.

Any way I suggest this data model:

CREATE TABLE ElementType
(
    Id (PK)
    Name
    ...
)

CREATE TABLE ElementBase
(
    Id (PK)
    Type (FK to ElementType)
    ...
)

(one to one relationship)

CREATE TABLE Element_A
(
    Id (PK, FK to ElementBase)
    ...
)

CREATE TABLE Element_B
(
    Id (PK, FK to ElementBase)
    ...
)

1) RBAC (many-to many relationship)

CREATE TABLE ElementType_To_Role_Rights
(
    RightId (PK)
    RoleId  (FK to Role)
    ElementTypeId (FK to ElementType)
    ...
)

2) DAC (many-to many relationship)

CREATE TABLE ElementBase_To_Actor_Rights
(
    RightId (PK)
    ElementBaseId (FK to ElementBase)
    ActorId (FK to Actor)
    ...
)

CREATE TABLE Actor
(
    Id (PK)
    Name
)

CREATE TABLE User
(
    Id (PK, FK to Actor)
    Password
    ...
)

CREATE TABLE Role
(
    Id (PK, FK to Actor)
    ...
)

Is it more secure to go through a 3rd database to connect two databases using the same login

One thing jumps out here:

The entire process uses the same set of login credentials

Problem

So hypothetical userX (whether some meatsack using Excel, or IIS AppPool Identity) can see some views and code. It doesn't matter what database these views and code are in because userX is setup in 3 databases anyway.

However, you lose ownership chaining like this.

Let's say WebDB.dbo.SomeProc calls PrivateDB.dbo.SomeTable. UserX requires permissions on both objects. If this was OneDB.WebGUI.SomeProc using OneDB.dbo.SomeTable then only the OneDB.WebGUI.SomeProc needs permissions. Permissions on referenced objects with the same owner are not checked.

Note: I haven't looked too deeply at cross database ownership chaining. I only know plain old "ownership chaining" well

Now, as per comments you really have 2 databases that can be combined. Not 3 which was originally implied. However, the intermediate and web can be combined.

The other "private" databases can perhaps be combined, but that'll be a separate issue. See the bottom link for a fuller discussion of "one database or many"

Solution?

If the extra databases are code containers only, then schemas are a better idea.

This sounds like you've used "Database" where you should use "Schema" (in the SQL Server sense, not MySQL sense). I'd have a WebGUI schema, a Helper or Common schema (to replace Intermediate database) and Desktop schema. This way you separate permissions based on clients and just have one database

With one database (in addition to "ownership chaining") you can also start to consider indexed views, SCHEMABINDING (I use it always) and such that can't be done with seperate databases

For more on schemas, see these questions:

Finally, there appears no reason to have separate databases based on "transactional integrity not required". See this question to explain this: Decision criteria on when to use a non-dbo schema vs a new Database