4 ms·

I know this probably doesn't help you in your environment, but there's not really any reason that the application needs to have only a single role that it uses

I know this probably doesn't help you in your environment, but there's not really any reason that the application needs to have only a single role that it uses to connect to the database. I implemented logging all the way down to Postgres roles - in that each user of the application had their own database user. We did a SET ROLE at the beginning of each transaction, to switch the transaction to the application user's role. I don't remember the details, but there is a way to do this very safely. I'm about to do it again on a new project I'm working on. It's actually not that difficult to implement if you have a homogenous or flexible application environment; but it's certainly not common, although I don't know why (other than inertia).

pdimitar 3y ago
You could be right but you have to keep in mind that this just adds several hours -- days if we're talking database non-experts which indeed most of us programmers are not -- and it is just one item in an other huge bucket-list of TODO items. Having a single DB user reduces time expense while also adding much-needed simplicity. Application-level logging of "who did action X and when" are pretty easy to implement in every major web framework anyway.
- doctor_eval 3y ago
  Having implemented it, I don’t really agree. It adds a layer of security and auditability at the database level; logging works even if someone logs into the database from a cli and modifies it directly; and you can implement roles in such a way that prevent changes to the audit logs. None of this is possible at the application level. To me this is one of those things that looks hard until you look deeply. Yes, it does take more time to do things right. But not that much more. Honestly, if your application is bound to a database but you’re not a database expert, it’s a gap you should probably consider filling. So much effort is put into making the application layer do things that the database layer can already do, but the database is generally much more efficient and correct.
  - pdimitar 3y ago
    > To me this is one of those things that looks hard until you look deeply. Yes, it does take more time to do things right. But not that much more. Nope, it's much, MUCH more. Application frameworks are not made to switch DB users, they expect a singular DB config with which they work. Can it be changed? Sure. Will any stakeholder pay for it? Nope.
    - doctor_eval 3y ago
      You’re really overstating your case. There is nothing intrinsic to frameworks that makes this difficult. It’s basically a few lines of SQL code emitted as part of database transaction setup. The frameworks I’ve used have had the ability to register a transaction initialiser during pool setup. Other than that the framework doesn’t even have to know about it. Most of the “complexity” is on the database side, but it’s really not that hard. “Will any stakeholder pay for it?”. If your stakeholders are serious about application security, then I would expect so. (I’ll just add that I know of at least one product that does this - Postgraphile sets the database role up front for requests from the web. I seem to recall that Supabase does something similar.)
      - pdimitar 3y ago
        > “Will any stakeholder pay for it?”. If your stakeholders are serious about application security, then I would expect so. I want to live in your world where they apparently care. In mine they never did. 20+ years career. > You’re really overstating your case. There is nothing intrinsic to frameworks that makes this difficult. You could be right that I am overstating it, though you also shouldn't underestimate the "blessed" ways that web frameworks do stuff. In practically all that I worked on they just wanted you to chuck either a database URL or break it apart on host/port/user/pass/db-name and didn't make any effort to dynamically create / drop users as the application is creating and deleting them. > The frameworks I’ve used have had the ability to register a transaction initialiser during pool setup. As said above, I know it's doable. What I am saying is that nobody ever gave me the time to do it and with time I stopped caring. Turned out that putting the currently logged in users in global logger and telemetry contexts is good enough. ¯\_(ツ)_/¯
- chasd00 3y ago
  I see this “system user” all the time in large integrations projects. It is indeed simpler and many times works just fine but it sure is nice to have something like a “run-as” capability so the far side system can apply user specific security policies based on who is accessing the system vs only the system user profile/role.
  - pdimitar 3y ago
    The run-as thingy is done at application level. I didn't find it hard to plug into my maintained apps' logic to insert audit log entries like that (you have access to the currently signed-in user anyway).
chasd00 3y ago
I don’t do this kind of stuff anymore but if I did I would do something similar. Maybe add a before trigger that caused an error if no role was set. That way all connecting applications must set a user to use the db. Then put the database in charge of audit logs (who did what and when) and data access control with standard permissions and row level permissions. I’m sure you can balance that against an identity provider too.
- doctor_eval 3y ago
  Yes, exactly this. You can even do the user creation with default privileges as part of the transaction setup, if absolutely necessary (ie, create role if not exists…, set role …). Typically the audit is a table trigger so you can fail at that point, or even add a created_by column that looks up the role and raises an error if it’s not set (technically the role would always be set to the application’s db access role, so the check would be that the current role is a valid end user role). I mean there are more and less efficient ways to do it, but why have a separate context setting for “user” when the (Postgres, at least) database has such great support for roles. I do find it bewildering that our tools ignore this by default.

