Thread: getting permission denied error for user2 while proper privilegesare present

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

rajan <vgmonnet@gmail.com> writes:
> Please someone help me on the below. Unable to understand why user2 is not
> having access.

Because you said "noinherit".  Per the CREATE ROLE man page:

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.

            regards, tom lane

Hi,

Thanks for the response. One more question.
Please notice the lines where GRANT and ALTER DEFAULT PRIVILEGES are
executed. Even though I am executing those statements as postgres user, when
viewing the privileges the grantor is always the learner user. How is that
possible?



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

rajan <vgmonnet@gmail.com> writes:
> Please notice the lines where GRANT and ALTER DEFAULT PRIVILEGES are
> executed. Even though I am executing those statements as postgres user, when
> viewing the privileges the grantor is always the learner user. How is that
> possible?

The GRANT page says

    If a superuser chooses to issue a GRANT or REVOKE command, the command
    is performed as though it were issued by the owner of the affected
    object. In particular, privileges granted via such a command will
    appear to have been granted by the object owner. (For role membership,
    the membership appears to have been granted by the containing role
    itself.)

Essentially, the superuser is making use of her privilege to become
the object's owning role.

            regards, tom lane

Ok, Thanks.



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