Role problem of PostgreSQL

Question

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;

Answer 1

You are explicitly setting the role to noinherit so you will need to use set role cp 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.

• TML: ach! Ya beat me! :D

Answer 2

You're specifically asking pg to NOT apply the roles of 'cp' to user 'cp_1'.

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.

Try something like this:

create role cp_1 login nosuperuser nocreatedb nocreaterole inherit encrypted password 'cp_1' in role cp

• Jack Douglas: I'd call that a tie :)
• francs: thank you very much , both the answer is right , but I just can choose a one