Role problem of PostgreSQL I want to create a role named cp with some defined privileges, then we will create some other roles which will be granted with cp role. I know Oracle can do this job. For examle grant resources to user_name; which means grant resources role to a user. I do the follwing test in PostgreSQL, but it does not work. Any body know this?

--create role cp and grant privilege
postgres=# create role cp login nosuperuser nocreatedb nocreaterole
           noinherit encrypted password 'cp';
CREATE ROLE

postgres=# grant connect on database skytf to cp;
GRANT

postgres=# \c skytf skytf;
You are now connected to database "skytf" as user "skytf".

skytf=&gt; grant usage on schema skytf to cp;
GRANT

skytf=&gt; grant  select on skytf.test_1 to cp;
GRANT


--create role cp_1, and grant cp role privilege to cp_1
skytf=&gt; \c postgres postgres
You are now connected to database "postgres" as user "postgres".

postgres=# create role cp_1 login nosuperuser nocreatedb nocreaterole
           noinherit encrypted password 'cp_1';
CREATE ROLE

skytf=# grant cp to cp_1;
GRANT ROLE


--test cp_1
skytf=# \c skytf cp_1;
You are now connected to database "skytf" as user "cp_1".

skytf=&gt; select * from skytf.test_1 limit 1;
ERROR:  permission denied for schema skytf
LINE 1: select * from skytf.test_1 limit 1;
You are explicitly setting the role to noinherit so you will need to use set rolecp before your select to use the permissions from role cp (but I'm guessing you probably just want to inherit)

From the docs:


  INHERIT
  NOINHERIT  
  
  These clauses determine whether a role "inherits"
  the privileges of roles it is a member of. A role with the INHERIT
  attribute can automatically use whatever database privileges have been
  granted to all roles it is directly or indirectly a member of. Without
  INHERIT, membership in another role only grants the ability to SET
  ROLE to that other role; the privileges of the other role are only
  available after having done so. If not specified, INHERIT is the
  default.