> adrian.kla...@aklaver.com wrote:
>
>> b...@yugabyte.com wrote:
>>
>>> adrian.kla...@aklaver.com wrote:
>>>
>>>> b...@yugabyte.com <mailto:b...@yugabyte.com> wrote:
>>>>
>>>>> neerajmr12...@gmail.com <mailto:neerajmr12...@gmail.com> wrote:
>>>>>
>>>>> ...
>>>>
>>>> What exactly do you mean by "have created a new user and granted
>>>> connection access to database"?
>>>
>>> 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... [But thing don't work as I want.]
>
> Because as mentioned previously you did not "revoke connect on database
> postgres from public".
Right, I see the importance of this now. I now realize that when a database is
created, CONNECT on it is automatically granted to PUBLIC. But there's no
mention of this (or what to read to learn that this is the case) in the
"pg_hba.conf" chapter. Nor does the section on the CREATE DATABASE statement
mention this. How is the neophyte supposed to know about this behavior?
Another thing that confused me was the significance of the lines for the
database "replication" in the "pg_hba.conf" that came with my installation. Add
to this the mutually exclusive keywords "REPLICATION" and "NO REPLICATION" in
the CREATE ROLE statement. ("These clauses determine whether a role is a
replication role.") So this seems to be a distinct use of the word from how
it's used in "pg_hba.conf" as the name of a database (that might well not
exist). Strangely, the CREATE ROLE doc says that you don't need either of
"REPLICATION" or "NO REPLICATION" but it doesn't say what the default is.
David Johnston wrote this in a separate thread:
> I don't quite know how to address your random experimentation with
> pg_hba.conf. None of the things you showed are surprising though - were you
> expecting different?
My reports of my random experimentation were the email equivalent of the "think
aloud" approach to usability testing. That paradigm has the creators of a
system observe a new user trying to get things done (using any appropriate doc
that's available). Sometimes, the user appears to be trying things randomly.
Then the creators ask "why did you do that"—and they learn what faulty mental
model the user has formed. And then they try to find out how the new user came
to acquire that model. Often, the problem is that the doc (or the UI, when it's
meant to me self-evident) suffers from what Steven Pinker calls the "curse of
knowledge" in his book "The sense of style".
Anyway, with my experimentation and with the clues that you two (Adrian and
David) have given me, I arrived that the following practice. It seems to give
me what I want—i.e. a regime where ordinary new users that I create can operate
without me needing to change the "pg_hba.conf" file and where they can connect
to the one-and-only database that I intend and then perform exactly and only
the tasks that I intend—in other words a regime that honors the principle of
least privilege. (We've discussed the caveat that I can't prevent them from
reading all of the metadata across all databases earlier.)
* I use this bare bones "pg_hba.conf" file.
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust
* I say "\c postgres postgres" and use a script to strip the cluster done to
its bare minimum—in my case: the users "Bllewell" and postgres; and the
databases postgres, template0, and template1.
I say "revoke connect on database postgres from public" and "drop schema if
exists public". (And I drop any other schemas that might have been created in
the database postgres).
* When I create a database, I immediately drop its public schema and revoke
connect on it from public.
* When I create a user, I say NOREPLICATION and grant it CONNECT on just the
one database (it's always one) that I intend. However, when I create a
superuser, I cannot prevent it from connecting to *any* database (present or
future).
