From: <Michael.Dietrich@swisscom.com>
To: <sfrost@snowman.net>
CC: <pgsql-admin@lists.postgresql.org>
Subject: AW: Connect to db denied for superuser inherited by group
Thread-Topic: Connect to db denied for superuser inherited by group
Thread-Index: AdO9KRETQiJ9PRhGQqaOXUjUgVW6cgAAZKvA///zOwD/+wNWQA==
Date: Tue, 20 Mar 2018 08:29:43 +0000
Message-ID: <214530c2d4cb438ca204bd2a3b5b4747@swisscom.com>
References: <b93d9993e79545ffaa54d440a6f4383f@swisscom.com>
 <9cd041b2b19143758e4f4d8a752421c5@swisscom.com>
 <20180316134234.GE2416@tamriel.snowman.net>
In-Reply-To: <20180316134234.GE2416@tamriel.snowman.net>
Accept-Language: de-CH, en-US
Content-Language: de-DE
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
MIME-Version: 1.0
Precedence: bulk

What I've done

CREATE ROLE superrole WITH  NOLOGIN  SUPERUSER ..;=09
CREATE USER nosuperuser WITH LOGIN NOSUPERUSER INHERIT ..;
GRANT superrole TO nosuperuser WITH ADMIN OPTION;
REVOKE CONNECT ON DATABASE nonpublicdb FROM public ;

psql -d postgres -U nosupuser

postgres=3D# set role superrole;

postgres=3D# \c nonpublicdb
FATAL:  permission denied for database " nonpublicdb "
DETAIL:  User does not have CONNECT privilege.

I expect that the connection to the database is allowed since a role with s=
uperuser is used.  (I don't' expect any difference between using a role (in=
cluding superuser) or a user (including superuser).)

Postgres Version: PostgreSQL 9.5.2 on x86_64-pc-linux-gnu, compiled by gcc =
(GCC) 4.8.5 20150623 (Red Hat 4.8.5-4), 64-bit

Regards --Michael


-----Urspr=FCngliche Nachricht-----
Von: Stephen Frost [mailto:sfrost@snowman.net]=20
Gesendet: Freitag, 16. M=E4rz 2018 14:43
An: Dietrich Michael, INI-ONE-CIS-GSV-MFS <Michael.Dietrich@swisscom.com>
Cc: pgsql-admin@lists.postgresql.org
Betreff: Re: Connect to db denied for superuser inherited by group

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=20
> (usage 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
> 2) granting connect to db for user
> 3) granting connect to db to group
> 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 a=
nd what you're expecting to work that apparently isn't.

Thanks!

Stephen