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

John,

> It's the user created by the one-click installer. I believe it owns the
> postgres data directory and is used to start the server. Other than that,
> the intention is for this user to have no other file privileges. The default
> is "postgres" but it could be anything.
>
> doing the default install, the installer uses cacls to correctly set the
access privileges to the database files for the created user.

AS the database directory was zipped and copied from computer to computer;
something different happened .... I know it is possible to transfer NTFS
files keeping their ACL, but I have no information HOW the files were
transferred.

HOW containing information as in: - under which user account - using which
method - preserving or not preserving ACLs

>
> >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. He was only available until
> 4PM today and we just discovered what was happening shortly before that
> point. The people that do their security should be available Monday and we
> can ask them this type of question.
>

> please check out the "cacls" command line utitlity of windows. With this
you should be able to print out all privileges of the PostgreSQL data
directory to a text file, which can be transferred to you. You can then
compare the privileges of the files on the non-working computer with the
working computer.

You can especially check the privileges for the toast-files (the ones named
in the error message)

>Any idea of what to look for?

when working with the first PostgreSQL versions on windows, I was surprised
by a group policy randomly taking away the "run as service" privilege for
the local user. Just want to point out that system-level changes which can
affect PostgreSQL WITHOUT anything in PostgreSQL.

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
> time when I wasn't available. (They zipped, then unzipped after logging in
> as the proper user.)
>
> Okay, that experimenting is good thing to do :) on development systems.

> As a developer for multiple clients, I need easy access to my development
> copies of my clients' postgres data files. Therefore I have experimented
> with allowing my own userid to have access to the "data" directory and the
> subdirectories and files. I believe postgres doesn't care if you allow extra
> users, as long as "postgres" still has the proper access.
>
> Postgres does not even know about extra access privileges. Only the
installer does something with access rights during database installation;
after that everything changing the access permissions is from outside.

(One possible scenario: the postgres service being started with its
authorization set to "local system" - that would explain your files with
owner "system". And "local system" (or similar) is the default for SQL
Server and Oracle ... the danger that one good-willing local administrator
changed the logon-credentials?)

Harald

> 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.
> >
>
>
>

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