postgres - run commands on login

Question

I would like to run a small logon script on my postgres server. If a user with a certain username connects, then I want to check the app name of the session, and if it contains a certain value I want to set idle_in_transaction_session_timeout for that session.

I fail to find where to put logon scripts/commands in postgres. Any ideas? TY!

From outside the other user's session, you can only configure default settings that will be applied next time they open a new session. You'd have to hijack their session in order to alter the settings that are already in place in their ongoing session. Logon/startup/init scripts are usually configurable on client end. — Zegarek
– Zegarek, Commented Feb 13, 2023 at 12:02

Frank Heikens · Accepted Answer · 2023-02-13 11:40:14Z

1

You can configure these settings on role level:

ALTER ROLE _your_user_name_ 
SET idle_in_transaction_session_timeout TO 3600000; -- one hour

Each time this user connects to your database, these settings will be applied. There is no need for an additional command after login. Other settings can be set on database level as well.

answered Feb 13, 2023 at 11:40

Frank Heikens

129k26 gold badges157 silver badges153 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Peter Over a year ago

yes thank you but i want different idle_in_transaction_session_timeout for the same role, depending on app name of the connection. we use the same role both manually and for the system. we want manual users to have a shorter timeout. ty

Frank Heikens Over a year ago

Advice: Use different roles

Bergi Over a year ago

@Peter a) just don't do that, use different roles b) set the parameters in the connection settings of the application or user tool, not in the database

Peter Over a year ago

thank you @FrankHeikens and bergi - I agree that we should use different roles. But this seems very difficult to impossible with postgres as only the owner of objects can modify them. so the manual user will not be able to create and drop objects of the system user and vice versa. that is why we gave up having different users doing DDL to the same objects in postgres.

Peter Over a year ago

I have opened stackoverflow.com/questions/75445136/… to elaborate on this follow up problem with the different roles

Collectives™ on Stack Overflow

postgres - run commands on login

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related