Re: Restricting user to see schema structure - Mailing list pgsql-general
| From | Bryn Llewellyn |
|---|---|
| Subject | Re: Restricting user to see schema structure |
| Date | |
| Msg-id | 766C91F2-6FF8-4D4B-94BB-39B3BAD2AFE8@yugabyte.com Whole thread Raw |
| In response to | Re: Restricting user to see schema structure (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Restricting user to see schema structure
Re: Restricting user to see schema structure |
| List | pgsql-general |
adrian.klaver@aklaver.com wrote:bryn@yugabyte.com wrote:adrian.klaver@aklaver.com wrote:bryn@yugabyte.com <mailto:bryn@yugabyte.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
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).
pgsql-general by date: