adrian.klaver@xxxxxxxxxxx wrote:bryn@xxxxxxxxxxxx wrote: Adrian, I have the "pg_hba.conf" unmodified that came with the "brew" PG installation of PG Version 14.2 on my MacOS Big Sur laptop. It has just six non-comment lines, thus: # TYPE DATABASE USER ADDRESS METHOD local all all trust host all all 127.0.0.1/32 trust host all all ::1/128 trust local replication all trust host replication all 127.0.0.1/32 trust host replication all ::1/128 trust This lines up with what "select * from pg_hba_file_rules" gets, thus: line_number | type | database | user_name | address | netmask | auth_method | options | error -------------+-------+---------------+-----------+-----------+-----------------------------------------+-------------+---------+------- 89 | local | {all} | {all} | | | trust | | 91 | host | {all} | {all} | 127.0.0.1 | 255.255.255.255 | trust | | 93 | host | {all} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | | 96 | local | {replication} | {all} | | | trust | | 97 | host | {replication} | {all} | 127.0.0.1 | 255.255.255.255 | trust | | 98 | host | {replication} | {all} | ::1 | ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff | trust | | I read the Current "21.1. The pg_hba.conf File" section and noted this tip: « To connect to a particular database, a user must not only pass the pg_hba.conf checks, but must have the CONNECT privilege for the database. If you wish to restrict which users can connect to which databases, it's usually easier to control this by granting/revoking CONNECT privilege than to put the rules in pg_hba.conf entries. » I'd like to do what this tip says. But the regime that I have allows any non-super user to connect to any database. I just re-tested this with a brand-new user "joe"—and after doing "revoke connect on database postgres from joe". I'm obviously missing critical "pg_hba.conf" line(s). But I can't see what to add from the section that I mentioned. There must be some keyword, like "none", meaning the opposite of "all" for users. I tried this. (I don't have a database called "replication" so I removed those lines.) local postgres postgres trust host postgres postgres 127.0.0.1/32 trust host postgres postgres ::1/128 trust But that idea didn't work because, with my newly created user "joe", my "\c postgres joe" failed with a complaint that my "pg_hba.conf" had no entry for « user "joe", database "postgres" ». I discovered (by "drop user") that « role name "none" is reserved ». So I added these lines: local postgres none trust host postgres none 127.0.0.1/32 trust host postgres none ::1/128 trust But even after "grant connect on database postgres to joe", my "\c postgres joe" still failed just as I described above. For sport, I tried this instead: local postgres joe trust host postgres joe 127.0.0.1/32 trust host postgres joe ::1/128 trust But this goes against what the tip says. Anyway, after "revoke connect on database postgres from joe", my "\c postgres joe" succeeded. I tried Googling. But all the hits that I found were about controlling which remote hosts can connect at all and what password security is to be used. What must I do? And where is this described in the PG doc? |
