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" - Mailing list pgsql-general
| From | David G. Johnston |
|---|---|
| Subject | 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" |
| Date | |
| Msg-id | CAKFQuwb8J4dJ2NR3y21_W0wiqdAXUo3uZVGg=mnNZJZUR09T-A@mail.gmail.com Whole thread Raw |
| In response to | 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" (Bryn Llewellyn <bryn@yugabyte.com>) |
| Responses |
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"
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" |
| List | pgsql-general |
Some repetition of what Adrian just posted ahead...
On Wed, Nov 2, 2022 at 3:31 PM Bryn Llewellyn <bryn@yugabyte.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.
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...
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.
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.
pgsql-general by date:
Previous
From: Adrian KlaverDate:
Subject: 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"
Next
From: "David G. Johnston"Date:
Subject: 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"