Thread: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
"peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Bryn Llewellyn
Date:
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:
...
I'm starting a new thread because my question, now, has nothing to do with the role whose interim name was deemed to be best spelled "bootstrap super user" for the time being.
This question is about "peer" authentication. I am able to make it work as long as my O/S user's name (what "pg_ident.conf" calls the "SYSTEM-USERNAME") is spelled identically to my partner cluster role's name (what "pg_ident.conf" calls the "PG-USERNAME"). But the doc for this file explains that you can define a mapping in "pg_ident.conf", give it any "MAPNAME" that you want, and map a "SYSTEM-USERNAME"to a differently spelled "PG-USERNAME". Or, as you put it, Adrian"
The purpose of mapping would be to do something like map OS user foo to PG user usr.
I want to get this to work because I want to use a role-name that has a dollar-sign in it (I don't care that this isn't in line with the Standard) and because the O/S uses dollar-sign in a reserved way and I don't want to go against the convention there by escaping things.
Here, I simply used o/s user "bob" and cluster role "alice".
And, yes, I did read the two doc sections "The pg_hba.conf File" and "User Name Maps" (for Version 11 'cos that's what I'm using). The latter shows this example:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
...
omicron robert bob
And I simply decided to follow its spirit with "bob" mapping to "alice", thus:
# MAPNAME SYSTEM-USERNAME PG-USERNAME
...
bllewell bob alice
bllewell bob alice
Here's my "pg_hba.conf":
local all postgres peer # See the essay at the start.
local all alice peer
local all bob peer
local all all peer
...
local all alice peer
local all bob peer
local all all peer
...
For reasons that will become clear in a moment, the file has entries for both "bob" and "alice".
Here's how I created the O/S user:
adduser bob # Password «x»
usermod -g postgres bob
usermod -g postgres bob
And here's how I created the cluster role:
create role alice with
nosuperuser
createrole
createdb
noreplication
nobypassrls
connection limit -1
login password 'x';
nosuperuser
createrole
createdb
noreplication
nobypassrls
connection limit -1
login password 'x';
(You can see that my plan is to follow the advice from the section "Role Attributes".) Again, for reasons that will become clear in a moment, I also created the role "bob" using an otherwise identically spelled "create role" statement.
Then I bounced the cluster thus (as my "postgres" O/S user):
sudo systemctl stop postgresql
sudo systemctl start postgresql
pg_ctl reload -D /etc/postgresql/11/main/
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.
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?
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Jeremy Smith
Date:
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 aliceThat worked fine—and "select current_role" showed "alice".Then I did the spelling for "peer", to authorize explicitly as "bob":psql -d postgres -U bobThat 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 postgresIt 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:
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
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
"David G. Johnston"
Date:
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 aliceHere's my "pg_hba.conf":...local all postgres peer # See the essay at the start.
local all alice peer
local all bob peer
local 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:
"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 postgresIt 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.
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Adrian Klaver
Date:
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
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Bryn Llewellyn
Date:
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"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)
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 | {}
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
# ---- -------------- -------------- ------ ----------------------------
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
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
# ------- --------------- -----------
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';
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?
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
"David G. Johnston"
Date:
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.
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Adrian Klaver
Date:
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
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
"Peter J. Holzer"
Date:
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!"
Attachment
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
"Peter J. Holzer"
Date:
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!"
Attachment
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
"Peter J. Holzer"
Date:
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!"
Attachment
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Karsten Hilbert
Date:
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"
showsthis:
>
> 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
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Karsten Hilbert
Date:
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
Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Adrian Klaver
Date:
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" showsthis: >> >> 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 givesthem access to some files which are not readable by the public. It might be useful for administrators, but AFAICS yourtest 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 breakingin.) That's no excuse for showing sloppy practices. I'll aim to do better.
CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Bryn Llewellyn
Date:
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:
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' ";
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.
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>
"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>
Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Adrian Klaver
Date:
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
Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Bryn Llewellyn
Date:
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.
Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Adrian Klaver
Date:
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
Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
"Peter J. Holzer"
Date:
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!"
Attachment
Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Bryn Llewellyn
Date:
> 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 tothis list where the issue was someone or something had changed a 'hidden' value in a env variable or conf file could notconnect 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 withoutexiting 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 connectionsto 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 needto 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 immediatelyafter 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-provisioningneeds by using the pre-installed “security definer” procedures. Even to the extend that they could easilyrestore 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 thatteam.) 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 thesetasks. But the idea that it’s simply impossible to start a session as one of these roles except by doing that appealsto my sense of what “hardening means. Another choice is to be stricter about “postgres” than about “clstr$mgs”—justas 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 passwordthe the config files that we’ve been discussing would allow them to use ONLY “local”, “peer” authentication.
Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Bryn Llewellyn
Date:
hjp-pgsql@hjp.at wrote:
This is what I see. I have Ubuntu 20.04 LTS VM using Parallels Desktop Version 18.
bryn@yugabyte.com 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 ...
This is what I see. I have Ubuntu 20.04 LTS VM using Parallels Desktop Version 18.
# adduser 'dog$house'
adduser: To avoid problems, the username should consist only of
letters, digits, underscores, periods, at signs and dashes, and not start with
a dash (as defined by IEEE Std 1003.1-2001). For compatibility with Samba
machine accounts $ is also supported at the end of the username
adduser: To avoid problems, the username should consist only of
letters, digits, underscores, periods, at signs and dashes, and not start with
a dash (as defined by IEEE Std 1003.1-2001). For compatibility with Samba
machine accounts $ is also supported at the end of the username
I tried your longer version verbatim:
useradd -m -s /bin/bash 'mac$crooge'
and that quietly succeeded. I'd left out "-m" and "-s" because, for an ordinary username, I get the home directory that I want and the (bash) shell that I want without explicitly asking for these.
It's bizarre that, merely by being explicit about these two fact, I'm now allowed to have a name with a dollar-sign—notwithstanding what the text of the earlier error message claimed. I wondered if that it wasn't an error message at all—and was just a warning. But "cat /etc/passwd" showed me that "dog$house" had not been created while "mac$crooge" HAD been.
So I've leaned something about yet another Linux weirdness.
However, now that I know what I know from what contributors to this thread have told me, I'll stick with plain "clstr_mgr" for the O/S user and use the "clstr$mgr" spelling just for the cluster role. It's a mild nuisance having to enquote this when it's the argument of psql's "-U" option, and in the config files. But I can live with that.
Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
"David G. Johnston"
Date:
On Mon, Oct 31, 2022 at 8:19 PM Bryn Llewellyn <bryn@yugabyte.com> wrote:
This is what I see. I have Ubuntu 20.04 LTS VM using Parallels Desktop Version 18.# adduser 'dog$house'
adduser: To avoid problems, the username should consist only of
letters, digits, underscores, periods, at signs and dashes, and not start with
a dash (as defined by IEEE Std 1003.1-2001). For compatibility with Samba
machine accounts $ is also supported at the end of the usernameI tried your longer version verbatim:useradd -m -s /bin/bash 'mac$crooge'and that quietly succeeded. I'd left out "-m" and "-s" because, for an ordinary username, I get the home directory that I want and the (bash) shell that I want without explicitly asking for these.It's bizarre that, merely by being explicit about these two fact, I'm now allowed to have a name with a dollar-sign—notwithstanding what the text of the earlier error message claimed. I wondered if that it wasn't an error message at all—and was just a warning. But "cat /etc/passwd" showed me that "dog$house" had not been created while "mac$crooge" HAD been.
Also note the "useradd" != "adduser" - you are running two different commands. One of them is stock Linux while the other is provided by Ubuntu (probably Debian, actually, too lazy to research specifics).
David J.
Re: CASE CLOSED... Re: "peer" authentication: cannot make "pg_ident.conf" work as I believe that the doc says that it should
From
Bryn Llewellyn
Date:
> david.g.johnston@gmail.com wrote: > >> bryn@yugabyte.com wrote: >> >> This is what I see. I have Ubuntu 20.04 LTS VM using Parallels Desktop Version 18. >> >> # adduser 'dog$house' >> adduser: To avoid problems, the username should consist only of >> letters, digits, underscores, periods, at signs and dashes, and not start with >> a dash (as defined by IEEE Std 1003.1-2001). For compatibility with Samba >> machine accounts $ is also supported at the end of the username >> >> I tried your longer version verbatim: >> >> useradd -m -s /bin/bash 'mac$crooge' >> >> and that quietly succeeded. I'd left out "-m" and "-s" because, for an ordinary username, I get the home directory thatI want and the (bash) shell that I want without explicitly asking for these. >> >> It's bizarre that, merely by being explicit about these two fact, I'm now allowed to have a name with a dollar-sign—notwithstandingwhat the text of the earlier error message claimed. I wondered if that it wasn't an error messageat all—and was just a warning. But "cat /etc/passwd" showed me that "dog$house" had not been created while "mac$crooge"HAD been. > > Also note the "useradd" != "adduser" - you are running two different commands. One of them is stock Linux while the otheris provided by Ubuntu (probably Debian, actually, too lazy to research specifics). Yes, indeed. I couldn't muster the strength to mention that piece of silliness. This explanation: https://askubuntu.com/questions/345974/what-is-the-difference-between-adduser-and-useradd is on a relatively trustworthy site. And its account sound plausible. (Maybe I should say the its ountacc sounds sibleplau.) My reading of it is that "adduser" is to be preferred. It certainly seems to be what you normally see in various randomexamples on the Internet. Anyway, my conclusion remains the same. I'll stick with "clstr_mgr" for my O/S user.