agora inbox for pgsql-admin@postgresql.org  

help / color / mirror / Atom feed

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
Date: Mon, 10 Jun 2024 12:59:58 +0200
Message-ID: <ZmbcriGHk23NPkMN@mail.ibu.de> (raw)
In-Reply-To: <CA+wokJ8-z3sXJbrWHoxEVa2dV2Qsp2OyqyzdJBa3rvhWOEyXOg@mail.gmail.com>
References: <CA+wokJ8-z3sXJbrWHoxEVa2dV2Qsp2OyqyzdJBa3rvhWOEyXOg@mail.gmail.com>

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

view thread (11+ messages)  latest in thread

Message-ID: <ZmbcriGHk23NPkMN@mail.ibu.de>
Permalink:  ../ZmbcriGHk23NPkMN@mail.ibu.de/
Also on:    postgresql.org/message-id/ZmbcriGHk23NPkMN@mail.ibu.de

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: np@ibu.de, edwin.uy@gmail.com, pgsql-admin@lists.postgresql.org
  Subject: Re: GRANT CONNECT ON DATABASE
  In-Reply-To: <ZmbcriGHk23NPkMN@mail.ibu.de>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox