From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Bryn Llewellyn <bryn(at)yugabyte(dot)com> |
Cc: | Neeraj M R <neerajmr12219(at)gmail(dot)com>, Tom Lane PostgreSQL <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>, pgsql-general list <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: Restricting user to see schema structure |
Date: | 2022-05-16 21:23:48 |
Message-ID: | bb217064-ac5f-fa7e-1e66-553373f05d76@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David G. Johnston | 2022-05-16 21:23:58 | Re: Restricting user to see schema structure |
Previous Message | Bryn Llewellyn | 2022-05-16 21:04:05 | Re: Restricting user to see schema structure |