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"
I followed Peter's recommendation NOT to put my "clstr_mgr" O/S user in the "postgres" group—having earlier had it there. But doing so brought this content-free error message on an attempt to authorize using the intended method:
Error: Invalid data directory for cluster 11 main
A bit of Googling got me to this on the pgsql-general list (from the Peter, in fact):
/messages/by-id/20190909171519.GA7858@hjp.at </messages/by-id/20190909171519.GA7858@hjp.at>
on that very topic.
It seems that the error message is simply misleading and that it should read "Cannot read the config_file, hba_file, or ident_file" — as they are named in this query's output":
select name, setting
from pg_settings
where category = 'File Locations';
Sure enough, neither my hba_file nor my ident_file were readable by "all" (but they were readable by "group"). However, the config_file was readable by "all". I've no idea what the history of those permissions is. Maybe I changed something along the way. I s'pose that I'd better regard my present installation as a dress rehearsal and simply redo it starting by restoring my "bare" Linux VM from file backup.
Anyway, just to prove the point, I chmod'd my hba_file and my ident_file to make them readable by all. And the silly error message went away.
However, that feels wrong to me. It would seem proper to put any user who you want to set up for "local", "peer" authentication into the "postgres" group.
What do you (all) think?
On Mon, Oct 31, 2022 at 10:03 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
It would seem proper to put any user who you want to set up for "local",
"peer" authentication into the "postgres" group
Did you really mean to write that?
The postgres o/s user should be able to login using peer. It is a one-way
idea though. Wanting to login using peer says nothing about whether the
user getting that capability should be allowed to mess with the running
server in the operating system.
As for the rest, all I see is that you are using an opinionated package
manager to install software whose opinions you don't agree with. Maybe
there is some buggy behavior with respect to shared o/s db
administration among users in a common group...you haven't demonstrated
that one way or the other here. I think it is pointless to have the o/s
admin and postgres bootstrap user be anything but postgres and this whole
thing is counter-productive. But if you are going down to first
principles maybe you should install from source and build your own
"package" from that.
David J.
On Mon, 2022-10-31 at 22:03 -0700, Bryn Llewellyn wrote:
I followed Peter's recommendation NOT to put my "clstr_mgr" O/S user in the "postgres"
group—having earlier had it there. But doing so brought this content-free error message
on an attempt to authorize using the intended method:Error: Invalid data directory for cluster 11 main
That*s a message from the *server*, which always runs under the same OS user.
A bit of Googling got me to this on the pgsql-general list (from the Peter, in fact):
/messages/by-id/20190909171519.GA7858@hjp.at
on that very topic.
It seems that the error message is simply misleading and that it should read "Cannot read
the config_file, hba_file, or ident_file" — as they are named in this query's output":select name, setting
from pg_settings
where category = 'File Locations';Sure enough, neither my hba_file nor my ident_file were readable by "all" (but they were
readable by "group"). However, the config_file was readable by "all". I've no idea what
the history of those permissions is. Maybe I changed something along the way. I s'pose
that I'd better regard my present installation as a dress rehearsal and simply redo it
starting by restoring my "bare" Linux VM from file backup.Anyway, just to prove the point, I chmod'd my hba_file and my ident_file to make them
readable by all. And the silly error message went away.However, that feels wrong to me. It would seem proper to put any user who you want to
set up for "local", "peer" authentication into the "postgres" group.What do you (all) think?
I think that you are doing something very weird, but I have no idea what it is.
Please tell us the exact commands you ran.
The client user should *never* read the PostgreSQL configuration files, so if changing
the permissions (which you should *never* do) has an effect, you must be doing something
very strange, like trying to start the database server with the wrong user.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
The client user should *never* read the PostgreSQL configuration files, so if changing
the permissions (which you should *never* do) has an effect, you must be doing something
very strange, like trying to start the database server with the wrong user.
It smells of trying to *embed* PostgreSQL ?
But that would not go with the account of multi-tenancy that's been presented.
Karsten
david.g.johnston@gmail.com wrote:
bryn@yugabyte.com wrote:
It would seem proper to put any user who you want to set up for "local", "peer" authentication into the "postgres" group
Did you really mean to write that?
The postgres o/s user should be able to login using peer. It is a one-way idea though. Wanting to login using peer says nothing about whether the user getting that capability should be allowed to mess with the running server in the operating system.
As for the rest, all I see is that you are using an opinionated package manager to install software whose opinions you don't agree with. Maybe there is some buggy behavior with respect to shared o/s db administration among users in a common group...you haven't demonstrated that one way or the other here. I think it is pointless to have the o/s admin and postgres bootstrap user be anything but postgres and this whole thing is counter-productive. But if you are going down to first principles maybe you should install from source and build your own "package" from that.
I meant only to ask a question—and not to pre-judge anything. I should have thought more carefully about its wording. (I'm thinking specifically of a PG installation on Linux—and only that.)
About "opinionated package manager", I created my installation by following the steps described here:
https://wiki.postgresql.org/wiki/Apt
My aim is simply to conform to recommended practice. When I've said (like I believe that I have) that I didn't get a chance, during the flow, to specify <this or that>, my intention was only to note this fact and to imply that what I got without any intervention coincided with the recommended practice. This suits me.
I'm going to need to use a term to denote the O/S user that very often ends up with the name "postgres". I don't know what the official term is. I'll simply say "postgres" here. I haven't yet found an overview of the set of files that jointly implement a freshly installed PG system. But, of course, I've noticed where critical directories are. For example:
/usr/lib/postgresql/11/bin
Lots of familiar names, like "initdb" are found there. They all have owner/group "root". And they're all executable by "all". The point has been made that its not who owns them that matters but, rather, what the effective user ID is at execution time. I can therefore ignore these for the purpose of my present question.
In contrast, on (my) "data_directory" (I have just one) here:
/var/lib/postgresql/11/main
I see that every file has owner/group "postgres/postgres". The "owner" has all privileges. And each of "group" and "all" have no privileges. This doesn't seem at all surprising. But it does indicate that whatever privileges some critical file has (anywhere), the set has been determined by design.
There are several "*.conf" files on the same directory as the "config_file ". For me, it's this:
/etc/postgresql/11/main
These, too, all have owner/group "postgres/postgres" — and "postgres" has all meaningful privileges (presumably "execute" means nothing for a text config file). But "group” has only "read" on them all and "all" has no privileges except (I believe) for “postgresql.conf"—which is readable by all.
I mentioned that I'll presently re-do my PG installation from scratch. Then I'll record the permissions that all the config files are set up with and know for sure. I looked in this section:
20.2. File Locations
https://www.postgresql.org/docs/current/runtime-config-file-locations.html <https://www.postgresql.org/docs/current/runtime-config-file-locations.html>
But "permission" isn't found on the page.
Anyway, it's only natural to assume that privileges on all of the "postgres/postgres" files have been determined in accordance with a deliberate design. The docs tell me to edit certain of these files to achieve certain intended effects. I've done this—by authorizing as the "postgres" O/S user. So there's an inevitable risk (at least for me) that I might have changed some permissions accidentally.
I've observed that, for my present use case (enable "local", "peer" authentication for an O/S user other than "postgres), the authorization attempt fails unless that user can read one critical config file. I just did a careful test with the brand-new O/S user "bob". Here's what "id" shows:
id=1003(bob) gid=1003(bob) groups=1003(bob)
uid=1001(postgres) gid=1001(postgres) groups=1001(postgres),27(sudo),114(ssl-cert)
<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.
</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".
»
On Tue, Nov 1, 2022 at 1:20 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
About "opinionated package manager", I created my installation by
following the steps described here:
Yes, apt is opinionated. And of important note here - its opinions, and
the supplemental wrapper scripts it implements to make those opinions
works, are outside the scope of what the official PostgreSQL documentation
is going to cover. (This is what the email you linked to is calling
pg_wrapper)
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".*
*»*
I think the intent of the design is for the custom Debian wrapper scripts
to be able to read the configuration files for the named version "11" and
configuration "main" to find out where certain things like the socket file
are being written to. The argument being the configuration files don't
actually contain secret data so reading shouldn't be an issue and can be
useful. Obviously the same does not apply to data files. On that basis it
would indeed make more sense to grant read to "all" rather than try and add
users to "postgres" to make the reading of the configuration files work.
David J.
On Tue, Nov 1, 2022 at 1:39 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
On Tue, Nov 1, 2022 at 1:20 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
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".*
*»*I think the intent of the design is for the custom Debian wrapper scripts
to be able to read the configuration files for the named version "11" and
configuration "main" to find out where certain things like the socket file
are being written to. The argument being the configuration files don't
actually contain secret data so reading shouldn't be an issue and can be
useful. Obviously the same does not apply to data files. On that basis it
would indeed make more sense to grant read to "all" rather than try and add
users to "postgres" to make the reading of the configuration files work.
Also, per the initdb documentation:
For security reasons the new cluster created by <command>initdb</command>
will only be accessible by the cluster user by default. The
<option>--allow-group-access</option> option allows any user in the same
group as the cluster owner to read files in the cluster. This is useful
for performing backups as a non-privileged user.
David J.
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
david.g.johnston@gmail.com EARLIER wrote:
The postgres o/s user should be able to login using peer. It is a one-way idea though. Wanting to login using peer says nothing about whether the user getting that capability should be allowed to mess with the running server in the operating system.
As for the rest, all I see is that you are using an opinionated package manager to install software whose opinions you don't agree with. Maybe there is some buggy behavior with respect to shared o/s db administration among users in a common group... you haven't demonstrated that one way or the other here. I think it is pointless to have the o/s admin and postgres bootstrap user be anything but postgres and this whole thing is counter-productive. But if you are going down to first principles maybe you should install from source and build your own "package" from that.
david.g.johnston@gmail.com LATER wrote:
I think the intent of the design is for the custom Debian wrapper scripts to be able to read the configuration files for the named version "11" and configuration "main" to find out where certain things like the socket file are being written to. The argument being the configuration files don't actually contain secret data so reading shouldn't be an issue and can be useful. Obviously the same does not apply to data files. On that basis it would indeed make more sense to grant read to "all" rather than try and add users to "postgres" to make the reading of the configuration files work.
Also, per the initdb documentation:
For security reasons the new cluster created by <command>initdb</command>
will only be accessible by the cluster user by default. The
<option>--allow-group-access</option> option allows any user in the same
group as the cluster owner to read files in the cluster. This is useful
for performing backups as a non-privileged user.
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.
Searching the actual PG doc took me here:
Chapter 17. Installation from Source Code
https://www.postgresql.org/docs/15/installation.html
That's the last thing I want to do. So then I read this:
Chapter 16. Installation from Binaries
https://www.postgresql.org/docs/15/install-binaries.html
(It was ranked lower by the doc's native search.) It says nothing of substance. But it does say this:
«
visit the download section on the PostgreSQL website at
https://www.postgresql.org/download/ <https://www.postgresql.org/download/>
and follow the instructions for the specific platform.
»
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:
Linux downloads (Ubuntu)
https://www.postgresql.org/download/linux/ubuntu/
It mentions that my (22.04, LTS) is supported. Then I did these simple steps:
sudo -s
apt install postgresql-common
/usr/share/postgresql-common/pgdg/apt.postgresql.org.sh
apt update
apt upgrade
apt install postgresql-11
I did have to look around a bit to find that recipe. But it completed quickly, without error, and without prompting me to make a single choice.
Now that I know what I do (and I confess that I did not know enough at the start) I could complete the whole thing in less than 30 minutes. (The time would be more or less according to what notes I decided to take along the way and what copy-and-paste-ready config file snippets and the like I had to hand.) I'm including, in this timing, the necessary post install steps to allow connections from other machines and to enable "local", "peer" authorization for my "superuser's assistant" that I implement with the cluster-role that I name "clstr$mgr".
I did the whole thing from scratch after trashing my provisional attempt. (This is easily afforded when you use a VM. Am I the only person who does this: practice, make mistakes, learn, trash, and then do it for real?)
I discovered this time around that the config files "arrive" like this:
-rw-r--r-- 1 postgres postgres 143 Nov 1 15:48 /etc/postgresql/11/main/pg_ctl.conf
-rw-r----- 1 postgres postgres 4686 Nov 1 15:48 /etc/postgresql/11/main/pg_hba.conf
-rw-r----- 1 postgres postgres 1636 Nov 1 15:48 /etc/postgresql/11/main/pg_ident.conf
-rw-r--r-- 1 postgres postgres 24321 Nov 1 15:48 /etc/postgresql/11/main/postgresql.conf
-rw-r--r-- 1 postgres postgres 317 Nov 1 15:48 /etc/postgresql/11/main/start.conf
If only somebody had said "This is what you should see. Do you?" then it would have been clear immediately that I'd made a slip-up. Never mind. I see now that it's the readability by "all" of "postgresql.conf" that's critical here. I had to edit this file to allow sessions to connect from other machines. So I must've removed readability by "all" (manually, and stupidly) when I did that. Obviously, the « membership in the "postgres" O/S group » rabbit hole goes away now.
Having said this, I still can’t see why enabling an O/S user to read (but not write) some config files that are not readable by “all” would be called "allowing them to mess with the server". Are ANY files with owner/group "postgres/postgres" (or its equivalent in a less standard installation) writeable by "group".
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.
Adrian gave me this link:
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).
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:
https://askubuntu.com/questions/385416/pg-ctl-command-not-found-what-package-has-this-command <https://askubuntu.com/questions/385416/pg-ctl-command-not-found-what-package-has-this-command>
And in particular, to this (from Peter Eisentraut—well-known on this list):
«
You shouldn't run pg_ctl directly under Ubuntu/Debian. Use pg_ctlcluster instead, which is installed by postgresql-common. See its man page for documentation.
»
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>.
Anyway, the examples show "sudo pg_ctlcluster ..." and that defeats the aim of doing everything as the "postgres" user.
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".
See here:
https://wiki.debian.org/PostgreSql
»
I hope that it's clear, now, that I want nothing more than to install PG Version 11 on the current LTE version of a very popular Linux flavor. without starting from source code, and that I followed links from the PG doc to get where I got.
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.)
How can it be that the PG doc itself leads you by the hand to a regime where you need to use undocumented features?
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".
Internet search doesn't help. I did find this:
https://fatdragon.me/blog/2016/05/managing-postgresql-process-ubuntu-service-pgctl-and-pgctlcluster
But it sounds like a random blogger who, to boot, thinks that the whole business is silly.
Meanwhile, can I appeal to one of you simply to tell me, here, the magic spells that I must write so that I can remain as the "postgres" O/S user and achieve my "trash an extant cluster and start again from a well-defined, pristine state" goal?
p.s. I did attempt (in a VM that I then trashed) to use "pg_ctl" and "initdb". It all "worked" in that there were no errors and I got a new cluster. But this query:
select name, setting
from pg_settings
where category = 'File Locations';
showed me that the "config_file" location, and that of the "hba_file" and the "ident_file" were now on the data directory ("/var/lib/postgresql/11/main") and NOT in their original locations on "/etc/postgresql/11/main/" where I had customized them following the doc. (And they remained there, of course, being silently ignored.) This regime is crazy, because I need to clear out the data directory before creating a new cluster—and so my customization is inevitably ignored—even if I copied my customizations there before starting. I even tried intervening with single-user mode when "initdb" finished to set the "config_file" parameter by hand. It seemed to work. But then "pg_ctl start ..." promptly countermanded my intention.
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?
On 11/2/22 15:31, Bryn Llewellyn wrote:
david.g.johnston@gmail.com <mailto:david.g.johnston@gmail.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@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/mainsudo 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@aklaver.com
Some repetition of what Adrian just posted ahead...
On Wed, Nov 2, 2022 at 3:31 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
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.
Some of what I wrote assumed a familiarity with the Linux distribution
ecosystem...Debian originated the opinions about how to install PostgreSQL;
Ubuntu, by virtue of building upon that distribution, inherited those
opinions. In particular, they are considerably different than what
CentOS/Red Hat thinks.
I did the whole thing from scratch after trashing my provisional attempt.
(This is easily afforded when you use a VM. Am I the only person who does
this: practice, make mistakes, learn, trash, and then do it for real?)
I do this all of the time myself - leveraging Ansible for
infrastructure-as-code as well.
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.
Debian/Ubuntu are opinionated, and enforce those opinions via the Apt-based
packaging that their community creates from the PostgreSQL source code.
There are many such communities out there (BSD, Windows, Red Hat, etc...)
and basically no one within core is interested in worrying about how those
different operating systems work at the DBA level. A generally shared
adherence to POSIX and the facilities provided by the C language make that
practical.
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).
Honestly, a server running PostgreSQL should have, at minimum, three
relevant users. Root, Postgres, and the user the system admin logs in as.
This third user should sudo to install PostgreSQL, su to initially
configure the system using the cluster owner (see my -hackers email for the
documentation patches this has inspired), then sudo again to create any
additional local users you might want if you aren't going to use the
postgres user name the packaging gives you for everyday use. The elided
script below should be run as your system admin user, not root nor postgres
(or just run it as root and su for the initdb part).
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.)How can it be that the PG doc itself leads you by the hand to a regime
where you need to use undocumented features?
The documentation tries to make clear that if you use third-party packaging
to install PostgreSQL (which most people should) that the documentation for
the packaging should describe this layer where PostgreSQL and the operating
system intersect. You even quoted it: "follow the instructions for the
specific platform.", though reading that now I think something along the
lines of:
"Additionally, while reading the next chapter, Server Setup and Operation,
is recommended if you are using a binary package the setup and operational
environment it creates is likely to be somewhat different than what is
described in this documentation. Please read the documentation for the
packages you install to learn how it behaves and what additional
platform-specific features it provides."
I haven't publicly (at least not recently...) voiced an opinion on the
quality of the Apt documentation, nor have volunteered to work on it. But
regardless it is an entirely different department run by volunteers that
package up many different applications, not just PostgreSQL. That
decentralization and spreading out of responsibilities is simply how this
overall community is structured and your frustrations stem a great deal
from this particular seam.
Meanwhile, can I appeal to one of you simply to tell me, here, the magic
spells that I must write so that I can remain as the "postgres" O/S user
and achieve my "trash an extant cluster and start again from a
well-defined, pristine state" goal?p.s. I did attempt (in a VM that I then trashed) to use "pg_ctl" and
"initdb". It all "worked" in that there were no errors and I got a new
cluster.
I think...
https://manpages.ubuntu.com/manpages/trusty/man8/pg_dropcluster.8.html
pg_dropcluster --stop 11 main
pg_createcluster 11 main
Again, I don't presently have a desire to investigate the usability of the
Apt packaging's documentation and overall usability for someone coming to
it from the PostgreSQL website. I do suspect, from past experience and
your comments here, that such an effort would be beneficial, though I know
not how many users are running Debian/Ubuntu that would see such
improvements. As I noted, for most it is simply enough to install
PostgreSQL and get the single running cluster managed by pg_ctlcluster
manually and integrated with systemd. Resetting is done by dropping the
container/VM and starting anew - letting a provisioning script flesh out
the customizations.
showed me that the "config_file" location, and that of the "hba_file" and
the "ident_file" were now on the data directory
("/var/lib/postgresql/11/main") and NOT in their original locations on
"/etc/postgresql/11/main/" where I had customized them following the doc.
Yes, because initdb has completely different opinions about where those
files belong than the Debian/Ubuntu packaging. Specifically, PostgreSQL
documentation is written to expect every file initdb creates to go into the
data directory (usually plopped under /usr/local/pgsql) while Debian makes
use of both /var and /etc in proscribing where different kinds of files
belong, and then specifically for PostgreSQL makes dealing with
multi-version/multi-cluster setups easy building both the version and a
label into the cluster path.
It's hard to imagine a more confusing design. What thinking underlies it?
The teams responsible for packaging up source code and building
user-friendly installers for their operating systems are all different from
the core team that doesn't really have to worry about distribution. Aside
from the presence of, and commentary on, the relevant file configuration
variables.
https://www.postgresql.org/docs/current/runtime-config-file-locations.html
In short, it is actually a very good design given the constraint that the
software has to work on so many different operating systems for many years
of supported lifetime.
David J.
On Wed, Nov 2, 2022 at 6:22 PM David G. Johnston <david.g.johnston@gmail.com>
wrote:
Some repetition of what Adrian just posted ahead...
On Wed, Nov 2, 2022 at 3:31 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
How can it be that the PG doc itself leads you by the hand to a regime
where you need to use undocumented features?The documentation tries to make clear that if you use third-party
packaging to install PostgreSQL (which most people should) that the
documentation for the packaging should describe this layer where PostgreSQL
and the operating system intersect. You even quoted it: "follow the
instructions for the specific platform.", though reading that now I think
something along the lines of:"Additionally, while reading the next chapter, Server Setup and
Operation, is recommended if you are using a binary package the setup and
operational environment it creates is likely to be somewhat different than
what is described in this documentation. Please read the documentation for
the packages you install to learn how it behaves and what additional
platform-specific features it provides."
Actually, not sure on the best approach here, since the Server Setup
chapter already says:
https://www.postgresql.org/docs/current/runtime.html
"The directions in this chapter assume that you are working with plain
PostgreSQL without any additional infrastructure, for example a copy that
you built from source according to the directions in the preceding
chapters. If you are working with a pre-packaged or vendor-supplied version
of PostgreSQL, it is likely that the packager has made special provisions
for installing and starting the database server according to your system's
conventions. Consult the package-level documentation for details."
However, that appears below-the-fold after a decent sized table of contents.
Changing anything now feels like an over-reaction to a single incident, but
I sympathize with the general confusion all this causes, and the fact it is
only in the recent past that we've made this first attempt to rectify the
situation by adding these comments. A second-pass based upon this
encounter seems at least reasonable. Whether I or others end up deciding
it is worth proposing a patch remains to be seen.
David J.
"Additionally, while reading the next chapter, Server Setup and
Operation, is recommended if you are using a binary package the setup
and operational environment it creates is likely to be somewhat
different than what is described in this documentation. Please read
the documentation for the packages you install to learn how it behaves
and what additional platform-specific features it provides."
I wonder if "binary package" would confuse some apt/yum/etc users?
Maybe "package supplied from a distributor"?
adrian.klaver@aklaver.com wrote:
bryn@yugabyte.com wrote:
Adrian gave me this link:
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.
I should make it clear that my VM has four "ordinary" users. Two are present on first use when VM creation finishes: the system administrator (called "parallels") and, of course, "root". The "parallels" user is enabled for "sudo". Installing PG brings "postgres". (I created mine before hand, and it was unperturbed by the PG installation. This is my private laptop. And it suits me to give it a home directory and to customize its ".bashrc". But that's so that I can look around with the minimum of fuss.) Finally, there's my "clstr_mgr" O/S user that acts as the authorization vehicle for my "clstr$mgr" cluster-role. Each of "postgres" and "clstr_mgr" is in its own singleton group—and in no other groups. And neither is set up for "sudo".)
The doc explains how to edit (at least) these: the "config_file", the "hba_file", and the "ident_file". You edit them to achieve certain supported outcomes. I mentioned that, during my "for real" installation (from a re-established freshly created Ubuntu VM) I noted how the config files were set up:
-rw-r--r-- 1 postgres postgres 143 Nov 1 15:48 /etc/postgresql/11/main/pg_ctl.conf
-rw-r----- 1 postgres postgres 4686 Nov 1 15:48 /etc/postgresql/11/main/pg_hba.conf
-rw-r----- 1 postgres postgres 1636 Nov 1 15:48 /etc/postgresql/11/main/pg_ident.conf
-rw-r--r-- 1 postgres postgres 24321 Nov 1 15:48 /etc/postgresql/11/main/postgresql.conf
-rw-r--r-- 1 postgres postgres 317 Nov 1 15:48 /etc/postgresql/11/main/start.conf
So only "postgres" can edit the files that must be so edited.
Apparently, an unwritten rule says that one must never end up so that "whoami" shows "postgres". I see that I can, then, always do, for example, this (from "parallels"):
sudo -u postgres vi pg_hba.conf
And, given that one major theme in our recent mutual, extended, exchanges is that I want to use "local", "peer" authentication for the cluster-role "postgres" via the O/S user with the same name, I see that I can always run all the SQL scripts that I want, using this authentication, like this:
sudo -u postgres psql -f my_script.sql
With this in mind, I re-wrote my "clean start" script thus:
#!/bin/bash
# do this as ANY user that can do "sudo" (and this includes "root" itself)
sudo pg_ctlcluster stop 11/main
sudo rm -Rf /var/lib/postgresql/11/main
sudo -u postgres initdb \
-U postgres --encoding UTF8 --locale=C --lc-collate=C --lc-ctype=en_US.UTF-8 \
-A md5 --pwfile=my_password_file \
-D /var/lib/postgresql/11/main
sudo pg_ctlcluster start 11/main
sudo -u postgres psql -f my_script.sql
It certainly works. And when it's finished, this:
sudo -u postgres psql -c " select name, setting from pg_settings where category = 'File Locations'; "
shows that my files are where they ought to be. Then, further tests show that "local", "peer" authorization works as intended for my "clstr$mgr" role and that I can connect from client machines. So all is good.
Why, though, is "pg_ctlcluster stop/start... " preferred over "systemctl stop/start postgresql?". Both are quick enough. And I can't see any difference in effect. Moreover, the latter is what https://ubuntu.com/server/docs/databases-postgresql <https://ubuntu.com/server/docs/databases-postgresql> recommends.
Notice that I'm still using the off-limits "initdb" here. (I wired it up with a link on "/usr/bin".) Is there any reason to change that and to try to work out how to use what David (in an email soon after Adrian's) suggested?
sudo pg_dropcluster --stop 11 main
sudo pg_createcluster 11 main
sudo pg_ctlcluster start 11/main
I tried it. But it "did not work". I don't have the energy to describe the errors that were reported and the nasty outcomes that I got when I tried to use the new cluster. There's no useful doc for that approach and I've already established that Internet search gets me nowhere. So I'm inclined not to use it.
Rather, I want, now, simply to declare victory with the script that I showed and return to ordinary productive work.
david.g.johnston@gmail.com wrote:
Some repetition of what Adrian just posted ahead...
bryn@yugabyte.com wrote:
How can it be that the PG doc itself leads you by the hand to a regime where you need to use undocumented features?
The documentation tries to make clear that if you use third-party packaging to install PostgreSQL (which most people should) that the documentation for the packaging should describe this layer where PostgreSQL and the operating system intersect. You even quoted it: "follow the instructions for the specific platform.", though reading that now I think something along the lines of:
"Additionally, while reading the next chapter, Server Setup and Operation, is recommended if you are using a binary package the setup and operational environment it creates is likely to be somewhat different than what is described in this documentation. Please read the documentation for the packages you install to learn how it behaves and what additional platform-specific features it provides."
Actually, not sure on the best approach here, since the Server Setup chapter already says:
https://www.postgresql.org/docs/current/runtime.html
"The directions in this chapter assume that you are working with plain PostgreSQL without any additional infrastructure, for example a copy that you built from source according to the directions in the preceding chapters. If you are working with a pre-packaged or vendor-supplied version of PostgreSQL, it is likely that the packager has made special provisions for installing and starting the database server according to your system's conventions. Consult the package-level documentation for details."
However, that appears below-the-fold after a decent sized table of contents.
Changing anything now feels like an over-reaction to a single incident, but I sympathize with the general confusion all this causes, and the fact it is only in the recent past that we've made this first attempt to rectify the situation by adding these comments. A second-pass based upon this encounter seems at least reasonable. Whether I or others end up deciding it is worth proposing a patch remains to be seen.
Thanks for your explanations, David. I believe that my point about how all this seems to me is well taken. I might concede that the Debian/Ubuntu packaging provides adequate reference doc by implementing its "man" pages. But I haven't found anything like a user guide that explains *why* ordinarily documented PG features have been hidden from sight (but not removed) and how (if the Debian/Ubuntu alternatives are just wrappers for the native PG) one might do that wrapping by hand. Doing this would demonstrate what benefits the wrapping brings.
Anyway, I now have a working PG system and useful notes. When, presently, I make a second VM for PG 15 (I prefer separate VMs over having both versions in the same VM) it should all go quickly and smoothly.
I have no reason to describe to anybody else how to install and configure PG—and I certainly won't do this.
My interest in being able to re-establish the pristine cluster starting state reliably and quickly is to support my own productivity. I'll presently have SQL scripts that establish the "multitenancy by self-imposed discipline" scheme that I've referred to from any arbitrary state of population of a cluster. I don't intend my scheme to co-exist with other schemes. And I don't expect there to be any real use cases for starting with an arbitrarily populated cluster and taking it to a state that conforms with my scheme. Rather, all this is about demonstrating how to establish the scheme on the assumption (but not requirement) that one starts with a brand-new cluster that will be dedicated to the approach that I've sketched.
I'm looking forward to returning to that project and putting all that we've been discussing here behind me.
On 11/3/22 14:49, Bryn Llewellyn wrote:
I should make it clear that my VM has four "ordinary" users. Two are
present on first use when VM creation finishes: the system administrator
(called "parallels") and, of course, "root". The "parallels" user is
enabled for "sudo". Installing PG brings "postgres". (I created mine
before hand, and it was unperturbed by the PG installation. This is my
private laptop. And it suits me to give it a home directory and to
customize its ".bashrc". But that's so that I can look around with the
minimum of fuss.) Finally, there's my "clstr_mgr" O/S user that acts as
the authorization vehicle for my "clstr$mgr" cluster-role. Each of
"postgres" and "clstr_mgr" is in its own singleton group—and in no other
groups. And neither is set up for "sudo".)
So only "postgres" can edit the files that must be so edited.
That is not true:
aklaver@arkansas:~$ whoami
aklaver
aklaver@arkansas:~$ sudo vi /etc/postgresql/14/main/pg_hba.conf
[sudo] password for aklaver:
which opens pg_hba.conf for editing.
Apparently, an unwritten rule says that one must never end up so that
"whoami" shows "postgres". I see that I can, then, always do, for
example, this (from "parallels"):*sudo -u postgres vi pg_hba.conf*
And, given that one major theme in our recent mutual, extended,
exchanges is that I want to use "local", "peer" authentication for the
cluster-role "postgres" via the O/S user with the same name, I see that
I can always run all the SQL scripts that I want, using this
authentication, like this:
You want to use local peer with OS user postgres, that is not a
requirement. You could set up Postgres to log in the db user postgres by
any of the other auth means and do the below without sudo(ing) to OS
user postgres.
*sudo -u postgres psql -f my_script.sql*
With this in mind, I re-wrote my "clean start" script thus:
*#!/bin/bash
*
*# do this as ANY user that can do "sudo" (and this includes "root" itself)sudo pg_ctlcluster stop 11/main
sudo rm -Rf /var/lib/postgresql/11/mainsudo -u postgres initdb \
-U postgres --encoding UTF8 --locale=C --lc-collate=C
--lc-ctype=en_US.UTF-8 \
-A md5 --pwfile=my_password_file \
-D /var/lib/postgresql/11/mainsudo pg_ctlcluster start 11/main
sudo -u postgres **psql -f my_script.sql**
*
It certainly works. And when it's finished, this:*sudo -u postgres psql -c " select name, setting from pg_settings where
category = 'File Locations'; "
*
shows that my files are where they ought to be. Then, further tests show
that "local", "peer" authorization works as intended for my "clstr$mgr"
role and that I can connect from client machines. So all is good.Why, though, is "pg_ctlcluster stop/start... " preferred over "systemctl
stop/start postgresql?". Both are quick enough. And I can't see any
difference in effect. Moreover, the latter is what
https://ubuntu.com/server/docs/databases-postgresql
<https://ubuntu.com/server/docs/databases-postgresql> recommends.
It isn't you where using pg_ctl and in the Debian/Ubuntu packaging the
better option for that is pg_ctlcluster. I generally use the systemd
scripts to start/stop Postgres instances, though when I do pg_lsclusters
I tend to fall into using pg_ctlcluster as the cluster info is right there.
Notice that I'm still using the off-limits "initdb" here. (I wired it up
with a link on "/usr/bin".) Is there any reason to change that and to
try to work out how to use what David (in an email soon after Adrian's)
suggested?
Yes and since that is basically coloring outside the lines, then that
leads to the below blowing up.
*sudo pg_dropcluster --stop 11 main
sudo pg_createcluster 11 main
sudo pg_ctlcluster start 11/main-
*
I tried it. But it "did not work". I don't have the energy to describe
the errors that were reported and the nasty outcomes that I got when I
tried to use the new cluster. There's no useful doc for that approach
and I've already established that Internet search gets me nowhere. So
I'm inclined not to use it.
Per the saying, "In a ham and eggs breakfast the chicken is involved but
the pig is committed", right now you are involved in the Debian/Ubuntu
process not committed. Until you commit you will not get the results you
want.
Rather, I want, now, simply to declare victory with the script that I
showed and return to ordinary productive work.
--
Adrian Klaver
adrian.klaver@aklaver.com
On 2022-11-03 15:37:07 -0700, Adrian Klaver wrote:
On 11/3/22 14:49, Bryn Llewellyn wrote:
So only "postgres" can edit the files that must be so edited.
That is not true:
aklaver@arkansas:~$ whoami
aklaveraklaver@arkansas:~$ sudo vi /etc/postgresql/14/main/pg_hba.conf
[sudo] password for aklaver:which opens pg_hba.conf for editing.
Well, yes. Root can edit the file, too. But root can edit anything[1]Except ... lots of stuff, actually..
hp
[1]: Except ... lots of stuff, actually.
--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"
adrian.klaver@aklaver.com wrote:
bryn@yugabyte.com wrote:
So only "postgres" can edit the files that must be so edited.
That is not true.... [sudo vi some-file] which opens [it for editing].
By all means. I didn't bother to spell that out;
It isn't you where using pg_ctl and in the Debian/Ubuntu packaging the better option for that is pg_ctlcluster. I generally use the systemd scripts to start/stop Postgres instances, though when I do pg_lsclusters I tend to fall into using pg_ctlcluster as the cluster info is right there.
Can't parse this. Sorry.
Notice that I'm still using the off-limits "initdb" here. (I wired it up with a link on "/usr/bin".) Is there any reason to change that and to try to work out how to use what David (in an email soon after Adrian's) suggested?
Yes and since that is basically coloring outside the lines, then that leads to ... blowing up ... Per the saying, "In a ham and eggs breakfast the chicken is involved but the pig is committed", right now you are involved in the Debian/Ubuntu process but not committed. Until you commit you will not get the results you want.
Fair enough. I started again from the state where my "config_file", my "hba_file", and my "ident_file" are all customized as I want them to be but where I hadn't yet tried to trash my cluster and re-create it. Then I tried with "pg_dropcluster --stop" and "pg_createcluster". I discovered immediately that this approach (in contrast to the "initdb" approach) blows away all the *.conf files and recreates them in canonical form—just as the "man" page says. This is a bit of a nuisance. But it's negotiable. I installed my files as I want them in a safe place, outside of the entire PG world, and then used this script:
sudo pg_dropcluster --stop 11 main
sudo pg_createcluster 11 main \
-e UTF8 --locale=C --lc-collate=C --lc-ctype=en_US.UTF-8 \
-d /var/lib/postgresql/11/main \
/dev/null
sudo cp /etc/ybmt-code/pg-and-yb-config-files/*.conf \
/etc/postgresql/11/main
sudo chown postgres /etc/postgresql/11/main/*.conf
sudo chgrp postgres /etc/postgresql/11/main/*.conf
sudo chmod 644 /etc/postgresql/11/main/*.conf
sudo chmod 640 /etc/postgresql/11/main/pg_hba.conf
sudo chmod 640 /etc/postgresql/11/main/pg_ident.conf
sudo pg_ctlcluster start 11/main
sudo -u postgres psql -c " select name, setting from pg_settings where category = 'File Locations'; "
sudo -u postgres psql -f /etc/ybmt-code/cluster-mgmt/01-initialize-brand-new-YB-or-PG-clstr/00.sql
It worked without error and had the intended effect. My old approach with the uncommitted chicken used to take ~3 sec. This new approach takes ~5 sec. The difference is completely unimportant.
For various reasons, I need the non-standard "--lc-collate=C" choice. I could doubtless leave all the other options unspecified. But that's the style discussion we had the other day—and I prefer, here, to self-doc my choices.
I discovered that using, say, the "initdb" approach, then the "kosher" approach, and then the "initdb" approach brought all sorts of errors. That's the beauty of using a VM and file backups (or snapshots). I suppose this is to be expected.
Can I declare victory, now, with the approach that I showed above?
I'm impatient to get back to my real project.
p.s. Is my pessimism justified—that there simply exists no plain English user guide for this whole Debian/Ubuntu apparatus—correct. Or is it, rather, that my search skills are just too feeble?
On 11/3/22 18:50, Bryn Llewellyn wrote:
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> wrote:
It isn't you where using pg_ctl and in the Debian/Ubuntu packaging the
better option for that is pg_ctlcluster. I generally use the systemd
scripts to start/stop Postgres instances, though when I do
pg_lsclusters I tend to fall into using pg_ctlcluster as the cluster
info is right there.Can't parse this. Sorry.
Short version, use what works for you.
Fair enough. I started again from the state where my "config_file", my
"hba_file", and my "ident_file" are all customized as I want them to be
but where I hadn't yet tried to trash my cluster and re-create it. Then
I tried with "pg_dropcluster --stop" and "pg_createcluster". I
discovered immediately that this approach (in contrast to the "initdb"
initdb will not work on an directory with existing files, so this:
sudo -u postgres initdb \
-U postgres --encoding UTF8 --locale=C --lc-collate=C
--lc-ctype=en_US.UTF-8 \
-A md5 --pwfile=my_password_file \
-D /var/lib/postgresql/11/main
Will only work if /var/lib/postgresql/11/main is empty, so none of your
customized files will be there. You will have to copy them in just as
you do below.
As example:
postgres@maura:/usr/local/pgsql15> bin/initdb -D data/
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.
initdb: error: directory "data" exists but is not empty
initdb: hint: If you want to create a new database system, either remove
or empty the directory "data" or run initdb with an argument other than
"data".
FYI, you might want to take a look at the the includes mechanism:
https://www.postgresql.org/docs/15/config-setting.html#CONFIG-SETTING-CONFIGURATION-FILE
approach) blows away all the *.conf files and recreates them in
canonical form—just as the "man" page says. This is a bit of a nuisance.
But it's negotiable. I installed my files as I want them in a safe
place, outside of the entire PG world, and then used this script:
sudo pg_dropcluster --stop 11 main
sudo pg_createcluster 11 main \
-e UTF8 --locale=C --lc-collate=C --lc-ctype=en_US.UTF-8 \
-d /var/lib/postgresql/11/main \
/dev/null
sudo cp /etc/ybmt-code/pg-and-yb-config-files/*.conf \
/etc/postgresql/11/main
sudo chown postgres /etc/postgresql/11/main/*.conf
sudo chgrp postgres /etc/postgresql/11/main/*.conf
sudo chmod 644 /etc/postgresql/11/main/*.conf
sudo chmod 640 /etc/postgresql/11/main/pg_hba.conf
sudo chmod 640 /etc/postgresql/11/main/pg_ident.conf
sudo pg_ctlcluster start 11/main
sudo -u postgres psql -c " select name, setting from pg_settings where
category = 'File Locations'; "
sudo -u postgres psql -f
/etc/ybmt-code/cluster-mgmt/01-initialize-brand-new-YB-or-PG-clstr/00.sql
It worked without error and had the intended effect. My old approach
with the uncommitted chicken used to take ~3 sec. This new approach
takes ~5 sec. The difference is completely unimportant.For various reasons, I need the non-standard "--lc-collate=C" choice. I
could doubtless leave all the other options unspecified. But that's the
style discussion we had the other day—and I prefer, here, to self-doc my
choices.I discovered that using, say, the "initdb" approach, then the "kosher"
approach, and then the "initdb" approach brought all sorts of errors.
That's the beauty of using a VM and file backups (or snapshots). I
suppose this is to be expected.
Hence commitment instead of involvement.
*Can I declare victory, now, with the approach that I showed above?*
You are setting the goals not us, that is your decision.
p.s. Is my pessimism justified—that there simply exists no plain English
user guide for this whole Debian/Ubuntu apparatus—correct. Or is it,
rather, that my search skills are just too feeble?
Not that I know of.
--
Adrian Klaver
adrian.klaver@aklaver.com