Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all |
| Date | |
| Msg-id | 8A2C8B1B-470B-42F9-9694-C1EDDAC06266@yugabyte.com Whole thread Raw |
| Responses |
Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all
Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all |
| List | pgsql-general |
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:
* You specify its name using the flag that's shown thus in response to "initdb —help"
-U, --username=NAME database superuser name
* 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, 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?
— — — — — — — — — — — — — — — — — — — —
* 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".)
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
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.
-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.
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.
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.
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?
pgsql-general by date:
Previous
From: Adrian KlaverDate:
Subject: Re: How to load data from CSV into a table that has array types in its columns?
Next
From: "David G. Johnston"Date:
Subject: Re: Seeking the correct term of art for the (unique) role that is usually called "postgres"—and the mental model that underlies it all