5 ms·

The application I develop for work has its own, horrible, home-brew solution to update log/audit log, you cannot really call it system-versioned, so I read each

The application I develop for work has its own, horrible, home-brew solution to update log/audit log, you cannot really call it system-versioned, so I read each of these posts with interest. One thing that nearly all of them gloss over that logging the "who did it" isn't as simple as they make it out to be. In most cases, there's a web application or an API gateway or so that connects to the database and does most of the modifications, and only logging the database user of that application makes this piece of information really useless. What we (and I imagine many others, though clearly not OP) would need is a "do-business-as" user that maps not to the database users, but to in-application users, and then a mechanism to pass this user to the DB, and log it in addition to the database user. The web frameworks and ORMs I've worked with in the past don't seem to have any built-in support for something like this, which somehow annoys me much more than it probably should. Sorry for the rant :-)

silvestrov 3y ago
Agree. Sounds like INSERT/UPDATE/DELETE should be extended with a LOG SET webuser='abc', app=42, ... clause that provides an "UPDATE history_table SET webuser..." construct. I.e. the history tables that temporal_tables provides, but with added columns that can include more information, and if the columns are NOT NULL then UPDATE/DELETE/INSERT should fail.
doctor_eval 3y ago
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.)
cpursley 3y ago
I handle that with a “event_source” enum column [“database”, “web app”, “services_app”, etc] paired with user_id (thought fk won’t work for a lot of other folks cases). But I like the idea of doing that at database user level as another commenter posted.
maxlang 3y ago
Might it be easier to just add a `last_updated_by` column? Then passing the data to the db is as simple as adding it to the insert and it would be automatically stored in the history table as well without needing any special handling.
indigo945 3y ago
> What we (and I imagine many others, though clearly not OP) would need is a "do- > business-as" user that maps not to the database users, but to in-application > users, and then a mechanism to pass this user to the DB, and log it in addition > to the database user. This is kind of what Supabase does, for use in tandem with row-level security. It stores the user's JWT in a custom configuration parameter via set_config() when the database connection is established on behalf of the user, so you can see the token by calling get_config() with the config parameter name. Supabase also provides convenience functions to destructure the token and get the things you need (like auth.uid()), so it would be easy to fetch the user ID or user name in a trigger to log it.
javier2 3y ago
Yes, usually «who did it» is at least as important as the history itself
koromak 3y ago
I actually "solved" this problem, though not for logging, and other restrictions made me move away from it. In our case it was for Row Level Security, but essentially we get our userId from a JWT in our API (you should already have something like that), then before each DB command, run a SET LOCAL auth.claums.userId = 'user_id'; Most ORMs let you set a variable like this automatically when opening a connection, so you don't have to think about it too much. Then you can get as fancy as you want with RLS policies automatically attached to each table and validate the user based on a user table with capabilities. I have a feeling you could instead have the RLS rules automatically insert data instead, but I never tried it. It would have to be multiple updates though, perhaps a trigger/function. The problem is SET applies to a session, and pgbouncer doesn't make guarantees about the session for a particular statement. Your statement might receive an existing session with another user's ID, or it might receive no ID at all. Through AWS's RDS Proxy (beefed up pgbouncer AFAIK), the situation is slightly better, in that AWS will detect the modification to the session and 'pin' it to the current connection. Your data will be consistent, but pinned sessions can no longer be multiplexed, so you've basically removed the whole point of connection pooling to begin with. Ultimately, its probably safer to just manually add the user into your queries. Its not that much overhead.

silvestrov 3y ago

Agree. Sounds like INSERT/UPDATE/DELETE should be extended with a LOG SET webuser='abc', app=42, ... clause that provides an "UPDATE history_table SET webuser..." construct. I.e. the history tables that temporal_tables provides, but with added columns that can include more information, and if the columns are NOT NULL then UPDATE/DELETE/INSERT should fail.

doctor_eval 3y ago

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.)

cpursley 3y ago

I handle that with a “event_source” enum column [“database”, “web app”, “services_app”, etc] paired with user_id (thought fk won’t work for a lot of other folks cases). But I like the idea of doing that at database user level as another commenter posted.

maxlang 3y ago

Might it be easier to just add a `last_updated_by` column? Then passing the data to the db is as simple as adding it to the insert and it would be automatically stored in the history table as well without needing any special handling.

indigo945 3y ago

> What we (and I imagine many others, though clearly not OP) would need is a "do- > business-as" user that maps not to the database users, but to in-application > users, and then a mechanism to pass this user to the DB, and log it in addition > to the database user. This is kind of what Supabase does, for use in tandem with row-level security. It stores the user's JWT in a custom configuration parameter via set_config() when the database connection is established on behalf of the user, so you can see the token by calling get_config() with the config parameter name. Supabase also provides convenience functions to destructure the token and get the things you need (like auth.uid()), so it would be easy to fetch the user ID or user name in a trigger to log it.

javier2 3y ago

Yes, usually «who did it» is at least as important as the history itself

koromak 3y ago

I actually "solved" this problem, though not for logging, and other restrictions made me move away from it. In our case it was for Row Level Security, but essentially we get our userId from a JWT in our API (you should already have something like that), then before each DB command, run a SET LOCAL auth.claums.userId = 'user_id'; Most ORMs let you set a variable like this automatically when opening a connection, so you don't have to think about it too much. Then you can get as fancy as you want with RLS policies automatically attached to each table and validate the user based on a user table with capabilities. I have a feeling you could instead have the RLS rules automatically insert data instead, but I never tried it. It would have to be multiple updates though, perhaps a trigger/function. The problem is SET applies to a session, and pgbouncer doesn't make guarantees about the session for a particular statement. Your statement might receive an existing session with another user's ID, or it might receive no ID at all. Through AWS's RDS Proxy (beefed up pgbouncer AFAIK), the situation is slightly better, in that AWS will detect the modification to the session and 'pin' it to the current connection. Your data will be consistent, but pinned sessions can no longer be multiplexed, so you've basically removed the whole point of connection pooling to begin with. Ultimately, its probably safer to just manually add the user into your queries. Its not that much overhead.