Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all

On 10/26/22 18:33, Bryn Llewellyn wrote:
> The 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.
> 
> MORE...
> 
> Here's what my empirical observations told me:
> 
> It's easy to characterize this role by describing the way that you get 
> it and the conspicuous unique emergent properties that it has.
> 
> * You specify its name using the flag that's shown thus in response to 
> "initdb —help"
> 
>    -U, --username=NAME       database superuser name

How much time would it have taken to go to the docs?:

https://www.postgresql.org/docs/current/app-initdb.html

"-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.
"

> 
> * It's listed as the owner of the pg_catalog schema, the objects in it, 
> other related schemas in every existing and yet-to-be-created database,

You need to define 'other related schemas'.


> and some global things too. Loosely, it's the owner of the SQL part of 
> the implementation of PostgreSQL.
> 
> The conventional choice is "postgres". I just did a brand-new PG 
> installation in a brand new Ubuntu VM and I simply ended up with this 
> name when the installation finished. (There was no chance in the 
> installation flow to choose the name.) However, an informal survey among 
> contacts who have PG installations on macOS showed that this "special" 
> role ends up with the name that you gave when you first configured your 
> new macOS for the admin O/S user. It's usually a cryptic form of one's 
> own name—as is my "Bllewell".
> 
> But the name "database superuser name" (in "initdb" speak) is useless as 
> a term of art for naming the phenomenon because you can have an 
> unlimited number of roles that are created "with superuser" in a PG cluster.
> 
> In another context, the comments in the shipped "pg_hba.conf" file (at 
> least on Ubuntu) include these:
> 
> # Database administrative login by Unix domain socket
> 
> for (in my case) this line:
> 
> local   all             postgres                                peer
> 
> (I failed when I tried to add a new one of my own. See below. But I 
> assume that it must be possible—also for a superuser.)
> 
> I noticed that in my case, the bare "psql" O/S command connects me to 
> "-d postgres -U postgres" without a password challenge. And the setup 
> had been done by the installation. Is "postgres" role uniquely able to 
> connect in this way with no password challenge? And might "the 
> administrative role" be the term of art that I'm seeking?

Nothing unique. The OS user postgres has been setup to login as database 
role user by the installation.

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.

> 
> — — — — — — — — — — — — — — — — — — — —
> 
> * B.t.w., 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                                     peer
> 
> (But there already is such a line for the special name "all".)

Read:

https://www.postgresql.org/docs/current/auth-peer.html

"

map

     Allows for mapping between system and database user names. See 
Section 21.2 for details.

"

Section 21.2

https://www.postgresql.org/docs/current/auth-username-maps.html

"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."
> 
> And I added a new line in "pg_ident.conf" (before, there were none at 
> all) thus:
> 
> # MAPNAME       SYSTEM-USERNAME         PG-USERNAME
> 
> usr             usr                     usr
> 
> But this attempt to connect:
> 
> psql -d postgres -U usr
> 
> failed with this error:
> 
> connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" 
> failed: FATAL:  Peer authentication failed for user "usr"
> 
> while this attempt:
> 
> psql -h localhost -p 5432 -d postgres -U usr
> 
> happily suceeded. I clearly missed some essential other steps. But the 
> doc didn't x-ref me to these.
> 
> I also tried this:
> 
> 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 succeeded. And, after re-start, I could connect as "usr". But I still 
> could not do this using the "peer" method. I saw that, now, "usr" owns 
> the within-cluster PG implementation artifacts.


Again because you probably where not running as OS user usr.

> 
> However, while "initdb" was working, it said this:
> 
> 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. And this 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.

> 
> *Where can I read a nice, linear, soup-to-nuts acount of this whole 
> business that introduces, and that consistently uses, the proper terms 
> of art?*
> 

-- 
Adrian Klaver
adrian.klaver@aklaver.com