Official Windows Installer and Documentation

David G. Johnston schrieb am 27.07.2022 um 21:21:

And then there is the issue of file ownership.

Assuming we want better documentation for this specific issue for
back-patching what would that look like?

Going forward should our installer be creating the postgres user for
consistency with other platforms or not?

Didn't the installer used to do that in earlier releases and that
was removed when Postgres was able to "drop privileges" when the
service is started?

I remember a lot of problems around the specific Postgres service
account when that still was the case.

As far as I can tell, most of the problems of the Windows installer
stem from the fact that it tries to use icacls to set privileges
on the data directory. This seems to fail quite frequently,
causing the infamous "Problem running post-install step" error.

The fact that the installer still defaults to using "c:\Program Files"
for the location of the data directoy might be related to that.
(but then I don't know enough of the internals of the installer
and Windows)

Just my 0.02€

Thomas

Hi,

On Wed, Jul 27, 2022 at 11:36:11PM +0200, Thomas Kellerer wrote:

David G. Johnston schrieb am 27.07.2022 um 21:21:

And then there is the issue of file ownership.

Assuming we want better documentation for this specific issue for
back-patching what would that look like?

Going forward should our installer be creating the postgres user for
consistency with other platforms or not?

Didn't the installer used to do that in earlier releases and that
was removed when Postgres was able to "drop privileges" when the
service is started?

I remember a lot of problems around the specific Postgres service
account when that still was the case.

Note that there's no "official" Windows installer, and companies providing one
are free to implement it the way they want, which can contradict the official
documentation. The download section of the website clearly says that this is a
third-party installer.

For now there's only the EDB installer that remains, but I think that some time
ago there was 2 or 3 different providers.

For the EDB installer, I'm not sure why or when it was changed, but it indeed
used to have a dedicated local account and now relies on "Local System Account"
or something like that. But IIRC, when it used to create a local account the
name could be configured, so there was no guarantee of a local "postgres"
account by then either.

On Wed, Jul 27, 2022 at 6:42 PM Julien Rouhaud <rjuju123@gmail.com> wrote:

Hi,

On Wed, Jul 27, 2022 at 11:36:11PM +0200, Thomas Kellerer wrote:

David G. Johnston schrieb am 27.07.2022 um 21:21:

And then there is the issue of file ownership.

Assuming we want better documentation for this specific issue for
back-patching what would that look like?

Going forward should our installer be creating the postgres user for
consistency with other platforms or not?

Didn't the installer used to do that in earlier releases and that
was removed when Postgres was able to "drop privileges" when the
service is started?

I remember a lot of problems around the specific Postgres service
account when that still was the case.

Note that there's no "official" Windows installer, and companies providing
one
are free to implement it the way they want, which can contradict the
official
documentation. The download section of the website clearly says that this
is a
third-party installer.

For now there's only the EDB installer that remains, but I think that some
time
ago there was 2 or 3 different providers.

For the EDB installer, I'm not sure why or when it was changed, but it
indeed
used to have a dedicated local account and now relies on "Local System
Account"
or something like that. But IIRC, when it used to create a local account
the
name could be configured, so there was no guarantee of a local "postgres"
account by then either.

Our technical definition aside, the fact is our users consider the sole EDB
installer to be official.

If the ultimate solution is to update:

https://www.enterprisedb.com/downloads/postgres-postgresql-downloads

to have its own installation and upgrade supplement to the official
documentation then I'd be fine with that. But as of now the "Installation
Guide" points back to the official documentation, which has no actual
distribution specific information while simultaneously reinforcing the fact
that it is an official installer.

I get sending people to the EDB web services team for download issues since
we don't host the binaries. That aspect of them being third-party doesn't
seem to be an issue.

But for documentation, given the current state of things, whether we amend
our docs or highly encourage the people who are benefiting financially from
being our de facto official Windows installer provider to provide separate
documentation to address this apparent short-coming that is harming our
image in the Windows community, I don't really care, as long as something
changes.

In the end the problem is ours and cannot be simply assigned to a
third-party. So let's resolve it here (on this list, whatever the
solution) where representatives from all parties are present.

David J.

On Wed, Jul 27, 2022 at 07:02:51PM -0700, David G. Johnston wrote:

In the end the problem is ours and cannot be simply assigned to a
third-party. So let's resolve it here (on this list, whatever the
solution) where representatives from all parties are present.

We could amend the pg_upgrade (and maybe other if needed, but I don't see any
other occurences of RUNAS) documentation to be a bit more general, like the
non-windows part of it, maybe something like

For Windows users, you must be logged into an administrative account, and then
start a shell as the user running the postgres service and set the proper path.
Assuming a user named postgres and the binaries installed in C:\Program
Files\PostgreSQL\14:

RUNAS /USER:postgres "CMD.EXE"
SET PATH=%PATH%;C:\Program Files\PostgreSQL\14\bin;

It's ultimately up to the users to adapt the commands to match their
environment.

On Wednesday, July 27, 2022, Julien Rouhaud <rjuju123@gmail.com> wrote:

On Wed, Jul 27, 2022 at 07:02:51PM -0700, David G. Johnston wrote:

