I am setting up a PostgreSQL database with a very small number of tables for a simple website. I am looking at ways to make it as solid and auditable as possible. Is there any particular reason why I should or should not create a PostgreSQL role for each user account on the website? The idea is that I could then use row level permissions on tables containing personal or private information and queries would look something like like this:
-- Service logs in with role mywebsite and runs: BEGIN SET LOCAL ROLE charlie; -- A user on the website INSERT ... -- DO STUFF COMMIT;
Logging code would then know both the system account (mywebsite in the above example) used to log in to the database and also the user on whose behalf changes are being made (charlie). The two are available in PostgreSQL as session_user (uid) and current_user (euid).
If you know of any precedents or any reasons why this would be a great or terrible idea I'd love to know.