Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1ewpd8-0007ao-3F for pgsql-admin@arkaria.postgresql.org; Fri, 16 Mar 2018 13:42:46 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ewpd6-0000eU-Br for pgsql-admin@arkaria.postgresql.org; Fri, 16 Mar 2018 13:42:44 +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_SHA384:256) (Exim 4.89) (envelope-from ) id 1ewpd6-0000eL-4b for pgsql-admin@lists.postgresql.org; Fri, 16 Mar 2018 13:42:44 +0000 Received: from tamriel.snowman.net ([2001:470:e38f::11]) by magus.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1ewpcx-0001lh-QZ for pgsql-admin@lists.postgresql.org; Fri, 16 Mar 2018 13:42:42 +0000 Received: by tamriel.snowman.net (Postfix, from userid 1000) id 877CC5F79C; Fri, 16 Mar 2018 09:42:34 -0400 (EDT) Date: Fri, 16 Mar 2018 09:42:34 -0400 From: Stephen Frost To: Michael.Dietrich@swisscom.com Cc: pgsql-admin@lists.postgresql.org Subject: Re: Connect to db denied for superuser inherited by group Message-ID: <20180316134234.GE2416@tamriel.snowman.net> References: <9cd041b2b19143758e4f4d8a752421c5@swisscom.com> MIME-Version: 1.0 Content-Type: multipart/signed; micalg=pgp-sha512; protocol="application/pgp-signature"; boundary="ix1cewRGZ1GgNwAa" Content-Disposition: inline In-Reply-To: <9cd041b2b19143758e4f4d8a752421c5@swisscom.com> User-Agent: Mutt/1.5.24 (2015-08-30) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --ix1cewRGZ1GgNwAa Content-Type: text/plain; charset=us-ascii Content-Disposition: inline Content-Transfer-Encoding: quoted-printable Greetings, * Michael.Dietrich@swisscom.com (Michael.Dietrich@swisscom.com) wrote: > Unexpected behavior when trying to connect to a database. Facts: > 1) The privilege to connect to the database was revoked from public. > 2) User without superuser privileges uses a role with superuser rights (u= sage confirmed with SHOW current_role.) Please provide more details about what this step #2 actually means. > 3) Unecpected Message: FATAL: permission denied for database "db" DETAIL:= User does not have CONNECT privilege. Details about exactly what you're doing to connect here would be helpful. > After granting one of the listed privileges it is working as expected. > 1) granting superuser to user=20 > 2) granting connect to db for user > 3) granting connect to db to group=20 > 3) granting connect to db to public >=20 > What am I missing? --Michael CONNECT privileges are needed to be able to connect to the database, so it's not surprising that you needed to GRANT them to the user after REVOKE'ing them from PUBLIC. What's not clear is what you're actually trying to do and what you're expecting to work that apparently isn't. Thanks! Stephen --ix1cewRGZ1GgNwAa Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- Version: GnuPG v1 iQIcBAEBCgAGBQJaq8nKAAoJEO1sijiDR2RVN14P/2717AycVUYPfm8WNfMgRe7N 6wHdAWSiaK5mluVLdFUpuYr/4I8gIwjeLk7MAByUY1ynDwAise9vmn2cFRm78Dp0 SBgMfYM5FOtzRH07kOY+BEOFSk9StIrUl+cAIW5JxKV8gTe5wANKaL8zSAstf53Y U6XjJ09YyBeTYjSwf+yg6PrDC8UmwIwKfffR2Ej5YcpKnDNgPfthGWxDDzYeQViP iNx27K1dovGUBmrY5Jy+DF7ReMH0yMOF/M0BonFMsGDof+UTfkgreymCDfi/VSSR paBlnMhJzhK9uyYIpPDqkgKFtHRVRwDGUlETQ4jAEHQZeub0x8dY9EpvlZ5J6P4l oVlg/wm2sOVZ9Qn/KTKb1U9qI9x3Xvk8vWaI7soTBug4ZXPuIq830Loz+Opx94+q Kp6KaRnw/mjH/mCpLaiuW1P9wmcjtP2nlYQJFfFT3eODcVzQWXwFlFvY6i5fFvtP Y2HO47NSMsxP7GMgM9qMgV+vInQeIShJ9cfKYdAFwtRjYTOF4zqZsIZK684aH8cH t/42jKtGxvvXLzDDMSX/O/aAGKrCHfvYwvFTOCz1vJCfrGzADkTUmCbMAwalXDMt od/Jy9Ma0956bkmhCyz9SLCrc75mhmono7cihVwTn0MvcIixDG1CZcm2S3vT5cih mIzPYBZZXkr1qpHxtdNY =hu8P -----END PGP SIGNATURE----- --ix1cewRGZ1GgNwAa--