Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all
| От | Bryn Llewellyn |
|---|---|
| Тема | Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all |
| Дата | |
| Msg-id | 30EF0834-8AA5-437D-94CB-23709E8EB091@yugabyte.com обсуждение исходный текст |
| Ответ на | Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Список | pgsql-general |
bryn@yugabyte.com wroteThe descriptive designation "the role that owns the SQL part of the implementation of PostgreSQL" is too much of a mouthful for daily use. And anyway, this notion captures only part of the story that makes "postgres" uniquely what it is—at least on Ubuntu.
How much time would it have taken to go to the docs:
«
https://www.postgresql.org/docs/current/app-initdb.htmlSelects the user name of the database superuser. This defaults to the name of the effective user running initdb. It is really not important what the superuser's name is, but one might choose to keep the customary name postgres, even if the operating system user's name is different.
»
I HAD read that. The phrase occurs three times on that page. But the account doesn't define the term. Rather, it's used as if everybody knows what it means. Yet there's no x-ref to where the definition is. I did refer, albeit implicitly, to this doc by citing the text that "initdb --help" gives. The doc and the help say the same thing. You, Adrian, often accuse me of being too wordy. So I catered to you by not using words to spell out what I just spelled out here.
A PG cluster has lots of databases. Lots of things have names whose uniqueness scope is (maximally) a single database. Just a couple of things, and roles in particular, need names that are unique in the cluster as a whole. Therefore, the term "database superuser" is tautologically wrong. It should, at least, be "cluster superuser". But then, like I said, you can have as many superusers as you please in a single cluster. So the idea that one is singled out as *THE* [cluster] superuser didn't make sense to me. Moreover, the "initdb" doc says that it doesn't really matter if what it calls the "database superuser" has the same name as the O/S ussr that owns (most of) the PG installation and cluster content. But David pointed out here:
that you lose a lot if these two sides of the same coin don't have the same name. (So the "initdb" doc would be improved by an x-ref to the discussion of the consequences of the name choices here.)
This implies that there's still a missing term of art that denotes the nicely matched *pair* of within-cluster role and O/S user.
I said all this in my reply to David and Ian Barwick. I did send it about half an hour before you wrote this. But I see now that I'd managed to omit "pgsql-general@lists.postgresql.org" from the addressee list. (Yes, another of my notorious typos.) I resent it moments ago. It's here:
...listed as the owner of the pg_catalog schema, the objects in it, other related schemas...
You need to define 'other related schemas'.
select nspname
from pg_namespace n inner join pg_roles r on n.nspowner = r.oid
where r.rolname = 'postgres';
produces this:
pg_toast
pg_catalog
information_schema
pg_catalog
information_schema
...
Same point as before. You and others in the cohort of "the pgsql-general list lawmakers" have made me nervous about spelling things out 'cos doing so uses words and code—and often I've been told off for being too wordy. This is a pity because accuracy and precision inevitably compete with brevity.
...For it to work you have to be operating as the OS user postgres. I'm guessing that is why your attempt as usr failed, you where not running as the OS user usr.
No, I've been super-aware of the current identity of the O/S user in all tests, I've typed "whoami" more times in the last few days than before in my whole life to date.
I tried to set up "peer" authentication for a brand new O/S user that I called "usr" to match a brand new cluster role that I also called "usr". I added a new line in "pg_hba.conf" thus:
local all usr peerRead:
https://www.postgresql.org/docs/current/auth-peer.html
I had. And I'd followed the link to Section 21.2:
«
"The pg_ident.conf file is read on start-up and when the main server process receives a SIGHUP signal. If you edit the file on an active system, you will need to signal the postmaster (using pg_ctl reload, calling the SQL function pg_reload_conf(), or using kill -HUP) to make it re-read the file.»
To be sure, I did the whole thing again now. (And, yes, my O/S user is "postgres", at the start of this account.) Here's the relevant part of the output from "cat /etc/passwd":
postgres:x:1001:1001:,,,:/home/postgres:/bin/bash
usr:x:1002:1001:,,,:/home/usr:/bin/bash
usr:x:1002:1001:,,,:/home/usr:/bin/bash
I did "sudo systemctl stop postgresql". Then I made sure that this line:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
usr usr usr
usr usr usr
was present in this file:
/etc/postgresql/11/main/pg_ident.conf
Then I did this:
rm -Rf /var/lib/postgresql/11/main
initdb \
-U usr --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
-U usr --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
It finished with "Success. You can now start the database server...". That should read:
« can now start the cluster server »
and not "database server", yes? Anyway, I used "sudo systemctl start postgresql" to start it and not "pg_ctl ... start" like the prompt says. (That's a different area of concern. But I won't go there now.)
Then I did this (even though it seems to me that having stopped and blown away the old cluster and then having started a brand new one, this would be superfluous):
pg_ctl reload -D /var/lib/postgresql/11/main
It responded with "server signaled" (and nothing else).
Next, I did "su usr" and confirmed that I'd got where I intended to with "whomai". Then I started a session with this command:
psql -h localhost -p 5432 -d postgres -U usr
That worked fine, so I did this:
select nspname
from pg_namespace n inner join pg_roles r on n.nspowner = r.oid
where r.rolname = 'usr';
from pg_namespace n inner join pg_roles r on n.nspowner = r.oid
where r.rolname = 'usr';
It produced this:
pg_toast
pg_temp_1
pg_toast_temp_1
pg_catalog
public
information_schema
pg_temp_1
pg_toast_temp_1
pg_catalog
public
information_schema
like I'd expected. So, according to the "initdb" doc, "usr" is very definitely the database superuser.
So then I tried the bare "psql". It failed with this error:
connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL: database "usr" does not exist
This is exactly what I described in my email to David and Ian (URL above).
Again because you probably where not running as OS user usr.
However, while "initdb" was working, it said « The files belonging to this database system will be owned by user "postgres". This user must also own the server process.» So "postgres" is clearly special in ways other than just as the name of the owner of the within-cluster implementation. [but the name "postgres" from the O/S user] was thrust upon me simply by using the recommended "apt install postgresql-11" method. I had no say at all in the choice of this name. (as it happens, I did have a Linux user called "postgres" before I started. But I seem to recall, from PG installations on Ubuntu that I did a few years ago, that the Linux user "postgres" was simply created for me when I didn't already have it.
Again you are not getting the distinction between OS and database user. The directory /var/lib/postgresql/11/main is owned by postgres.postgres so any files created in it will be, as the message stated, owned by OS user postgres. Going back to this:
«
-U username
--username=username
Selects the user name of the database superuser. This defaults to the name of the effective user running initdb. It is really not important what the superuser's name is, but one might choose to keep the customary name postgres, even if the operating system user's name is different.
«
in the database cluster (the SQL part) itself the 'owning' database role will be usr. The package installation set up an OS user postgres that runs the OS side of the operation e.g the server code. It also by default uses that same name as the database superuser when creating a new cluster. This user then owns the SQL side. You can, however, change the SQL 'owner' for new cluster as you did.
That's unfair. I do appreciate the distinction. And I tried my best to show this in what I wrote. Moreover, my empirical tests seem to show that you can start a session without specifying the name of the cluster role as which to authorize, its password, and the name of the database to which to connect ONLY when these things are true:
1. The within-cluster, uniquely special, role that owns the catalogs and similar (designated as the "database superuser") has a certain name, say "pg_system".
2. The O/S user that owns (most of) the O/S presence of the cluster and the software that accesses it has the identical name "pg_system".
3. The current O/S user when you make the attempt to connect is "pg_system".
It's uncomfortable when I've merely speculated that this is the rule. And I want to read the definitive account. That's why I asked this:
*Where can I read a nice, linear, soup-to-nuts account of this whole business that introduces, and that consistently uses, the proper terms of art?*
Maybe there simply is no such account. And maybe one simply has to pick up the correct "do this, and this happens" understanding—without the support of an explicated mental model with suitable associated terms of art. But I think that my question is fair—and that it deserves an answer.
It's essential to understand this when you perform the Ubuntu installation as the doc specifies. If you don't, then you'll be stuck when you want to try the first, and critical, test for a successful installation—to start a session using psql. If you know what conventions the installation follows, and if you know the mental model, then you'll know that you can do a bare "psql" or, if you prefer, this as the "pstgres" O/S user
psql -c "alter role postgres with password 'x'";
where, here, the names of both halves of the coin were non-negotiably chosen by the installation flow. But if you don't know these things, then you'll be stuck.
В списке pgsql-general по дате отправления:
Предыдущее
От: Bryn LlewellynДата:
Сообщение: Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all
Следующее
От: Jeremy SmithДата:
Сообщение: Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all