Re: pg_upgrade failure on Windows Server

Hi,

When pg_upgrade is being run by Administrator user to upgrade database from
Windows Server (2003/2008/2012), pg_upgrade fail with permission denied
error. When an application (run by Administrator) create files, the default
ownership for the newly created file/folder goes to administrators group
rather than Administrator user.

https://www.microsoft.com/resources/documentation/windows/xp/all/proddocs/en-us/634.mspx?mfr=true

> System objects: Default owner for objects created by members of the
> administrators group
> Description
> Determines whether the Administrators group or an object creator is the
> default owner of any system objects that are created.
> Default: Administrators group (on servers).

Above setting is available in Windows Server 2003, If I change this policy
setting from “Administrators group" to "object creator", pg_upgrade work
without issue. Unfortunately it got obsolete in later version of Windows
Server. On Windows Server 2008, there seems workaround available i.e.
http://support.microsoft.com/kb/947721 but it did not worked for me.

PFA patch. To run pg_upgrade (and its child processes) without
administrative privileges (when available), I have copied restricted token
logic from src/bin/initdb/initdb.c, that enables pg_upgrade to rerun itself
with removed Administrators group privileges, this way it create files with
the ownership of user running pg_upgrade. With the patch applied,
pg_upgrade (or its child process) don’t create files with administrators
group owner and works fine without issue.

As pg_resetxlog change file in data directory, it do suffer from same
permission issue, it also require similar changes. PFA for pg_resetxlog
fix. Please do let me know if I missed something or more information is
required. Thanks.

Regards,
Muhammad Asif Naeem

On Mon, Jan 5, 2015 at 5:34 AM, Asif Naeem <anaeem(dot)it(at)gmail(dot)com> wrote:

> Hi,
>
> It is been observed that pg_upgrade fail to upgrade on Windows Server
> machine if being run as Administrator user (other local users works fine),
> It fail with the following error message i.e.
>
>> ""c:\pg93\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "c:\pgdata93" -o
>> "-p 3333 -b -c autovacuum_multixact_freeze_max_age=2000000000 -c
>> synchronous_commit=off -c fsync=off -c full_page_writes=off" start >>
>> "pg_upgrade_server_start.log" 2>&1"
>> *failure*
>> There were problems executing """c:\pg93\bin/pg_ctl" -w -l
>> "pg_upgrade_server.log" -D "c:\pgdata93" -o "-p 3333 -b -c
>> autovacuum_multixact_freeze_max_age=2000000000 -c synchronous_commit=off
>> -c fsync=off -c full_page_writes=off" start >>
>> "pg_upgrade_server_start.log" 2>&1""
>> Consult the last few lines of "pg_upgrade_server_start.log" or
>> "pg_upgrade_server.log" for
>> the probable cause of the failure.
>> connection to database failed: could not connect to server: Connection
>> refused (0x0000274D/10061)
>> Is the server running on host "localhost" (127.0.0.1) and
>> accepting
>> TCP/IP connections on port 3333?
>>
>> could not connect to new postmaster started with the command:
>> "c:\pg93\bin/pg_ctl" -w -l "pg_upgrade_server.log" -D "c:\pgdata93" -o
>> "-p 3333 -b -c autovacuum_multixact_freeze_max_age=2000000000 -c
>> synchronous_commit=off -c fsync=off -c full_page_writes=off" start
>> Failure, exiting
>
>
> pg_upgrade_server.log
>
>> PANIC: could not open control file "global/pg_control": Permission denied
>
>
> "global/pg_control” file in data directory owned by
> "BUILTIN\Administrators” that was owned by “Administrator” user before
> pg_upgrade. I have verified it on Windows Server 2003/2008 R2/2012.
>
> Steps to reproduce the issue :
>
>> 1. Login as Administrator on Windows Server 2012 or Windows Server 2008
>> 2. On command prompt run "c:\pg92\bin\initdb -D c:\pgdata92"
>> 3. On command prompt run "c:\pg93\bin\initdb -D c:\pgdata93"
>> 4. On command prompt run "c:\pg93\bin\pg_upgrade -d c:\pgdata92 -D
>> c:\pgdata93 -b c:\pg92\bin -B c:\pg93\bin -p 2222 -P 3333 -v"
>
>
> Issue is reproducible with PG 9.2, 9.3 and 9.4. It is caused by
> pg_resetxlog utility (It is internally used by pg_upgrade) that fail to
> keep correct file permissions/ownership (as required by dbserver process)
> while rewriting "global/pg_control” in data directory. pg_resetxlog utility
> don’t sandbox and make changes in data directory, when it is being run as
> Administrator ; it create files that are owned by "BUILTIN\Administrators”
> group. pg_ctl fails because it is using CreateRestrictedProcess() to
> sandbox and give up administrator privileges that make it fail to open
> "global/pg_control” file (that is now owned by "BUILTIN\Administrators”
> group) in write mode i.e.
>
> 2014-12-19 03:19:34 PST PANIC: 42501: could not open control file
>> "global/pg_control": Permission denied
>> 2014-12-19 03:19:34 PST LOCATION: ReadControlFile,
>> src\backend\access\transam\xlog.c:4458
>
>
> I think Domain users that are members of Administrators group suffer from
> same problem. On Windows Server 2003, changing Group Policy “System
> objects: Default owner for objects created by members of the Administrators
> group” to “Object Creator” fixes the problem. Microsoft obsolete this Group
> Policy in later version of Windows Server. I have tried a workaround (
> http://support.microsoft.com/kb/947721) on Windows Server 2008 R2 but it
> seems did not worked for me. As it is default behavior for Windows Server,
> I would suggest to use CreateRestrictedProcess function (sandbox or
> restricted tokens approach) in pg_resetxlog utility or explicitly change
> ownership to current user instead of "BUILTIN\Administrators” group if
> required (If you agree with the approach I can submit patch for this).
> Please do let me know if I missed something or more information is
> required. Thanks.
>
> Regards,
> Muhammad Asif Naeem
>