Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

Find out whether system time changed through Postrges

I have a record in postgres that stores time at which the record was created. The user can keep using the system, after 14 days my application should shutdown as the time has elapsed.

If the user resets the system time, can I find out through postgres whether the system time has been changed or whether  the 14 days have been elapsed?

For eg, if the record was created on 1st July, the user keeps using the system till 12th July, but on the 13th he resets the system date to 5th July, can I still come to know whether the 12days  have elapsed only through postgres?
1 Solution
On the face of your question, no, PostgreSQL cannot independently determine whether the system time has been rolled back or not.  It relies on the system time; to my knowledge it does not have the capability (a la NTP or the like) to determine time independent of the system it is installed on.

That said, however, your queries can look at the information available in the tables.  Based on your description, you have some table that has a field for storing when a record in that table was created.  So you have information about past dates accessed.  For discussion, let's say the table is Example, and the field is Created_on.  Right now, your application may be doing a query similar to the first one below.  You get that minimum date from the table, you check if it's 14 days older than the current time.  But as you say, this is defeated if the administrator rolls the time back on the system.  However, you could perform a second query to also look for any records that were created "after" the current time.  On a correctly operating system, this will never be the case.  (The exception would be cases where system time drifts ahead of real time, and the administrator rolls back the time to correct that.  But that should only be a few minutes, at most a few hours.  And if they're that worried about it, they would have NTP already configured and running, so it wouldn't happen anyways.  Also, it assumes that the time used to set the Created_on field is from the database system itself.  If it is from an application system, which may be running remote from the database system, then there could be time differences there that could trip this query.  Again, they should not be greatly different, but you should be aware of it.)  So if any records are found that have a Created_on timestamp ahead of the current time, your application would know that the system time has likely been rolled back and could perform actions based on that (like preventing the user from further use).

Of course, if all the records in Example are created before July 5th, then this option still does not work.  Presumably the real table would be some sort of logging table which would likely have new records added to it with even nominal usage.  Also, if the user has the ability to roll back the time on the system, technically there is nothing to prevent them from going into the database itself and changing the values in the Created_on field for all records so that the second query returned 0.  But they would need to know that that's what they needed to do in order to defeat that countermeasure, which is unlikely.  Also, at some point it just does not become worth the effort: if they want to use the application regularly, they pay the licensing fee; if they don't need it that badly, they let the trial lapse.
# determine the earliest date in the Example table
SELECT min(Created_on) FROM Example;
# find records created _after_ the current time
SELECT count(*) FROM Example where Created_on > now();

Open in new window

earth man2Commented:
Your application should look at the timestamp of certain system files to determine if system time has been reset.
a_bAuthor Commented:
Thanks cminea, I will be going with the implementation suggested by you.

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now