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, Pennsylvania 19073
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, Pennsylvania 19073On 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, Pennsylvania 19073"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-----
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
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
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
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, Pennsylvania 19073Import Notes
Reply to msg id not found: 41264AFC.5080501@pse-consulting.de | Resolved by subject fallback
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:
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, Pennsylvania 19073Import Notes
Reply to msg id not found: 41268754.3070108@pse-consulting.de | Resolved by subject fallback
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
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-----