"peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should

*sudo systemctl stop postgresql**sudo *
*systemctl start postgresql**pg_ctl reload -D /etc/postgresql/11/main/*

(I know that I could've used "systemctl restart ".) Like I said elsewhere,
the "reload" seems to be superfluous. But it costs nothing to do it.

Actually, you only needed the reload, you don't need to restart postgres
for changes to pg_hba.conf.

Then I did "su bob" and first did this sanity test:

*psql -h localhost -p 5432 -d postgres -U alice*

That worked fine—and "select current_role" showed "alice".

Then I did the spelling for "peer", to authorize explicitly as "bob":

*psql -d postgres -U bob*
That worked too so that "select current_role" now showed "bob". Finally, I
omitted "bob" here in the belief that this would make my mapping kick in
and authorize using the cluster role "alice":

*psql -d postgres*

It got me in without error. (And, as hoped for, there was no password
challenge.) But "select current_role" showed that the mapping had been
ignored and that I was connected again as "bob".

*What am I doing wrong?*

I think maybe you are considering psql and the postgres cluster to be more
tightly integrated than they actually are. The psql process does not know
anything about your pg_hba.conf or your identity map.

Remember that if you don't specify a username for psql, psql (not
postgres!) will default to using your system user. So, if you are the user
bob, these two commands are equivalent:
*psql -d postgres -U bob*
*psql -d postgres*

You can read more about the connection negotiation here:
https://www.postgresql.org/docs/current/protocol-flow.html#id-1.10.6.7.3

Importantly, the frontend (psql in this case) sends both the username and
the database name as part of the first message, so it has to figure out
what username and db name to use before initiating any communication with
the database.

-Jeremy

On Fri, Oct 28, 2022 at 5:26 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:

And I simply decided to follow its spirit with "bob" mapping to "alice",
thus:

*# MAPNAME SYSTEM-USERNAME PG-USERNAME*

*...bllewell bob alice*
Here's my "pg_hba.conf":

*...*

*local all postgres peer #
See the essay at the start.local all alice
peerlocal all bob
peerlocal all all
peer...*

So, none of those make use of the mapping (named bllewell) that you created
since you don't have a map option as described here:

https://www.postgresql.org/docs/current/auth-username-maps.html
"To use user name mapping, specify map=map-name in the options field in
pg_hba.conf."

Then I did "su bob" and first did this sanity test:

*psql -h localhost -p 5432 -d postgres -U alice*

I don't see how that worked given the above observation...

Finally, I omitted "bob" here in the belief that this would make my
mapping kick in and authorize using the cluster role "alice":

*psql -d postgres*

It got me in without error. (And, as hoped for, there was no password
challenge.) But "select current_role" showed that the mapping had been
ignored and that I was connected again as "bob".

*What am I doing wrong?*

As noted elsewhere, the name you specify on the psql command line, whether
chosen by default or explicitly, will be the name you end up connected as
should your connection attempt be accepted.

The mapping file simply allows the DBA to accept a role name that you
specify that, in the case of peer, is different than the o/s user name you
are physically establishing the connection over. In short, bob can request
to login as alice but the database is not going to peer authenticate that
request unless the pg_ident.conf file says that it ok for bob (o/s) to be
alice (system).

David J.

On 10/28/22 17:23, Bryn Llewellyn wrote:

Adrian, thank you for your reply to my « Seeking the correct term of art
for the (unique) role that is usually called "postgres"... » thread here:

It got me in without error. (And, as hoped for, there was no password
challenge.) But "select current_role" showed that the mapping had been
ignored and that I was connected again as "bob".

*What am I doing wrong?*

You skipped over this part of my post and the documentation:

Section 21.2

"The map-name is an arbitrary name that will be used to refer to this
mapping in pg_hba.conf."

This example below id for the ident auth method but the same syntax
applies to peer.

https://www.postgresql.org/docs/current/auth-pg-hba-conf.html

# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.0.0/16 ident
map=omicron

pg_ident.conf and pg_hba.conf are two separate files and the only way
information gets from the former to the latter is if you explicitly
include the map name under METHOD for the the auth line.

--
Adrian Klaver
adrian.klaver@aklaver.com

adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> wrote:

bryn@yugabyte.com <mailto:bryn@yugabyte.com> wrote:

...What am I doing wrong?

You skipped over this part of my post and the documentation (Section 21.2):
https://www.postgresql.org/docs/current/auth-pg-hba-conf.html <https://www.postgresql.org/docs/current/auth-pg-hba-conf.html&gt;

"The map-name is an arbitrary name that will be used to refer to this mapping in pg_hba.conf."

This example below is for the ident auth method but the same syntax applies to peer.

# TYPE DATABASE USER ADDRESS METHOD
host all all 192.168.0.0/16 ident map=omicron

pg_ident.conf and pg_hba.conf are two separate files and the only way information gets from the former to the latter is if you explicitly include the map name under METHOD for the the auth line.

Yes, Adrian, I see that I did slip up. Thanks, David, for pointing out this same error in your separate reply. I’m advertising my stupidity in this area rather effectively. My problem stems from the fact that the goal statement that my inner voice expresses seems so simple to state. This is what I want:

1. I want to do this at the O/S prompt on the machine where my PG cluster has been started: "su mary".

2. Then I want to start a session (I use "psql" here an an example) like this: "psql -d postgres".

3. Then, at the "psql" prompt, I want "select session_user" to show "bob".

It would have seemed to me, knowing just that the goal is achievable, that I could express this declaratively in one place—without needing to name the mapping between the system user's name and the cluster role's name, thus:

authentication type: local
authentication method: peer
system user: mary
cluster role: bob

I know that it isn't like this. But I have no intuition for why it could not be like this—and so it's easy for me to get muddled.

For the purpose of the tests that follow, I set up the O/S users "bob" and "mary" so that "id bob mary postgres" shows this:

id=1002(bob) gid=1001(postgres) groups=1001(postgres)
uid=1003(mary) gid=1001(postgres) groups=1001(postgres)
uid=1001(postgres) gid=1001(postgres) groups=1001(postgres),27(sudo),114(ssl-cert)

And I set up the cluster-roles "bob" and "mary" so that "\du" shows this:

bob | | {}
mary | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Then I started with this in "pg_hba.conf":

...
# TYPE DATABASE USER METHOD [auth-options]
# ---- -------------- -------------- ------ ----------------------------
local all bob peer
local all mary peer
...

together with en empty "pg_ident.conf". So far, after either "su bob" or "su mary", i was able to confirm that the bare "psql -d postgres" worked fine and that then "select session_user" showed, respectively, "bob" or "mary", too.

Then I changed "pg_hba.conf" to add a mapping for "mary" thus:

# TYPE DATABASE USER METHOD [auth-options]
# ---- -------------- -------------- ------ ----------------------------
local all bob peer
local all mary peer map=bllewell

But I left "pg_ident.conf" deliberately empty. I expected, now, that "psql -d postgres" would still work fine for "bob" but that if would fail for "mary". With this deliberate error in place, I found that after "su bob", the bare "psql -d postgres" worked fine. But after "su mary", the same command caused this error:

Peer authentication failed for user "mary"

I assume that the phrase « user "mary" » means the O/S user "mary".

It seems to me that the message « no entry for the mapping "bllewell" in "pg_ident.conf" » would be more helpful. But maybe that would need psychic powers.

Next, I put an identity mapping in for "mary" in "pg_ident.conf" thus:

# MAPNAME SYSTEM-USERNAME PG-USERNAME
# ------- --------------- -----------
bllewell mary mary

So, "bob" is the simple case. And "mary" is one step harder. Now, the unqualified "psql -d postgres" works again for "mary" (and it still works, of course, for "bob").

So far, so good. Now for the test, I mapped "mary" to "bob" in "pg_ident.conf" thus:

# MAPNAME SYSTEM-USERNAME PG-USERNAME
# ------- --------------- -----------
bllewell mary bob

As I'd expect, O/S "bob" still works fine and ends up as cluster-role "bob". But now, the attempt by O/S "mary" to connect using "psql -d postgres" fails, as it had ealier, with what boils sown to "computer says No":

Peer authentication failed for user "mary"

I still don't have a mental model that can explain this. As I reason it, the name "mary" is passed to the step that's informed by "pg_hba.conf" because it's available from the facts about the O/S user that's running the shell. Then, seeing "map=bllewell", the name "mary" is looked up in "pg_ident.conf" (just as it was in the previous test). Only now, instead of mapping it to the cluster-role "mary", which had worked, it now maps it to the role "bob". Why can it not connect, now, as "Bob"?

Clutching at straws, I reversed the mutual order of "mary" and "bob" in "pg_ident.conf"—even though that seemed wrong. It made no difference to the spelling of the « Peer authentication failed for user "mary" » error.

I've read the two relevant doc sections as carefully as I can. I can't see what I'm missing. I also confirmed with this query

select name, setting
from pg_settings
where category = 'File Locations';

that the files that I've been editing are indeed the files that the server uses.

Do I have to grant permission in some way to allow O/S "mary" to connect as cluster-role "bob". I though that I had exactly done this by the facts in "pg_hba.conf" and "pg_ident.conf".

I'm still missing something. What is it?

On Sat, Oct 29, 2022 at 8:20 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:This
is what I want:

1. I want to do this at the O/S prompt on the machine where my PG cluster
has been started: *"su mary"*.

2. Then I want to start a session (I use "psql" here an an example) like
this: *"psql -d postgres"*.

3. Then, at the "psql" prompt, I want *"select session_user"* to show
"bob".

I seriously do not understand why in the world you want an authentication
system where you tell the server "my user name is mary" and expect the
server to then say "ok, but I'm going to log you in as bob just for the fun
of it". The way I see it, when I tell the server "my user name is mary",
upon successful completion of the login I am logged in as, wait for
it........., mary.

*Peer authentication failed for user "mary"*

I assume that the phrase « user "mary" » means the O/S user "mary".

No, the server is never going to give you an error message with your o/s
user name showing. It is going to tell you "no, you may not login as mary,
because your name is bob and I have not been informed that you, bob, are
allowed to login as the user mary in this cluster".

Next, I put an identity mapping in for "mary" in "pg_ident.conf" thus:

*# MAPNAME SYSTEM-USERNAME PG-USERNAME*

*# ------- --------------- ----------- bllewell mary
mary*

As has been said numerous times, it is utterly pointless to define a
mapping like this - you get mary-is-mary for free just by saying peer.

So, "bob" is the simple case. And "mary" is one step harder. Now, the
unqualified "psql -d postgres" works again for "mary" (and it still works,
of course, for "bob").

So far, so good. Now for the test, I mapped "mary" to "bob" in
"pg_ident.conf" thus:

*# MAPNAME SYSTEM-USERNAME PG-USERNAME# -------
--------------- ----------- bllewell mary bob*

Now you are saying mary is allow to claim she is bob. Which requires the
o/s user to be mary and her psql command line have "-U bob"

It is not possible to make an alias mapping work without specifying "-U" on
the psql command line. Period. The -U is precisely how you tell the
server you are using an alias - without it the server expects that the o/s
user is logging in using their own name as the requested login role. In
that case either a peer entry for the user exists - and thus authentication
is successful - or it doesn't - and authentication will fail.

David J.

On 10/29/22 20:38, David G. Johnston wrote:

On Sat, Oct 29, 2022 at 8:20 PM Bryn Llewellyn <bryn@yugabyte.com
<mailto:bryn@yugabyte.com>> wrote:This is what I want:

1. I want to do this at the O/S prompt on the machine where my PG
cluster has been started: *"su mary"*.

2. Then I want to start a session (I use "psql" here an an example)
like this: *"psql -d postgres"*.

3. Then, at the "psql" prompt, I want *"select session_user"* to
show "bob".

I seriously do not understand why in the world you want an
authentication system where you tell the server "my user name is mary"
and expect the server to then say "ok, but I'm going to log you in as
bob just for the fun of it".  The way I see it, when I tell the server
"my user name is mary", upon successful completion of the login I am
logged in as, wait for it........., mary.

*Peer authentication failed for user "mary"*

I assume that the phrase « user "mary" » means the O/S user "mary".

No, the server is never going to give you an error message with your o/s
user name showing.  It is going to tell you "no, you may not login as
mary, because your name is bob and I have not been informed that you,
bob, are allowed to login as the user mary in this cluster".

Next, I put an identity mapping in for "mary" in "pg_ident.conf" thus:

*# MAPNAME    SYSTEM-USERNAME   PG-USERNAME*
*# -------    ---------------   -----------
  bllewell   mary              mary
*

As has been said numerous times, it is utterly pointless to define a
mapping like this - you get mary-is-mary for free just by saying peer.

+1

**
So, "bob" is the simple case. And "mary" is one step harder. Now,
the unqualified "psql -d postgres" works again for "mary" (and it
still works, of course, for "bob").

So far, so good. Now for the test, I mapped "mary" to "bob" in
"pg_ident.conf" thus:

*# MAPNAME    SYSTEM-USERNAME   PG-USERNAME
# -------    ---------------   -----------
  bllewell   mary              bob
*

Now you are saying mary is allow to claim she is bob.  Which requires
the o/s user to be mary and her psql command line have "-U bob"

It is not possible to make an alias mapping work without specifying "-U"
on the psql command line.  Period.  The -U is precisely how you tell the
server you are using an alias - without it the server expects that the
o/s user is logging in using their own name as the requested login
role.  In that case either a peer entry for the user exists - and thus
authentication is successful - or it doesn't - and authentication will fail.

+1

David J.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2022-10-29 20:38:07 -0700, David G. Johnston wrote:

Next, I put an identity mapping in for "mary" in "pg_ident.conf" thus:

# MAPNAME    SYSTEM-USERNAME   PG-USERNAME
# -------    ---------------   -----------
  bllewell   mary              mary

As has been said numerous times, it is utterly pointless to define a mapping
like this - you get mary-is-mary for free just by saying peer.

If this is the only line in pg_ident.conf I agree. But identity mappings
do serve a purpose. Consider this excerpt from one of our database
clusters:

localusers hjp hjp
localusers hjp wdsimp
localusers hjp wdsro
localusers hjp wdsacct

It says that I can login as hjp, wdsimp, wdsro and wdsacct without a
password. If the first entry wasn't there I wouldn't be able to log in
as myself.

It is not possible to make an alias mapping work without specifying "-U" on the
psql command line.  Period.  The -U is precisely how you tell the server you
are using an alias - without it the server expects that the o/s user is logging
in using their own name as the requested login role.

I think that's not quite correct. The -U option affects which user name
psql uses to connect to the server. It is psql which defaults to the
OS user name in the absence of the -U option (or the PGUSER environment
variable). The server has nothing to do with it.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

On 2022-10-29 20:20:50 -0700, Bryn Llewellyn wrote:

My problem stems from the fact that the goal statement that my inner
voice expresses seems so simple to state. This is what I want:

1. I want to do this at the O/S prompt on the machine where my PG cluster has
been started: "su mary".

2. Then I want to start a session (I use "psql" here an an example) like this:
"psql -d postgres".

3. Then, at the "psql" prompt, I want "select session_user" to show "bob".

Set the PGUSER=bob in mary's environment.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

On 2022-10-29 20:20:50 -0700, Bryn Llewellyn wrote:

For the purpose of the tests that follow, I set up the O/S users "bob" and
"mary" so that "id bob mary postgres" shows this:

id=1002(bob) gid=1001(postgres) groups=1001(postgres)
uid=1003(mary) gid=1001(postgres) groups=1001(postgres)

This has nothing to do with your problem, but don't do this. Normal
users should not be in group "postgres". That gives them access to some
files which are not readable by the public. It might be useful for
administrators, but AFAICS your test users aren't supposed to be that.

uid=1001(postgres) gid=1001(postgres) groups=1001(postgres),27(sudo),114
(ssl-cert)

And is there a reason for posgres to be in group sudo?

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

Am Sat, Oct 29, 2022 at 08:20:50PM -0700 schrieb Bryn Llewellyn:

For the purpose of the tests that follow, I set up the O/S users "bob" and "mary" so that "id bob mary postgres" shows this:

id=1002(bob) gid=1001(postgres) groups=1001(postgres)
uid=1003(mary) gid=1001(postgres) groups=1001(postgres)
uid=1001(postgres) gid=1001(postgres) groups=1001(postgres),27(sudo),114(ssl-cert)

And I set up the cluster-roles "bob" and "mary" so that "\du" shows this:

bob | | {}
mary | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Just a hint: you may want to use "mary_os" and "mary_db",
respectively, such that error messages can become less
ambivalent... (same for bob*).

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Am Sat, Oct 29, 2022 at 09:15:08PM -0700 schrieb Adrian Klaver:

*# MAPNAME    SYSTEM-USERNAME   PG-USERNAME*
*# -------    ---------------   -----------
  bllewell   mary              mary
*

As has been said numerous times, it is utterly pointless to define a mapping like this
- you get mary-is-mary for free just by saying peer.

It certainly is but he probably did it just to check whether
anything changes when another code path is run (the mapping)
while the previously-working result (= successful login)
should not change (mary is still mary). despite the
additional code path.

It seems to be a way of bisecting in order to verify/falsify
assumptions in his mental model.

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

On 10/30/22 09:16, Karsten Hilbert wrote:

Am Sat, Oct 29, 2022 at 09:15:08PM -0700 schrieb Adrian Klaver:

*# MAPNAME    SYSTEM-USERNAME   PG-USERNAME*
*# -------    ---------------   -----------
  bllewell   mary              mary
*

As has been said numerous times, it is utterly pointless to define a mapping like this
- you get mary-is-mary for free just by saying peer.

It certainly is but he probably did it just to check whether
anything changes when another code path is run (the mapping)
while the previously-working result (= successful login)
should not change (mary is still mary). despite the
additional code path.

Except the mapping was never in play as the map was not specified in
ph_hba.conf.

It seems to be a way of bisecting in order to verify/falsify
assumptions in his mental model.

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

--
Adrian Klaver
adrian.klaver@aklaver.com

hjp-pgsql@hjp.at wrote:

bryn@yugabyte.com:

For the purpose of the tests that follow, I set up the O/S users "bob" and "mary" so that "id bob mary postgres" shows this:

id=1002(bob) gid=1001(postgres) groups=1001(postgres)
uid=1003(mary) gid=1001(postgres) groups=1001(postgres)

This has nothing to do with your problem, but don't do this. Normal users should not be in group "postgres". That gives them access to some files which are not readable by the public. It might be useful for administrators, but AFAICS your test users aren't supposed to be that.

uid=1001(postgres) gid=1001(postgres) groups=1001(postgres),27(sudo),114 (ssl-cert)

And is there a reason for postgres to be in group sudo?

Thanks for pointing this out, Peter.

I was careless. I'm testing ideas using my laptop. And apart from the fragments of SQL, O/S scripts, and what these report, that I've shown on this list, everything is private. (Nobody else can access my laptop without stealing it and breaking in.)

That's no excuse for showing sloppy practices. I'll aim to do better.

INTRODUCTION

Thanks to all who've helped me on this topic. Forgive me if I left out anybody on the "To" list.

I suppose that I should have explained my use case more carefully. I did sketch it earlier on. But, not surprisingly, this got lost in the noise. I was afraid of being accused of writing too much, and so I kept my account short. Maybe too much so. Anyway, I've written it up more fully at the end. Feel free to ignore that account.

Very briefly, I find the notion appealing that you can authorize a client session as "postgres" (using this actual role name to denote the cluster's bootstrap superuser) by authorizing an O/S session on the machine that hosts the cluster's data and the software that manages it without needing a (second) password because being able to log in as the right O/S user is considered enough of a check. I'll call this O/S user "postgres", too, recognizing the common convention and to save myself some typing. This allows the possibility to set the password for the "postgres" cluster-role to NULL so that you MUST use the O/S prompt to start a session as this role. In other words, make it such that "local", "peer" authentication is the ONLY way to start a session as the "postgres" role". (This would echo a very popular, highly recommended, practice with Oracle Database and its corresponding SYS database user.)

"Local", "peer" authentication is actually essential when you install PG on Ubuntu because the "apt install postgresql-11" flow (at least) offers no opportunity for user input and finishes up with an already-started cluster that has password authentication turned on (using the "md5" method). But the password is a secret. So the only way to make progress its to start with this:

psql -c " alter role postgres with password 'x' ";

"Local", "peer" authentication is also a useful backdoor (even when a NOT NULL role password is defined) for the case that a human being forgets the password that allows starting a session as the "postgres" role from a client machine.

Further, if the "postgres" cluster-role's password is deliberately set to NULL, then there's no second password to keep safe—but you can, nevertheless, start a session as the "postgres" cluster-role from the O/S prompt where the cluster is hosted by authorizing as the "postgres" O/S user. I'll think more about this. But it seems that it might be a useful "hardening" notion in the general business of security practice to adopt this regime intentionally.

As an extension of this thinking, I've resolved to adopt the practice recommendation from the doc always to use a dedicated, slightly junior, role for provisioning databases and roles. I want to call this role "clstr$mgr". And, yes, I do want that dollar sign in place. I explain why below. The practice goes hand-in-hand with keeping the password that allows starting a session as the "postgres" role a very closely guarded secret. This means that the people who know the password that allows starting a session as the "clstr$mgr" role will NOT know the password that allows starting a session as the "postgres" role.

Of course, because what's sauce for the goose is sauce for the gander, I want to show that it's possible to implement the same practice for "clstr$mgr" sessions as for "postgres" sessions. In other words, to make it possible to start a session as the "clstr$mgr" role ONLY by using "local", "peer" authentication—but, obviously, via its own dedicated O/S user.

However, Linux (at least) simply disallows O/S users that have a dollar sign in the name. That's where the idea of using a mapping from the O/S user "clstr_mgr" to the cluster role "clstr$mgr" comes from. (So if Linux had a different philosophy for user names, like it already has for file names, then I wouldn't be talking about mapping.)

THE SOLUTION

I now have an end-to-end solution where I can, for example, "ssh" to the cluster's host machine as the O/S user "clstr_mgr" and simply type "psql" at the O/S prompt, as soon as I'm in, to take me to a session where this:

select current_database()||' > '||session_user as "Where/who ami I";

shows this:

Where/who ami I
----------------------
postgres > clstr$mgr

I actually have no requirement to elide the database name or the role name. The only thing I *require* is not to need a second password. But Peter showed me how—so why not follow his plan? It's a nice, albeit small, usability benefit. Here's how I got there.

1. Create the database role
---------------------------

create role clstr$mgr with
nosuperuser
createrole
createdb
noreplication
nobypassrls
connection limit -1
login password null;

2. Create the partner O/S user
------------------------------

I use "sudo" from any starting place that allows this. I (with another hat on) have to be allowed to do this, also, for, e.g, "systemctl start postgresql" and its cousins.

sudo adduser clstr_mgr

Then (as per Peter) I put this in the ".bashrc" for the O/S user "clstr_mgr":

export PGDATABASE='postgres'
export PGUSER='clstr$mgr'

3. Set up the config files
--------------------------

Here's (the relevant extract from) my "pg_hba.conf" file:

# TYPE DATABASE USER METHOD [auth-options]
# ---- -------- -------------- ------ ----------------------------
local all postgres peer
local all "clstr$mgr" peer map=bllewell
local all all peer

And here's my "pg_ident.conf" file in its entirety:

# MAPNAME SYSTEM-USERNAME PG-USERNAME
# -------- --------------- -----------
bllewell clstr_mgr "clstr$mgr"

Regard my name, "bllewell", as just a placeholder for something more suitable if I ever use this for real.

And that's it!

Of course, these two longer forms work too. This:

psql -h localhost -p 5432 -d postgres -U 'clstr$mgr'

But this DOES require the role's password. So I should really say that it works only when I set a NOT NULL password for the role—and so it doesn't suit my purpose.

This, on the other hand:

psql -d postgres -U 'clstr$mgr'

calls for "local", "peer" authentication as so it does NOT require a password. That would be enough for me. But, naturally, and now that it's working. I prefer the Peter-inspired bare "psql".
— — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — — —

MY ACTUAL USE CASE

I want to implement a robust convention for multitenancy. It depends critically on a "local role" notion that is defined as follows. A local role can at least connect, and maybe do other things, to exactly one database. This convention allows each database to seem to be its own private world where I can, therefore, choose the names for my local roles without considering collision with the names of other local roles.

My scheme is naïve. But it works. I enforce the rule that database names are short and sweet: "d0", "d1", and so on up to any integer following the "d". I use the "comment" statement to express the purpose of the database without trying to reflect this in the name. I could have made the database names pure integers. But I don't want to be burdened with double quoting the corresponding identifiers. Once I've authorized a session, of course, the name of the database doesn't matter.

Next, I want to use role "nicknames" that you can choose freely as long as the nickname has only Lower-case latin letters, digits or underscores—with the familiar extra rule about how it can start. Notatbly, the nickname must not contain a dollar-sign.

The real role name is then exemplified thus:

"d42$mgr", "d42$client", "d42$what_ever", ...

Here the first two names are special (and are set up by the provisioning of the tenant database itself). The others are all provisioned by "security definer" procedures that "dNN$mgr" has "execute" on. They use "current_database() to confine the scope of the roles they create by granting "connect" (and so on) only to that. Of course, the arguments to these procedures expect the nickname. And they generate the actual name behind the scenes. The role-provisioning procs are in a dedicated schema "mgr" that is brought by "template1". And they're owned by "clstr$mgr". I've seen the need, so far, for just one special role-provisioning proc that's owned by "postgres". This is needed for setting parameters that must be done by a superuser. Once a new tenant database (as I call it) has been provisioned by a session that authorized as "clstr$mgr", then such a session is no longer needed (except, maybe, later to drop the database). Of course, the whole regime has to be set up in a big bootstrap while the cluster is still new and (effectively) single-user. Some of this needs a "postgres" session. And some needs a "clstr$mgr" session. It doesn't harm usability to require that this bootstrapping (just like cluster creation itself) is done by working at the O/S prompt.

The dollar-sign helps the convention because it makes the rule that governs the legality of a role nickname easy to state. And it doesn't matter if this is a theoretical portability problem because the scheme is oriented specifically to how multitenancy works in PG.

Finally, the role provisioning procs grant each newly-created role to "clstr$admin". And then new local roles are granted to "dNN$mgr" so that, according to rank in the hierarchy, "clstr$mgr" can "set role" to ANY local role in ANY tenant database. And the "dNN#mgr" role for some tenant database "dNN" can "set role" to any local role in the tenant that it manages.

The reason for liking the name "clstr$mgr" is obvious now: the more general form is "<scope>$<nickname>". If it weren't for the existing convention, I'd call the cluster bootstrap superuser "clstr$super". (I'm still tempted. I can see, now, how ro do this—thanks to everybody's help. But I fear that that this might be wroo unconventional to be wise.)

<note>
I could give up my dollar-sign idea for my naming convention and, instead, use (say) double-underscore as the separator for the two components of the "scope-nickname" template. Then "clstr__mgr" would be legal both as a role name and as an O/S user name. But this idea appeals to me less, aesthetically, than using the dollar-sign.
</note>

On 10/30/22 21:01, Bryn Llewellyn wrote:

See comments inline.

*INTRODUCTION

*Thanks to all who've helped me on this topic. Forgive me if I left out
anybody on the "To" list.

I suppose that I should have explained my use case more carefully. I did
sketch it earlier on. But, not surprisingly, this got lost in the noise.
I was afraid of being accused of writing too much, and so I kept my
account short. Maybe too much so. Anyway, I've written it up more fully
at the end. Feel free to ignore that account.

Very briefly, I find the notion appealing that you can authorize a
client session as "postgres" (using this actual role name to denote the
cluster's bootstrap superuser) by authorizing an O/S session on the

Unless you are using a different package manager, say Postgres.app:

https://postgresapp.com/

User your system user name

machine that hosts the cluster's data and the software that manages it
without needing a (second) password because being able to log in as the
right O/S user is considered enough of a check. I'll call this O/S user
"postgres", too, recognizing the common convention and to save myself
some typing. This allows the possibility to set the password for the
"postgres" cluster-role to NULL so that you MUST use the O/S prompt to
start a session as this role. In other words, make it such that "local",
"peer" authentication is the ONLY way to start a session as the
"postgres" role". (This would echo a very popular, highly
recommended, practice  with Oracle Database and its corresponding SYS
database user.)

"Local", "peer" authentication is actually essential when you install PG
on Ubuntu because the "apt install postgresql-11" flow (at least) offers
no opportunity for user input and finishes up with an already-started
cluster that has password authentication turned on (using the "md5"
method). But the password is a secret. So the only way to make progress
its to start with this:

It is not a secret, it does not exist. In other words it is never set as
that is left for the DBA to do.

psql -c " alter role postgres with password 'x' ";

"Local", "peer" authentication is also a useful backdoor (even when a
NOT NULL role password is defined) for the case that a human being
forgets the password that allows starting a session as the "postgres"
role from a client machine.

Actually on the server machine as 'local' is a socket connection.

As an extension of this thinking, I've resolved to adopt the practice
recommendation from the doc always to use a dedicated, slightly junior,
role for provisioning databases and roles. I want to call this role
"clstr$mgr". And, yes, I do want that dollar sign in place. I explain
why below. The practice goes hand-in-hand with keeping the password that
allows starting a session as the "postgres" role a very closely guarded
secret. This means that the people who know the password that allows
starting a session as the "clstr$mgr" role will NOT know the password
that allows starting a session as the "postgres" role.

Good idea.

*THE SOLUTION

*I now have an end-to-end solution where I can, for example, "ssh" to
the cluster's host machine as the O/S user "clstr_mgr" and simply type
"psql" at the O/S prompt, as soon as I'm in, to take me to a session
where this:

select current_database()||' > '||session_user as "Where/who ami I";

shows this:

   Where/who ami I
----------------------
 postgres > clstr$mgr

I actually have no requirement to elide the database name or the role
name. The only thing I *require* is not to need a second password. But
Peter showed me how—so why not follow his plan? It's a nice, albeit
small, usability benefit. Here's how I got there.

1. Create the database role
---------------------------

create role clstr$mgr with
  nosuperuser
  createrole
  createdb
  noreplication
  nobypassrls
  connection limit -1
  login password null;

2. Create the partner O/S user
------------------------------

I use "sudo" from any starting place that allows this. I (with another
hat on) have to be allowed to do this, also, for, e.g, "systemctl start
postgresql" and its cousins.

sudo adduser clstr_mgr

Then (as per Peter) I put this in the ".bashrc" for the O/S user
"clstr_mgr":

export  PGDATABASE='postgres'
export      PGUSER='clstr$mgr'

3. Set up the config files
--------------------------

Here's (the relevant extract from) my "pg_hba.conf" file:

# TYPE  DATABASE  USER            METHOD  [auth-options]
# ----  --------  --------------  ------  ----------------------------
  local all       postgres        peer
  local all       "clstr$mgr"     peer    map=bllewell
  local all       all             peer

And here's my "pg_ident.conf" file in its entirety:

# MAPNAME   SYSTEM-USERNAME  PG-USERNAME
# --------  ---------------  -----------
  bllewell  clstr_mgr        "clstr$mgr"

Thumbs up.

Regard my name, "bllewell", as just a placeholder for something more
suitable if I ever use this for real.

And that's it!

Of course, these two longer forms work too. This:

psql -h localhost -p 5432 -d postgres -U 'clstr$mgr'

But this DOES require the role's password. So I should really say that
it works only when I set a NOT NULL password for the role—and so it
doesn't suit my purpose.

This, on the other hand:

psql -d postgres -U 'clstr$mgr'

calls for "local", "peer" authentication as so it does NOT require a
password. That would be enough for me. But, naturally, and now that it's
working. I prefer the Peter-inspired bare "psql".

Personally, I use longer forms like above as a form of explicit is
better then implicit. There are no end of posts to this list where the
issue was someone or something had changed a 'hidden' value in a env
variable or conf file could not connect or connected to wrong cluster
and/or database.

--
Adrian Klaver
adrian.klaver@aklaver.com

adrian.klaver@aklaver.com wrote:

bryn@yugabyte.com wrote:

This, on the other hand:

psql -d postgres -U 'clstr$mgr'

calls for "local", "peer" authentication as so it does NOT require a password. That would be enough for me. But, naturally, and now that it's working. I prefer the Peter-inspired bare "psql".

Personally, I use longer forms like above as a form of explicit is better then implicit. There are no end of posts to this list where the issue was someone or something had changed a 'hidden' value in a env variable or conf file could not connect or connected to wrong cluster and/or database.

Yes. I think the same as you about being explicit (in programs and scripts). That's why the "create role" statement that I showed mentioned every settable attribute. It's relatively rare that my requirement is "use the reigning defaults, whatever they might be now and whatever they might be changed to later". (Having said this, that for me rare scenario is proper in certain cases.)

So when I write a script to connect as "clstr$mgr", I'll use the explicit form that calls for "local", "peer" authentication and that uses the "-d" and "-U" flags. And I'll add a comment to say that, because the script is run only on the cluster's host machine after logging in as the O/S user "clstr_mgr", the (only) required password challenge has already been met. I plan to stage all of my "PG multitenancy by imposed convention" code in a dedicated Yugabyte, Inc GitHub repo. This will allow the code comment that I mentioned to x-ref the README.md that explains how I set up "pg_hba.conf" and "pg_ident.conf" to define the mapping between the O/S principal and its partner within-cluster principal.

This thinking extends, of course, to:

psql -d postgres -U 'postgres'

having logged in as the O/S user "postgres". (And here, I can simply "set role" to "clstr$mgr" when I need to without exiting one session, logging in as a different O/S user, and then starting a new session.)

But when I'm working interactively, I might well allow myself to type the bare minimum, on the fly, that gets the result.

On 10/31/22 10:31 AM, Bryn Llewellyn wrote:

adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> wrote:

bryn@yugabyte.com <mailto:ryn@yugabyte.com> wrote:

This, on the other hand:

psql -d postgres -U 'clstr$mgr'

calls for "local", "peer" authentication as so it does NOT require a
password. That would be enough for me. But, naturally, and�now that
it's working. I prefer the Peter-inspired bare "psql".

Personally, I use longer forms like above as a form of explicit is
better then implicit. There are no end of posts to this list where�the
issue was someone or something had changed a 'hidden' value in a env
variable or conf file could not connect or connected to�wrong cluster
and/or database.

This thinking extends, of course, to:

psql -d postgres -U 'postgres'

having logged in as the O/S user "postgres". (And here, I can simply
"set role" to "clstr$mgr" when I need to without exiting one session,
logging in as a different O/S user, and then starting a new session.)

This implies that the only auth method you will be using is peer, is
that correct?

This also means that the only connections to the cluster will be done as
local, is that correct?

But when I'm working interactively, I might well allow myself to type
the bare minimum, on the fly, that gets the result.

--
Adrian Klaver
adrian.klaver@aklaver.com

On 2022-10-30 21:01:25 -0700, Bryn Llewellyn wrote:

However, Linux (at least) simply disallows O/S users that have a
dollar sign in the name.

This is getting quite off-topic, but that isn't true:

trintignant:~ 22:46 :-) 1015# useradd -m -s /bin/bash 'mac$crooge'
trintignant:~ 22:46 :-) 1016# su - 'mac$crooge'
mac@trintignant:~$ id
uid=1002(mac$crooge) gid=1003(mac$crooge) groups=1003(mac$crooge)
mac@trintignant:~$

I'm not saying that doing this is a good idea ...

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

adrian.klaver@aklaver.com wrote:

bryn@yugabyte.com wrote:

adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> wrote:

bryn@yugabyte.com wrote:

This, on the other hand:

psql -d postgres -U 'clstr$mgr'

calls for "local", "peer" authentication as so it does NOT require a password. That would be enough for me. But, naturally, and now that it's working. I prefer the Peter-inspired bare "psql".

Personally, I use longer forms like above as a form of explicit is better then implicit. There are no end of posts to this list where the issue was someone or something had changed a 'hidden' value in a env variable or conf file could not connect or connected to wrong cluster and/or database.

This thinking extends, of course, to:

psql -d postgres -U ‘postgres'

having logged in as the O/S user "postgres". (And here, I can simply "set role" to "clstr$mgr" when I need to without exiting one session, logging in as a different O/S user, and then starting a new session.) But when I'm working interactively, I might well allow myself to type the bare minimum, on the fly, that gets the result.

This implies that the only auth method you will be using is peer, is that correct? This also means that the only connections to the cluster will be done as local, is that correct?

I must stress that this is just an idea that I’m thinking about. I’m not committed to anything. At the very least, I’ll need to implement the complete convention-based multitenancy scheme that I sketched and try out some use cases.

The idea that informs this is that, maybe, sessions authorized as “postgres” or “clstr$mgr” would be needed only immediately after creating a new cluster to bootstrap the regime into place and to create, say, 100 empty databases.

Maybe, from time to time, it would be appropriate to patch the artifacts that implement the scheme. But that should be doable (with the usual discipline for making only compatible changes).

On a daily basis, the people who know the password for the “dNN$mgr” tenant database’s manager could meet all their role-provisioning needs by using the pre-installed “security definer” procedures. Even to the extend that they could easily restore it to the pristine state and start again. Or they could simply send an email to say they were done with it. And then the “clstr$mgr” guy would change the password and return it to the pool. (So another very rare task for that team.)

It might be too strict to force the “clstr$mgr” guys (and the “postgres” guys too) to “ssh” the to cluster’s host to do these tasks. But the idea that it’s simply impossible to start a session as one of these roles except by doing that appeals to my sense of what “hardening means. Another choice is to be stricter about “postgres” than about “clstr$mgs”—just as the doc talks about.

So, yes, if I still like it when it’s all working, then each of the “postgres” and “clstr$mgr” roles would have a NULL password the the config files that we’ve been discussing would allow them to use ONLY “local”, “peer” authentication.