[postgresql 9.2.3] schema and privileges

Hi ,

I'm new in postgresql but have been using mysql for years ,

I planning to migrate to postgresql, I currently playing around with the
schema and privileges and encounter strange problem

1. with postgres superuser , I create 2 database , db1 and db2
2. I revoke all on database db1/db2 from public; I also revoke public from
database postgres, template0, template1
3. I drop schema public on db1/db2.
4. i create role user1 and user2
5. when I try psql -h 192.168.56.200 -d db1 -U user1 , i get permission
denied which is correct
6. using postgres , alter role user1 login
7. when I try psql -h 192.168.56.200 -d db1 -U user1 , i get permission
denied for db1 which is correct , because I do not give privileges to
connect to the db1,
8. using postgres, grant connect on database db1 to user1.
9. when I try psql -h 192.168.56.200 -d db1 -U user1 , this time it let me
in

up until here I understand postgres login/connect security works.
the reason why I need to type -h 192.168.56.200 is because my plan is
superuser can only connect from local

here my setting in pg_hba.conf
host all postgres 0.0.0.0/0 reject
local all all peer
host all all 127.0.0.1/32 md5
host all all 192.168.56.0/24 md5

10. using postgres , \c db1 and then create schema sh1 . sh1 schema owner is
postgres
11. using postgres, revoke all privileges on schema sh1 from public.
12. using postgres, create sh1.tb1 (emp_no integer, emp_name varchar(100))
13. using user1, insert into sh1.tb1 values (1,'AAA'); . I get look like
syntax error

LINE 1: insert into sh1.tb1 values (1, 'AAA');
^
14. using postgres , grant usage on schema sh1 to user1;
15. using user1, insert into sh1.tb1 values (1,'AAA'); i get the error

db1=> insert into sh1.tb1 values (1, 'AAA');
ERROR: permission denied for relation tb1

my first question , why I not getting the error message?

16. user1 cannot insert data to tb1 or create new table.
17. using postgres, grant all privileges on schema sh1 to user1.
18. using user1, when i try insert into tb1, i get error.

db1=> insert into sh1.tb1 values (1, 'AAA');
ERROR: permission denied for relation tb1

19. using user1, create sh1.tb2 (emp_no integer, emp_name varchar(100)) . it
allow me and it allow me to insert data into tb2.

20. using user1, when i try drop table sh1.tb1 , i get this error

db1=> drop table sh1.tb1;
ERROR: must be owner of relation tb1

my second question. since I already grant all privileges on schema sh1 to
user1 , why can't user1 insert data into table sh1.tb1 ?
do I need to be owner of schema sh1 ? what should I do if I need to give the
schema to others roles?

rgds,
CCC

--
View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-9-2-3-schema-and-privileges-tp5748331.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.