Re: BUG #16259: Cannot Use "pg_ctl start -l logfile" on Clean Installon Windows Server 2012/2016 - Mailing list pgsql-bugs

On Fri, Feb 14, 2020 at 2:57 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Heath Lord <heath.lord@crunchydata.com> writes:
> >    So we had a lot of issues when setting up Dory to work with the
> > build farm and we had to
> > open up some permissions to fix these issues.  We added the "CREATOR
> > OWNER" to have
> > permissions on any files created under the build farm directory.  This
> > is to fix broken inheritance on
> > Windows Server machines when using a directory created at the root
> > (C:\, D:\, etc).  With this change, when the
> > logfile gets created by pg_ctl it will default to have the permissions
> > necessary to write to it.
> >    On a clean install of Server 2016 or Server 2012 this is not the
> > case and without modifying the default
> > permissions on an install done anywhere other than in the user's home
> > directory.  In the PG 12.1 release
> > we do not see this issue and the logfile is created with the necessary
> > permissions for the creator to write
> > to this file.
> >    We have now reproduced this issue using 3 different versions of
> > Server 2016 and a version of Server 2012.
> > If we change the location of the logfile to be anywhere inside of the
> > current user's home directory, then
> > we do not see the issue described.  However, if you try to put the
> > logfile into any directory outside of the
> > current user's home directory this issue will manifest.
>
> Hm.  So that maybe explains why dory isn't showing this problem,
> but it's still pretty unclear exactly what the problem *is*,
> or what we should do about it.  It sounds like 'fopen(..., "a")'
> is creating the file with different permissions than CMD.EXE
> would do, but why should that be?  And if that is what's happening,
> why aren't lots of other places falling over in a similar fashion?
> It's not to be credited that the postmaster logfile is magically
> different from every other file we create.
>
> I'm inclined to guess that maybe what we need is some different
> parameters to the CreateFile call in pgwin32_open() (which is
> what that fopen devolves to).  The lpSecurityAttributes parameter
> looks suspicious --- I see that we're just setting
>
>         sa.lpSecurityDescriptor = NULL;
>
> and maybe that's not sufficient in whatever environment you're
> testing in?  There's some stuff in
>
> https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-createfilea
>
> about inherited attributes, which I don't understand, but perhaps
> that's related to what you're talking about.
>
>                         regards, tom lane

   Another interesting thing that we have found is that this issue
only manifests
itself if you are trying to launch the pg_ctl command as a privileged
user.  If you
try to run this command as a user who is not an Administrator, then we do not
see this issue.  This is another reason why Dory does not exhibit this behavior.
  With this finding, it has to be something with how pg_ctl is restricting the
user when running the postgres.exe command from the CMD.exe process,
so we are not trying to launch postgres as a privileged user.
  The fact that I am able to launch the postgres processes using the pg_ctl
command as a privileged user is what seems to be causing this issue.
The fopen appears to be running as the elevated Administrator, while the
postgres.exe is being run with restricted privileges.
   In the pg_ctl code, I see that when we spawn the CMD process we are
doing it by restricting the users privileges.  So, what I think is happening
is that the fopen is being run by the Administrator with full privileges,
and then the CMD process is being run with restricted privileges so it
can run the postgres process.  It does not have the necessary privileges
to write to the logfile that was created before.

-Heath