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(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)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(dot)klaver(at)aklaver(dot)com