CODEWITHSUNDEEP
postgresql
Oliver Rice
Oliver Rice

Reputation: 893

Setting statement_timeout on a role correctly in postgres

Assigning a statement_timeout to a role in postgres does not appear to persist the setting.

Example:

-- Set the statement_timeout on a role
alter role api_user set statement_timeout = '500ms';

-- Start a transaction
begin;
    -- Assume the role
    set role api_user;

    -- Display statement_timeout setting
    show statement_timeout;
    -- |--------------------------|
    -- | statement_timeout (text) |
    -- |--------------------------|
    -- | 0                        |
    -- |--------------------------|

Since the statement_timeout for api_user was set to 500ms I expected 500ms as the output. Instead, it shows the default statement_timeout of 0, or unlimited.

What am I missing here?

Upvotes: 1

Views: 1183

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 246483

The session is persisted, but assuming the role with SET ROLE or SET SESSION AUTHORIZATION doesn't set the parameter. You have to log into the database as role api_user for that.

Upvotes: 2

Related Questions