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

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.

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 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

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

-Andy

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

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]http://www.postgresql.org/docs/9.4/static/sql-set-session-authorization.html 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?

[1]: http://www.postgresql.org/docs/9.4/static/sql-set-session-authorization.html
http://www.postgresql.org/docs/9.4/static/sql-set-session-authorization.html

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

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 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:

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

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:

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.

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:

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

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.

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:

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:

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

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: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

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]http://www.postgresql.org/docs/9.4/static/sql-set-role.html "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.

[2]: http://www.postgresql.org/docs/9.4/static/sql-set-role.html

--
// Dmitry.