Postgres tables created on a schema are visible under Public - How can I prevent that?

647 Views Asked by At

Been searching to get this answer, but guess keywords on my question are common and search engine returning different answers.

I have created a schema with few tables. Why my tables are visible on public Schema? though the tables are not accessible, but visible. Can I prevent that? Or does it mean all tables will automatically come under Public schema?

CREATE SCHEMA IF NOT EXISTS my_schema;
alter database zsdev02x set search_path = "$user", public, my_schema;

GRANT USAGE ON SCHEMA my_schema TO user_boss,user_standard;
GRANT CONNECT ON DATABASE zsdev02x TO user_boss,user_standard;


create table my_schema.lookup(test text);
insert into my_schema.lookup values('hello 1');
insert into my_schema.lookup values('hello 2');

create table my_schema.project(test text);
insert into my_schema.project values('hello 1');
insert into my_schema.project values('hello 2');

REVOKE ALL ON SCHEMA my_schema FROM PUBLIC;


GRANT SELECT ON ALL TABLES IN SCHEMA my_schema TO user_boss,user_standard;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA my_schema TO user_boss,user_standard;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA my_schema TO user_boss,user_standard;

GRANT SELECT, INSERT, UPDATE, DELETE ON my_schema.lookup TO user_boss;
GRANT SELECT, INSERT, UPDATE, DELETE ON my_schema.project TO user_standard;

enter image description here

0

There are 0 best solutions below