pdimitar 3y ago

You could be right but you have to keep in mind that this just adds several hours -- days if we're talking database non-experts which indeed most of us programmers are not -- and it is just one item in an other huge bucket-list of TODO items. Having a single DB user reduces time expense while also adding much-needed simplicity. Application-level logging of "who did action X and when" are pretty easy to implement in every major web framework anyway.

doctor_eval 3y ago
Having implemented it, I don’t really agree. It adds a layer of security and auditability at the database level; logging works even if someone logs into the database from a cli and modifies it directly; and you can implement roles in such a way that prevent changes to the audit logs. None of this is possible at the application level. To me this is one of those things that looks hard until you look deeply. Yes, it does take more time to do things right. But not that much more. Honestly, if your application is bound to a database but you’re not a database expert, it’s a gap you should probably consider filling. So much effort is put into making the application layer do things that the database layer can already do, but the database is generally much more efficient and correct.
- pdimitar 3y ago
  > To me this is one of those things that looks hard until you look deeply. Yes, it does take more time to do things right. But not that much more. Nope, it's much, MUCH more. Application frameworks are not made to switch DB users, they expect a singular DB config with which they work. Can it be changed? Sure. Will any stakeholder pay for it? Nope.
  - doctor_eval 3y ago
    You’re really overstating your case. There is nothing intrinsic to frameworks that makes this difficult. It’s basically a few lines of SQL code emitted as part of database transaction setup. The frameworks I’ve used have had the ability to register a transaction initialiser during pool setup. Other than that the framework doesn’t even have to know about it. Most of the “complexity” is on the database side, but it’s really not that hard. “Will any stakeholder pay for it?”. If your stakeholders are serious about application security, then I would expect so. (I’ll just add that I know of at least one product that does this - Postgraphile sets the database role up front for requests from the web. I seem to recall that Supabase does something similar.)
    - pdimitar 3y ago
      > “Will any stakeholder pay for it?”. If your stakeholders are serious about application security, then I would expect so. I want to live in your world where they apparently care. In mine they never did. 20+ years career. > You’re really overstating your case. There is nothing intrinsic to frameworks that makes this difficult. You could be right that I am overstating it, though you also shouldn't underestimate the "blessed" ways that web frameworks do stuff. In practically all that I worked on they just wanted you to chuck either a database URL or break it apart on host/port/user/pass/db-name and didn't make any effort to dynamically create / drop users as the application is creating and deleting them. > The frameworks I’ve used have had the ability to register a transaction initialiser during pool setup. As said above, I know it's doable. What I am saying is that nobody ever gave me the time to do it and with time I stopped caring. Turned out that putting the currently logged in users in global logger and telemetry contexts is good enough. ¯\_(ツ)_/¯
chasd00 3y ago
I see this “system user” all the time in large integrations projects. It is indeed simpler and many times works just fine but it sure is nice to have something like a “run-as” capability so the far side system can apply user specific security policies based on who is accessing the system vs only the system user profile/role.
- pdimitar 3y ago
  The run-as thingy is done at application level. I didn't find it hard to plug into my maintained apps' logic to insert audit log entries like that (you have access to the currently signed-in user anyway).

chasd00 3y ago

I don’t do this kind of stuff anymore but if I did I would do something similar. Maybe add a before trigger that caused an error if no role was set. That way all connecting applications must set a user to use the db. Then put the database in charge of audit logs (who did what and when) and data access control with standard permissions and row level permissions. I’m sure you can balance that against an identity provider too.

doctor_eval 3y ago
Yes, exactly this. You can even do the user creation with default privileges as part of the transaction setup, if absolutely necessary (ie, create role if not exists…, set role …). Typically the audit is a table trigger so you can fail at that point, or even add a created_by column that looks up the role and raises an error if it’s not set (technically the role would always be set to the application’s db access role, so the check would be that the current role is a valid end user role). I mean there are more and less efficient ways to do it, but why have a separate context setting for “user” when the (Postgres, at least) database has such great support for roles. I do find it bewildering that our tools ignore this by default.