Database refuses to start on windows with privileged user

[dpeger]

With versions 1.2.8 and above running

EmbeddedPostgres.builder().start();

on Windows with a privileged user results in the this log output followed by a timeout exception:

2021-07-23 11:27:41,101 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - The files belonging to this database system will be owned by user "dpeger". [] []
2021-07-23 11:27:41,102 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - This user must also own the server process. [] []
2021-07-23 11:27:41,102 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres -  [] []
2021-07-23 11:27:41,102 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - The database cluster will be initialized with locale "English_United Kingdom.1252". [] []
2021-07-23 11:27:41,102 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - The default text search configuration will be set to "english". [] []
2021-07-23 11:27:41,102 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres -  [] []
2021-07-23 11:27:41,102 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - Data page checksums are disabled. [] []
2021-07-23 11:27:41,102 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres -  [] []
2021-07-23 11:27:41,102 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - fixing permissions on existing directory D:/Temp/2/epg5065321025544086736 ... ok [] []
2021-07-23 11:27:41,104 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - creating subdirectories ... ok [] []
2021-07-23 11:27:41,168 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - selecting default max_connections ... 100 [] []
2021-07-23 11:27:41,231 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - selecting default shared_buffers ... 128MB [] []
2021-07-23 11:27:41,231 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - selecting default timezone ... CET [] []
2021-07-23 11:27:41,232 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - selecting dynamic shared memory implementation ... windows [] []
2021-07-23 11:27:41,235 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - creating configuration files ... ok [] []
2021-07-23 11:27:41,894 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - running bootstrap script ... ok [] []
2021-07-23 11:27:43,290 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - performing post-bootstrap initialization ... ok [] []
2021-07-23 11:27:53,826 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - syncing data to disk ... ok [] []
2021-07-23 11:27:53,826 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres -  [] []
2021-07-23 11:27:53,826 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres - Success. You can now start the database server using: [] []
2021-07-23 11:27:53,826 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres -  [] []
2021-07-23 11:27:53,826 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres -     D:/Temp/2/embedded-pg/PG-0889a6c23fa62a15a75cf8774df8b36a/bin/pg_ctl -D ^"D^:^\Temp^\2^\epg5065321025544086736^" -l logfile start [] []
2021-07-23 11:27:53,826 [initdb:id(1711641083)] INFO  i.z.t.d.p.e.EmbeddedPostgres -  [] []
2021-07-23 11:27:53,838 [main] INFO  i.z.t.d.p.e.EmbeddedPostgres - df83925d-b31a-42cf-82f9-cc52176ce149 initdb completed in 00:00:12.807 [] []
2021-07-23 11:27:53,851 [main] INFO  i.z.t.d.p.e.EmbeddedPostgres - df83925d-b31a-42cf-82f9-cc52176ce149 postmaster started as java.lang.ProcessImpl@27fde870 on port 59767.  Waiting up to PT10S for server startup to finish. [] []
2021-07-23 11:27:53,983 [postgres:id(670951536)] INFO  i.z.t.d.p.e.EmbeddedPostgres - Execution of PostgreSQL by a user with administrative permissions is not [] []
2021-07-23 11:27:53,983 [postgres:id(670951536)] INFO  i.z.t.d.p.e.EmbeddedPostgres - permitted. [] []
2021-07-23 11:27:53,983 [postgres:id(670951536)] INFO  i.z.t.d.p.e.EmbeddedPostgres - The server must be started under an unprivileged user ID to prevent [] []
2021-07-23 11:27:53,984 [postgres:id(670951536)] INFO  i.z.t.d.p.e.EmbeddedPostgres - possible system security compromises.  See the documentation for [] []
2021-07-23 11:27:53,984 [postgres:id(670951536)] INFO  i.z.t.d.p.e.EmbeddedPostgres - more information on how to properly start the server. [] []
2021-07-23 11:28:04,075 [main] WARN  c.r.t.t.j.PostgresEmbeddedTestDatabase - No database instance available. Database already closed? [] []

This is caused by switching from pg_ctl to postgres executable for starting up postgres in PR #39. According to the PR this was done to get a proper process tree to ensure the postgres process doesn't stay alive after Java is terminated. Which makes perfect sense.

