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]

 




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.

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

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

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

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:


...listed as the owner of the pg_catalog schema, the objects in it, other related schemas...

You need to define 'other related schemas'.

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

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

I had. And I'd followed the link to Section 21.2:
«
"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.
»

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/bas
h

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

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

No, like I just showed you, I was running as "usr". 

However, while "initdb" was working, it said « 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. [but the name "postgres" from the O/S user] 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.

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.

[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