Re: Restricting user to see schema structure

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

>
> 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(dot)klaver(at)aklaver(dot)com