Postgres Pro
Downloads
Home   >   mailing lists

Thread: Session Identifiers

Session Identifiers

From
oleg yusim
Date:
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?

Thanks,

Oleg

Re: Session Identifiers

From
Pavel Stehule
Date:
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

Re: Session Identifiers

From
Melvin Davidson
Date:
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:

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.

Re: Session Identifiers

From
Andy Colson
Date:
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
myquestions: 
>
>  From the security standpoint we have to assure that database invalidates session identifiers upon user logout or
othersession 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?
>
> Thanks,
>
> Oleg

Are you talking about a website session?  Does this website session happen to be stored in PG?

-Andy

Re: Session Identifiers

From
Dmitry Igrishin
Date:
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 current user is not a superuser.
But it would be very nice to have a feature to change the session authorization
of current user even without superuser's privilege by supplying a password of
the user specified in SET SESSION AUTHORIZATION. This feature allows
to use PostgreSQL's native privileges via connection pools -- i.e. without
needs to open a dedicated connection for authenticated user. Is it possible
to implement it?

Re: Session Identifiers

From
Pavel Stehule
Date:


2015-12-20 17:30 GMT+01:00 Dmitry Igrishin <dmitigr@gmail.com>:
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 current user is not a superuser.
But it would be very nice to have a feature to change the session authorization
of current user even without superuser's privilege by supplying a password of
the user specified in SET SESSION AUTHORIZATION. This feature allows
to use PostgreSQL's native privileges via connection pools -- i.e. without
needs to open a dedicated connection for authenticated user. Is it possible
to implement it?

there is a workaround with security definer function and SET role TO ?

Pavel
 

Re: Session Identifiers

From
oleg yusim
Date:
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?

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


Re: Session Identifiers

From
oleg yusim
Date:
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 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
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.

Re: Session Identifiers

From
Pavel Stehule
Date:


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



Re: Session Identifiers

From
oleg yusim
Date:
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)?

Oleg

On Sun, Dec 20, 2015 at 11:02 AM, 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




Re: Session Identifiers

From
Melvin Davidson
Date:
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.

Re: Session Identifiers

From
oleg yusim
Date:
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@gmail.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.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.

Re: Session Identifiers

From
Tom Lane
Date:
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

Re: Session Identifiers

From
Melvin Davidson
Date:
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.

Re: Session Identifiers

From
oleg yusim
Date:
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. 

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

Re: Session Identifiers

From
oleg yusim
Date:
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 <melvin6925@gmail.com> wrote:
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.

Re: Session Identifiers

From
Pavel Stehule
Date:


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


Re: Session Identifiers

From
oleg yusim
Date:
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 <pavel.stehule@gmail.com> wrote:


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



Re: Session Identifiers

From
Pavel Stehule
Date:


2015-12-20 18:45 GMT+01:00 oleg yusim <olegyusim@gmail.com>:
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 id are valid only for one session, and after logout these ids are invalid - usually they are used for fast access to static shared arrays - PGPROC array and similar - mainly for info about snapshots and locks. These arrays are static - new sessions immediately reuse space after destroyed sessions.

But there are not any info comparable with session id on web applications. It is significantly different architecture - fast, simply and different.

Pavel
 

Oleg

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


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




Re: Session Identifiers

From
Dmitry Igrishin
Date:


2015-12-20 19:44 GMT+03:00 Pavel Stehule <pavel.stehule@gmail.com>:


2015-12-20 17:30 GMT+01:00 Dmitry Igrishin <dmitigr@gmail.com>:
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 current user is not a superuser.
But it would be very nice to have a feature to change the session authorization
of current user even without superuser's privilege by supplying a password of
the user specified in SET SESSION AUTHORIZATION. This feature allows
to use PostgreSQL's native privileges via connection pools -- i.e. without
needs to open a dedicated connection for authenticated user. Is it possible
to implement it?

there is a workaround with security definer function and SET role TO ?
No there isn't. According to [2] "SET ROLE cannot be used within SECURITY
DEFINER function". Furthermore, SET ROLE doesn't affects the session_user's
function result which can be used by a logic.


--
// Dmitry.

Re: Session Identifiers

From
Pavel Stehule
Date:


2015-12-20 18:56 GMT+01:00 Dmitry Igrishin <dmitigr@gmail.com>:


2015-12-20 19:44 GMT+03:00 Pavel Stehule <pavel.stehule@gmail.com>:


