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 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/CADGZSSd9UoN7KFhfY-noxkwxbY_oO7N6uwrBN3VSv9FOW5Qg%3DQ%40mail.gmail.com.
