8

I have multiple users in Postgres. I would like to set up different statement timeouts for different users.
Eg: Guest 5 minutes and Admin 10 minutes.

Is it possible in Postgres 11.11?

2 Answers 2

15

Yes, Postgres allows settings per user or even per database and user, including statement_timeout:

ALTER ROLE foo SET statement_timeout = 12345;  -- milliseconds

Related:

To see the currently active setting for the session:

SHOW statement_timeout;

Or get more details from the view pg_settings (including how it was set):

SELECT *
FROM   pg_settings
WHERE  name = 'statement_timeout';

To check current settings for a role:

SELECT rolname, rolconfig
FROM   pg_roles
WHERE  rolname = 'foo';

rolconfig is an array, unnest it to get one setting per row:

SELECT rolname, unnest(rolconfig) AS setting
FROM   pg_roles
WHERE  rolname = 'foo';
3
  • This does not prevent the user from changing the statement_timeout back though, which makes it kinda useless for anything security related
    – phiresky
    May 31, 2022 at 17:30
  • Is this also working for AWS RDS Postgresql? Feb 21, 2023 at 17:28
  • @phiresky I think it does. superuser can set statement_timeout for other user. other user can change it, but it won't make effect. the statement_timeout will set as per superuser.
    – jian
    Apr 4, 2023 at 3:22
1

Found that we can set the timeout on Role.

ALTER ROLE guest SET statement_timeout='5min';
ALTER ROLE admin SET statement_timeout='10min';
2
  • 2
    Use min or ms, because m was deprecated. Jan 17, 2022 at 15:53
  • 1
    Tested: alter role xxx set idle_in_transaction_session_timeout = '90m'; ERROR: invalid value for parameter "idle_in_transaction_session_timeout": "90m" HINT: Valid units for this parameter are "us", "ms", "s", "min", "h", and "d". Jan 17, 2022 at 15:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.