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=> grant usage on schema skytf to cp;
GRANT
skytf=> grant select on skytf.test_1 to cp;
GRANT
--create role cp_1, and grant cp role privilege to cp_1
skytf=> \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=> select * from skytf.test_1 limit 1;
ERROR: permission denied for schema skytf
LINE 1: select * from skytf.test_1 limit 1;