Re: Restricting user to see schema structure

[Bryn Llewellyn <bryn@xxxxxxxxxxxx>]

neerajmr12219@xxxxxxxxx wrote:

bryn@xxxxxxxxxxxx wrote:

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>; 

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.