0

While running my application, some times I'm getting the following errors. The application will halt the execution with this exception.

org.postgresql.util.PSQLException: ERROR: could not open file "base/1459687/1472150": Permission denied; State=42501; ErrorCode=0

DB.saveError: DBExecuteError - ERROR: could not open file "base/1459687/1472150": Permission denied

After restarting my application, the same process is successfully executing and there is no errors in the console.

I'm using a java client server application (ADempiere) through a LAN.
I'm using Windows serer 2008. [Editor's note] Copied from comment, since OP didn't.

Why is the permission denied on some specific tables at specific times?

Sajeev
  • 783
  • 3
  • 14
  • 46
  • OS is Windows Server 2008 R2 Standard – Sajeev Aug 26 '14 at 04:14
  • pg_log folder logs in the server listing out the following statements regarding this error. `2014-08-27 01:12:39 GST LOG: could not open file "base/1459687/1461015": sharing violation 2014-08-27 01:12:39 GST DETAIL: Continuing to retry for 30 seconds. 2014-08-27 01:12:39 GST HINT: You might have antivirus, backup, or similar software interfering with the database system. 2014-08-27 01:12:39 GST CONTEXT: writing block 32962 of relation base/1459687/1461015` – Sajeev Aug 28 '14 at 06:06
  • There was no backup process in that time; so the cause of error nail down to the antivirus/similar software involvement. Any clue from here or need more information? – Sajeev Aug 28 '14 at 06:30
  • Try to exclude the Postgres data directory from scanning by the anti-virus software. Or even un-install the anti-virus software and see if that solves the problem. –  Aug 28 '14 at 07:09
  • 3
    @Saj "I'm on windows 2008" is the kind of detail you *put in the original question*, especially when dealing with things like permissions errors. I copy the relevant part of my answer below: *This isn't PostgreSQL SQL-level permissions. It's filesystem level permissions on the underlying tables. This should never happen in normal correct operation of the database server. Somebody's been messing with the PostgreSQL data directory directly, or something's broken in the file system. Had any recent fsck runs? Hard crashes/shutdowns? Disk faults?* – Craig Ringer Aug 28 '14 at 07:10
  • Thank you for your attention. There was such things on server. – Sajeev Aug 28 '14 at 07:19
  • @a_horse_with_no_name The permission denied exception is not thrown always. Once this exception has thrown, the users are restarting the application and doing the same thing again successfully. If the culprit is antivirus will it again block this activity? – Sajeev Aug 28 '14 at 07:32
  • Yes, this erratic behavior _could_ be caused by some anti-virus software (not with all of them and not all the time). –  Aug 28 '14 at 07:38
  • @a_horse_with_no_name I have disable the antivirus(ESET EndPoint) program. But the same issue is repeating. Both (Client application and the application in the DB server) is throwing this permission error. – Sajeev Aug 29 '14 at 08:47
  • @Sajeev : I have got the same issue, did you find the problem's source or any solution ? – willll Sep 06 '16 at 18:55
  • 1
    @willll It was java issue(I don't have any proof for that). I had reinstalled the java(it was V.1.6), then the error gone. Java certificate was expired in my case; You can trace this from the java control panel. – Sajeev Sep 11 '16 at 12:24

2 Answers2

0

I suspect your issue is described here: http://support.microsoft.com/kb/2434932

They say there is an inconsistent synchronization between original and temporary files. This occurs at file system level and there is a hotfix for this.

I suspect this happens when the number of transactions / sec (or DB load in general) increases. How much of your DB stays in memory? Do you use serializable snapshot isolation level (I'd recommend this)? I would try to find some patterns in the DB metrics when the error occurs.

Paul Ianas
  • 501
  • 3
  • 4
  • The DataBase size is about 4GB. max_connections are set as 300 in postgresql.config. The `permission denied/could not open file/sharing violation` error happening with the number of connections less than 128. How can I confirm this error is with the inconsistent sysc of original and temp files? Is the Hotfix available in the above link(the link advising Microsoft updation) is only the remedy? – Sajeev Sep 10 '14 at 06:33
  • Not sure it's *the only*; it is however a problem in the OS and Microsoft acknowledged it. Since you are anyway using a Win Server 2008, I would try to see whether that update is present or not on your system and install it. This kind of problem (sort of multi-threaded sync problems) is hard to reproduce, so you need to find the possible sources for it going through all the software stack. It's definitely not an application problem, but either an OS (file system) or DBMS or both. – Paul Ianas Sep 10 '14 at 07:47
  • I got this issue from the last July 27th. Before that there was no occurrence of this issue! The application is working for the last 18 months! – Sajeev Sep 10 '14 at 09:24
0

Privileges on the database are not enough. You also need at least the USAGE privilege for relevant schema and the SELECT privilege for the table.

More details in this closely related answer: How to grant all privileges on views to arbitrary user

Navnish Bhardwaj
  • 1,687
  • 25
  • 39
  • The client and server applications are accessing the data base. This error `permission denied/could not open file` happening only few time. There is no specific users or tables. – Sajeev Sep 10 '14 at 06:43