Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxx






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux