Teradata has a nice feature called “Query Bands” that allow the application to assign arbitrary key/value metadata to a session or transaction. We’ve used this to great success to keep track of which batch jobs were responsible for particular queries. An administrator can even use the workload manager to dynamically assign queries to different workload profiles.
This sounds like a more structured solution like the query comments mentioned above by @viraptor.
From what I know: MySQL is also working on query attributes. Similar to connection attributes, but for queries. This comes very close to the Query Bands feature (without the workload manager part).
An alternative approach for extra information in SQL: use a query comment. There are a few libraries doing it - for example “marginalia” Ruby gem for ActiveRecord will append something like “– controller: Foo action: bar app: frobnicator host: baz.net” to the end. That makes it immediately clear where to look for the problem when checking the running query list or slow query logs.
I was aware of the trick with the SQL Query comment. At trivago, we also use this a lot. I was not aware of any library explicitly doing this. Thanks for the hint! For all others, here you can find the library basecamp/marginalia.
I can second the usefulness of this trick, especially during an audit, replication, and slow query logs. Similar to connection naming, it is close to zero engineering effort. Assuming here that you craft your SQL queries by hand rather than using a DBAL. In the case of a DBAL, this might be a bit more tricky to sneak in SQL comments.
I agree, but we just do it by username, every app should have it’s own access controls, so you need a diff. user for each app anyway.
i.e. we would just make the username: currency-conversion-app or stock-exchange-rate-importer
for multiple processes for a given app, again each process generally wants it’s own ACL’s, so usernames might be currency-conversion-app-web and currency-conversion-app-fetcher` or something.
no extra training required, teaching devops how to name a connection. But it’s neat that PG and friends let you do that!
The only upside to naming the connection would be if you put in the remote host and PID# maybe, in case you have more than 1 webserver, but you would basically get that information anyway based on the source IP. Is it easier to get that info from the source IP or from the connection name? shrugs For all other programs, you are pretty much forced to get it from the source IP. So lowest common denominator wins?
I agree with you, what you describe should be the standard, and it has even the security benefits. Using your own username is also the workaround, once a system doesn’t support connection naming at all. However, worked in several companies has seen many more systems, I can tell you that this is even often considered as “overhead”.. Why? Because here often Dev and Ops don’t play together. Dev is adjusting their DB calls. Ops want to avoid adjusting the permission of the user every release, …. You know the game. Sad, but true.
Admitting that connection naming is not implemented in these environments either.
But I am with you. I would even go one step further and advocate for every application document their DB commands. Like currency-conversion-app-web is doing only SELECT and INSERT. This would enable (dev-)ops to limit the permission of the single user to exactly these operations. Rarely, I have seen this, even in Open Source Software.
I share your happiness. The design of the database is on one side. The design of the application is another. I have seen many systems that have several different use cases for the database connection (like @zie describes with -web and -fetcher. However, I have not seen different database connections to the same database with different users. Often they share the same database connection pool.
Teradata has a nice feature called “Query Bands” that allow the application to assign arbitrary key/value metadata to a session or transaction. We’ve used this to great success to keep track of which batch jobs were responsible for particular queries. An administrator can even use the workload manager to dynamically assign queries to different workload profiles.
That is neat!
This sounds like a more structured solution like the query comments mentioned above by @viraptor.
From what I know: MySQL is also working on query attributes. Similar to connection attributes, but for queries. This comes very close to the Query Bands feature (without the workload manager part).
An alternative approach for extra information in SQL: use a query comment. There are a few libraries doing it - for example “marginalia” Ruby gem for ActiveRecord will append something like “– controller: Foo action: bar app: frobnicator host: baz.net” to the end. That makes it immediately clear where to look for the problem when checking the running query list or slow query logs.
I was aware of the trick with the SQL Query comment. At trivago, we also use this a lot. I was not aware of any library explicitly doing this. Thanks for the hint! For all others, here you can find the library basecamp/marginalia.
I can second the usefulness of this trick, especially during an audit, replication, and slow query logs. Similar to connection naming, it is close to zero engineering effort. Assuming here that you craft your SQL queries by hand rather than using a DBAL. In the case of a DBAL, this might be a bit more tricky to sneak in SQL comments.
I agree, but we just do it by username, every app should have it’s own access controls, so you need a diff. user for each app anyway.
i.e. we would just make the username:
currency-conversion-apporstock-exchange-rate-importerfor multiple processes for a given app, again each process generally wants it’s own ACL’s, so usernames might be
currency-conversion-app-web andcurrency-conversion-app-fetcher` or something.no extra training required, teaching devops how to name a connection. But it’s neat that PG and friends let you do that!
The only upside to naming the connection would be if you put in the remote host and PID# maybe, in case you have more than 1 webserver, but you would basically get that information anyway based on the source IP. Is it easier to get that info from the source IP or from the connection name? shrugs For all other programs, you are pretty much forced to get it from the source IP. So lowest common denominator wins?
Author from the article here.
I agree with you, what you describe should be the standard, and it has even the security benefits. Using your own username is also the workaround, once a system doesn’t support connection naming at all. However, worked in several companies has seen many more systems, I can tell you that this is even often considered as “overhead”.. Why? Because here often Dev and Ops don’t play together. Dev is adjusting their DB calls. Ops want to avoid adjusting the permission of the user every release, …. You know the game. Sad, but true.
Admitting that connection naming is not implemented in these environments either.
But I am with you. I would even go one step further and advocate for every application document their DB commands. Like
currency-conversion-app-webis doing onlySELECTandINSERT. This would enable (dev-)ops to limit the permission of the single user to exactly these operations. Rarely, I have seen this, even in Open Source Software.In an ideal world, people would design their DBs to support multiple users with ACLs correctly used between them.
I am happy that so many systems let us cope with not living in an ideal world, by providing an alternative label which can be client-supplied.
Author here.
I share your happiness. The design of the database is on one side. The design of the application is another. I have seen many systems that have several different use cases for the database connection (like @zie describes with
-weband-fetcher. However, I have not seen different database connections to the same database with different users. Often they share the same database connection pool.