Received: from malur.postgresql.org ([217.196.149.56])
	by arkaria.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1sGckv-00FEEi-MF
	for pgsql-admin@arkaria.postgresql.org; Mon, 10 Jun 2024 11:00:06 +0000
Received: from localhost ([127.0.0.1] helo=malur.postgresql.org)
	by malur.postgresql.org with esmtp (Exim 4.94.2)
	(envelope-from <pgsql-admin-owner+archive@lists.postgresql.org>)
	id 1sGcku-00AnWg-9P
	for pgsql-admin@arkaria.postgresql.org; Mon, 10 Jun 2024 11:00:05 +0000
Received: from makus.postgresql.org ([2001:4800:3e1:1::229])
	by malur.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <np@ibu.de>)
	id 1sGckt-00AnWY-Uo
	for pgsql-admin@lists.postgresql.org; Mon, 10 Jun 2024 11:00:04 +0000
Received: from mail.ibu.de ([136.243.18.157] helo=ibu.de)
	by makus.postgresql.org with esmtps  (TLS1.3) tls
 TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384
	(Exim 4.94.2)
	(envelope-from <np@ibu.de>)
	id 1sGckr-000cOo-V8
	for pgsql-admin@lists.postgresql.org; Mon, 10 Jun 2024 11:00:03 +0000
Received: from mail.ibu.de (localhost [127.0.0.1])
	by ibu.de (8.17.1/8.17.1) with ESMTPS id 45AAxwln090190
	(version=TLSv1.3 cipher=TLS_AES_256_GCM_SHA384 bits=256 verify=NO);
	Mon, 10 Jun 2024 12:59:58 +0200 (CEST)
	(envelope-from np@ibu.de)
Received: (from np@localhost)
	by mail.ibu.de (8.17.1/8.17.1/Submit) id 45AAxwRM090189;
	Mon, 10 Jun 2024 12:59:58 +0200 (CEST)
	(envelope-from np@ibu.de)
X-Authentication-Warning: mail.your-server.de: np set sender to np@ibu.de
 using -f
Date: Mon, 10 Jun 2024 12:59:58 +0200
From: Norbert Poellmann <np@ibu.de>
To: Edwin UY <edwin.uy@gmail.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: GRANT CONNECT ON DATABASE
Message-ID: <ZmbcriGHk23NPkMN@mail.ibu.de>
References: 
 <CA+wokJ8-z3sXJbrWHoxEVa2dV2Qsp2OyqyzdJBa3rvhWOEyXOg@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: 
 <CA+wokJ8-z3sXJbrWHoxEVa2dV2Qsp2OyqyzdJBa3rvhWOEyXOg@mail.gmail.com>
"From: np@ibu.de"
List-Id: <pgsql-admin.lists.postgresql.org>
List-Help: <https://lists.postgresql.org/manage/>
List-Subscribe: <https://lists.postgresql.org/manage/>
List-Post: <mailto:pgsql-admin@lists.postgresql.org>
List-Owner: <mailto:pgsql-admin-owner@lists.postgresql.org>
List-Archive: <https://www.postgresql.org/list/pgsql-admin>
Archived-At: 
 <https://www.postgresql.org/message-id/ZmbcriGHk23NPkMN%40mail.ibu.de>
Precedence: bulk

On Mon, Jun 10, 2024 at 12:09:14PM +1200, Edwin UY wrote:
> Hi,
> 
> A role was created as below:
> CREATE ROLE [blah] WITH NOLOGIN NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE
> NOREPLICATION VALID UNTIL 'infinity';
> 
> Doesn't the following SQLs supposed to give the role login access?
> 
> ALTER ROLE [blah] WITH ENCRYPTED PASSWORD 'blahpassword' ;
> GRANT CONNECT ON DATABASE [blahdb] TO [blahuser] ;
> 
> We're trying to take the minimalist approach for a user access to have
> access to only the tables he has created and only to a specific database
> and schema.

Hi, 

I would suggest, additionally, the strictest doorman for your database 
is a record in ${data_directory}/pg_hba.conf, example:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
hostssl   blahdb       blahuser       1.2.3.4/32            scram-sha-256

changes followed by a server reload.

cheers
Norbert Poellmann

> 
> Regards,
> Ed