agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedgetting permission denied error for user2 while proper privileges are present
5+ messages / 2 participants
[nested] [flat]
* getting permission denied error for user2 while proper privileges are present
@ 2019-05-18 04:13 rajan <vgmonnet@gmail.com>
2019-05-18 04:46 ` Re: getting permission denied error for user2 while proper privileges are present Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: rajan @ 2019-05-18 04:13 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: getting permission denied error for user2 while proper privileges are present
2019-05-18 04:13 getting permission denied error for user2 while proper privileges are present rajan <vgmonnet@gmail.com>
@ 2019-05-18 04:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-05-18 05:50 ` Re: getting permission denied error for user2 while proper privileges are present rajan <vgmonnet@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2019-05-18 04:46 UTC (permalink / raw)
To: rajan <vgmonnet@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: getting permission denied error for user2 while proper privileges are present
2019-05-18 04:13 getting permission denied error for user2 while proper privileges are present rajan <vgmonnet@gmail.com>
2019-05-18 04:46 ` Re: getting permission denied error for user2 while proper privileges are present Tom Lane <tgl@sss.pgh.pa.us>
@ 2019-05-18 05:50 ` rajan <vgmonnet@gmail.com>
2019-05-18 15:07 ` Re: getting permission denied error for user2 while proper privileges are present Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: rajan @ 2019-05-18 05:50 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: getting permission denied error for user2 while proper privileges are present
2019-05-18 04:13 getting permission denied error for user2 while proper privileges are present rajan <vgmonnet@gmail.com>
2019-05-18 04:46 ` Re: getting permission denied error for user2 while proper privileges are present Tom Lane <tgl@sss.pgh.pa.us>
2019-05-18 05:50 ` Re: getting permission denied error for user2 while proper privileges are present rajan <vgmonnet@gmail.com>
@ 2019-05-18 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-05-18 16:35 ` Re: getting permission denied error for user2 while proper privileges are present rajan <vgmonnet@gmail.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2019-05-18 15:07 UTC (permalink / raw)
To: rajan <vgmonnet@gmail.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: getting permission denied error for user2 while proper privileges are present
2019-05-18 04:13 getting permission denied error for user2 while proper privileges are present rajan <vgmonnet@gmail.com>
2019-05-18 04:46 ` Re: getting permission denied error for user2 while proper privileges are present Tom Lane <tgl@sss.pgh.pa.us>
2019-05-18 05:50 ` Re: getting permission denied error for user2 while proper privileges are present rajan <vgmonnet@gmail.com>
2019-05-18 15:07 ` Re: getting permission denied error for user2 while proper privileges are present Tom Lane <tgl@sss.pgh.pa.us>
@ 2019-05-18 16:35 ` rajan <vgmonnet@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: rajan @ 2019-05-18 16:35 UTC (permalink / raw)
To: pgsql-sql
Ok, Thanks.
-----
--
Thanks,
Rajan.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-sql-f2142323.html
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2019-05-18 16:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2019-05-18 04:13 getting permission denied error for user2 while proper privileges are present rajan <vgmonnet@gmail.com>
2019-05-18 04:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-05-18 05:50 ` rajan <vgmonnet@gmail.com>
2019-05-18 15:07 ` Tom Lane <tgl@sss.pgh.pa.us>
2019-05-18 16:35 ` rajan <vgmonnet@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox