Обсуждение: Session Identifiers

Hi

2015-12-20 16:16 GMT+01:00 oleg yusim <olegyusim@gmail.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

Hi

2015-12-20 16:16 GMT+01:00 oleg yusim <olegyusim@gmail.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

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 exact version of PostgreSQL and the O/S you are working with.

On Sun, Dec 20, 2015 at 11:08 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

Hi

2015-12-20 16:16 GMT+01:00 oleg yusim <olegyusim@gmail.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:

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.

2015-12-20 17:52 GMT+01:00 oleg yusim <olegyusim@gmail.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.stehule@gmail.com> wrote:

Hi

2015-12-20 16:16 GMT+01:00 oleg yusim <olegyusim@gmail.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

2015-12-20 17:52 GMT+01:00 oleg yusim <olegyusim@gmail.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.stehule@gmail.com> wrote:

Hi

2015-12-20 16:16 GMT+01:00 oleg yusim <olegyusim@gmail.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

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.stehule@gmail.com> wrote:

2015-12-20 17:52 GMT+01:00 oleg yusim <olegyusim@gmail.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.stehule@gmail.com> wrote:

Hi

2015-12-20 16:16 GMT+01:00 oleg yusim <olegyusim@gmail.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.

oleg yusim <olegyusim@gmail.com> 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 compliant algorithms, or anything of that sort)?

I don't think Postgres even has any concept that matches what you seem
to think a Session ID is.

If you're looking for communication security/integrity checking, that's
something we leave to other software such as SSL.

                        regards, tom lane


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

oleg yusim <olegyusim@gmail.com> 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 compliant algorithms, or anything of that sort)?

I don't think Postgres even has any concept that matches what you seem
to think a Session ID is.

If you're looking for communication security/integrity checking, that's
something we leave to other software such as SSL.

                        regards, tom lane

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 <tgl@sss.pgh.pa.us> wrote:

oleg yusim <olegyusim@gmail.com> 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 compliant algorithms, or anything of that sort)?

I don't think Postgres even has any concept that matches what you seem
to think a Session ID is.

If you're looking for communication security/integrity checking, that's
something we leave to other software such as SSL.

                        regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

2015-12-20 18:37 GMT+01:00 oleg yusim <olegyusim@gmail.com>:

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 case, here we would be dealing with Session IDs belonging to DB itself, not OpenSSL.

all necessary data are stored local in process memory. No session ID is required.

Pavel

 

Please, correct me if I'm wrong.

Thanks,

Oleg

On Sun, Dec 20, 2015 at 11:28 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

oleg yusim <olegyusim@gmail.com> 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 compliant algorithms, or anything of that sort)?

I don't think Postgres even has any concept that matches what you seem
to think a Session ID is.

If you're looking for communication security/integrity checking, that's
something we leave to other software such as SSL.

                        regards, tom lane

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 <tgl@sss.pgh.pa.us> wrote:

oleg yusim <olegyusim@gmail.com> 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 compliant algorithms, or anything of that sort)?

I don't think Postgres even has any concept that matches what you seem
to think a Session ID is.

If you're looking for communication security/integrity checking, that's
something we leave to other software such as SSL.

                        regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Oleg,

* oleg yusim (olegyusim@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 broken and session left hanging. For psql prompt case looks
> like pg_terminate_backend() would be the only solution.

Those settings aren't for controlling idle timeout of a connection.

pg_terminate_backend() will work and could be run out of a cronjob.

Thanks!

Stephen

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 add OR current_query = '<IDLE>' to kill stagnant connections.

On Mon, Dec 21, 2015 at 11:42 AM, Stephen Frost <sfrost@snowman.net> wrote:

Oleg,

* oleg yusim (olegyusim@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 broken and session left hanging. For psql prompt case looks
> like pg_terminate_backend() would be the only solution.

Those settings aren't for controlling idle timeout of a connection.

pg_terminate_backend() will work and could be run out of a cronjob.

Thanks!

Stephen

--

Melvin Davidson
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

On Tue, Dec 22, 2015 at 1:42 AM, Stephen Frost <sfrost@snowman.net> wrote:
> Oleg,
>
> * oleg yusim (olegyusim@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 broken and session left hanging. For psql prompt case looks
>> like pg_terminate_backend() would be the only solution.
>
> Those settings aren't for controlling idle timeout of a connection.
>
> pg_terminate_backend() will work and could be run out of a cronjob.

Or a background worker if you are using PG >= 9.3:
https://github.com/michaelpq/pg_plugins/tree/master/kill_idle
This has the advantage to not have the cronjob error out should the
server be stopped. That's less error handling to take care of at
frontend level.
--
Michael