adrian.klaver@xxxxxxxxxxx wrote:bryn@xxxxxxxxxxxx wrote: 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:
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). |