2015-12-20 17:30 GMT+01:00 Dmitry Igrishin <dmitigr@gmail.com>:
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 current user is not a superuser.
But it would be very nice to have a feature to change the session authorization
of current user even without superuser's privilege by supplying a password of
the user specified in SET SESSION AUTHORIZATION. This feature allows
to use PostgreSQL's native privileges via connection pools -- i.e. without
needs to open a dedicated connection for authenticated user. Is it possible
to implement it?

there is a workaround with security definer function and SET role TO ?
No there isn't. According to [2] "SET ROLE cannot be used within SECURITY
DEFINER function". Furthermore, SET ROLE doesn't affects the session_user's
function result which can be used by a logic.

you want to modify result of session_user? It's looks like possible security issue to me.

postgres=# create role tom ;
CREATE ROLE
Time: 91.461 ms
postgres=# select current_user;
┌──────────────┐
│ current_user │
╞══════════════╡
│ pavel        │
└──────────────┘
(1 row)

Time: 15.692 ms
postgres=# set role tom;
SET
Time: 0.609 ms
postgres=> select current_user;
┌──────────────┐
│ current_user │
╞══════════════╡
│ tom          │
└──────────────┘
(1 row)


 

Re: Session Identifiers

From
Dmitry Igrishin
Date:


2015-12-20 21:00 GMT+03:00 Pavel Stehule <pavel.stehule@gmail.com>:


2015-12-20 18:56 GMT+01:00 Dmitry Igrishin <dmitigr@gmail.com>:


2015-12-20 19:44 GMT+03:00 Pavel Stehule <pavel.stehule@gmail.com>:


2015-12-20 17:30 GMT+01:00 Dmitry Igrishin <dmitigr@gmail.com>:
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 current user is not a superuser.
But it would be very nice to have a feature to change the session authorization
of current user even without superuser's privilege by supplying a password of
the user specified in SET SESSION AUTHORIZATION. This feature allows
to use PostgreSQL's native privileges via connection pools -- i.e. without
needs to open a dedicated connection for authenticated user. Is it possible
to implement it?

there is a workaround with security definer function and SET role TO ?
No there isn't. According to [2] "SET ROLE cannot be used within SECURITY
DEFINER function". Furthermore, SET ROLE doesn't affects the session_user's
function result which can be used by a logic.

you want to modify result of session_user? It's looks like possible security issue to me.
I want to be able to change the session user  without creating the new connection, like this
(pseudo REPL):
notsuperuser > SELECT current_user, session_user;
notsuperuser notsuperuser
notsuperuser > SET SESSION AUTHORIZATION notsuperuser2 PASSWORD 'password_of_notsuperuser2';
SET SESSION AUTHORIZATION
notsuperuser2 > SELECT current_user, session_user;
notsuperuser2 notsuperuser2

I don't see any security issue here.


postgres=# create role tom ;
CREATE ROLE
Time: 91.461 ms
postgres=# select current_user;
┌──────────────┐
│ current_user │
╞══════════════╡
│ pavel        │
└──────────────┘
(1 row)

Time: 15.692 ms
postgres=# set role tom;
SET
Time: 0.609 ms
postgres=> select current_user;
┌──────────────┐
│ current_user │
╞══════════════╡
│ tom          │
└──────────────┘
(1 row)


 




--
// Dmitry.

Re: Session Identifiers

From
Pavel Stehule
Date:


2015-12-20 19:08 GMT+01:00 Dmitry Igrishin <dmitigr@gmail.com>:


2015-12-20 21:00 GMT+03:00 Pavel Stehule <pavel.stehule@gmail.com>:


2015-12-20 18:56 GMT+01:00 Dmitry Igrishin <dmitigr@gmail.com>:


2015-12-20 19:44 GMT+03:00 Pavel Stehule <pavel.stehule@gmail.com>:


2015-12-20 17:30 GMT+01:00 Dmitry Igrishin <dmitigr@gmail.com>:
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 current user is not a superuser.
But it would be very nice to have a feature to change the session authorization
of current user even without superuser's privilege by supplying a password of
the user specified in SET SESSION AUTHORIZATION. This feature allows
to use PostgreSQL's native privileges via connection pools -- i.e. without
needs to open a dedicated connection for authenticated user. Is it possible
to implement it?

there is a workaround with security definer function and SET role TO ?
No there isn't. According to [2] "SET ROLE cannot be used within SECURITY
DEFINER function". Furthermore, SET ROLE doesn't affects the session_user's
function result which can be used by a logic.

