Thread: Re: Re: Error on Windows server could not open relation base/xxx/xxx Permission denied

I posted this two days ago and nobody has commented. I'm reposting the message because I really need advice. Background
info:My client got the "permission denied" error on his original server and we removed the AV software without solving
theproblem. Since the computer is running Windows 2000 Server, SP4, it was suggested that a newer OS might help. We
movedthe data and the problem persists even on the new computer. What follows is my status report about the new
computeras well as the process of copying the data. Note that backing up the database on the original server also gets
the"permission denied" error. 

Posting from 6/10 follows.........

Here's the current status.

We installed postgres on an XP machine, 2002 SP3. (Same as my computer, which never has a problem.)

We tried to do a backup of the database on the old computer, to copy the data to the new computer. The backup failed
withthe same problem mentioned in the subject line. 

So we zipped up the data directory and unzipped it on the XP computer.

We then attempted to paste in the "large" block of text (200 lines of plain ascii, 49000 bytes) and got the same
problemas before. 

Note that the load on the server and on postgres is very low, and that the problem can be recreated with 100% certainty
whenwe paste certain text into certain fields. 

This computer is running "Symantec Endpoint Protection", with the proactive threat feature turned off.

Question: Is it possible that there's corruption in the database which is being incorrectly reported as "Permission
denied"?

Perhaps the original problem on the other computer created the corruption? Or the corruption came from another source
andon both computers creates the incorrect message? 

We could of course recreate pretty much the same database. We're in development mode now: it was loaded with data from
thelegacy system extracted a few months ago and since then there has been additional data entered and changed as people
haveplayed with and tested the application. 

Is this a random event? A bug? Advice please on what to do next.

John





On Tue, 08 Jun 2010 08:37:02 -0400, John T. Dow wrote:

>On Tue, 08 Jun 2010 10:25:49 +0800, Craig Ringer wrote:
>
>>On 8/06/2010 9:11 AM, John T. Dow wrote:
>>> OP here....
>>>
>>> We removed AVG from the computer and rebooted.
>>>
>>> Same problem.
>>
>>OK, good to know. Thanks very much for testing that, and my apologies
>>for recommending something that didn't work out. Of course, it would
>>have been hard to progress without eliminating that possible factor.
>>
>>> Could it be 2000 Server? SP4? I've seen reports of other problems that went away depending on the version of
Windows.
>>
>>Well, certainly I'd expect that Pg on Windows 2000 server gets about
>>zero regular testing. Why would you deploy a server OS that's already 10
>>years out of date, went EOL five years ago, and lost even the option of
>>paid extended support this year?
>>
>>http://support.microsoft.com/lifecycle/?LN=en-au&x=14&y=11&p1=7274
>>
>
>
>
>
>
>Good point.
>
>It's not my server, it's my client's server, and I don't know the history of it.
>
>They have mentioned another computer which runs XP I believe. It's dedicated to a single task and could double as the
databaseserver, although I don't think it has any RAID. I will suggest that we try installing Postgres on that computer
andsee if the problem goes away. If so, they might choose to make that their solution (perhaps adding another hard
driveand a RAID controller). The application, daily backups, and WAL files could all live on the original server. 
>
>If they go that route, we'd never know for certain what the original problem was.
>
>I'll post back after anything is done.
>
>JOhn
>
>
>
>
>
>
>
>
>
>>It'd be interesting to investigate this issue ... but win2k server isn't
>>exactly easy to come by. Anyone on the list got a win2k server (or
>>license) around they can do some experimenting on? All I have here is
>>NT4 (not kidding - legacy system) and Win2k8 plus the usual desktop
>>suspects.
>>
>>--
>>Craig Ringer
>>
>>
>>--
>>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>>To make changes to your subscription:
>>http://www.postgresql.org/mailpref/pgsql-general
>
>
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

On Saturday 12 June 2010 11:07:32 am John T. Dow wrote:
> I posted this two days ago and nobody has commented. I'm reposting the
> message because I really need advice. Background info: My client got the
> "permission denied" error on his original server and we removed the AV
> software without solving the problem. Since the computer is running Windows
> 2000 Server, SP4, it was suggested that a newer OS might help. We moved the
> data and the problem persists even on the new computer. What follows is my
> status report about the new computer as well as the process of copying the
> data. Note that backing up the database on the original server also gets
> the "permission denied" error.
>
> Posting from 6/10 follows.........
>
> Here's the current status.
>
> We installed postgres on an XP machine, 2002 SP3. (Same as my computer,
> which never has a problem.)
>
> We tried to do a backup of the database on the old computer, to copy the
> data to the new computer. The backup failed with the same problem mentioned
> in the subject line.
>
> So we zipped up the data directory and unzipped it on the XP computer.
>
> We then attempted to paste in the "large" block of text (200 lines of plain
> ascii, 49000 bytes) and got the same problem as before.
>
> Note that the load on the server and on postgres is very low, and that the
> problem can be recreated with 100% certainty when we paste certain text
> into certain fields.
>
> This computer is running "Symantec Endpoint Protection", with the proactive
> threat feature turned off.
>
> Question: Is it possible that there's corruption in the database which is
> being incorrectly reported as "Permission denied"?
>
> Perhaps the original problem on the other computer created the corruption?
> Or the corruption came from another source and on both computers creates
> the incorrect message?
>
> We could of course recreate pretty much the same database. We're in
> development mode now: it was loaded with data from the legacy system
> extracted a few months ago and since then there has been additional data
> entered and changed as people have played with and tested the application.
>
> Is this a random event? A bug? Advice please on what to do next.
>
> John
>

Some more questions.
What is the relation that is having the permissions issue?
Are the permissions on that file different from the others in the base directory
tree?
What is the 'certain text'?
What are the 'certain fields' and do they have any functions running on them?



--
Adrian Klaver
adrian.klaver@gmail.com

On 13/06/10 02:34, Adrian Klaver wrote:

>> Question: Is it possible that there's corruption in the database which is
>> being incorrectly reported as "Permission denied"?

It's certainly not impossible. It'd really help if Pg would print more
details from Windows' error reporting - GetLastError() etc - in cases
like this. In fact, some searching reveals complaints about just that as
far back as mid-2008 related to the exact error you're encountering.


Anyway: When you moved the data dir over, did you reset all the
permissions on it so that it is owned by the "postgres" user on the new
machine? Applying those permissions recursively?

Does the file that PostgreSQL is complaining about actually exist?

Is it always the same 'xxx/xxx'?


Is it an index or a relation? You can find out using the Pg catalogs:

 http://www.postgresql.org/docs/current/static/storage-file-layout.html
 http://www.postgresql.org/docs/current/static/catalog-pg-class.html

... from which you'll see that of:

   base/xxx/yyy

'base/xxx' is the prefix for your database, and within that 'yyy' is the
oid of the table, so you can  find out some details about it with the
following SQL:


  \x
  select * from pg_class where oid = yyy;


Does the table/index name reported by that query match one that is
actually used in the problem query? What is it? Please post the full
output of the above query.


If it's an index, does REINDEXing your database help?

If it's a relation, does CLUSTERing that relation succeed? Help?

>> Is this a random event? A bug? Advice please on what to do next.

It's really, really hard to know, especially with the involvement of
elderly OSes and antivirus software. Could it be a Pg bug causing this?
Of course. But it's really, really hard to know what, when, and how,
especially with no access to the machines and data in question.

Please keep a copy of this damaged cluster around, even if you decide to
go ahead and rebuild the cluster. Now that its on a known-working
platform and the issue has been shown not to be proximately* caused by
antivirus software, it'd be preferable to find out what's actually going
on here. That will be impossible without the damaged cluster.

(* ie if the AV software was involved, it was to damage something that
stays damaged after the AV is taken out of the picture)

--
Craig Ringer

On Sun, Jun 13, 2010 at 5:11 AM, Craig Ringer
<craig@postnewspapers.com.au> wrote:
> On 13/06/10 02:34, Adrian Klaver wrote:
>
>>> Question: Is it possible that there's corruption in the database which is
>>> being incorrectly reported as "Permission denied"?
>
> It's certainly not impossible. It'd really help if Pg would print more
> details from Windows' error reporting - GetLastError() etc - in cases
> like this. In fact, some searching reveals complaints about just that as
> far back as mid-2008 related to the exact error you're encountering.

