getting permission denied error for user2 while proper privilegesare present - Mailing list pgsql-sql

Hi there,

Please someone help me on the below. Unable to understand why user2 is not
having access.

adptesting=# select current_user;
 current_user
--------------
 postgres
(1 row)

adptesting=# \dn+
                             List of schemas
     Name     |  Owner   |  Access privileges   |      Description
--------------+----------+----------------------+------------------------
 public       | postgres | postgres=UC/postgres+| standard public schema
              |          | =C/postgres          |
 timedilation | learner  |                      |
(2 rows)

adptesting=# \dt
Did not find any relations.
adptesting=# \dt timedilation.erp
           List of relations
    Schema    | Name | Type  |  Owner
--------------+------+-------+---------
 timedilation | erp  | table | learner
(1 row)

adptesting=# \dt+ timedilation.erp
                       List of relations
    Schema    | Name | Type  |  Owner  |  Size   | Description
--------------+------+-------+---------+---------+-------------
 timedilation | erp  | table | learner | 0 bytes |
(1 row)

adptesting=# grant usage on schema timedilation to user1;
GRANT
adptesting=# grant select on table timedilation.erp to user1;
GRANT
adptesting=# \dn+
                             List of schemas
     Name     |  Owner   |  Access privileges   |      Description
--------------+----------+----------------------+------------------------
 public       | postgres | postgres=UC/postgres+| standard public schema
              |          | =C/postgres          |
 timedilation | learner  | learner=UC/learner  +|
              |          | user1=U/learner   |
(2 rows)

adptesting=# alter default privileges for role learner in schema
timedilation grant select on tables to user1;
ALTER DEFAULT PRIVILEGES
adptesting=# \ddp
              Default access privileges
  Owner  |    Schema    | Type  | Access privileges
---------+--------------+-------+--------------------
 learner | timedilation | table | user1=r/learner
(1 row)

adptesting=# set role user1;
SET
adptesting=> select count(*) from timedilation.erp ;
 count
-------
     0
(1 row)

adptesting=> \c
You are now connected to database "adptesting" as user "postgres".
adptesting=# create role user2 with login noinherit;
CREATE ROLE
adptesting=# grant user1 to user2;
GRANT ROLE
adptesting=# set role user2;
SET
adptesting=> select count(*) from timedilation.erp ;
*ERROR:  permission denied for schema timedilation
LINE 1: select count(*) from timedilation.erp ;*
                             ^
adptesting=>




-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-sql-f2142323.html