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@xxxxxxxxxxxxxxxxxxxx" from the addressee list. (Yes, another of my notorious typos.) I resent it moments ago. It's here:
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 ... 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 had. And I'd followed the link to Section 21.2:
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 I did "sudo systemctl stop postgresql". Then I made sure that this line: # MAPNAME SYSTEM-USERNAME PG-USERNAME 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 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'; It produced this: pg_toast 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).
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. |
