From: Adrian Klaver <adrian.klaver@aklaver.com>
To: Bryn Llewellyn <bryn@yugabyte.com>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-general list <pgsql-general@lists.postgresql.org>
Cc: Peter J. Holzer <hjp-pgsql@hjp.at>
Cc: laurenz.albe@cybertec.at, Karsten Hilbert <Karsten.Hilbert@gmx.net>
Subject: 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"
Date: Tue, 1 Nov 2022 17:40:36 -0700
Message-ID: <8a3deda3-73ba-25bf-527d-dba758156e5f@aklaver.com> (raw)
In-Reply-To: <4B667995-647F-48BB-94CC-22ADE19F0BFB@yugabyte.com>
References: <5EDD4790-7982-4610-8371-9E7F9C7F960B@yugabyte.com>
<CAKFQuwYYhpzYX5=xfa3NtTdoLNinSZ460hCaJx+gE7esGmKTWw@mail.gmail.com>
<4B667995-647F-48BB-94CC-22ADE19F0BFB@yugabyte.com>
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/PostgreSqlhttps://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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: adrian.klaver@aklaver.com, bryn@yugabyte.com, david.g.johnston@gmail.com, hjp-pgsql@hjp.at, Karsten.Hilbert@gmx.net
Subject: 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"
In-Reply-To: <8a3deda3-73ba-25bf-527d-dba758156e5f@aklaver.com>
References: <5EDD4790-7982-4610-8371-9E7F9C7F960B@yugabyte.com> <CAKFQuwYYhpzYX5=xfa3NtTdoLNinSZ460hCaJx+gE7esGmKTWw@mail.gmail.com> <4B667995-647F-48BB-94CC-22ADE19F0BFB@yugabyte.com> <8a3deda3-73ba-25bf-527d-dba758156e5f@aklaver.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox