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

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@arkansas:~$ whoami
aklaver


aklaver@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.klaver@aklaver.com