Re: pg_upgrade failure on Windows Server - Mailing list pgsql-bugs
| From | Asif Naeem |
|---|---|
| Subject | Re: pg_upgrade failure on Windows Server |
| Date | |
| Msg-id | CAEB4t-OHNE95=n5U4ySsYkWipQsWeQuTBSJkaYJ63_1VzkzkhA@mail.gmail.com Whole thread Raw |
| In response to | pg_upgrade failure on Windows Server (Asif Naeem <anaeem.it@gmail.com>) |
| Responses |
Re: pg_upgrade failure on Windows Server
(Bruce Momjian <bruce@momjian.us>)
|
| List | pgsql-bugs |
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
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
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.it@gmail.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, exitingpg_upgrade_server.logPANIC: 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:4458I 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
Attachment
pgsql-bugs by date: