Solved

Find out whether system time changed through Postrges

Posted on 2009-07-01
3
478 Views
Last Modified: 2012-05-07
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?
0
Comment
Question by:a_b
3 Comments
 
LVL 12

Accepted Solution

by:
cminear earned 500 total points
Comment Utility
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

0
 
LVL 22

Expert Comment

by:earth man2
Comment Utility
Your application should look at the timestamp of certain system files to determine if system time has been reset.
0
 
LVL 20

Author Closing Comment

by:a_b
Comment Utility
Thanks cminea, I will be going with the implementation suggested by you.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Best database to use for Maps is PostgreSQL. This is an open source database. Comes as a package with most Linux OS. For more info visit the following site: http://www.postgresql.org/ (http://www.postgresql.org/) This requires some add-o…
Many developers have database experience, but are new to PostgreSQL. It has some truly inspiring capabilities. I have several years' experience with Microsoft's SQL Server. When I began working with MySQL, I wanted a quick-reference to MySQL (htt…
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now