Postgresql – Role problem of PostgreSQL

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=> 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;

Best Answer

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.

Related Question