I'm developing a web application, I normally write the authentication using a database table for usernames and passwords, I would like to implement this app using postgresql's authentication, the user types his user/pass and that's the user/pass used for database connectivity, so each user are guaranteed to only be able to read or write data on tables they are allowed instead using a single database user with read/write access to everything and checking before performing an action to see if the user is allowed.
I own the server on which this would run, so I'm ok with creating the users and setting up the privileges using the psql prompt, but I do not know this isn't a good idea in servers where I don't have a superuser, because a user with a "create user" role becomes a superuser and has read/write access to the entire server's database.
Can anybody tell me more about this, what should I do? which is the best aproach? Do I need to provide more information?