Re: Restricting user to see schema structure - Mailing list pgsql-general

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@aklaver.com