On 5/16/22 22:21, Bryn Llewellyn wrote:
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?
CREATE DATABASE is an object creation command and what you are concerned
with is not that but the privileges associated with the object. As David
pointed out that is spelled out in the section on privileges. To me it
makes sense to aggregate privilege information rather then have to walk
through all the object creation sections.
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.
From here:
https://www.postgresql.org/docs/current/auth-pg-hba-conf.html
"The value replication specifies that the record matches if a physical
replication connection is requested, however, it doesn't match with
logical replication connections. Note that physical replication
connections do not specify any particular database whereas logical
replication connections do specify it."
Makes sense to me, it is a 'dummy' value for binary replication
connections as they do not actually connect to a database but to a cluster.
As to CREATE ROLE:
https://www.postgresql.org/docs/current/sql-createrole.html
REPLICATION
NOREPLICATION
" These clauses determine whether a role is a replication role. A
role must have this attribute (or be a superuser) in order to be able to
connect to the server in replication mode (physical or logical
replication) and in order to be able to create or drop replication
slots. A role having the REPLICATION attribute is a very highly
privileged role, and should only be used on roles actually used for
replication. If not specified, NOREPLICATION is the default. You must be
a superuser to create a new role having the REPLICATION attribute.
"
So the default of NOREPLICATION is mentioned.
replication(pg_hba.conf 'dummy' value) and REPLICATION/NOREPLICATION
roles are referring to different aspects of the same process. Honestly
I'm not seeing how this is any different from database postgres and role
postgres.
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
