Search Postgresql Archives

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 Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 11/2/22 15:31, Bryn Llewellyn wrote:
david.g.johnston@xxxxxxxxx <mailto:david.g.johnston@xxxxxxxxx> 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@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.klaver@xxxxxxxxxxx






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux