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 Paquierwrote: > 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 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: [GENERAL] Session Identifiers
On Tue, Dec 22, 2015 at 1:42 AM, Stephen Frostwrote: > 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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 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 Davidsonwrote: > 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 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: [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, 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 Davidsonwrote: > 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 = '' to kill stagnant connections. > > On Mon, Dec 21, 2015 at 11: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 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: [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 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 signature.asc Description: Digital signature
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 add OR current_query = '' to kill stagnant connections. On Mon, Dec 21, 2015 at 11:42 AM, Stephen Frostwrote: > 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 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. kill_long_idles.sh Description: Bourne shell script -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 : >>> 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) > > > > >> >> [2] http://www.postgresql.org/docs/9.4/static/sql-set-role.html >> >> -- >> // Dmitry. >> >> > -- // Dmitry.
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 : >>> 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 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) >> >> >> >> >>> >>> [2] http://www.postgresql.org/docs/9.4/static/sql-set-role.html >>> >>> -- >>> // Dmitry. >>> >>> >> > > > -- > // Dmitry. > >
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 > 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: [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 AM, Melvin Davidsonwrote: > 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 > wrote: > >> 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 >>> 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: [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)? Oleg On Sun, Dec 20, 2015 at 11:02 AM, Pavel Stehulewrote: > > > 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 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 >> wrote: >> >>> 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 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: [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 will probably answer some additional questions for you. On Sun, Dec 20, 2015 at 12:02 PM, Pavel Stehulewrote: > > > 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 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 >> wrote: >> >>> 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 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: [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 AM, Melvin Davidsonwrote: > 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 > wrote: > >> >> >> 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 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 >> > wrote: >>> 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 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: [GENERAL] Session Identifiers
oleg yusimwrites: > 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
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 Lanewrote: > 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 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: [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 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 Lanewrote: > 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 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: [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 Davidsonwrote: > 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 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: [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 > 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 wrote: > >> 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 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: [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 Stehulewrote: > > > 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 >> 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 wrote: >> >>> 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 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: [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 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 > wrote: > >> >> >> 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 >>> 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 wrote: >>> 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 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: [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 >>> 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. [2] http://www.postgresql.org/docs/9.4/static/sql-set-role.html -- // Dmitry.
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 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) > > [2] http://www.postgresql.org/docs/9.4/static/sql-set-role.html > > -- > // Dmitry. > >
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 exact version of PostgreSQL and the O/S you are working with. On Sun, Dec 20, 2015 at 11:08 AM, Pavel Stehulewrote: > 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 >> 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: [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 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
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 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
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 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 > > [1] > http://www.postgresql.org/docs/9.4/static/sql-set-session-authorization.html >
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 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 Stehulewrote: > 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 >> 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: [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 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 > wrote: > >> 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 >>> 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: [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 > 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 a...@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: [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 On Sun, Dec 20, 2015 at 1:38 PM, Andrew Sullivanwrote: > 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 > a...@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 >