Database initialization fails on Windows for custom data directory

[dpeger]

If I use a custom data directory

EmbeddedPostgres.builder().setDataDirectory("C:/ws/projects/zonkyio/embedded-postgres/test-data").start();

Creation of the database fails:

2021-07-26 14:14:14,540 [main] INFO  i.z.t.d.p.e.EmbeddedPostgres - Detected a Windows x86_64 system [] []
2021-07-26 14:14:14,548 [main] INFO  i.z.t.d.p.e.DefaultPostgresBinaryResolver - Detected distribution: 'Unknown' [] []
2021-07-26 14:14:14,549 [main] INFO  i.z.t.d.p.e.DefaultPostgresBinaryResolver - System specific postgres binaries found: 'postgres-windows-x86_64.txz' [] []
2021-07-26 14:14:14,620 [main] INFO  i.z.t.d.p.e.EmbeddedPostgres - Postgres binaries at D:\Temp\2\embedded-pg\PG-0889a6c23fa62a15a75cf8774df8b36a [] []
2021-07-26 14:14:14,676 [initdb:id(333034015)] INFO  i.z.t.d.p.e.EmbeddedPostgres - The files belonging to this database system will be owned by user "dpeger". [] []
2021-07-26 14:14:14,676 [initdb:id(333034015)] INFO  i.z.t.d.p.e.EmbeddedPostgres - This user must also own the server process. [] []
2021-07-26 14:14:14,676 [initdb:id(333034015)] INFO  i.z.t.d.p.e.EmbeddedPostgres -  [] []
2021-07-26 14:14:14,676 [initdb:id(333034015)] INFO  i.z.t.d.p.e.EmbeddedPostgres - The database cluster will be initialized with locale "English_United Kingdom.1252". [] []
2021-07-26 14:14:14,676 [initdb:id(333034015)] INFO  i.z.t.d.p.e.EmbeddedPostgres - The default text search configuration will be set to "english". [] []
2021-07-26 14:14:14,676 [initdb:id(333034015)] INFO  i.z.t.d.p.e.EmbeddedPostgres -  [] []
2021-07-26 14:14:14,677 [initdb:id(333034015)] INFO  i.z.t.d.p.e.EmbeddedPostgres - Data page checksums are disabled. [] []
2021-07-26 14:14:14,677 [initdb:id(333034015)] INFO  i.z.t.d.p.e.EmbeddedPostgres -  [] []
2021-07-26 14:14:14,677 [initdb:id(333034015)] INFO  i.z.t.d.p.e.EmbeddedPostgres - fixing permissions on existing directory C:/ws/projects/zonkyio/embedded-postgres/test-data ... initdb: could not change permissions of directory "C:/ws/projects/zonkyio/embedded-postgres/test-data": Permission denied [] []
java.lang.IllegalStateException: Process [D:\Temp\2\embedded-pg\PG-0889a6c23fa62a15a75cf8774df8b36a\bin\initdb.exe, -A, trust, -U, postgres, -D, C:\ws\projects\zonkyio\embedded-postgres\test-data, -E, UTF-8] failed
	at io.zonky.test.db.postgres.embedded.EmbeddedPostgres.system(EmbeddedPostgres.java:632)
	at io.zonky.test.db.postgres.embedded.EmbeddedPostgres.initdb(EmbeddedPostgres.java:252)
	at io.zonky.test.db.postgres.embedded.EmbeddedPostgres.<init>(EmbeddedPostgres.java:159)
	at io.zonky.test.db.postgres.embedded.EmbeddedPostgres$Builder.start(EmbeddedPostgres.java:583)
...

This is very strange as my account definitively has "Full Control" privileges (inherited from local Administrators group) on the respective folder. However calling initdb directly from command line fails with the same permission denied error. I tested a little bit and was able to make initdb succeed by granting "Write" permission to the data directory to my account directly. Additionally initdb created non existing data directories on the fly (with the correct permissions).

Although this actually seems to be an issue with posgres' initdb utility I think it would be good to workaround this problem by not eagerly creating the data directory in EmbeddedPostgres:

embedded-postgres/src/main/java/io/zonky/test/db/postgres/embedded/EmbeddedPostgres.java

Line 154 in 81236d6

mkdirs(this.dataDirectory);

Simply omitting this line made database initialization succeed.

[tomix26]

Thanks for the report. I need to test it first, then I'll let you know. However, I don't think removing that line would solve the problem, because on the next line a lock file is created and that also requires the proper permissions.

[dpeger]

Simply creating a File instance doesn't require any permissions or the file to be present.

The lock file is first used in lock(), which is called after initdb ran (and created the data directory with correct permissions).

I'd be happy to provide a PR. But writing a unit test for this problem is rather complicated as it requires permissions to be inherited from a group...

[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]

I was able to reproduce the issue in a virtual machine based on Windows 2019 Server (10.0.17763 Build 17763) with the Administrator account.

I was however not able to reproduce on the same machine with another account that is member of the local Administrators group or with a normal user account without administrative privileges.

I noticed that there are differences in the permissions for newly created folders. Folders created by the Administrator user have these permissions

while folders created by the AnotherAdmin user have these permissions

That is there is a dedicated entry granting AnotherAdmin full control. As adding a dedicated Write-permission entry for Administrator also resolves the problem, this is probably the reason why its working for AnotherAdmin but not Administrator.

On my actual machine folders I create with my AD account have no dedicated permission entry for my user - similar to Administrator in the virtual setup. I'm however unsure where these entries (or the lack of) come from or how to change this.

[dpeger]

@tomix26 would you accept a PR with the change described earlier?

Although this actually seems to be an issue with posgres' initdb utility I think it would be good to workaround this problem by not eagerly creating the data directory in EmbeddedPostgres:

embedded-postgres/src/main/java/io/zonky/test/db/postgres/embedded/EmbeddedPostgres.java

Line 154 in 81236d6

mkdirs(this.dataDirectory);

Simply omitting this line made database initialization succeed.

[tomix26]

@dpeger I would prefer to leave it as is. I think the problem is not the calling of the mkdir command, but the insufficient permissions to access the file system. Removing this line maybe solve the database initialization, but a similar problem occurs when downloading and extracting postgres executables, where proper filesystem access is also required.

[dpeger]

I think manual creation of the data directory is pointless as it is done by initdb nonetheless. Seemingly there is are subtle differences in how the directory is created which cause the permission issues on windows when using mkdir but cause no issues when the directory is created in the context of initdb. This is not a permission issue in the first place as my user has sufficient permissions and download and everything else doesn't cause any issues.

As the manual creation is pointless and causing problems, I see no point in keeping it. But it's not up to me to decide.

If you really want to keep it, making the eager creation of the data directory optional (with default to eagerly create the directory) would be another possiblity.