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.kla...@aklaver.com
