0

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

Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
Eyedia Tech
  • 135
  • 1
  • 11
  • I can't reproduce this. I think either you executed more code than you show (creating the same tables in two different schemas) or your visualization tool (what is it?) is doing something strange, like displaying tables under "public" even when they are not there. – jjanes Jun 03 '20 at 19:09
  • From which tool is that screenshot? –  Jun 03 '20 at 19:19
  • @jjanes - hmm, looks like. I dont think my visualization tool is doing anything, but let me do some more research. – Eyedia Tech Jun 03 '20 at 20:15
  • @a_horse_with_no_name - Its RazorSQL, one of the best tool, supports many databases. – Eyedia Tech Jun 03 '20 at 20:16
  • If it doesn't handle schemas properly, I wouldn't call it "one of the best". Did you verify with e.g. `psql` that this is actually a problem in the database? –  Jun 03 '20 at 20:53

0 Answers0