What exactly do you mean by "have created a new user and granted connection access to database"? As I understand it, there's no such thing. I mentioned a simple test in my earlier email that showed that any user (with no schema of its own and no granted privileges) can connect to any database—and see the full metadata account of all its content. I'm teaching myself to live with this.
What I meant by 'created a new user' is that I have used the following commands.
CREATE USER <user_name> WITH ENCRYPTED PASSWORD '<password>'; GRANT CONNECT ON DATABASE <database> TO <user_name>; GRANT USAGE ON SCHEMA <schema> TO <user_name>;
In a freshly initialized cluster the newly created user will have already inherited the necessary connect privilege making this one redundant (though that property can be considered useful here).
Ah… there's obviously something I don't understand here. I've never used "grant connect on database"—and not experience an ensuing problem. I just tried this:
\c postgres postgres create user joe login password 'joe'; revoke connect on database postgres from joe; \c postgres joe
It all ran without error. (I've turned off the password challenge in my MacBook PG cluster.) I don't have a mental model that accommodates this. And a quick skim for this variant in the "GRANT" section of the PG doc didn't (immediately) help me. I obviously need to do more study. I'll shut up until I have.
It's because joe hasn't been granted connect on the database directly. It is through their mandatory membership in the PUBLIC pseudo-role, and that role's default grant of connect on all newly created databases, that joe receives permission to connect. You can only revoke what has been explicitly granted so one must revoke the grant from PUBLIC - then re-assign it to the subset of roles that require it.
