Re: [GENERAL] Session Identifiers

Thanks Michael, you are right, that is a very good alternative solution. Oleg On Tue, Dec 22, 2015 at 6:27 AM, Michael Paquier wrote: > On Tue, Dec 22, 2015 at 1:42 AM, Stephen Frost wrote: > > Oleg, > > > > * oleg yusim (olegyu...@gmail.com)

Re: [GENERAL] Session Identifiers

On Tue, Dec 22, 2015 at 1:42 AM, Stephen Frost wrote: > Oleg, > > * oleg yusim (olegyu...@gmail.com) wrote: >> tcp_keepalives_idle = 900 >> tcp_keepalives_interval=0 >> tcp_keepalives_count=0 >> >> Doesn't terminate connection to database in 15 minutes of inactivity of >> psql

Re: [GENERAL] Session Identifiers

Melvin, I promised to let you know results of my experiment, so here is goes: tcp_keepalives_idle = 900 tcp_keepalives_interval=0 tcp_keepalives_count=0 Doesn't terminate connection to database in 15 minutes of inactivity of psql prompt. So, it looks like that would work only for case if

Re: [GENERAL] Session Identifiers

Melvin, Stephen, Thanks for your responses, guys. I think we can finally put this topic to the bed with that - I have satisfactory answer. For those who would be interested and would dig into this topic later on, here is fairly detailed explanation on how to use pg_terminate_backend in this case,

Re: [GENERAL] Session Identifiers

Oleg, * oleg yusim (olegyu...@gmail.com) wrote: > tcp_keepalives_idle = 900 > tcp_keepalives_interval=0 > tcp_keepalives_count=0 > > Doesn't terminate connection to database in 15 minutes of inactivity of > psql prompt. So, it looks like that would work only for case if network > connection is

Re: [GENERAL] Session Identifiers

Pursuant to Stehen's suggestion, I've attached a scripts that you can execeute from a cron. I wrote it when I was working for a previous company that used to have users that opened connections and transaction that did nothing for a long time. Just adjust the max_time for your liking. You can also

Re: [GENERAL] Session Identifiers

2015-12-20 21:00 GMT+03:00 Pavel Stehule : > > > 2015-12-20 18:56 GMT+01:00 Dmitry Igrishin : > >> >> >> 2015-12-20 19:44 GMT+03:00 Pavel Stehule : >> >>> >>> >>> 2015-12-20 17:30 GMT+01:00 Dmitry Igrishin :

Re: [GENERAL] Session Identifiers

2015-12-20 19:08 GMT+01:00 Dmitry Igrishin : > > > 2015-12-20 21:00 GMT+03:00 Pavel Stehule : > >> >> >> 2015-12-20 18:56 GMT+01:00 Dmitry Igrishin : >> >>> >>> >>> 2015-12-20 19:44 GMT+03:00 Pavel Stehule :

[GENERAL] Session Identifiers

