How to Prevent PostgreSQL User with Connect on DB and Usage Granted on 1 Schema from Viewing Metadata on ALL Schemas

  Kiến thức lập trình

I’ve granted these permissions only, but somehow when I add a connection to DBeaver, PGAdmin 4 or DataGrip, the IDEs can see the tables and columns in ALL other schemas.

CREATE USER user_name WITH PASSWORD '***';
grant connect on database database_name to user_name;
grant usage on schema ext to user_name;
grant select on ext.table_1 to user_name;
grant select on ext.table_2 to user_name;

This user should be able to see the 2 tables that it has select grants on. It should not be able to see the tables and columns in other tables, or other schemas.

I have run the below SQl to ensure the user is not part of any group role that might have access to other schemas

SELECT rolname FROM pg_roles WHERE oid IN (
    SELECT roleid FROM pg_auth_members WHERE member = (SELECT oid FROM pg_roles WHERE rolname = 'user_name')
);

LEAVE A COMMENT