4

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.

  • Had the same thoughts today. Glad to see you already asked. If I may ask, what did you settle on? Using the traditional way or going with the inbuilt roles? Basically, I reasoned along the same lines as you did concerning programming error et al. And I don't really know why row level perms will be available if this is not a good idea? – frostymarvelous May 11 '16 at 12:30
  • 1
    @frostymarvelous It's on hold at the moment. This was for a new project so I spun up a minimal product. The promised customers didn't arrive, so I stopped. There is another application for the core algorithm so I am pushing that at the moment. The new slant has one "user", no need for user management and no need strong separation between user data. I'm still interested in this approach though so if a similar circumstance appears again I will drive further. I did make a demo that showed this working - it was like using SELinux - a pain to get started but seemingly very secure thereafter. – Max Murphy May 11 '16 at 14:51
  • Exactly my suspicions. It might be hard to start, but can have some good benefits after. Or it might just be the worst thing ever. Won't know until we try it. – frostymarvelous May 11 '16 at 15:09
2

Roles are generally used to group users together. For example, we have several moderators on Programmers. Rather than creating a role for each user, we likely have one "moderator" role assigned to multiple users. This makes it easy to see who belongs to what group and to modify permissions for multiple users at once.

It sounds like you have a classic case of "my web site has multiple users, but the web site only uses one user account to log in to the database" common to most public web applications.

Using roles to identify web site users is not what that feature is designed to do, and may hamstring you in the future if you need roles for another purpose. It also duplicates functionality: if you can create roles, why not just create additional users?

I think one approach here would be to nix the roles-based approach and just modify the data directly. Rather than using built-in logging, maybe create a separate logging table where you can log the event. It might contain fields for the following:

  • PostgreSQL user
  • Application user
  • Table modified
  • Field modified
  • Notes (free-form text, whatever is relevant)

I would not advocate using this log for every modification, only the important ones that require an audit trail.

Another option I have seen is to include record state information on each table where you need accountability. It does not maintain a history, but may be good enough if you just need the most recent edit. Several open-source frameworks do this. You could add the following fields to important tables:

  • PostgreSQL user
  • Application user
  • Date modified
  • 1
    Thanks for your thoughts. I have audit tables and logs that behave much as you described, however in my experience I have only ever seen roles being used to control what the technical folks can do when they log in and, more importantly, what their scripts can do if they go haywire. As such there is only a relative handful of postgres roles. For website users I have usually seen a separate "users" table and access control on the web side being implemented by joining on to that table. That approach is susceptible to programmer error, hence my thoughts about using postgres auth for everyone. – Max Murphy Jan 25 '16 at 23:40
  • Identifying web site users in the first instance will be done by OAuth or similar. The question is how to bind that authorised identity to table rows very tightly. Can you explain how using roles might hamstring me in the future? If there is a set of server roles and a set of website user roles, what do you think might go wrong? – Max Murphy Jan 25 '16 at 23:45
  • @MaxMurphy using one role per user is essentially mis-using roles, and could make role management messy if you later decide you want to use them as user groups later on. I guess it will not limit you, but it will make your user management a bit weird. – user22815 Jan 26 '16 at 0:37
  • I see what you mean. I'll read through the pg manual and maybe tweak this model. I don't know of a user equivalent of "SET LOCAL ROLE" but maybe I've just missed it. If I had to implement groups my first instinct would be to use the role hierarchy but we will see. Do you know of any existing web frameworks or open source web pages that use a similar approach? Similar in the sense of using the Postgres auth system rather than rolling their own? Sometimes reading through other implementations can be quite enlightening. – Max Murphy Jan 26 '16 at 0:56
  • Great answer, using database roles as application roles is a huge mistake. – Tulains Córdova Jan 26 '16 at 14:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.