0

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.

Improve this question

1 Answer 1

Reset to default
1

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Sorry to be picky, I'm new to PSQL! What are the standard database security features that you mentioned, and does option 2 from the Q sacrifice security, and in what ways? Thanks for your help! (edit: apologies, if for the security you mean permission control with GRANT ACCESS of the single role, then I understand :) )
Solves my question and then some! Thank you so much for your help Patrick

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.