CODEWITHSUNDEEP
postgresqlauthentication
Nick Bull
Nick Bull

Reputation: 9876

User accounts using PostgreSQL

This may be a simple question; apologies if this question is basic stuff.

I'm attempting to create an accounts system for a website using Node.js (this is all just for learning!) and I've already thrown together forms, Postgres connection, etc. However, I'm now wondering how I should store information about users, such as their username and password.

I know that user accounts for Postgres databases can be created using CREATE USER name WITH PASSWORD '';, however I'm not sure if I should:

  1. Create an account for accessing the database, and return a table containing user information; is this what the accounts system in Postgres is designed for, or is it for database administration only?
  2. Create a user with the above command whenever somebody signs up, and then add extra information either in another table.

Thanks for your help.

Upvotes: 0

Views: 675

Answers (1)

Patrick
Patrick

Reputation: 32346

In a typical web application where you have little control over who signs up you should not make a separate ROLE for every user. Instead, your web application has 1 or a few roles and user account information is stored in a regular table.

This way you can use standard database security for the roles that the web application (your code, basically) is using, while your application logic determines what your users can see or do.

The standard database security features include things who can access tables to view, insert, modify or delete data. When every user of your application has a database role they need to have the appropriate permissions set. You can manage that with group roles, but you would have other issues to deal with. As an example: You have 1,000 users who all store their personal details in a table user_details. Those users need to be able to view and modify their own data, but not that of any of the other 999 users. You can set that up with a view that filters data to be belonging to the CURRENT_USER of the database session, but it is a lot of maintenance. It is easier - and more secure - to have just the single account of your web application and let your business logic do the filtering.

I would suggest that you read up on database roles and security to properly understand how to set up a secure environment.

Upvotes: 1

Related Questions