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"

On 11/3/22 14:49, Bryn Llewellyn wrote:

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

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

That is not true:

aklaver(at)arkansas:~$ whoami
aklaver

aklaver(at)arkansas:~$ sudo vi /etc/postgresql/14/main/pg_hba.conf
[sudo] password for aklaver:

which opens pg_hba.conf for editing.

>
> 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:

You want to use local peer with OS user postgres, that is not a
requirement. You could set up Postgres to log in the db user postgres by
any of the other auth means and do the below without sudo(ing) to OS
user postgres.

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

It isn't you where using pg_ctl and in the Debian/Ubuntu packaging the
better option for that is pg_ctlcluster. I generally use the systemd
scripts to start/stop Postgres instances, though when I do pg_lsclusters
I tend to fall into using pg_ctlcluster as the cluster info is right there.

>
> 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?

Yes and since that is basically coloring outside the lines, then that
leads to the below blowing up.

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

Per the saying, "In a ham and eggs breakfast the chicken is involved but
the pig is committed", right now you are involved in the Debian/Ubuntu
process not committed. Until you commit you will not get the results you
want.

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

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com