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/2/22 15:31, Bryn Llewellyn wrote:
>> david(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com> EARLIER
>> wrote:
>>

> A strange mutual misunderstanding has arisen here. I suppose that it
> must be my fault. I have no interest whatsoever in "going down to first
> principles". And I most certainly never said that I want to "have the
> o/s admin and postgres bootstrap user be anything but postgres". On the
> contrary: I want just that. Saying this more abstractly, I want to
> install PG (admittedly the old version 11) in a freshly created Ubuntu
> 20.04 LTS VM. And I want to follow the reigning notions of proper
> practice. As far as possible, I'd like to find that I simply get such an
> outcome without explicit intervention—or at least by accepting all the
> defaults.
>

> I did exactly that. And I selected "Linux" and under that "Ubuntu".
> Notice that I did NOT select "Debian", though it was on offer, because
> that's not what I have. If Ubuntu and Debian were effectively the same,
> then there wouldn't be two distinct choices. My choice took me here:

They use the same basic packaging and wrapper mechanism. They are
separated out because, very simple explanation, Ubuntu is downstream of
Debian and the version releases are not the same.

> In summary, then, I followed a link from the PG doc to a site whose URL
> makes it sound "official", selected my environment, and followed some
> simple steps as specified. That doesn't sound like "opinionated" to me.
> Nor does it sound like stubbornly insisting on doing things my own way.

It is official as these are the community released packages.

The opinionated reference was to what the Debian/Ubuntu packaging does,
not you.

>
> Adrian gave me this link:
>
> https://ubuntu.com/server/docs/databases-postgresql
> <https://ubuntu.com/server/docs/databases-postgresql>
>
> Of course I'd read that right at the outset. The subtext is loud and
> clear. You need to do some things as the "postgres" user and some things
> as "root". That's why I enabled "sudo" for "postgres" (just as the code
> examples on that page imply).

You don't need to do that. Just use sudo as what ever user you log in
as. For example:

aklaver(at)arkansas:~$ sudo pg_ctlcluster stop 14/main
[sudo] password for aklaver:

Again very simple explanation, the OS postgres user is just created to
run the server. It does not even have a home directory.

>
> However, I have no a priori requirement to use the root-needing
> "systemctl" to stop and start my cluster. But I do want to be able to
> use "initdb" so that I can simply trash an extant cluster and start
> again from a well-defined, pristine state. (I want to do this to be
> completely sure that my own scripts make no assumptions about
> pre-existing objects.) This script works perfectly well:
>
> *sudo systemctl stop postgresql
>
> rm -Rf /var/lib/postgresql/11/main
>
> initdb \
>   -U postgres --encoding UTF8 --locale=C --lc-collate=C
> --lc-ctype=en_US.UTF-8 \
>   -A md5 --pwfile=/etc/ybmt-code/misc/.superuser-passwd-for-initdb \
>   -D /var/lib/postgresql/11/main
>
> sudo systemctl start postgresql
>
> psql -f <my_script.sql>
> *
> But it does require that my "postgres" O/S user is enabled for "sudo" —
> which you all insist is a terrible crime, even on my personal laptop
> that nobody else can access. Moreover, I'm using "initdb". This is
> apparently verboten (it isn't exposed via a link on "/usr/bin")—in the
> env produced by the installation procedure that the PG doc lead me to.
> The same is true for "pg_ctl" and "postgres". A bit of Googling took me
> here:

Again, when in Rome:

sudo pg_createcluster 15 test
Creating new PostgreSQL cluster 15/test ...
/usr/lib/postgresql/15/bin/initdb -D /var/lib/postgresql/15/test
--auth-local peer --auth-host scram-sha-256 --no-instructions
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.UTF-8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/15/test ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... America/Los_Angeles
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok
Ver Cluster Port Status Owner Data directory Log file
15 test 5433 down postgres /var/lib/postgresql/15/test
/var/log/postgresql/postgresql-15-test.log

man pg_createcluster

for more options.

> So, apparently, Debian and Ubuntu are the same after all, even though
> they have separate install pages from www.postgresql.org/download/linux
> <http://www.postgresql.org/download/linux>.

The same as Debian and Ubuntu are.

>
> Anyway, the examples show "sudo pg_ctlcluster ..." and that defeats the
> aim of doing everything as the "postgres" user.

But as my example above shows you will be, in that the cluster will be
owned by postgres.

>
> This, I believe, is why Adrian wrote this:
>
> «
> If you are going to use the Debian/Ubuntu packaging then you will need
> to follow its "rules".

Which you have not been doing.

> But it seems that I'm now in a regime where critical PG utilities don't
> work like the PG doc says, and where what you need, "pg_ctlcluster",
> isn't even mentioned in the PG doc. (I do see that it's present in my
> env and is properly wired up from "/usr/bin". (Actually, it's right
> there on that directory as an executable file.)

Because they are package specific programs. You will find similar
differences in the RH family packaging.

>
> How can it be that the PG doc itself leads you by the hand to a regime
> where you need to use undocumented features?

Because the docs are for the unpackaged version of Postgres, namely what
you would get if you installed from source.

>
> And, b.t.w., the debian.org site that Adrian linked me to is NOT
> ordinary user-facing doc. Moreover, "pg_ctlcluster --help" fails with
> "Unknown option: help". That's a terrible start. Of course I did "man
> pg_ctlcluster". This doesn't give a single example of using the command.
> It says that it "essentially wraps the pg_ctl(1) command". But what does
> "essentially" mean—is that some flavor of "partially"? And it doesn't
> hint at why wrapping is essential and why the bare command cannot be used.
>
> Where is the ordinary, prose, account of what this is all about—and why
> the ordinary documented PG executables cannot be used as intended. It's
> the same story for "pg_createcluster" as a wrapper for "initdb".

They can be used as intended, though you may end up with Postgres
clusters outside the place where the packaging expects them.

>
> Yet, somehow, "systemctl start postgresql" happily manages to find my
> customized config files in the location where I did the customization.
> It's hard to imagine a more confusing design. What thinking underlies it?
>

Don't customize, use the provided tools. The idea behind all this is to
have the provided tools allow you to run multiple clusters of the same
version or different versions of Postgres concurrently and have the
tools take care of setting up the cluster data_dir and config_dir and
assign ports.

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