Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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;
share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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.

share|improve this answer
    
ach! Ya beat me! :D –  TML Nov 28 '11 at 6:18
add comment

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

share|improve this answer
    
I'd call that a tie :) –  Jack Douglas Nov 28 '11 at 6:22
    
thank you very much , both the answer is right , but I just can choose a one –  francs Nov 28 '11 at 7:15
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.