Ok, so jumping back to this. So I can control my dev database, but not the real one used in auto testing.
If I wanted to try to go the route of setting `SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;` Is there a way to use have that (or something else) run on all connections in pool? I see there is a `after_connect` method, but looks like it is depricated and gone in Sequel 6. Is there a replacement? or way to make something run on every connection in pool? ideally at the time it is created...but for my needs, I think that isn't necessary . On Wednesday, July 12, 2023 at 3:58:47 AM UTC Grant Schoep wrote: > Thanks, that was kind of what I was thinking, Wasn't sure if that run > command ran on each connection(I was thinking it didn't) > > So, the other method we thought of is what you mentioned using different > useraccounts. It doesn't work perfectly for us, at least with out current > automated test, we can't play tricks with the PG and user accounts, BUT in > our locally ran testing, we can. > > On Mon, Jul 10, 2023 at 5:38 PM Jeremy Evans <[email protected]> wrote: > >> On Mon, Jul 10, 2023 at 3:40 PM [email protected] <[email protected]> >> wrote: >> >>> So I have a application that makes two different PG connection pools, >>> one to a read-only server, and one to a read-write server. >>> All was going great, until I accidentally use my RO connection for a >>> write. >>> >>> Sadly, all my automated testing didn't catch it, as we just have a RW >>> server automated test environment.(ok the code gets two hostnames, but they >>> are the same. >>> >>> We just have those in our production env (I'd love it to match, but... >>> costs money) >>> >>> So what I am thinking about doing... when I setup my RO connection I run >>> `SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;` >>> >>> i.e I make my RO guy like this >>> ``` >>> config = { >>> host: 'some_ro_server', >>> port: '1234' >>> ... >>> } >>> db_ro = Sequel.postgres(config) >>> db_ro.run('SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;') >>> ``` >>> >>> But, after working with the Sequel gem for so long typing in direct >>> stuff like this always seem so, non sequel.. >>> >>> Anyways, is there an option when setting up my connection, to make it >>> read-only, (i.e. do this command) >>> >>> In this document >>> >>> https://github.com/jeremyevans/sequel/blob/master/doc/opening_databases.rdoc >>> >>> I see a `:readonly ` flag but that looks like it sis for `sqlite` only >>> <https://github.com/jeremyevans/sequel/blob/master/doc/opening_databases.rdoc#label-sqlite> >>> Am I missing anything? >>> >> >> Sequel doesn't natively support what you are doing. The approach with >> `run` is not safe as it is only run on a single connection, when it needs >> to be run on every connection. >> >> The best way to do what you want is to have a separate database user with >> only read access and no write access, and use that user when connecting. >> It's also possible to use Sequel's sharding with the same host but two >> separate database users for a similar effect. >> >> Thanks, >> Jeremy >> >> -- >> You received this message because you are subscribed to a topic in the >> Google Groups "sequel-talk" group. >> To unsubscribe from this topic, visit >> https://groups.google.com/d/topic/sequel-talk/nCuzOABU540/unsubscribe. >> To unsubscribe from this group and all its topics, send an email to >> [email protected]. >> To view this discussion on the web visit >> https://groups.google.com/d/msgid/sequel-talk/CADGZSSd9UoN7KFhfY-noxkwxbY_oO7N6uwrBN3VSv9FOW5Qg%3DQ%40mail.gmail.com >> >> <https://groups.google.com/d/msgid/sequel-talk/CADGZSSd9UoN7KFhfY-noxkwxbY_oO7N6uwrBN3VSv9FOW5Qg%3DQ%40mail.gmail.com?utm_medium=email&utm_source=footer> >> . >> > -- You received this message because you are subscribed to the Google Groups "sequel-talk" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/sequel-talk/f5c521d6-00ce-4e04-8b9f-2c48e44a9d75n%40googlegroups.com.
