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"

> david(dot)g(dot)johnston(at)gmail(dot)com EARLIER wrote:
>
> The postgres o/s user should be able to login using peer. It is a one-way idea though. Wanting to login using peer says nothing about whether the user getting that capability should be allowed to mess with the running server in the operating system.
>
> As for the rest, all I see is that you are using an opinionated package manager to install software whose opinions you don't agree with. Maybe there is some buggy behavior with respect to shared o/s db administration among users in a common group... you haven't demonstrated that one way or the other here. I think it is pointless to have the o/s admin and postgres bootstrap user be anything but postgres and this whole thing is counter-productive. But if you are going down to first principles maybe you should install from source and build your own "package" from that.

> david(dot)g(dot)johnston(at)gmail(dot)com LATER wrote:
>
> I think the intent of the design is for the custom Debian wrapper scripts to be able to read the configuration files for the named version "11" and configuration "main" to find out where certain things like the socket file are being written to. The argument being the configuration files don't actually contain secret data so reading shouldn't be an issue and can be useful. Obviously the same does not apply to data files. On that basis it would indeed make more sense to grant read to "all" rather than try and add users to "postgres" to make the reading of the configuration files work.
>
> Also, per the initdb documentation:
>
> For security reasons the new cluster created by <command>initdb</command>
> will only be accessible by the cluster user by default. The
> <option>--allow-group-access</option> option allows any user in the same
> group as the cluster owner to read files in the cluster. This is useful
> for performing backups as a non-privileged user.

A strange mutual misunderstanding has arisen here. I suppose that it must be my fault. I have no interest whatsoever in "going down to first principles". And I most certainly never said that I want to "have the o/s admin and postgres bootstrap user be anything but postgres". On the contrary: I want just that. Saying this more abstractly, I want to install PG (admittedly the old version 11) in a freshly created Ubuntu 20.04 LTS VM. And I want to follow the reigning notions of proper practice. As far as possible, I'd like to find that I simply get such an outcome without explicit intervention—or at least by accepting all the defaults.

Searching the actual PG doc took me here:

Chapter 17. Installation from Source Code
https://www.postgresql.org/docs/15/installation.html

That's the last thing I want to do. So then I read this:

Chapter 16. Installation from Binaries
https://www.postgresql.org/docs/15/install-binaries.html

(It was ranked lower by the doc's native search.) It says nothing of substance. But it does say this:

«
visit the download section on the PostgreSQL website at
https://www.postgresql.org/download/ <https://www.postgresql.org/download/>
and follow the instructions for the specific platform.
»

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. My choice took me here:

Linux downloads (Ubuntu)
https://www.postgresql.org/download/linux/ubuntu/

It mentions that my (22.04, LTS) is supported. Then I did these simple steps:

sudo -s
apt install postgresql-common
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

apt update
apt upgrade
apt install postgresql-11

I did have to look around a bit to find that recipe. But it completed quickly, without error, and without prompting me to make a single choice.

Now that I know what I do (and I confess that I did not know enough at the start) I could complete the whole thing in less than 30 minutes. (The time would be more or less according to what notes I decided to take along the way and what copy-and-paste-ready config file snippets and the like I had to hand.) I'm including, in this timing, the necessary post install steps to allow connections from other machines and to enable "local", "peer" authorization for my "superuser's assistant" that I implement with the cluster-role that I name "clstr$mgr".

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 discovered this time around that the config files "arrive" like this:

-rw-r--r-- 1 postgres postgres 143 Nov 1 15:48 /etc/postgresql/11/main/pg_ctl.conf
-rw-r----- 1 postgres postgres 4686 Nov 1 15:48 /etc/postgresql/11/main/pg_hba.conf
-rw-r----- 1 postgres postgres 1636 Nov 1 15:48 /etc/postgresql/11/main/pg_ident.conf
-rw-r--r-- 1 postgres postgres 24321 Nov 1 15:48 /etc/postgresql/11/main/postgresql.conf
-rw-r--r-- 1 postgres postgres 317 Nov 1 15:48 /etc/postgresql/11/main/start.conf

If only somebody had said "This is what you should see. Do you?" then it would have been clear immediately that I'd made a slip-up. Never mind. I see now that it's the readability by "all" of "postgresql.conf" that's critical here. I had to edit this file to allow sessions to connect from other machines. So I must've removed readability by "all" (manually, and stupidly) when I did that. Obviously, the « membership in the "postgres" O/S group » rabbit hole goes away now.

Having said this, I still can’t see why enabling an O/S user to read (but not write) some config files that are not readable by “all” would be called "allowing them to mess with the server". Are ANY files with owner/group "postgres/postgres" (or its equivalent in a less standard installation) writeable by "group".

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. Nor does it sound like stubbornly insisting on doing things my own way.

Adrian gave me this link:

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).