Greetings! I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to the here is my questions: >From the security standpoint we have to assure that database invalidates session identifiers upon user logout or other session termination (timeout counts

Re: [GENERAL] Session Identifiers

Hi 2015-12-20 16:16 GMT+01:00 oleg yusim : > Greetings! > > I'm new to PostgreSQL, working on it from the point of view of Cyber > Security assessment. In regards to the here is my questions: > > From the security standpoint we have to assure that database invalidates >

Re: [GENERAL] Session Identifiers

Hi Melvin, Thank you very much, that logging option really helps (I need to give instructions, people, who are not very code literate should be capable of executing). And, point taken about exact version and enviornment - PostgreSQL 9.4.5, Linux box. Thanks, Oleg On Sun, Dec 20, 2015 at 10:19

Re: [GENERAL] Session Identifiers

Got it, thanks... Now, is it any protection in place currently against replacing Session ID (my understanding, it is kept in memory, belonging to the session process) or against guessing Session ID (i.e. is Session ID generated using FIPS 140-2 compliant algorithms, or anything of that sort)?

Re: [GENERAL] Session Identifiers

Regarding timeouts, PostgreSQL will use the system tcp_keepalives_* parms by default, but you can also configure it separately in postgresql.conf. http://www.postgresql.org/docs/9.4/static/runtime-config-connection.html I suggest you review all available parameters in the postgresql.conf, as it

Re: [GENERAL] Session Identifiers

Thanks you very much Melvin, once again, very useful. So, let me see if I got it right, following configuration should cause my database connection to terminate in 15 minutes, right? tcp_keepalives_idle = 900 tcp_keepalives_interval=1 tcp_keepalives_count=3 Oleg On Sun, Dec 20, 2015 at 11:14

Re: [GENERAL] Session Identifiers

oleg yusim writes: > Got it, thanks... Now, is it any protection in place currently against > replacing Session ID (my understanding, it is kept in memory, belonging to > the session process) or against guessing Session ID (i.e. is Session ID > generated using FIPS 140-2

Re: [GENERAL] Session Identifiers

Actually, I'm not an expert on the tcp_keepalives, but I believe the tcp_keepalives_count should be 1, otherwise it will take 45 minutes minutes to timeout. Then again, I could be wrong. On Sun, Dec 20, 2015 at 12:28 PM, Tom Lane wrote: > oleg yusim

Re: [GENERAL] Session Identifiers

Tom, I understand the idea that for external communication you rely on SSL. However, how about me opening psql prompt into the database directly from my Linux box, my db is installed at? I thought, it would be considered local connection and would not go through the SSL channels. If that is the

Re: [GENERAL] Session Identifiers

Thanks Melvin, Let me experiment with it for a bit. I will let you know results. Oleg On Sun, Dec 20, 2015 at 11:33 AM, Melvin Davidson wrote: > Actually, I'm not an expert on the tcp_keepalives, but I believe the > tcp_keepalives_count > should be 1, otherwise it will

Re: [GENERAL] Session Identifiers

2015-12-20 18:37 GMT+01:00 oleg yusim : > Tom, > > I understand the idea that for external communication you rely on SSL. > However, how about me opening psql prompt into the database directly from > my Linux box, my db is installed at? I thought, it would be considered >

Re: [GENERAL] Session Identifiers

So Pavel, are are saying there is no such thing as Session ID in PostgreSQL DB at all? Everything is tight to the process, session is accociated with, so in essence pid is session id? Oleg On Sun, Dec 20, 2015 at 11:40 AM, Pavel Stehule wrote: > > > 2015-12-20 18:37

Re: [GENERAL] Session Identifiers

2015-12-20 18:45 GMT+01:00 oleg yusim : > So Pavel, are are saying there is no such thing as Session ID in > PostgreSQL DB at all? Everything is tight to the process, session is > accociated with, so in essence pid is session id? > There is backendId and processid, but these

Re: [GENERAL] Session Identifiers

2015-12-20 19:44 GMT+03:00 Pavel Stehule : > > > 2015-12-20 17:30 GMT+01:00 Dmitry Igrishin : > >> Can be totally different if you use some connection pooler like pgpool or >>> pgbouncer - these applications can reuse Postgres server sessions for more

Re: [GENERAL] Session Identifiers

2015-12-20 18:56 GMT+01:00 Dmitry Igrishin : > > > 2015-12-20 19:44 GMT+03:00 Pavel Stehule : > >> >> >> 2015-12-20 17:30 GMT+01:00 Dmitry Igrishin : >> >>> Can be totally different if you use some connection pooler like pgpool

Re: [GENERAL] Session Identifiers

PostgreSQL does not "store" the session_id per se in any system catalogs/tables, however, you can configure the log_line_prefix in postgresql.conf to record it for each connection. It will then be stored in the postgresql log file. Please not that in the future, it is always helpful to provide the

Re: [GENERAL] Session Identifiers

On 12/20/2015 09:16 AM, oleg yusim wrote: Greetings! I'm new to PostgreSQL, working on it from the point of view of Cyber Security assessment. In regards to the here is my questions: From the security standpoint we have to assure that database invalidates session identifiers upon user

Re: [GENERAL] Session Identifiers

> > Can be totally different if you use some connection pooler like pgpool or > pgbouncer - these applications can reuse Postgres server sessions for more > user sessions. > BTW, AFAIK, it's not possible to change the session authentication information by using SET SESSION AUTHORIZATION [1] if the

Re: [GENERAL] Session Identifiers

2015-12-20 17:30 GMT+01:00 Dmitry Igrishin : > Can be totally different if you use some connection pooler like pgpool or >> pgbouncer - these applications can reuse Postgres server sessions for more >> user sessions. >> > BTW, AFAIK, it's not possible to change the session

Re: [GENERAL] Session Identifiers

Hi Pavel, Thanks, for your response, it helps. Now, from my observations (PostgreSQL 9.4.5, installed on Linux box), if I enter psql prompt at my ssh to the box session and leave it open like that, it doesn't time out. Is it really a case? Session to PostgreSQL DB doesn't terminate on timeout (or

Re: [GENERAL] Session Identifiers

2015-12-20 17:52 GMT+01:00 oleg yusim : > Hi Pavel, > > Thanks, for your response, it helps. Now, from my observations (PostgreSQL > 9.4.5, installed on Linux box), if I enter psql prompt at my ssh to the box > session and leave it open like that, it doesn't time out. Is it

Re: [GENERAL] Session Identifiers

On Sun, Dec 20, 2015 at 11:25:45AM -0600, oleg yusim wrote: > Thanks you very much Melvin, once again, very useful. So, let me see if I > got it right, following configuration should cause my database connection > to terminate in 15 minutes, right? > > tcp_keepalives_idle = 900 >

Re: [GENERAL] Session Identifiers

Hi Andrew, Exactly! Vulnerability is the direct analogy of one with Unix shell. The way we generally deal with Unix shell vulnerability, we configure the shell to terminate on its own if timeout was exceeded. The question here is, can we configure psql client to behave the same? Thanks, Oleg