Обсуждение: 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):
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): > > https://www.postgresql.org/message-id/20190909171519.GA7858%40hjp.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:
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
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)
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.
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:
That's the last thing I want to do. So then I read this:
Chapter 16. Installation from Binaries
(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
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)
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
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
-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:
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>
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:
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.
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';
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/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@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.
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...
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.
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:
"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.
I wonder if "binary package" would confuse some apt/yum/etc users? Maybe "package supplied from a distributor"?"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."
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
-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
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 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
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 somethingalong the lines of: > > "Additionally, while reading the next chapter, Server Setup and Operation, is recommended if you are using a binary packagethe setup and operational environment it creates is likely to be somewhat different than what is described in thisdocumentation. Please read the documentation for the packages you install to learn how it behaves and what additionalplatform-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 youare working with a pre-packaged or vendor-supplied version of PostgreSQL, it is likely that the packager has made specialprovisions for installing and starting the database server according to your system's conventions. Consult the package-leveldocumentation 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 allthis causes, and the fact it is only in the recent past that we've made this first attempt to rectify the situation byadding these comments. A second-pass based upon this encounter seems at least reasonable. Whether I or others end updeciding 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 concedethat the Debian/Ubuntu packaging provides adequate reference doc by implementing its "man" pages. But I haven't foundanything like a user guide that explains *why* ordinarily documented PG features have been hidden from sight (but notremoved) and how (if the Debian/Ubuntu alternatives are just wrappers for the native PG) one might do that wrapping byhand. 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 separateVMs 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'vereferred 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 itto 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'vesketched. 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/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. 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
> aklaver
>
>
> aklaver@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].
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