It does if you enable debug logging. DEBUG5 is required from what I
can tell (see src/port/win32error.c, function _dosmaperr(), which is
called from pgwin32_open()).

In a lot of cases it maps straight over, but in the cases where we
have to map to an errno value and use that, there can be more than
one. In the case of access denied, it can be:
ERROR_ACCESS_DENIED
ERROR_CURRENT_DIRECTORY
ERROR_LOCK_VIOLATION
ERROR_SHARING_VIOLATION (but this is taken care of already in pgwin32_open)
ERROR_NETWORK_ACCESS_DENIED
ERROR_CANNOT_MAKE
ERROR_FAIL_I24
ERROR_DRIVE_LOCKED
ERROR_SEEK_ON_DEVICE
ERROR_NOT_LOCKED
ERROR_LOCK_FAILED

Most of these can't (shouldn't be possible at least) appear when we're
opening a file for reading. But it'd be interesting to know what they
were.

So it'd be interesting to see the output of this at DEBUG5 (there
should be a line saying "mapped win32 error code <n> to <n>" showing
up - there will be *tons* of other logging output of course)


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

I have information

We had noticed two relations, their numbers being 16384/16642 and 16384/16792.

Here is what pg_class has for them.



"relname";"relnamespace";"reltype";"relowner";"relam";"relfilenode";"reltablespace";"relpages";"reltuples";"reltoastrelid";"reltoastidxid";"relhasindex";"relisshared";"relistemp";"relkind";"relnatts";"relchecks";"relhasoids";"relhaspkey";"relhasrules";"relhastriggers";"relhassubclass";"relfrozenxid";"relacl";"reloptions"

"pg_toast_16638";99;16643;16510;0;16642;0;0;0;0;16644;t;f;f;"t";3;0;f;t;f;f;f;1581;"";""

"pg_toast_16788";99;16793;16510;0;16792;0;0;0;0;16794;t;f;f;"t";3;0;f;t;f;f;f;2202;"";""

We also looked at the permissions and whether the files actually exist.

Findings: The files are both marked "system file" and have size 0 K. When logging on as an administrator and opening
thefiles (eg with notepad, just to see if there is nothing at all) they appear to be empty. 

However, while we were working on the problem, pgadmin3 started reporting "permission denied" for 2611. At the same
time,pgadmin was unable to see the columns of the tables. Attempting to do so is what caused the error for 2611. 

2611 also appeared to be a system file with 0 bytes.

Meantime, pgadmin was able to create a table and see the columns on the standard postgres database.

Also, the Java application was able to see the columns and list them out as well.

I have noticed that postgres is very unhappy if the proper "postgres" user doesn't have access to the files. But I have
alsonoticed that other users seem to be able to have access without causing problems. I realize this compromises
security,but in a development environment it is very convenient, eg when doing a system backup. 

Is it possible that some type of user might be causing files to be created as or changed to system files, marked read
only,and apparently empty? 

I am not certain which users have access to the files at the client's site, but I know it's more than just the postgres
user.

All of these findings were on the second computer running XP. We ran out of time today before we investigated the
originalserver to see if it also had system files marked read only with no apparent contents. 

John



On Sun, 13 Jun 2010 11:51:45 +0200, Magnus Hagander wrote:

>On Sun, Jun 13, 2010 at 5:11 AM, Craig Ringer
><craig@postnewspapers.com.au> wrote:
>> On 13/06/10 02:34, Adrian Klaver wrote:
>>
>>>> Question: Is it possible that there's corruption in the database which is
>>>> being incorrectly reported as "Permission denied"?
>>
>> It's certainly not impossible. It'd really help if Pg would print more
>> details from Windows' error reporting - GetLastError() etc - in cases
>> like this. In fact, some searching reveals complaints about just that as
>> far back as mid-2008 related to the exact error you're encountering.
>
>It does if you enable debug logging. DEBUG5 is required from what I
>can tell (see src/port/win32error.c, function _dosmaperr(), which is
>called from pgwin32_open()).
>
>In a lot of cases it maps straight over, but in the cases where we
>have to map to an errno value and use that, there can be more than
>one. In the case of access denied, it can be:
>ERROR_ACCESS_DENIED
>ERROR_CURRENT_DIRECTORY
>ERROR_LOCK_VIOLATION
>ERROR_SHARING_VIOLATION (but this is taken care of already in pgwin32_open)
>ERROR_NETWORK_ACCESS_DENIED
>ERROR_CANNOT_MAKE
>ERROR_FAIL_I24
>ERROR_DRIVE_LOCKED
>ERROR_SEEK_ON_DEVICE
>ERROR_NOT_LOCKED
>ERROR_LOCK_FAILED
>
>Most of these can't (shouldn't be possible at least) appear when we're
>opening a file for reading. But it'd be interesting to know what they
>were.
>
>So it'd be interesting to see the output of this at DEBUG5 (there
>should be a line saying "mapped win32 error code <n> to <n>" showing
>up - there will be *tons* of other logging output of course)
>
>
>--
> Magnus Hagander
> Me: http://www.hagander.net/
> Work: http://www.redpill-linpro.com/
>
>--
>Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
>To make changes to your subscription:
>http://www.postgresql.org/mailpref/pgsql-general

On Sunday 13 June 2010 1:41:01 pm John T. Dow wrote:
> I have information
>
> We had noticed two relations, their numbers being 16384/16642 and
> 16384/16792.
>
> Here is what pg_class has for them.
>
>
> "relname";"relnamespace";"reltype";"relowner";"relam";"relfilenode";"reltab
>lespace";"relpages";"reltuples";"reltoastrelid";"reltoastidxid";"relhasindex
>";"relisshared";"relistemp";"relkind";"relnatts";"relchecks";"relhasoids";"r
>elhaspkey";"relhasrules";"relhastriggers";"relhassubclass";"relfrozenxid";"r
>elacl";"reloptions"
>
> "pg_toast_16638";99;16643;16510;0;16642;0;0;0;0;16644;t;f;f;"t";3;0;f;t;f;f
>;f;1581;"";""
>
> "pg_toast_16788";99;16793;16510;0;16792;0;0;0;0;16794;t;f;f;"t";3;0;f;t;f;f
>;f;2202;"";""
>
> We also looked at the permissions and whether the files actually exist.
>
> Findings: The files are both marked "system file" and have size 0 K. When
> logging on as an administrator and opening the files (eg with notepad, just
> to see if there is nothing at all) they appear to be empty.

Whose permissions do they have?

>
> However, while we were working on the problem, pgadmin3 started reporting
> "permission denied" for 2611. At the same time, pgadmin was unable to see
> the columns of the tables. Attempting to do so is what caused the error for
> 2611.
>
> 2611 also appeared to be a system file with 0 bytes.

What does Postgres think it is? Another TOAST table?

>
> Meantime, pgadmin was able to create a table and see the columns on the
> standard postgres database.

Now I am confused. What are you calling the standard Postgres database?

>
> Also, the Java application was able to see the columns and list them out as
> well.

Of which database?

>
> I have noticed that postgres is very unhappy if the proper "postgres" user
> doesn't have access to the files. But I have also noticed that other users
> seem to be able to have access without causing problems. I realize this
> compromises security, but in a development environment it is very
> convenient, eg when doing a system backup.

Sort of the purpose of permissions :)

>
> Is it possible that some type of user might be causing files to be created
> as or changed to system files, marked read only, and apparently empty?

It would seem so. The question is whether this a historical artifact from
corruption in the past or is ongoing?

>
> I am not certain which users have access to the files at the client's site,
> but I know it's more than just the postgres user.
>
> All of these findings were on the second computer running XP. We ran out of
> time today before we investigated the original server to see if it also had
> system files marked read only with no apparent contents.
>
> John




--
Adrian Klaver
adrian.klaver@gmail.com

>can you please give  more information about the (windows)-user "postgres" ?
>is it a local user on that machine? How was that user created?