you want to modify result of session_user? It's looks like possible security issue to me.
I want to be able to change the session user  without creating the new connection, like this
(pseudo REPL):
notsuperuser > SELECT current_user, session_user;
notsuperuser notsuperuser
notsuperuser > SET SESSION AUTHORIZATION notsuperuser2 PASSWORD 'password_of_notsuperuser2';
SET SESSION AUTHORIZATION
notsuperuser2 > SELECT current_user, session_user;
notsuperuser2 notsuperuser2

I don't see any security issue here.

It needs a change in PGPROC - and maybe invalidation some memory structures. I don't know why it is limited to superuser only.

Pavel
 


postgres=# create role tom ;
CREATE ROLE
Time: 91.461 ms
postgres=# select current_user;
┌──────────────┐
│ current_user │
╞══════════════╡
│ pavel        │
└──────────────┘
(1 row)

Time: 15.692 ms
postgres=# set role tom;
SET
Time: 0.609 ms
postgres=> select current_user;
┌──────────────┐
│ current_user │
╞══════════════╡
│ tom          │
└──────────────┘
(1 row)


 




--
// Dmitry.


Re: Session Identifiers

From
Andrew Sullivan
Date:
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
> tcp_keepalives_interval=1
> tcp_keepalives_count=3

Only if your psql session ends.  Psql is a client program.  It keeps
its connection to the database alive.

In this sense, the vulnerability you're looking at is analagous to the
case where someone logs into a UNIX shell and then leaves the shell
open.  If the system can be compromised such that someone else can get
control of that shell, you have a problem.  Otherwise, the session
can't really be taken over.  So, your exposure is exactly as great as
the exposure from UNIX process takeover.

You can prove to yourself that the process doesn't linger by opening
up a TCP connection (or for that matter a UNIX socket connection) and
somehow making the containing program fail (e.g. open a psql
connection and then sever your connection to the machine that had the
shell that initiated the psql session, without properly closing the
shell so that the session hangs around).  Eventually, the Postgres
backend will try to talk to the session and discover it isn't there,
and you'll get a termination logged (assuming you have loging turned
up that high).

A

--
Andrew Sullivan
ajs@crankycanuck.ca

Re: Session Identifiers

From
oleg yusim
Date:
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 


On Sun, Dec 20, 2015 at 1:38 PM, Andrew Sullivan <ajs@crankycanuck.ca> wrote:
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
> tcp_keepalives_interval=1
> tcp_keepalives_count=3

Only if your psql session ends.  Psql is a client program.  It keeps
its connection to the database alive.

In this sense, the vulnerability you're looking at is analagous to the
case where someone logs into a UNIX shell and then leaves the shell
open.  If the system can be compromised such that someone else can get
control of that shell, you have a problem.  Otherwise, the session
can't really be taken over.  So, your exposure is exactly as great as
the exposure from UNIX process takeover.

You can prove to yourself that the process doesn't linger by opening
up a TCP connection (or for that matter a UNIX socket connection) and
somehow making the containing program fail (e.g. open a psql
connection and then sever your connection to the machine that had the
shell that initiated the psql session, without properly closing the
shell so that the session hangs around).  Eventually, the Postgres
backend will try to talk to the session and discover it isn't there,
and you'll get a termination logged (assuming you have loging turned
up that high).

A

--
Andrew Sullivan
ajs@crankycanuck.ca


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

Re: Session Identifiers

From
oleg yusim
Date:
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 network connection is broken and session left hanging. For psql prompt case looks like pg_terminate_backend() would be the only solution.

Thanks,

Oleg



On Sun, Dec 20, 2015 at 11:33 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
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.

Re: Session Identifiers

From
Stephen Frost
Date:
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
Attachment

Re: Session Identifiers

From
Melvin Davidson
Date:
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.
Attachment

Re: Session Identifiers

From
oleg yusim
Date:
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, coupled with usage of pg_stat_activity and cron (it also has code too): http://stackoverflow.com/questions/12391174/how-to-close-idle-connections-in-postgresql-automatically

Thanks everybody,

Oleg

On Mon, Dec 21, 2015 at 10:51 AM, Melvin Davidson <melvin6925@gmail.com> wrote:
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.

Re: Session Identifiers

From
Michael Paquier
Date:
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

Re: Session Identifiers

From
oleg yusim
Date:
Thanks Michael, you are right, that is a very good alternative solution.

Oleg

On Tue, Dec 22, 2015 at 6:27 AM, Michael Paquier <michael.paquier@gmail.com> wrote:
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