Re: 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 AM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:

> 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 will probably answer some additional questions for you.
>
> On Sun, Dec 20, 2015 at 12:02 PM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
> wrote:
>
>>
>>
>> 2015-12-20 17:52 GMT+01:00 oleg yusim <olegyusim(at)gmail(dot)com>:
>>
>>> 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
>>> rather doesn't have one), or I just happened to miss configuration option?
>>>
>>>
>> any unbound process started as custom session means critical error - and
>> there are not any related known bug. Postgres hasn't any build option for
>> terminating session. If you need it - the pgbouncer has one or you can
>> terminate session via pg_terminate_backend and cron. Maybe somebody will
>> write background worker for this purpose. Internally, the system processes
>> and sessions has pretty strong relation in Postgres. - there cannot be
>> process without session and session without process.
>>
>> Pavel
>>
>>
>>> Thanks,
>>>
>>> Oleg
>>>
>>> On Sun, Dec 20, 2015 at 10:08 AM, Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com
>>> > wrote:
>>>
>>>> Hi
>>>>
>>>> 2015-12-20 16:16 GMT+01:00 oleg yusim <olegyusim(at)gmail(dot)com>:
>>>>
>>>>> 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 too).
>>>>>
>>>>> Does PostgreSQL perform this type of actions? If so, where are those
>>>>> Session IDs are stored, so I can verify it?
>>>>>
>>>>
>>>> Postgres is based on processes - for any session is created new process
>>>> when user is logged and this process is destroyed when user does logout.
>>>> Almost all data are in process memory only, but shared data related to
>>>> sessions are stored in shared memory - in array of PGPROC structures.
>>>> Postgres invalidates these data immediately when process is destroyed.
>>>> Search PGPROC in our code. Look to postmaster.c, where these operations are
>>>> described.
>>>>
>>>> What I know, there are not any other session data - so when process is
>>>> destroyed, then all is destroyed by o.s.
>>>>
>>>> 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.
>>>>
>>>> Regards
>>>>
>>>> Pavel
>>>>
>>>>
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Oleg
>>>>>
>>>>
>>>>
>>>
>>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>