[Bug] For security reasons, psql requires password verification before executing SQL after the session times out and disconnects

[z17033]

Apache Cloudberry version

master

What happened

psql did not vertify the user password when executing SQL after disconnection due to session timeout. There is a security risk.

What you think should happen instead

For security reasons, psql requires password verification before executing SQL after the session times out and disconnects

How to reproduce

Step 1:
set idle_sessin_timeout = 5s;
Step 2:
restart cluster
Step 3:
Using psql login database and have the session remain idle for more than 5 seconds.

Operating System

OS-independent

Anything else

0f8eef5

Are you willing to submit PR?

• Yes, I am willing to submit a PR!

Code of Conduct

• I agree to follow this project's Code of Conduct.

[github-actions]

Hey, @z17033 welcome!🎊 Thanks for taking the time to point this out.🙌

[fanfuxiaoran]

hi @z17033 ， thanks for submitting the bug.
When the connection is closed by the server, psql will try to reset the connection by closing and creating a new connection using the old connection params. This is kind of a optimization of psql.
Could you give more explanation about what security problems this can bring?

[z17033]

hi @z17033 ， thanks for submitting the bug. When the connection is closed by the server, psql will try to reset the connection by closing and creating a new connection using the old connection params. This is kind of a optimization of psql. Could you give more explanation about what security problems this can bring?

@fanfuxiaoran Thank you for your reply.
First, while it is indeed possible to configure trust during user login, but it is generally disabled in our commercial environments. Consider the following scenario:

Step 1: User gpadmin is a superuser with the password Passw0rd@_ and must use a password to log in. To prevent long idle sessions, idle_session_timeout is set to 60 seconds.

Step 2: On the first login (psql -U gpadmin), the user needs to enter the password to log in, which is expected behavior.

Step 3: After logging in, if the user is idle for more than 1 minute and then tries to execute a query, a new session connection is automatically created without requiring password input. I believe this is not expected behavior.

postgres=# select 1;
FATAL: terminating connection due to idle-session timeout
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
seasql=#

A password prompt should appear when a new connection is created correctly.

postgres=# select 1;
FATAL: terminating connection due to idle-session timeout
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset:
Password for user gpadmin:

[avamingli]

FATAL: terminating connection due to idle-session timeout
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset:

Hi, this should be a crash , is it on our CBDB? You could use select version() to show the info.
And could you provide the coredump file for us to see the crash?

[avamingli]

FATAL: terminating connection due to idle-session timeout
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset:

Hi, this should be a crash , is it on our CBDB? You could use select version() to show the info. And could you provide the coredump file for us to see the crash?

Hi, @z17033 ,

After a think, I agree with @fanfuxiaoran this is not a bug.

This is kind of a optimization of psql

However, if you are convinced about that, my suggestion is that you could submit a patch to Postgres https://www.postgresql.org/list/pgsql-bugs/

Psql codes come from Postgres upstream, we follow that.
If there were some bugs, they should be fixed there and then cherry-pick to CBDB.

[z17033]

@avamingli Alright, I'll give it a try, thank you for your reply.