Re: Re: Error on Windows server could not open relation base/xxx/xxx Permission denied - Mailing list pgsql-general

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