Обсуждение: 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