Re: Restricting user to see schema structure

> adrian(dot)klaver(at)aklaver(dot)com wrote:
>
>> bryn(at)yugabyte(dot)com wrote:
>>
>>> neerajmr12219(at)gmail(dot)com wrote:
>>>
>>> ...
>>
>> What exactly do you mean by "have created a new user and granted connection access to database"? As I understand it, there's no such thing. I mentioned a simple test in my earlier email that showed that any user (with no schema of its own and no granted privileges) can connect to any database—and see the full metadata account of all its content. I'm teaching myself to live with this.
>
> Besides the REVOKE CONNECT, it is also possible to prevent connections to a given database by a particular user by using settings in pg_hba.conf.

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?