Thread: pg_ctl behavior on Windows
Hi, So, I am not a Windows native, and here I am mentoring a GSoC student setting up CI on multiple environments, including Windows. In my own development and testing, by habit I do everything from unprivileged accounts, just spinning up an instance in a temp location, running some tests, and shutting it down. So I rarely run into the "I refuse to run from a privileged account" check in postgres. So rarely that it tends to slip my mind. The popular hosted CI services, by contrast, tend to run one's test scripts from a privileged account, so the scripts can just blithely install packages, frob configurations, and so on, and it all just works, Except for just spinning up a one-off postgres instance; that runs afoul of the privilege check. One workaround, of course, is to just use the postgres instance officially supplied by the CI service, already started and listening on the standard port. Then, in fact, you /need/ to be running with privilege, so you can install into the standard locations, frob configs, restart it, etc. Another is for the testing script to use its admin powers to create a new user without admin powers, and switch to that identity for the rest of the show. If I understand correctly what I'm seeing in the pg_ctl source, that would be the sole other option on a non-Windows system; 'pg_ctl start' as root on non-Windows will simply refuse, the same way direct invocation of postgres would. On the other hand, it seems that pg_ctl start on Windows has another trick up its sleeve, and in about 180 lines of fussing with arcane Windows APIs, it can arrange to run under the current identity but with its administrator-ness removed and privileges capped. Which seems cool. But there's a NOTE! in the comment for CreateRestrictedProcess: "Job object will only work when running as a service, because it's automatically destroyed when pg_ctl exits." I haven't been able to find any documentation of what that really means in practical terms, or quite figure it out from the code. Does that mean 'pg_ctl start' won't really work after all from a privileged account, or will seem to work but something will go wrong after the server is ready and pg_ctl exits? Does it mean the tersely-documented 'register' operation must be used, and that's the only way to start from a privileged account? I don't have an especially easy way to experiment on Windows; I can push experiments to the CI service and wait a bit to see what they do, but I figured I'd ask here first. Regards, -Chap
On Sat, Jul 18, 2020 at 5:33 AM Chapman Flack <chap@anastigmatix.net> wrote: > > But there's a NOTE! in the comment for CreateRestrictedProcess: "Job object > will only work when running as a service, because it's automatically > destroyed when pg_ctl exits." > > I haven't been able to find any documentation of what that really means > in practical terms, or quite figure it out from the code. Does that mean > 'pg_ctl start' won't really work after all from a privileged account, or > will seem to work but something will go wrong after the server is ready > and pg_ctl exits? Does it mean the tersely-documented 'register' operation > must be used, and that's the only way to start from a privileged account? > I don't think so. I think you can use 'pg_ctl start' to achieve that. I think the JOBS stuff is primarily required when we use 'register' operation (aka runs server via service). For example, if you see one of the Job options "JOB_OBJECT_LIMIT_DIE_ON_UNHANDLED_EXCEPTION", it suppresses dialog box for a certain type of errors and causes a termination of the process with the exception code as the exit status (See [1]) which I think is essential for a service. > I don't have an especially easy way to experiment on Windows; I can push > experiments to the CI service and wait a bit to see what they do, but > I figured I'd ask here first. > I have tried and 'pg_ctl stuff seems to be working for a privileged account. See below: postgres.exe -D ..\..\Data Execution of PostgreSQL by a user with administrative permissions is not permitted. The server must be started under an unprivileged user ID to prevent possible system security compromises. See the documentation for more information on how to properly start the server. pg_ctl.exe start -D ..\..\Data waiting for server to start....2020-07-18 14:53:46.120 IST [8468] LOG: starting PostgreSQL 14devel, compiled by Visual C++ build 1915, 64-bit 2020-07-18 14:53:46.136 IST [8468] LOG: listening on IPv6 address "::1", port 5432 2020-07-18 14:53:46.136 IST [8468] LOG: listening on IPv4 address "127.0.0.1", port 5432 2020-07-18 14:53:46.214 IST [7512] LOG: database system was shut down at 2020-07-18 14:53:22 IST 2020-07-18 14:53:46.245 IST [8468] LOG: database system is ready to accept connections done server started I have run above two commands from an account with administrative privilege and 'pg_ctl start' is working. I have further tried a few operations after connecting with the client and everything is working fine. [1] - https://docs.microsoft.com/en-us/windows/win32/api/winnt/ns-winnt-jobobject_basic_limit_information -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
On 07/18/20 05:46, Amit Kapila wrote: > I don't think so. I think you can use 'pg_ctl start' to achieve that. > I think the JOBS stuff is primarily required when we use 'register' > operation (aka runs server via service). For example, if you see one > of the Job options "JOB_OBJECT_LIMIT_DIE_ON_UNHANDLED_EXCEPTION", it > suppresses dialog box for a certain type of errors and causes a > termination of the process with the exception code as the exit status Thanks very much, that helps a lot. I still wonder, though, about some of the other limits also placed on that job object, such as JOB_OBJECT_SECURITY_NO_ADMIN | JOB_OBJECT_SECURITY_ONLY_TOKEN Those seem closely related to the purpose of CreateRestrictedProcess. Does the NOTE! mean that, when not running as a service, the job object disappears as soon as pg_ctl exits, and does the job object's disappearance simply mean those limits are no longer enforced for the remaining life of the process? Or do they remain in effect for the process even after the job object is reclaimed (and if so, what does the NOTE! really mean)? I could add that for my current purpose, running a few tests on a CI virtual host where everything is admin anyway and it all gets wiped after the test, I don't really care whether those restrictions are enforced, and in fact the whole "I refuse to start as admin" check seems a punctilious headache. But for other uses, what that NOTE! means about those restrictions might matter more, or even be worth mentioning in the docs. Regards, -Chap
On 7/18/20 5:46 AM, Amit Kapila wrote: > >> I don't have an especially easy way to experiment on Windows; I can push >> experiments to the CI service and wait a bit to see what they do, but >> I figured I'd ask here first. >> > I have tried and 'pg_ctl stuff seems to be working for a privileged > account. See below: > postgres.exe -D ..\..\Data > Execution of PostgreSQL by a user with administrative permissions is > not permitted. > The server must be started under an unprivileged user ID to prevent > possible system security compromises. See the documentation for > more information on how to properly start the server. > > pg_ctl.exe start -D ..\..\Data > waiting for server to start....2020-07-18 14:53:46.120 IST [8468] LOG: > starting PostgreSQL 14devel, compiled by Visual C++ build 1915, > 64-bit > 2020-07-18 14:53:46.136 IST [8468] LOG: listening on IPv6 address > "::1", port 5432 > 2020-07-18 14:53:46.136 IST [8468] LOG: listening on IPv4 address > "127.0.0.1", port 5432 > 2020-07-18 14:53:46.214 IST [7512] LOG: database system was shut down > at 2020-07-18 14:53:22 IST > 2020-07-18 14:53:46.245 IST [8468] LOG: database system is ready to > accept connections > done > server started > > I have run above two commands from an account with administrative > privilege and 'pg_ctl start' is working. I have further tried a few > operations after connecting with the client and everything is working > fine. > This has been true for a long time, and since commit ce5d3424d6 we've been able to run all the regression and TAP tests safely under an administrative account. cheers andrew -- Andrew Dunstan https://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
On Sat, Jul 18, 2020 at 6:31 PM Chapman Flack <chap@anastigmatix.net> wrote:
>
> On 07/18/20 05:46, Amit Kapila wrote:
> > I don't think so. I think you can use 'pg_ctl start' to achieve that.
> > I think the JOBS stuff is primarily required when we use 'register'
> > operation (aka runs server via service). For example, if you see one
> > of the Job options "JOB_OBJECT_LIMIT_DIE_ON_UNHANDLED_EXCEPTION", it
> > suppresses dialog box for a certain type of errors and causes a
> > termination of the process with the exception code as the exit status
>
> Thanks very much, that helps a lot. I still wonder, though, about some
> of the other limits also placed on that job object, such as
> JOB_OBJECT_SECURITY_NO_ADMIN | JOB_OBJECT_SECURITY_ONLY_TOKEN
>
> Those seem closely related to the purpose of CreateRestrictedProcess.
> Does the NOTE! mean that, when not running as a service, the job object
> disappears as soon as pg_ctl exits,
>
From the comments in that part of code, it seems like Job object will
be closed as soon as pg_ctl exits. However, as per my understanding
of specs [1], it will be closed once the process with which it is
associated is gone which in this case should be the new process
created with "CreateProcessAsUser". This has been added by the below
commit, so Magnus might remember something about this.
commit a25cd81007e827684343a53a80e8bc90f585ca8e
Author: Tom Lane <tgl@sss.pgh.pa.us>
Date: Fri Feb 10 22:00:59 2006 +0000
Enable pg_ctl to give up admin privileges when starting the server under
Windows (if newer than NT4, else works same as before).
Magnus
[1] - https://docs.microsoft.com/en-us/windows/win32/api/winbase/nf-winbase-createjobobjecta
--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com