Re: Restricting user to see schema structure - Mailing list pgsql-general

From Bryn Llewellyn
Subject Re: Restricting user to see schema structure
Date
Msg-id 84B060E2-4393-4D49-AB4C-BAFCA94620F1@yugabyte.com
Whole thread Raw
In response to Re: Restricting user to see schema structure  (Adrian Klaver <adrian.klaver@aklaver.com>)
Responses Re: Restricting user to see schema structure
Re: Restricting user to see schema structure
List pgsql-general
adrian.klaver@aklaver.com wrote:

bryn@yugabyte.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?

pgsql-general by date:

Previous
From: Bryn Llewellyn
Date:
Subject: Re: Deferred constraint trigger semantics
Next
From: Adrian Klaver
Date:
Subject: Re: Restricting user to see schema structure