[postgresql 9.2.3] schema and privileges
От | kobolds |
---|---|
Тема | [postgresql 9.2.3] schema and privileges |
Дата | |
Msg-id | 1363178650306-5748331.post@n5.nabble.com обсуждение исходный текст |
Список | pgsql-novice |
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.
В списке pgsql-novice по дате отправления: