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:
Show quoted text
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