Обсуждение: [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.
Hello In PostgreSQL you need to grant privileges to databases, schemas and tables. In your example the table is owned by postgres and user1 has no rights in it. bye. -christian-
Hi, thanks for reply. from what i read in postgresql doc "By default, users cannot access any objects in schemas they do not own. To allow that, the owner of the schema must grant the USAGE privilege on the schema. To allow users to make use of the objects in the schema, additional privileges might need to be granted, as appropriate for the object." but i already did grant all privileges on schema sh1 to user1; so why user1 still unable to access. -- View this message in context: http://postgresql.1045698.n5.nabble.com/postgresql-9-2-3-schema-and-privileges-tp5748331p5748489.html Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
kobolds <dreammes2007@gmail.com> wrote: > "By default, users cannot access any objects in schemas they do > not own. To allow that, the owner of the schema must grant the > USAGE privilege on the schema. To allow users to make use of the > objects in the schema, additional privileges might need to be > granted, as appropriate for the object." > > but i already did grant all privileges on schema sh1 to user1; > > so why user1 still unable to access. Maybe a metaphor would help. By granting access to the database, you have given the user access to the building. Picture a building with one key for the front door, a different key for each floor, and a different key for each room on each floor. By granting access to the schema you have given the user a key to the floor. Granting access to the individual objects in the schema is like giving them keys to the individual rooms on the floor. You haven't done that yet. -- Kevin Grittner EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Read the last sentence again ,-) You gave rights to only the Schema not everything below it. bye, -christian-
Like Christian Hammers already mentioned, you need to grant access to each and every object in that schema to user1 for having access to it.
Here is an example of how those grants look like:GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE table_x TO user1;
GRANT SELECT, UPDATE ON TABLE table_x_table_x_id_seq TO user1;
Thanking you,
Prashanth Kumar Goriparthi
Prashanth Kumar Goriparthi
On Thu, Mar 14, 2013 at 2:08 PM, Christian Hammers <ch@lathspell.de> wrote:
Read the last sentence again ,-) You gave rights to only the Schema not everything below it.
bye,
-christian---
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice