Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hRqir-00067C-Dm for pgsql-sql@arkaria.postgresql.org; Sat, 18 May 2019 04:13:25 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hRqip-00033R-9H for pgsql-sql@arkaria.postgresql.org; Sat, 18 May 2019 04:13:23 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1hRqip-00033K-1O for pgsql-sql@lists.postgresql.org; Sat, 18 May 2019 04:13:23 +0000 Received: from n3.nabble.com ([162.255.23.22]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1hRqil-0004II-EY for pgsql-sql@postgresql.org; Sat, 18 May 2019 04:13:22 +0000 Received: from n3.nabble.com (localhost [127.0.0.1]) by n3.nabble.com (Postfix) with ESMTP id E9F9214196D10 for ; Fri, 17 May 2019 21:13:16 -0700 (MST) Date: Fri, 17 May 2019 21:13:16 -0700 (MST) From: rajan To: pgsql-sql@postgresql.org Message-ID: <1558152796956-0.post@n3.nabble.com> Subject: getting permission denied error for user2 while proper privileges are present MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk 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