Thread: Transaction prevention
Hi,
If I have a user that is restricted to select access (only) on a single table, is there any way to prevent that user from starting a transaction?
The reason for the question is that the select-only user can block another session trying to run an alter table on that table if the select-only user runs their query in a transaction.
This happened to us recently where a migration script that was being run during a ci deployment 'hung' until the select-only user happened to disconnect. I'm wondering if we need to terminate any sessions from select-only users at the start of the migration, or if there is a better way.
Thanks,
Steve
Steve Baldwin <steve.baldwin@gmail.com> writes:
> If I have a user that is restricted to select access (only) on a single
> table, is there any way to prevent that user from starting a transaction?
No, but maybe setting statement_timeout and/or
idle_in_transaction_session_timeout for that user would be helpful
(cf ALTER USER ... SET ...).
regards, tom lane
Thanks Tom. The idle_in_transaction_session_timeout could work well, but it seems to be just a default that can be overridden by a user post-login (or am I missing something?). I'm thinking of setting lock_timeout as part of the migration process so it will fail if it is unable to obtain a lock in a 'reasonable' amount of time. I wonder what other folks do?
Cheers,
Steve
On Thu, Jul 30, 2020 at 10:23 AM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Steve Baldwin <steve.baldwin@gmail.com> writes:
> If I have a user that is restricted to select access (only) on a single
> table, is there any way to prevent that user from starting a transaction?
No, but maybe setting statement_timeout and/or
idle_in_transaction_session_timeout for that user would be helpful
(cf ALTER USER ... SET ...).
regards, tom lane
Steve Baldwin <steve.baldwin@gmail.com> writes:
> Thanks Tom. The idle_in_transaction_session_timeout could work well, but it
> seems to be just a default that can be overridden by a user post-login (or
> am I missing something?).
It is that, but if you have an actively malicious user then you need to
keep them from issuing SQL directly at all. There are far too many ways
to cause effective denial-of-service, eg a single query that runs
"forever".
> I'm thinking of setting lock_timeout as part of
> the migration process so it will fail if it is unable to obtain a lock in a
> 'reasonable' amount of time. I wonder what other folks do?
If you'd rather fail the migration process, sure.
regards, tom lane