Thread: [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-