However as pg_ctl is the recommended/official way to start postgres I think there should at least be an option to switch to pg_ctl or make it system dependent. That is use postgres under unix and pg_ctl under Windows.

Version 1.2.7 is working fine for me under windows.

[jameshilliard]

However as pg_ctl is the recommended/official way to start postgres

This doesn't appear to be the only official way for starting postgres, see here which in many cases does not use pg_ctl, for example when run as a managed systemd service you would use /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data which avoids pg_ctl I think for similar reasons we want to avoid it.

That is use postgres under unix and pg_ctl under Windows.

Might be a good idea to investigate how pg_ctl is avoiding the permissions error on windows and replicate that logic.

[dpeger]

All examples mainly seem to refer to unix, where running programs as non-root user is the normal case. However on windows (especially for software development) using accounts that are local administrators is still the standard.

Might be a good idea to investigate how pg_ctl is avoiding the permissions error on windows and replicate that logic.

The logic seems to be in CreateRestrictedProcess (https://github.com/postgres/postgres/blob/8fa6e6919c1aaa6f74c74e16452aaf0b5f3b4cd5/src/bin/pg_ctl/pg_ctl.c#L1753). But I'm not too eager that this can be rebuilt in Java or that this is even the right way.

[jameshilliard]

The logic seems to be in CreateRestrictedProcess (https://github.com/postgres/postgres/blob/8fa6e6919c1aaa6f74c74e16452aaf0b5f3b4cd5/src/bin/pg_ctl/pg_ctl.c#L1753). But I'm not too eager that this can be rebuilt in Java or that this is even the right way.

Hmm, maybe wrapping it in runas could be used to restrict the process. Something along these lines maybe?

runas.exe /trustlevel:0x20000 postgres.exe

[dpeger]

Indeed using runas with trustlevel 0x20000 can be used to start postgres using the postgres binary under windows as administrative user. But this will create a new independent process (actually even new console window) that will not get terminated on JVM exist, which was the reason not to use pg_ctl in the first place. So not a real option.

I still think pg_ctl should be the preferred way of starting postgres under windows. Even initdb says to do so:

2021-07-26 12:36:49,456 [initdb:id(734971558)] INFO  i.z.t.d.p.e.EmbeddedPostgres - Success. You can now start the database server using: [] []
2021-07-26 12:36:49,456 [initdb:id(734971558)] INFO  i.z.t.d.p.e.EmbeddedPostgres -  [] []
2021-07-26 12:36:49,456 [initdb:id(734971558)] INFO  i.z.t.d.p.e.EmbeddedPostgres -     D:/Temp/2/embedded-pg/PG-0889a6c23fa62a15a75cf8774df8b36a/bin/pg_ctl -D ^"D^:^\Temp^\2^\epg7943541016448514640^" -l logfile start [] []

And I'd value the possibility to use EmbeddedPostgres at all higher than potential zombie processes, if EmbeddedPostgres is not properly shutdown. As said at least providing an option to use pg_ctl would be good.

[tomix26]

@dpeger Thanks for the report.

From my point of view, using postgres executable is the correct way to start the server in this case. Especially because of the process logging, which would stop after the server has been started if pg_ctl executable was used.

So as @jameshilliard said, we need to figure out how pg_ctl is avoiding the permissions error and use the same solution to fix the problem in case of postgres executable.

[dpeger]

@tomix26 thanks for the reply. I totally agree with @jameshilliard and you. If possible postgres.exe should be preferred over pg_ctl for the usecase of this library. I'm however not too confident that this can be achieved on windows with java. But I'd be happy to be proven wrong!

[tomix26]

I've finally tested it and everything worked as expected. There were no problems. Tested on Windows 10 Pro, version 20H2 and also version 21H1, with the same result. Note that I tested it as admin and also as standard user.

So what system are you using? Could you provide more information to reproduce the issue?

[dpeger]

Similar to issue #67 I could reproduce this problem in a virtual machine based on Windows 2019 Server (10.0.17763 Build 17763) with the Administrator account. On this machine other accounts (local administrators or not) worked while on my actual machine (some OS) my AD account doesn't.

[ErwanLeroux]

I have the same problem on windows 7, maybe something change in Windows 10 that allows it on that version.