It's the user created by the one-click installer. I believe it owns the postgres data directory and is used to start
theserver. Other than that, the intention is for this user to have no other file privileges. The default is "postgres"
butit could be anything. 


>
>Are there any group-policies or similar, or "security-applications" present,
>which can change the rights of this user postgres? (Or, can change the
>access-properties of files on the system?)

I don't know. It is not my computer, it is my client's computer. We will investigate if anything like that is going on.
Hewas only available until 4PM today and we just discovered what was happening shortly before that point. The people
thatdo their security should be available Monday and we can ask them this type of question. 

Any idea of what to look for?

>
>Your sentenceabout "postgres being unhappy when not having access to the
>files" makes me curious how you did learn that --- was somebody / something
>taking file access away from Postgres? Could that somebody / something still
>be active?

That somebody was me, experimenting over the years. But I have not been messing around with this particular
application.However, I'm not sure what the client did, as they copied the data files between the two computers at a
timewhen I wasn't available. (They zipped, then unzipped after logging in as the proper user.) 

As a developer for multiple clients, I need easy access to my development copies of my clients' postgres data files.
ThereforeI have experimented with allowing my own userid to have access to the "data" directory and the subdirectories
andfiles. I believe postgres doesn't care if you allow extra users, as long as "postgres" still has the proper access. 

John

>
>Harald
>
>
>--
>GHUM Harald Massa
>persuadere et programmare
>Harald Armin Massa
>Spielberger StraAYe 49
>70435 Stuttgart
>0173/9409607
>no fx, no carrier pigeon
>-
>Using PostgreSQL is mostly about sleeping well at night.
>

I was talking to a friend (Joe Newcomer) who said that Unix doesn't have mandatory file locks and he guessed that the
empty,system, read only files I saw at my client's site were unix-like lock files. 

To test that, on my home development computer I typed this command in the base\16384 diretory:

attrib +r 2611

That is, I made 2611 read only.

Sure enough, pgadmin can't display the columns for any of the tables. I get "permission denied" for 2611.

And sure enough, the Java application runs fine and indeed is able to export the table definition, complete with
columns.

So this is exactly the behavior observed at my client's site.

Apparently the problem boils down to this question: how did some of the files get set to be system and read only?

Anybody ever seen this?

Perhaps it's not even a postgres question.

We will investigate further Monday when people are in the office. Any thoughts from anybody would be appreciated.

Reminder: the problem with 2611 was observed on the second computer, which runs XP Pro 2002 SP3. The problems pasting
50Kof text was first observed on the first computer, running 2000 Server if I remember right. It does not therefore
seemto be related to AV software (the original suggestion) or the OS. 

John





On Sun, 13 Jun 2010 14:10:27 -0700, Adrian Klaver wrote:

