This page is not created by, affiliated with, or supported by Slack Technologies, Inc.
2023-09-26
Channels
- # aleph (3)
- # announcements (6)
- # babashka (14)
- # beginners (8)
- # biff (16)
- # calva (4)
- # cider (7)
- # clj-kondo (8)
- # cljs-dev (26)
- # clojure (65)
- # clojure-austin (2)
- # clojure-brasil (1)
- # clojure-europe (35)
- # clojure-nl (4)
- # clojure-norway (45)
- # clojure-portugal (1)
- # clojure-uk (9)
- # clr (5)
- # community-development (6)
- # conjure (1)
- # cursive (3)
- # events (8)
- # fulcro (1)
- # honeysql (19)
- # hyperfiddle (31)
- # introduce-yourself (1)
- # lsp (7)
- # matcher-combinators (10)
- # off-topic (17)
- # practicalli (1)
- # ring (30)
- # shadow-cljs (6)
- # testing (2)
Hi, I hope this is the right place to ask, as it's not only about HoneySql. I'm working on a luminus project. I need to implement some sort of mechanism to ensure that all queries which touch certain tables include certain WHERE conditions, depending on the user for whom the query is being run. I don't want to have to pass the current user into each query, I'd rather set some sort of global variable, but only for the context of the users request. I'd love to hear recommendations of how to do this nicely, or recommendations for a different direction as well if you have one
Would using 'binding' be an appropriate solution?
In case it's for security, I'd first reach out to the tools available at the DB level. E.g. PostgreSQL has RLS (Row-Level Security) support built in. I don't know for sure but if you don't have separate DB users for each service user you can still set some transaction-level variable to the username and perhaps that can be used inside RLS as well.
It is indeed for security, but it is in this case necessary to implement this mechanism at the application level. I am using PlanetScale, which provides horizontally sharded MySql. They dispatch the query to the correct shard based on the filters in the query
PlanetScale does support subqueries, if that's what you're asking. (at least it's not in the list of things they don't support)
Another hypothesis: perhaps you can still set that transaction-level variable to the right username, use the variable in a view definition to always have that WHERE
statement, and then only use that view in the right contexts.
If that's a no-go for some reason, I myself would definitely prefer passing the username around. You can create a reasonable abstraction layer around it that combines the connection data (which, I presume, you're already passing around) with the username.
But if you don't like that approach, dynamic variables with binding
is also a solution, yes.
Interesting. I would be concerned that developers would forget to use that view. Also, wouldn't that not be sufficient for update/insert statements? On the other hand, if i have a dynamic variable which determines the behaviour of my system wide function for communicating with the database I could sleep (more) soundly
That being said I do want to avoid some sneaky dynamic global value
Personally, I’d wrap some part of the db interface in a new function that enforced this constraint and checked its input data for a username, throwing an exception if it’s not present. I’d just pass the username down the callstack rather than use some kind of fancy global, it’d probably hang out in some kind of data map that was working its way down the callstack anyways. I’ve long thought that despite the many advantages of the Clojure community’s minimalist approach to database interaction, one disadvantage is that there’s not a good place to encode constraints like “always add a where clause to this table”. With ORMs there’s usually some method on the table class you can use for that sort of thing. I have some half-finished designs in a doc somewhere for a functional approach to this sort of thing, but I never got around to actually implementing it.
> I would be concerned that developers would forget to use that view
The underlying table can be named in an inconvenient way that would e.g. preclude using keyword literals. Or just precede it with -
. But it assumes that you can change the names of those tables, of course.
> wouldn't that not be sufficient for update/insert statements?
PostgreSQL supports INSTEAD OF
triggers that let you define the behavior of data changing queries on a view.
Perhaps MySQL has something similar.
> I’d just pass the username down the callstack rather than use some kind of fancy global, it’d probably hang out in some kind of data map that was working its way down the callstack anyways.
Yeah, that's exactly what I meant by putting the username behind some abstraction, whatever it might be.
> there’s not a good place to encode constraints like “always add a where clause to this table”
There is - in your own wrapper for all the DB stuff. If you need this functionality, especially if you need it for many tables, it's somewhat natural to completely eschew working directly with HoneySQL and route everything via your wrapper with an API that's suitable for this kind of logic.
Even if an ORM has something like that that's also easy to use and will work in all sorts of nested scenarios, using it still wouldn't be worth it IMO. :)
Eh, I think there’s a way to do it while still maintaining the flexibility of using honeysql. The entire advantage of representing queries as data is that data is more composable. That doesn’t have to be thrown out the window to compose in one additional clause. The interesting part is where you compose that clause in…
I'm not talking about query representation, I'm talking about query construction. The latter can be done via a custom API, similar to how Honey's helpers do it, only in an app-specific way.
@U2FRKM4TW i didn't follow what the advantage of leaving honeySQL for this would be, can you please clarify? What I am considering is always querying the db through a function which accepts a honeySQL data structure, and for that function to update that honeySQL data structure based on the global context. what would be the advantage of implementing logic for working with the query string over working with the data representation of the query?
@U01EB0V3H39 regarding ORMs giving a nice way to register logic that should always be applied, how is that different from modifying a global variable which you don't like? In working with Laravel's ORM I have been put off by the magic of registering a 'scope' which magically gets applied. I would rather have a single place in my own code which registers some global value that i have full control over
Re having a fn update the query data structure: that’s exactly what I’d recommend doing, as long as you have a good way to make sure people don’t forget to use that fn. That’s the tricky part 😜
Yes I hear that. I don't know if it's possible to completely prevent developers from having to make the right decision, but I think it will be easier to form the habit of always using the same function (which has the db connection string pre-loaded by the way...) than to form the habit of thinking about how to query. What @U2FRKM4TW is suggesting regarding giving the tables inconvenient names is interesting, but I would like to have a convenient database to interact with from the sql console too.
To answer your other question, there are two different bits happening here:
1. How to get the username into the query-building fn
2. How to add the where
clause to the query, hopefully in a way that makes it hard for devs to forget to add it
My recommendation for 1 is just pass it down the callstack, probably in some data map with other stuff in it. There’ll be a fn somewhere that adds the where
clause, and possibly it should blow up if it’s not given a username. That’s definitely preferable to a global var.
For 2, it sounds like @U2FRKM4TW and I both agree you can have a fn that adds it in. If you wanted to go further to ensure devs don’t forget to use it, you could bundle a call to that fn into a replacement for one of the library fns (e.g. sql/format
) so devs literally can’t forget to use it. The design I was playing with did just that, but abstracted through some multimethods that made it easier to define such rules per-table (and query method, etc.) kind of like middleware for db interactions. That’s the thing I was saying I miss from ORMs, being able to hang additional constraint enforcement off of tables. You’d still have to pass the username in, but you wouldn’t be able to forget to apply the constraint. Anyways, you’ll have to determine whether you think that’s a good idea for your project or not.