Re: Restricting user to see schema structure

On 5/16/22 2:04 PM, Bryn Llewellyn wrote:
>> /adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> wrote:/
>>
>>> /bryn@yugabyte.com <mailto:bryn@yugabyte.com> wrote:/
>>>
>>>> /neerajmr12219@gmail.com <mailto:neerajmr12219@gmail.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:

> 
> 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" ».

So it worked you could not connect.

> 
> 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

none is not going to work per:

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

"user

     Specifies which database user name(s) this record matches. The 
value all specifies that it matches all users. Otherwise, this is either 
the name of a specific database user, or a group name preceded by +. 
(Recall that there is no real distinction between users and groups in 
PostgreSQL; a + mark really means “match any of the roles that are 
directly or indirectly members of this role”, while a name without a + 
mark matches only that specific role.) For this purpose, a superuser is 
only considered to be a member of a role if they are explicitly a member 
of the role, directly or indirectly, and not just by virtue of being a 
superuser. Multiple user names can be supplied by separating them with 
commas. A separate file containing user names can be specified by 
preceding the file name with @.
"

none is not listed as a special name.

> But this goes against what the tip says. Anyway, after "revoke connect 
> on database postgres from joe", my "\c postgres joe" succeeded.

Because as mentioned previously you did not "revoke connect on database 
postgres from public".


-- 
Adrian Klaver
adrian.klaver@aklaver.com