dropping privileges on windows vs privileged accounts

Hi,

While working to address some of Dave's concerns at [1], I encountered the odd
issue of tests failing because postmaster not being allowed to open
pg_control. This did not happen for all tests, but for a lot of tests.

For example, the only output in
pg_upgrade/003_logical_slots/log/003_logical_slots_oldpub.log would be

2024-07-06 00:31:58.293 UTC [4432] PANIC: could not open file "global/pg_control": Permission denied

For some other tests it was pg_regress --config-auth that couldn't open
pg_hba.conf for writing.

I spent an embarassingly large amount of time debugging this. Unfortuntely I
couldn't reproduce this locally, just in Dave's github-actions environment.

After going down *many* rabbitholes, I figured out that this is due to a
poorly documented peculiarity of windows file-ownership logic - which
apparently is only active when "UAC" is disabled - which is the case for the
images github actions [2].

From what I gather from old documentation [3], when a highly privileged user
creates a directory/file with UAC disabled, the newly created object is *not*
owned by the user, but magically owned by the "Administrators" group.

Normally that's kinda fine, the user presumably continues to be a member of
the admin group and can access the file that way. However, this blows up when
combined with pg_ctl dropping privileges - after the privileges are dropped,
the user is *not* considered a member of the administrators group anymore.
And boom, postmaster can't write to pg_control in some circumstances.

What made this nastier is that this only applied to *some* tests, not
all. Sometimes it works, because the database is created via initdb, which
does also drop privileges (albeit slightly differently). However, that
doesn't get us very far:
1) initdb template gets copied (we could fix this by adding /sec to robocopy)
2) pg_basebackup doesn't do the get_restricted_token() dance (we could add that)
3) there are quite a few other sources of data directories being copied,
e.g. Cluster.pm's init_from_backup()

Once one knows what the issue is, it's not too hard to work around - adding an
inheritable ACL explicitly granting the user permissions works. E.g. with
icacls.exe . /inheritance:e /grant 'runneradmin:(OI)(CI)F';
(with the current user being runneradmin)

That way the current user has access, even if not considered a domain admin
anymore.

The only other postgres person that referenced this issue is Alexander Lakhin,
at [4].

This email is partially just so I have something to find if I ever
re-encounter this issue. I intend to forget this ASAP.

Greetings,

Andres Freund

[1] https://www.postgresql.org/message-id/CA%2BOCxowQhMHFNRLTsXNuJpC96KRtSPHYKJuOS%3Db-Zrwmy-P4-g%40mail.gmail.com
[2] https://github.com/actions/runner-images/blob/ca499b86975e62edd4a0ac336de94af096635838/images/windows/scripts/build/Configure-BaseImage.ps1#L28-L31
[3] https://community.netapp.com/t5/AFF/Owner-on-newly-created-files-and-folders-default-to-built-in-Administrators/td-p/146645
[4] https://www.postgresql.org/message-id/3f72f608-88ab-bd43-b7de-685c26e69421%40gmail.com