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