However, I have no a priori requirement to use the root-needing "systemctl" to stop and start my cluster. But I do want to be able to use "initdb" so that I can simply trash an extant cluster and start again from a well-defined, pristine state. (I want to do this to be completely sure that my own scripts make no assumptions about pre-existing objects.) This script works perfectly well:

sudo systemctl stop postgresql

rm -Rf /var/lib/postgresql/11/main

initdb \
-U postgres --encoding UTF8 --locale=C --lc-collate=C --lc-ctype=en_US.UTF-8 \
-A md5 --pwfile=/etc/ybmt-code/misc/.superuser-passwd-for-initdb \
-D /var/lib/postgresql/11/main

sudo systemctl start postgresql

psql -f <my_script.sql>

But it does require that my "postgres" O/S user is enabled for "sudo" — which you all insist is a terrible crime, even on my personal laptop that nobody else can access. Moreover, I'm using "initdb". This is apparently verboten (it isn't exposed via a link on "/usr/bin")—in the env produced by the installation procedure that the PG doc lead me to. The same is true for "pg_ctl" and "postgres". A bit of Googling took me here:

https://askubuntu.com/questions/385416/pg-ctl-command-not-found-what-package-has-this-command <https://askubuntu.com/questions/385416/pg-ctl-command-not-found-what-package-has-this-command>

And in particular, to this (from Peter Eisentraut—well-known on this list):

«
You shouldn't run pg_ctl directly under Ubuntu/Debian. Use pg_ctlcluster instead, which is installed by postgresql-common. See its man page for documentation.
»

So, apparently, Debian and Ubuntu are the same after all, even though they have separate install pages from www.postgresql.org/download/linux <http://www.postgresql.org/download/linux>.

Anyway, the examples show "sudo pg_ctlcluster ..." and that defeats the aim of doing everything as the "postgres" user.

This, I believe, is why Adrian wrote this:

«
If you are going to use the Debian/Ubuntu packaging then you will need to follow its "rules".

See here:

https://wiki.debian.org/PostgreSql
»

I hope that it's clear, now, that I want nothing more than to install PG Version 11 on the current LTE version of a very popular Linux flavor. without starting from source code, and that I followed links from the PG doc to get where I got.

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?

And, b.t.w., the debian.org site that Adrian linked me to is NOT ordinary user-facing doc. Moreover, "pg_ctlcluster --help" fails with "Unknown option: help". That's a terrible start. Of course I did "man pg_ctlcluster". This doesn't give a single example of using the command. It says that it "essentially wraps the pg_ctl(1) command". But what does "essentially" mean—is that some flavor of "partially"? And it doesn't hint at why wrapping is essential and why the bare command cannot be used.

Where is the ordinary, prose, account of what this is all about—and why the ordinary documented PG executables cannot be used as intended. It's the same story for "pg_createcluster" as a wrapper for "initdb".

Internet search doesn't help. I did find this:

https://fatdragon.me/blog/2016/05/managing-postgresql-process-ubuntu-service-pgctl-and-pgctlcluster

But it sounds like a random blogger who, to boot, thinks that the whole business is silly.

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. But this query:

select name, setting
from pg_settings
where category = 'File Locations';

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. (And they remained there, of course, being silently ignored.) This regime is crazy, because I need to clear out the data directory before creating a new cluster—and so my customization is inevitably ignored—even if I copied my customizations there before starting. I even tried intervening with single-user mode when "initdb" finished to set the "config_file" parameter by hand. It seemed to work. But then "pg_ctl start ..." promptly countermanded my intention.

Yet, somehow, "systemctl start postgresql" happily manages to find my customized config files in the location where I did the customization. It's hard to imagine a more confusing design. What thinking underlies it?