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

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(dot)klaver(at)gmail(dot)com