Thread: postgres uptime
Hi all, It seems that there is no way to know the postgres uptime, a sort of uptime() function could be usefull. I had recently the necessity of detect a node fail over, and the only way I can do it with a SQL connection is asking the engine uptime. Of course I can do it with PS but now that windows version is out I believe a platform indipendent way is required. Any objection to add it ? Regards Gaetano Mendola
> It seems that there is no way to know the postgres > uptime, a sort of uptime() function could be usefull. > I had recently the necessity of detect a node fail over, > and the only way I can do it with a SQL connection is asking > the engine uptime. Of course I can do it with PS but > now that windows version is out I believe a platform > indipendent way is required. Any objection to add it ? That sounds like a cool idea to me, although I would suggest a function pg_uptime() that returns an interval or something. However, adding a new function requires a re-initdb, so it's quite unlikely this will be in for 8.0. Chris
On Fri, Aug 20, 2004 at 09:43:13AM +0800, Christopher Kings-Lynne wrote: > >It seems that there is no way to know the postgres > >uptime, a sort of uptime() function could be usefull. > >I had recently the necessity of detect a node fail over, > >and the only way I can do it with a SQL connection is asking > >the engine uptime. Of course I can do it with PS but > >now that windows version is out I believe a platform > >indipendent way is required. Any objection to add it ? > > That sounds like a cool idea to me, although I would suggest a function > pg_uptime() that returns an interval or something. > > However, adding a new function requires a re-initdb, so it's quite > unlikely this will be in for 8.0. Is the uptime kept internally anywhere? Or even the start time? If it is, it would be quite trivial to provide access to it, and there is at least one other thing to do "if we initdb" in the open items list for 8.0. -- Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) "El miedo atento y previsor es la madre de la seguridad" (E. Burke)
Added to TODO: * Add a function that returns the 'uptime' of the postmaster --------------------------------------------------------------------------- Alvaro Herrera wrote: > On Fri, Aug 20, 2004 at 09:43:13AM +0800, Christopher Kings-Lynne wrote: > > >It seems that there is no way to know the postgres > > >uptime, a sort of uptime() function could be usefull. > > >I had recently the necessity of detect a node fail over, > > >and the only way I can do it with a SQL connection is asking > > >the engine uptime. Of course I can do it with PS but > > >now that windows version is out I believe a platform > > >indipendent way is required. Any objection to add it ? > > > > That sounds like a cool idea to me, although I would suggest a function > > pg_uptime() that returns an interval or something. > > > > However, adding a new function requires a re-initdb, so it's quite > > unlikely this will be in for 8.0. > > Is the uptime kept internally anywhere? Or even the start time? > > If it is, it would be quite trivial to provide access to it, and there > is at least one other thing to do "if we initdb" in the open items list > for 8.0. > > -- > Alvaro Herrera (<alvherre[a]dcc.uchile.cl>) > "El miedo atento y previsor es la madre de la seguridad" (E. Burke) > > > ---------------------------(end of broadcast)--------------------------- > TIP 7: don't forget to increase your free space map settings > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Thu, 19 Aug 2004, Alvaro Herrera wrote: > On Fri, Aug 20, 2004 at 09:43:13AM +0800, Christopher Kings-Lynne wrote: > > >It seems that there is no way to know the postgres > > >uptime, a sort of uptime() function could be usefull. > > >I had recently the necessity of detect a node fail over, > > >and the only way I can do it with a SQL connection is asking > > >the engine uptime. Of course I can do it with PS but > > >now that windows version is out I believe a platform > > >indipendent way is required. Any objection to add it ? > > > > That sounds like a cool idea to me, although I would suggest a function > > pg_uptime() that returns an interval or something. > > > > However, adding a new function requires a re-initdb, so it's quite > > unlikely this will be in for 8.0. > > Is the uptime kept internally anywhere? Or even the start time? We do this at the start of ServerLoop(): gettimeofday(&earlier, &tz); and that value isn't changed, AFAICT. However, I'm not sure why an uptime is all that useful? Gavin
On Fri, 20 Aug 2004, Gavin Sherry wrote: > > On Thu, 19 Aug 2004, Alvaro Herrera wrote: > >> On Fri, Aug 20, 2004 at 09:43:13AM +0800, Christopher Kings-Lynne wrote: >>>> It seems that there is no way to know the postgres >>>> uptime, a sort of uptime() function could be usefull. >>>> I had recently the necessity of detect a node fail over, >>>> and the only way I can do it with a SQL connection is asking >>>> the engine uptime. Of course I can do it with PS but >>>> now that windows version is out I believe a platform >>>> indipendent way is required. Any objection to add it ? >>> >>> That sounds like a cool idea to me, although I would suggest a function >>> pg_uptime() that returns an interval or something. >>> >>> However, adding a new function requires a re-initdb, so it's quite >>> unlikely this will be in for 8.0. >> >> Is the uptime kept internally anywhere? Or even the start time? > > We do this at the start of ServerLoop(): > > gettimeofday(&earlier, &tz); > > and that value isn't changed, AFAICT. However, I'm not sure why an uptime > is all that useful? Bragging rights? :) ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Marc G. Fournier wrote: > On Fri, 20 Aug 2004, Gavin Sherry wrote: > >> >> On Thu, 19 Aug 2004, Alvaro Herrera wrote: >> >>> On Fri, Aug 20, 2004 at 09:43:13AM +0800, Christopher Kings-Lynne >>> wrote: >>>>> It seems that there is no way to know the postgres uptime, a sort >>>>> of uptime() function could be usefull. >>>>> I had recently the necessity of detect a node fail over, and the >>>>> only way I can do it with a SQL connection is asking the engine >>>>> uptime. Of course I can do it with PS but now that windows version >>>>> is out I believe a platform indipendent way is required. Any >>>>> objection to add it ? >>>> >>>> That sounds like a cool idea to me, although I would suggest a >>>> function pg_uptime() that returns an interval or something. >>>> >>>> However, adding a new function requires a re-initdb, so it's quite >>>> unlikely this will be in for 8.0. >>> >>> Is the uptime kept internally anywhere? Or even the start time? >> >> We do this at the start of ServerLoop(): >> >> gettimeofday(&earlier, &tz); >> >> and that value isn't changed, AFAICT. However, I'm not sure why an >> uptime is all that useful? > > Bragging rights? :) > If we ever do SNMP stuff, it will be useful for those packets as well. LER -- Larry Rosenman http://www.lerctr.org/~ler Phone: +1 972-414-9812 E-Mail: ler@lerctr.org US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749
Alvaro Herrera <alvherre@dcc.uchile.cl> writes:
> Is the uptime kept internally anywhere? Or even the start time?
No, and no (at least not in any globally accessible variable).
> If it is, it would be quite trivial to provide access to it
Not really --- in the EXEC_BACKEND case, we'd have to do something
explicit to pass the value down to backends.
I'd like to see more than one person requesting this (and with solider
rationales) before it gets added to TODO. If I wanted to be picky I
would suggest that knowledge of the server start time might be useful
information to an attacker. It would for instance narrow down the
number of possible starting seeds for the postmaster's random number
generator.
regards, tom lane
Tom Lane wrote: > Alvaro Herrera <alvherre@dcc.uchile.cl> writes: > > Is the uptime kept internally anywhere? Or even the start time? > > No, and no (at least not in any globally accessible variable). > > > If it is, it would be quite trivial to provide access to it > > Not really --- in the EXEC_BACKEND case, we'd have to do something > explicit to pass the value down to backends. > > I'd like to see more than one person requesting this (and with solider > rationales) before it gets added to TODO. If I wanted to be picky I > would suggest that knowledge of the server start time might be useful > information to an attacker. It would for instance narrow down the > number of possible starting seeds for the postmaster's random number > generator. I have already seen several people who are interested in it for adding it to TODO. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
On Thu, 19 Aug 2004, Tom Lane wrote: > Alvaro Herrera <alvherre@dcc.uchile.cl> writes: >> Is the uptime kept internally anywhere? Or even the start time? > > No, and no (at least not in any globally accessible variable). > >> If it is, it would be quite trivial to provide access to it > > Not really --- in the EXEC_BACKEND case, we'd have to do something > explicit to pass the value down to backends. Couldn't it be stored in shared memory? > I'd like to see more than one person requesting this (and with solider > rationales) before it gets added to TODO. If I wanted to be picky I > would suggest that knowledge of the server start time might be useful > information to an attacker. It would for instance narrow down the > number of possible starting seeds for the postmaster's random number > generator. Wouldn't an attacker have to have access to the server in the first place to get that information? ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
Marc G. Fournier wrote: > On Thu, 19 Aug 2004, Tom Lane wrote: > > > Alvaro Herrera <alvherre@dcc.uchile.cl> writes: > >> Is the uptime kept internally anywhere? Or even the start time? > > > > No, and no (at least not in any globally accessible variable). > > > >> If it is, it would be quite trivial to provide access to it > > > > Not really --- in the EXEC_BACKEND case, we'd have to do something > > explicit to pass the value down to backends. > > Couldn't it be stored in shared memory? We already have a mechanism to pass global variables to EXEC_BACKEND so that part is pretty simple. > > I'd like to see more than one person requesting this (and with solider > > rationales) before it gets added to TODO. If I wanted to be picky I > > would suggest that knowledge of the server start time might be useful > > information to an attacker. It would for instance narrow down the > > number of possible starting seeds for the postmaster's random number > > generator. > > Wouldn't an attacker have to have access to the server in the first place > to get that information? I think so, and if they do, they can use 'ps' to see the start time of the postmaster. However, I do think this is an 8.1 item. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> On Thu, 19 Aug 2004, Tom Lane wrote:
>> I'd like to see more than one person requesting this (and with solider
>> rationales) before it gets added to TODO. If I wanted to be picky I
>> would suggest that knowledge of the server start time might be useful
>> information to an attacker. It would for instance narrow down the
>> number of possible starting seeds for the postmaster's random number
>> generator.
> Wouldn't an attacker have to have access to the server in the first place
> to get that information?
They'd only need SQL access to run the proposed uptime() function. The
question is what they could parlay the information into --- perhaps the
ability to break into a more-privileged database account, or maybe even
the ability to break into other services on the same machine. It's not
unreasonable to suppose that the postmaster start time tells you the
most recent boot time of the server, and so you might be able to apply
the same sort of I-know-the-random-seed attack to other daemons on the
same machine.
This is certainly all speculative. But I thought the rationale for
clients wanting to know the postmaster start time in the first place
was pretty dang thin. I am simply pointing out that this is not a
zero-risk addition, and so we ought to ask just how much more than zero
benefit it really has.
regards, tom lane
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> On Fri, 20 Aug 2004, Gavin Sherry wrote:
>> However, I'm not sure why an uptime is all that useful?
> Bragging rights? :)
The folks who have a legitimate interest in that number can surely find
it out from "ps". What I'm having a hard time with here is the value of
allowing the number to be found out remotely. Most sites disabled
remote "ps" decades ago, for good and sufficient reason.
regards, tom lane
On Fri, 20 Aug 2004, Tom Lane wrote: > "Marc G. Fournier" <scrappy@postgresql.org> writes: >> On Thu, 19 Aug 2004, Tom Lane wrote: >>> I'd like to see more than one person requesting this (and with solider >>> rationales) before it gets added to TODO. If I wanted to be picky I >>> would suggest that knowledge of the server start time might be useful >>> information to an attacker. It would for instance narrow down the >>> number of possible starting seeds for the postmaster's random number >>> generator. > >> Wouldn't an attacker have to have access to the server in the first place >> to get that information? > > They'd only need SQL access to run the proposed uptime() function. The > question is what they could parlay the information into --- perhaps the > ability to break into a more-privileged database account, or maybe even > the ability to break into other services on the same machine. It's not > unreasonable to suppose that the postmaster start time tells you the > most recent boot time of the server, and so you might be able to apply > the same sort of I-know-the-random-seed attack to other daemons on the > same machine. > > This is certainly all speculative. But I thought the rationale for > clients wanting to know the postmaster start time in the first place > was pretty dang thin. I am simply pointing out that this is not a > zero-risk addition, and so we ought to ask just how much more than zero > benefit it really has. Good point(s) ... but, what would that give an attacker? Being able to isolate the random seed, that is? For instance, if you are thinking of shared memory allocations, unless we have a hole in the server itself that we aren't aware of, the only way that does any good is if the attacker does have access to the host machine itself to start with, and then he can get that info simply using ps (or the uptime command) ... Does anyone have any 'benefits' to implementing such a thing that we can list? The cons appear to be easy, what about pros? Note that I don't put my weight on my comment about 'bragging rights' as a pro, since, again, that info is just as easy to get by the admin via ps ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
On Fri, 20 Aug 2004, Tom Lane wrote: > "Marc G. Fournier" <scrappy@postgresql.org> writes: >> On Fri, 20 Aug 2004, Gavin Sherry wrote: >>> However, I'm not sure why an uptime is all that useful? > >> Bragging rights? :) > > The folks who have a legitimate interest in that number can surely find > it out from "ps". What I'm having a hard time with here is the value of > allowing the number to be found out remotely. Most sites disabled > remote "ps" decades ago, for good and sufficient reason. There used to be a 'remote ps'? That I didn't know ... But, back to the question at hand ... if we're worried about 'regular users' having access to it (which, I'm still a bit confused as to why, but that's in my other email) ... why not make it a 'superuser only' function? In fact, from that perspective, there would be use, since it would allow the admin to check remotely for situations where the server is/has crashed ... something that could also be easily (I'd imagine) to admin software like pgadmin, where if user has superuser access, a 'time since last reboot' could be included as part of the status screen ... ---- Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: scrappy@hub.org Yahoo!: yscrappy ICQ: 7615664
"Marc G. Fournier" <scrappy@postgresql.org> writes:
> Good point(s) ... but, what would that give an attacker? Being able to
> isolate the random seed, that is?
Well, the random seed determines the salt values that will be used to
challenge password logins. So it might help you execute a
password-replay attack, ie try just at the time when you'll be offered a
salt that you saw before. But I'm not a blackhat by nature and I'm sure
I'm missing a lot of possibilities.
> Does anyone have any 'benefits' to implementing such a thing that we can
> list? The cons appear to be easy, what about pros?
That's exactly what's bugging me --- I have not seen any particularly
strong defense of why we *should* have this function.
Your suggestion in another mail of restricting it to superusers would
eliminate most or all of the security gripes I'm raising. Whether that
still leaves it useful to the original suggestor's purpose, I dunno...
regards, tom lane
Tom Lane wrote: > "Marc G. Fournier" <scrappy@postgresql.org> writes: > >>Does anyone have any 'benefits' to implementing such a thing that we can >>list? The cons appear to be easy, what about pros? > > > That's exactly what's bugging me --- I have not seen any particularly > strong defense of why we *should* have this function. > > Your suggestion in another mail of restricting it to superusers would > eliminate most or all of the security gripes I'm raising. Whether that > still leaves it useful to the original suggestor's purpose, I dunno... If you had a pressing need, couldn't you just hack the startup script(s) to insert a timestamp in a suitable table? Or am I missing something here (as usual)? -- Richard Huxton Archonet Ltd
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Christopher Kings-Lynne wrote: |> It seems that there is no way to know the postgres |> uptime, a sort of uptime() function could be usefull. |> I had recently the necessity of detect a node fail over, |> and the only way I can do it with a SQL connection is asking |> the engine uptime. Of course I can do it with PS but |> now that windows version is out I believe a platform |> indipendent way is required. Any objection to add it ? | | | That sounds like a cool idea to me, although I would suggest a function | pg_uptime() that returns an interval or something. | | However, adding a new function requires a re-initdb, so it's quite | unlikely this will be in for 8.0. Even if the re-initdb was not necessary we are in beta, and usualy the new features are not allowed. Regards Gaetano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBJcBG7UpzwH2SGd4RAgj/AJ0ZldSnVca/OJ/WQhpkEnrYhi439gCgs4Uw Rqv0nDE7O7APcT2qgDz2BPI= =gyq5 -----END PGP SIGNATURE-----
Tom Lane wrote: > I'd like to see more than one person requesting this (and with solider > rationales) before it gets added to TODO. If I wanted to be picky I > would suggest that knowledge of the server start time might be useful > information to an attacker. It would for instance narrow down the > number of possible starting seeds for the postmaster's random number > generator. Good point, however I can add that also the versione engine knowledge could be a good start point. Gaetano
Richard Huxton wrote: > Tom Lane wrote: > >> "Marc G. Fournier" <scrappy@postgresql.org> writes: >> >>> Does anyone have any 'benefits' to implementing such a thing that we >>> can list? The cons appear to be easy, what about pros? >> >> >> >> That's exactly what's bugging me --- I have not seen any particularly >> strong defense of why we *should* have this function. >> >> Your suggestion in another mail of restricting it to superusers would >> eliminate most or all of the security gripes I'm raising. Whether that >> still leaves it useful to the original suggestor's purpose, I dunno... > > > If you had a pressing need, couldn't you just hack the startup script(s) > to insert a timestamp in a suitable table? > > Or am I missing something here (as usual)? That's right, I'm not stuck with this problem I solved it with `ps`, I suggested a global uptime function considering that postgres is not snmp capable and for windows administrators is a bit more difficult retrieve this value. Regards Gaetano Mendola
On Fri, 2004-08-20 at 05:41, Gaetano Mendola wrote:
> Tom Lane wrote:
>
> > I'd like to see more than one person requesting this (and with solider
> > rationales) before it gets added to TODO. If I wanted to be picky I
> > would suggest that knowledge of the server start time might be useful
> > information to an attacker. It would for instance narrow down the
> > number of possible starting seeds for the postmaster's random number
> > generator.
>
> Good point, however I can add that also the versione engine knowledge
> could be a good start point.
>
I'd offer that given a simple call to version() will give you PostgreSQL
version, cpu architecture, os/distribution information, and the version
of the compiler used, that letting someone know the uptime of the
postmaster would be last on my list of things I would worry about, and
version() is a non-superuser executable function.
If we do add this function, I guarantee you that you'll see it added to
phppgadmin and pgadmin, because it helps make these remote
administration tools more complete.
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
Robert Treat wrote:
>
> If we do add this function, I guarantee you that you'll see it added to
> phppgadmin and pgadmin, because it helps make these remote
> administration tools more complete.
:-)
Guess what I just implemented...
pg_postmaster_starttime() RETURNS timestamp, currently implemented in
the admin module for win32 using GetProcessTimes(PostmasterHandle).
What's the equivalent for posix? Interpreting popen("ps...") might get
messy.
Regards,
Andreas
Andreas Pflug wrote:
> Robert Treat wrote:
>
> >
> > If we do add this function, I guarantee you that you'll see it added to
> > phppgadmin and pgadmin, because it helps make these remote
> > administration tools more complete.
>
> :-)
> Guess what I just implemented...
>
> pg_postmaster_starttime() RETURNS timestamp, currently implemented in
> the admin module for win32 using GetProcessTimes(PostmasterHandle).
>
> What's the equivalent for posix? Interpreting popen("ps...") might get
> messy.
I think we should just call gettimeofday() at postmaster start and store
it somewhere.
-- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610)
359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square,
Pennsylvania19073
Bruce Momjian wrote: > > I think we should just call gettimeofday() at postmaster start and store > it somewhere. No objections, but that's probably not done in 8.0 any more, right? Regards, Andreas
Andreas Pflug wrote: > Bruce Momjian wrote: > > > > > I think we should just call gettimeofday() at postmaster start and store > > it somewhere. > > No objections, but that's probably not done in 8.0 any more, right? Right. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian wrote:
> Andreas Pflug wrote:
>>Guess what I just implemented...
>>
>>pg_postmaster_starttime() RETURNS timestamp, currently implemented in
>>the admin module for win32 using GetProcessTimes(PostmasterHandle).
>>
>>What's the equivalent for posix? Interpreting popen("ps...") might get
>>messy.
>
>
> I think we should just call gettimeofday() at postmaster start and store
> it somewhere.
Isn't the shared memory a good place ?
Regards
Gaetano Mendola
Gaetano Mendola <mendola@bigfoot.com> writes:
>> I think we should just call gettimeofday() at postmaster start and store
>> it somewhere.
> Isn't the shared memory a good place ?
Depends. Do you want to reset it during a backend-crash-recovery cycle?
You'll have to, if it's only stored in shared memory. Depending on what
your definition of "uptime" is, that could be a reasonable thing to do,
or not.
There's been a remarkable lack of discussion about exactly what this
number would mean, anyway. Does "postmaster start" mean postmaster
process start? Or when we are first ready to receive a connection?
There could be a *very* large difference, in the case of a hot-standby
postmaster.
regards, tom lane
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Tom Lane wrote: | Gaetano Mendola <mendola@bigfoot.com> writes: | |>>I think we should just call gettimeofday() at postmaster start and store |>>it somewhere. | | |>Isn't the shared memory a good place ? | | | Depends. Do you want to reset it during a backend-crash-recovery cycle? | You'll have to, if it's only stored in shared memory. Depending on what | your definition of "uptime" is, that could be a reasonable thing to do, | or not. | | There's been a remarkable lack of discussion about exactly what this | number would mean, anyway. Does "postmaster start" mean postmaster | process start? Or when we are first ready to receive a connection? | There could be a *very* large difference, in the case of a hot-standby | postmaster. Speaking of my needs I wish to have the time when the server was able to accept connections and if the backend do a crash-recovery cicle we can have a GUC, similar to statistics reset, in order to have an uptime reset or not. Regards Gaetano Mendola -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBJxnQ7UpzwH2SGd4RAoi6AJ4sm64TNT8SCGvkoaEaN9f82RFS4wCgiq0L MrCYrpKNA7AKT1k+jRoypgQ= =ezvn -----END PGP SIGNATURE-----