>On Sunday 13 June 2010 1:41:01 pm John T. Dow wrote:
>> I have information
>>
>> We had noticed two relations, their numbers being 16384/16642 and
>> 16384/16792.
>>
>> Here is what pg_class has for them.
>>
>>
>> "relname";"relnamespace";"reltype";"relowner";"relam";"relfilenode";"reltab
>>lespace";"relpages";"reltuples";"reltoastrelid";"reltoastidxid";"relhasindex
>>";"relisshared";"relistemp";"relkind";"relnatts";"relchecks";"relhasoids";"r
>>elhaspkey";"relhasrules";"relhastriggers";"relhassubclass";"relfrozenxid";"r
>>elacl";"reloptions"
>>
>> "pg_toast_16638";99;16643;16510;0;16642;0;0;0;0;16644;t;f;f;"t";3;0;f;t;f;f
>>;f;1581;"";""
>>
>> "pg_toast_16788";99;16793;16510;0;16792;0;0;0;0;16794;t;f;f;"t";3;0;f;t;f;f
>>;f;2202;"";""
>>
>> We also looked at the permissions and whether the files actually exist.
>>
>> Findings: The files are both marked "system file" and have size 0 K. When
>> logging on as an administrator and opening the files (eg with notepad, just
>> to see if there is nothing at all) they appear to be empty.
>
>Whose permissions do they have?
>
>>
>> However, while we were working on the problem, pgadmin3 started reporting
>> "permission denied" for 2611. At the same time, pgadmin was unable to see
>> the columns of the tables. Attempting to do so is what caused the error for
>> 2611.
>>
>> 2611 also appeared to be a system file with 0 bytes.
>
>What does Postgres think it is? Another TOAST table?
>
>>
>> Meantime, pgadmin was able to create a table and see the columns on the
>> standard postgres database.
>
>Now I am confused. What are you calling the standard Postgres database?
>
>>
>> Also, the Java application was able to see the columns and list them out as
>> well.
>
>Of which database?
>
>>
>> I have noticed that postgres is very unhappy if the proper "postgres" user
>> doesn't have access to the files. But I have also noticed that other users
>> seem to be able to have access without causing problems. I realize this
>> compromises security, but in a development environment it is very
>> convenient, eg when doing a system backup.
>
>Sort of the purpose of permissions :)
>
>>
>> Is it possible that some type of user might be causing files to be created
>> as or changed to system files, marked read only, and apparently empty?
>
>It would seem so. The question is whether this a historical artifact from
>corruption in the past or is ongoing?
>
>>
>> I am not certain which users have access to the files at the client's site,
>> but I know it's more than just the postgres user.
>>
>> All of these findings were on the second computer running XP. We ran out of
>> time today before we investigated the original server to see if it also had
>> system files marked read only with no apparent contents.
>>
>> John
>
>
>
>
>--
>Adrian Klaver
>adrian.klaver@gmail.com

On Mon, Jun 14, 2010 at 05:17, John T. Dow <john@johntdow.com> wrote:
> I was talking to a friend (Joe Newcomer) who said that Unix doesn't have mandatory file locks and he guessed that the
empty,system, read only files I saw at my client's site were unix-like lock files. 

They are not. They are regular relation files.


> To test that, on my home development computer I typed this command in the base\16384 diretory:
>
> attrib +r 2611
>
> That is, I made 2611 read only.
>
> Sure enough, pgadmin can't display the columns for any of the tables. I get "permission denied" for 2611.
>
> And sure enough, the Java application runs fine and indeed is able to export the table definition, complete with
columns.

Most likely because pgadmin tries to fetch all information about the
table, including toast relations, whereas the java application only
fetches the information it actually needs.


> So this is exactly the behavior observed at my client's site.
>
> Apparently the problem boils down to this question: how did some of the files get set to be system and read only?

Yes. That would be very interesting to know. PostgreSQL never
(intentionally) sets these flags, so they must've come from something
else.

If you remove those flags, do they eventually come back on? Is so, you
probably want to install some level of monitoring tool (process
monitor from sysinternals is recommended) to figure out when that gets
set.


> Anybody ever seen this?

Well, at the risk of sounding like a broken clock, yes - with
antivirus or antispyware that sets the flag on things they find
suspicious.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

On Mon, Jun 14, 2010 at 2:35 AM, Magnus Hagander <magnus@hagander.net> wrote:
> On Mon, Jun 14, 2010 at 05:17, John T. Dow <john@johntdow.com> wrote:
>> Apparently the problem boils down to this question: how did some of the files get set to be system and read only?
>
> Yes. That would be very interesting to know. PostgreSQL never
> (intentionally) sets these flags, so they must've come from something
> else.

Being a non-privaledged account, does the postgres user even have the
power to do that?

On Mon, Jun 14, 2010 at 10:57, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On Mon, Jun 14, 2010 at 2:35 AM, Magnus Hagander <magnus@hagander.net> wrote:
>> On Mon, Jun 14, 2010 at 05:17, John T. Dow <john@johntdow.com> wrote:
>>> Apparently the problem boils down to this question: how did some of the files get set to be system and read only?
>>
>> Yes. That would be very interesting to know. PostgreSQL never
>> (intentionally) sets these flags, so they must've come from something
>> else.
>
> Being a non-privaledged account, does the postgres user even have the
> power to do that?

Yes, IIRC any user that has write permissions on a file can set the
attributes, including readonly and system.


--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/