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"

> adrian(dot)klaver(at)aklaver(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com wrote:
>>
>> 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).
>
> You don't need to do that. Just use sudo as what ever user you log in as. For example:
>
> aklaver(at)arkansas:~$ sudo pg_ctlcluster stop 14/main
> [sudo] password for aklaver:
>
> Again very simple explanation, the OS postgres user is just created to run the server. It does not even have a home directory.

I should make it clear that my VM has four "ordinary" users. Two are present on first use when VM creation finishes: the system administrator (called "parallels") and, of course, "root". The "parallels" user is enabled for "sudo". Installing PG brings "postgres". (I created mine before hand, and it was unperturbed by the PG installation. This is my private laptop. And it suits me to give it a home directory and to customize its ".bashrc". But that's so that I can look around with the minimum of fuss.) Finally, there's my "clstr_mgr" O/S user that acts as the authorization vehicle for my "clstr$mgr" cluster-role. Each of "postgres" and "clstr_mgr" is in its own singleton group—and in no other groups. And neither is set up for "sudo".)

The doc explains how to edit (at least) these: the "config_file", the "hba_file", and the "ident_file". You edit them to achieve certain supported outcomes. I mentioned that, during my "for real" installation (from a re-established freshly created Ubuntu VM) I noted how the config files were set up:

-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

So only "postgres" can edit the files that must be so edited.

Apparently, an unwritten rule says that one must never end up so that "whoami" shows "postgres". I see that I can, then, always do, for example, this (from "parallels"):

sudo -u postgres vi pg_hba.conf

And, given that one major theme in our recent mutual, extended, exchanges is that I want to use "local", "peer" authentication for the cluster-role "postgres" via the O/S user with the same name, I see that I can always run all the SQL scripts that I want, using this authentication, like this:

sudo -u postgres psql -f my_script.sql

With this in mind, I re-wrote my "clean start" script thus:

#!/bin/bash

# do this as ANY user that can do "sudo" (and this includes "root" itself)

sudo pg_ctlcluster stop 11/main
sudo rm -Rf /var/lib/postgresql/11/main

sudo -u postgres initdb \
-U postgres --encoding UTF8 --locale=C --lc-collate=C --lc-ctype=en_US.UTF-8 \
-A md5 --pwfile=my_password_file \
-D /var/lib/postgresql/11/main

sudo pg_ctlcluster start 11/main

sudo -u postgres psql -f my_script.sql

It certainly works. And when it's finished, this:

sudo -u postgres psql -c " select name, setting from pg_settings where category = 'File Locations'; "

shows that my files are where they ought to be. Then, further tests show that "local", "peer" authorization works as intended for my "clstr$mgr" role and that I can connect from client machines. So all is good.

Why, though, is "pg_ctlcluster stop/start... " preferred over "systemctl stop/start postgresql?". Both are quick enough. And I can't see any difference in effect. Moreover, the latter is what https://ubuntu.com/server/docs/databases-postgresql <https://ubuntu.com/server/docs/databases-postgresql> recommends.

Notice that I'm still using the off-limits "initdb" here. (I wired it up with a link on "/usr/bin".) Is there any reason to change that and to try to work out how to use what David (in an email soon after Adrian's) suggested?

sudo pg_dropcluster --stop 11 main
sudo pg_createcluster 11 main
sudo pg_ctlcluster start 11/main

I tried it. But it "did not work". I don't have the energy to describe the errors that were reported and the nasty outcomes that I got when I tried to use the new cluster. There's no useful doc for that approach and I've already established that Internet search gets me nowhere. So I'm inclined not to use it.

Rather, I want, now, simply to declare victory with the script that I showed and return to ordinary productive work.