Re: Putting the O/S user for "local" "peer" authentication in the "postgres" group vs chmod'ing the "pg*.conf" files to be readable by "all"

Some repetition of what Adrian just posted ahead...

On Wed, Nov 2, 2022 at 3:31 PM Bryn Llewellyn <bryn(at)yugabyte(dot)com> wrote:

>
> I did exactly that. And I selected "Linux" and under that "Ubuntu". Notice
> that I did NOT select "Debian", though it was on offer, because that's not
> what I have. If Ubuntu and Debian were effectively the same, then there
> wouldn't be two distinct choices.
>

Some of what I wrote assumed a familiarity with the Linux distribution
ecosystem...Debian originated the opinions about how to install PostgreSQL;
Ubuntu, by virtue of building upon that distribution, inherited those
opinions. In particular, they are considerably different than what
CentOS/Red Hat thinks.

> I did the whole thing from scratch after trashing my provisional attempt.
> (This is easily afforded when you use a VM. Am I the only person who does
> this: practice, make mistakes, learn, trash, and then do it for real?)
>

I do this all of the time myself - leveraging Ansible for
infrastructure-as-code as well.

> In summary, then, I followed a link from the PG doc to a site whose URL
> makes it sound "official", selected my environment, and followed some
> simple steps as specified. That doesn't sound like "opinionated" to me.
>

Debian/Ubuntu are opinionated, and enforce those opinions via the Apt-based
packaging that their community creates from the PostgreSQL source code.
There are many such communities out there (BSD, Windows, Red Hat, etc...)
and basically no one within core is interested in worrying about how those
different operating systems work at the DBA level. A generally shared
adherence to POSIX and the facilities provided by the C language make that
practical.

>
>
> https://ubuntu.com/server/docs/databases-postgresql
>
> Of course I'd read that right at the outset. The subtext is loud and
> clear. You need to do some things as the "postgres" user and some things as
> "root". That's why I enabled "sudo" for "postgres" (just as the code
> examples on that page imply).
>

Honestly, a server running PostgreSQL should have, at minimum, three
relevant users. Root, Postgres, and the user the system admin logs in as.
This third user should sudo to install PostgreSQL, su to initially
configure the system using the cluster owner (see my -hackers email for the
documentation patches this has inspired), then sudo again to create any
additional local users you might want if you aren't going to use the
postgres user name the packaging gives you for everyday use. The elided
script below should be run as your system admin user, not root nor postgres
(or just run it as root and su for the initdb part).

>
> But it seems that I'm now in a regime where critical PG utilities don't
> work like the PG doc says, and where what you need, "pg_ctlcluster", isn't
> even mentioned in the PG doc. (I do see that it's present in my env and is
> properly wired up from "/usr/bin". (Actually, it's right there on that
> directory as an executable file.)
>
> How can it be that the PG doc itself leads you by the hand to a regime
> where you need to use undocumented features?
>

The documentation tries to make clear that if you use third-party packaging
to install PostgreSQL (which most people should) that the documentation for
the packaging should describe this layer where PostgreSQL and the operating
system intersect. You even quoted it: "follow the instructions for the
specific platform.", though reading that now I think something along the
lines of:

"Additionally, while reading the next chapter, Server Setup and Operation,
is recommended if you are using a binary package the setup and operational
environment it creates is likely to be somewhat different than what is
described in this documentation. Please read the documentation for the
packages you install to learn how it behaves and what additional
platform-specific features it provides."

I haven't publicly (at least not recently...) voiced an opinion on the
quality of the Apt documentation, nor have volunteered to work on it. But
regardless it is an entirely different department run by volunteers that
package up many different applications, not just PostgreSQL. That
decentralization and spreading out of responsibilities is simply how this
overall community is structured and your frustrations stem a great deal
from this particular seam.

> Meanwhile, can I appeal to one of you simply to tell me, here, the magic
> spells that I must write so that I can remain as the "postgres" O/S user
> and achieve my "trash an extant cluster and start again from a
> well-defined, pristine state" goal?
>
> p.s. I did attempt (in a VM that I then trashed) to use "pg_ctl" and
> "initdb". It all "worked" in that there were no errors and I got a new
> cluster.
>

I think...

https://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html

pg_dropcluster --stop 11 main
pg_createcluster 11 main

Again, I don't presently have a desire to investigate the usability of the
Apt packaging's documentation and overall usability for someone coming to
it from the PostgreSQL website. I do suspect, from past experience and
your comments here, that such an effort would be beneficial, though I know
not how many users are running Debian/Ubuntu that would see such
improvements. As I noted, for most it is simply enough to install
PostgreSQL and get the single running cluster managed by pg_ctlcluster
manually and integrated with systemd. Resetting is done by dropping the
container/VM and starting anew - letting a provisioning script flesh out
the customizations.

showed me that the "config_file" location, and that of the "hba_file" and
> the "ident_file" were now on the data directory
> ("/var/lib/postgresql/11/main") and NOT in their original locations on
> "/etc/postgresql/11/main/" where I had customized them following the doc.
>

Yes, because initdb has completely different opinions about where those
files belong than the Debian/Ubuntu packaging. Specifically, PostgreSQL
documentation is written to expect every file initdb creates to go into the
data directory (usually plopped under /usr/local/pgsql) while Debian makes
use of both /var and /etc in proscribing where different kinds of files
belong, and then specifically for PostgreSQL makes dealing with
multi-version/multi-cluster setups easy building both the version and a
label into the cluster path.

>
> It's hard to imagine a more confusing design. What thinking underlies it?
>

The teams responsible for packaging up source code and building
user-friendly installers for their operating systems are all different from
the core team that doesn't really have to worry about distribution. Aside
from the presence of, and commentary on, the relevant file configuration
variables.

https://www.postgresql.org/docs/current/runtime-config-file-locations.html

In short, it is actually a very good design given the constraint that the
software has to work on so many different operating systems for many years
of supported lifetime.

David J.