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/1/22 13:20, Bryn Llewellyn wrote:
>> david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.com> wrote:
>>

> 

> <aside>
> I know that I've been told off for allowing "sudo" for "postgres". I'm 
> only experimenting on my laptop. But I want to be able to stop the 
> server, delete the datafiles, create a new cluster, and then start that 
> using a single script. I can't use "pg_ctl stop/start" because it 
> expects to find its config files on the data directory. (That's a 
> different story. And I'm not ready to ask about that yet.) So I use 
> "sudo systemctl stop/start postgresql" because this method looks in the 
> right place for the config files.

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

https://ubuntu.com/server/docs/databases-postgresql

Also do:

man pg_wrapper

Your best bet is to systemctl to start stop Postgres, but if you want to 
use pg_ctl then you need to use the wrapped version. As example:

pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
14  main    5432 online postgres /var/lib/postgresql/14/main 
/var/log/postgresql/postgresql-14-main.log
15  main    5434 down   postgres /var/lib/postgresql/15/main 
/var/log/postgresql/postgresql-15-main.log

sudo pg_ctlcluster stop 14/main

pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
14  main    5432 down   postgres /var/lib/postgresql/14/main 
/var/log/postgresql/postgresql-14-main.log
15  main    5434 down   postgres /var/lib/postgresql/15/main 
/var/log/postgresql/postgresql-15-main.log

sudo pg_ctlcluster start 14/main

  pg_lsclusters
Ver Cluster Port Status Owner    Data directory              Log file
14  main    5432 online postgres /var/lib/postgresql/14/main 
/var/log/postgresql/postgresql-14-main.log
15  main    5434 down   postgres /var/lib/postgresql/15/main 
/var/log/postgresql/postgresql-15-main.log


There are more pg_*cluster* wrapped commands:

pg_archivecleanup  pg_buildext        pg_createcluster   pg_dump 
    pg_lsclusters      pg_recvlogical     pg_restorecluster 
pg_virtualenv
pg_backupcluster   pg_config          pg_ctlcluster      pg_dumpall 
    pg_receivewal      pg_renamecluster   pg_updatedicts
pg_basebackup      pg_conftool        pg_dropcluster     pg_isready 
    pg_receivexlog     pg_restore         pg_upgradecluster


> </aside>
> 
> Here's what my "pg_hba.conf" has:
> 
> *  local   all   bob   peer*
> 
> My experiment shows that my attempt to authorize  as "bob" using 
> "local", "peer" authorization fails when every config file is readable 
> only by "owner" and "group". And it shows that a sufficient fix is to 
> make just "postgresql.conf" readable by "all" (as I believe is the 
> intention). Of course, an alternative fix (in the sesne that it would 
> work) would be to have "postgresql.conf" not readable by all but to put 
> "bob" in the "postgres" group.
> 
> All this leads to an obvious question:
> 
> *«*
> *Given that all of the config files have been made readable by "group" 
> (in contrast to the regime for the data files), what is the intention of 
> this design? In other words, when is it proper to put an O/S user in the 
> "postgres" group? After all, if the answer is "never" than no privileges 
> on "postgres/postgres" files would ever have been granted to "group".*
> *»*
> 
> 
> 
> 
> 
> 
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com