In the end the problem is ours and cannot be simply assigned to a
third-party. So let's resolve it here (on this list, whatever the
solution) where representatives from all parties are present.

We could amend the pg_upgrade (and maybe other if needed, but I don't see
any
other occurences of RUNAS) documentation to be a bit more general, like the
non-windows part of it, maybe something like

For Windows users, you must be logged into an administrative account, and
then
start a shell as the user running the postgres service and set the proper
path.
Assuming a user named postgres and the binaries installed in C:\Program
Files\PostgreSQL\14:

RUNAS /USER:postgres "CMD.EXE"
SET PATH=%PATH%;C:\Program Files\PostgreSQL\14\bin;

It's ultimately up to the users to adapt the commands to match their
environment.

Ultimately we do our users the best service if when they operate an
installation using defaults that they have documentation showing how to
perform something like an upgrade that works with those defaults. I don’t
see much point making that change in isolation until it is obvious nothing
better is forthcoming. If the o/s user postgres doesn’t exist then you need
to supply -U postgres cause the install user for PostgresSQL is still
postgres. So why not assume the user is whatever the EDB installer uses
and make that the example? If someone has an install on Windows that uses
the postgres account adapting the command for them should be trivial and
the majority installer users get a command sequence that works.

David J.

"David G. Johnston" <david.g.johnston@gmail.com> writes:

On Wed, Jul 27, 2022 at 6:42 PM Julien Rouhaud <rjuju123@gmail.com> wrote:

Note that there's no "official" Windows installer,

Yeah, that.

Our technical definition aside, the fact is our users consider the sole EDB
installer to be official.
If the ultimate solution is to update:
https://www.enterprisedb.com/downloads/postgres-postgresql-downloads
to have its own installation and upgrade supplement to the official
documentation then I'd be fine with that.

That's what needs to happen. On the Linux side for example, we do
not address packaging-specific behaviors of Devrim's builds, or Debian's
builds, or Red Hat's builds --- all of which act differently, in ways
that are sadly a lot more critical to novices than seasoned users.
If EDB isn't adequately filling in the documentation for the behavior
of their packaging, that's on them.

regards, tom lane

On Wed, Jul 27, 2022 at 07:31:35PM -0700, David G. Johnston wrote:

Ultimately we do our users the best service if when they operate an
installation using defaults that they have documentation showing how to
perform something like an upgrade that works with those defaults.

I don't really agree that it's best service to let users assume that they can
blindly copy/paste some commands without trying to understand them, or how to
adapt them to their specificities. That's in my opinion particularly true on
windows, since to my knowledge most companies will have the binaries installed
in one place (C:\Program Files\PostgreSQL might be frequent), and have the data
stored in another place (D: or other). So I don't think the default command
will actually work for any non toy installation.

So why not assume the user is whatever the EDB installer uses
and make that the example?

Well, IIUC that used to be the case until EDB changed its installer. Maybe the
odds for an impacting change to happen again are low, but it's certainly not a
great idea to assume that the community will regularly check their installer
and update the doc to match what they're doing. So yeah it may be better for
them to provide a documentation adapted to their usage.

I wrote:

If EDB isn't adequately filling in the documentation for the behavior
of their packaging, that's on them.

Having now looked more closely at the pg_upgrade documentation,
I don't think this is exactly EDB's fault; it's text that should
never have been there to begin with. ISTM we need to simply rip out
lines 431..448 of pgupgrade.sgml, that is all the Windows-specific
text starting with

For Windows users, you must be logged into an administrative account, and

That has got nothing to recommend it: we do not generally provide
platform-specific details in these man pages, and to the extent it
provides details, those details are likely to be wrong. We need
look no further than the references to "9.6" to establish that.
Yeah, it says "e.g.", but novices will probably fail to understand
which parts of the example are suitable to copy verbatim and which
aren't. Meanwhile non-novices don't need the example to begin with.
On top of which, the whole para has been inserted into
non-platform-specific text, seemingly with the aid of a dartboard,
because it doesn't particularly connect to what's before or after it.

regards, tom lane

On Wed, Jul 27, 2022 at 8:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

I wrote:

If EDB isn't adequately filling in the documentation for the behavior
of their packaging, that's on them.

Having now looked more closely at the pg_upgrade documentation,
I don't think this is exactly EDB's fault; it's text that should
never have been there to begin with. ISTM we need to simply rip out
lines 431..448 of pgupgrade.sgml, that is all the Windows-specific
text starting with

For Windows users, you must be logged into an administrative account,
and

That has got nothing to recommend it: we do not generally provide
platform-specific details in these man pages, and to the extent it
provides details, those details are likely to be wrong.

I mean, we do provide platform-specific details/examples, it's just that
platform is a source installed Linux platform (though pathless)

Does the avoidance of dealing with other platforms also apply to NET STOP
or do you find that an acceptable variance? Or are you suggesting that
basically all O/S commands should be zapped? If not, then rewriting 442 to
446 to just be the command seems worthwhile. I'd say pg_upgrade warrants
an examples section like pg_basebackup has (though obviously pg_upgrade is
procedural).

I do have another observation:

https://github.com/postgres/postgres/blob/4fc6b6eefcf98f79211bb790ee890ebcb05c178d/src/bin/pg_upgrade/check.c#L665

if (PQntuples(res) != 1 ||
atooid(PQgetvalue(res, 0, 1)) != BOOTSTRAP_SUPERUSERID)
pg_fatal("database user \"%s\" is not the install user",
os_info.user);

Any reason to not inform the DBA the name of the install user here? Sure,
it is almost certainly postgres, but it also seems like an easy win in
order for them, and anyone they may ask for help, to know exactly the name
of install user in the clusters should that end up being the issue.
Additionally, from what I can tell, if that check does fail (or any of the
checks really) it is not possible to tell whether the check was being
performed against the old or new server. The user does not know that
checks against the old server are performed first then checks against the
new one, and there are no banners saying "checking old/new"

David J.

On Wed, Jul 27, 2022 at 09:28:51PM -0700, David G. Johnston wrote:

On Wed, Jul 27, 2022 at 8:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

I wrote:

If EDB isn't adequately filling in the documentation for the behavior
of their packaging, that's on them.

Having now looked more closely at the pg_upgrade documentation,
I don't think this is exactly EDB's fault; it's text that should
never have been there to begin with.  ISTM we need to simply rip out
lines 431..448 of pgupgrade.sgml, that is all the Windows-specific
text starting with

     For Windows users, you must be logged into an administrative account,
and

That has got nothing to recommend it: we do not generally provide
platform-specific details in these man pages, and to the extent it
provides details, those details are likely to be wrong.

I mean, we do provide platform-specific details/examples, it's just that
platform is a source installed Linux platform (though pathless)

Does the avoidance of dealing with other platforms also apply to NET STOP or do
you find that an acceptable variance?  Or are you suggesting that basically all
O/S commands should be zapped?  If not, then rewriting 442 to 446 to just be
the command seems worthwhile.  I'd say pg_upgrade warrants an examples section
like pg_basebackup has (though obviously pg_upgrade is procedural).

I have developed the attached patch to remove RUNAS and SET PATH,
neither of which appear anywhere else in our docs.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Tue, Oct 31, 2023 at 11:16:29AM -0400, Bruce Momjian wrote:

On Wed, Jul 27, 2022 at 09:28:51PM -0700, David G. Johnston wrote:

On Wed, Jul 27, 2022 at 8:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

I wrote:

If EDB isn't adequately filling in the documentation for the behavior
of their packaging, that's on them.

Having now looked more closely at the pg_upgrade documentation,
I don't think this is exactly EDB's fault; it's text that should
never have been there to begin with.  ISTM we need to simply rip out
lines 431..448 of pgupgrade.sgml, that is all the Windows-specific
text starting with

     For Windows users, you must be logged into an administrative account,
and

That has got nothing to recommend it: we do not generally provide
platform-specific details in these man pages, and to the extent it
provides details, those details are likely to be wrong.

I mean, we do provide platform-specific details/examples, it's just that
platform is a source installed Linux platform (though pathless)

Does the avoidance of dealing with other platforms also apply to NET STOP or do
you find that an acceptable variance?  Or are you suggesting that basically all
O/S commands should be zapped?  If not, then rewriting 442 to 446 to just be
the command seems worthwhile.  I'd say pg_upgrade warrants an examples section
like pg_basebackup has (though obviously pg_upgrade is procedural).

I have developed the attached patch to remove RUNAS and SET PATH,
neither of which appear anywhere else in our docs.

Sorry, fixed patch.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.

On Tue, Oct 31, 2023 at 11:24:24AM -0400, Bruce Momjian wrote:

On Tue, Oct 31, 2023 at 11:16:29AM -0400, Bruce Momjian wrote:

On Wed, Jul 27, 2022 at 09:28:51PM -0700, David G. Johnston wrote:

On Wed, Jul 27, 2022 at 8:22 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

I wrote:

If EDB isn't adequately filling in the documentation for the behavior
of their packaging, that's on them.

Having now looked more closely at the pg_upgrade documentation,
I don't think this is exactly EDB's fault; it's text that should
never have been there to begin with.  ISTM we need to simply rip out
lines 431..448 of pgupgrade.sgml, that is all the Windows-specific
text starting with

     For Windows users, you must be logged into an administrative account,
and

That has got nothing to recommend it: we do not generally provide
platform-specific details in these man pages, and to the extent it
provides details, those details are likely to be wrong.

I mean, we do provide platform-specific details/examples, it's just that
platform is a source installed Linux platform (though pathless)

Does the avoidance of dealing with other platforms also apply to NET STOP or do
you find that an acceptable variance?  Or are you suggesting that basically all
O/S commands should be zapped?  If not, then rewriting 442 to 446 to just be
the command seems worthwhile.  I'd say pg_upgrade warrants an examples section
like pg_basebackup has (though obviously pg_upgrade is procedural).

I have developed the attached patch to remove RUNAS and SET PATH,
neither of which appear anywhere else in our docs.

Sorry, fixed patch.

Patch applied.

--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EDB https://enterprisedb.com

Only you can decide what is important to you.