Обсуждение: GRANT CONNECT ON DATABASE
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.
Regards,
Ed
To grant the role login access, you need to modify the role to have the LOGIN attribute. You can do this by running the following command:
ALTER ROLE [blah] WITH LOGIN;
On Sun, 9 Jun 2024 at 20:09 Edwin UY <edwin.uy@gmail.com> 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.Regards,Ed
On Sunday, June 9, 2024, Edwin UY <edwin.uy@gmail.com> wrote:
A role was created as below:CREATE ROLE [blah] WITH NOLOGIN
As already noted this means “do not allow to login”.
ALTER ROLE [blah] WITH ENCRYPTED PASSWORD 'blahpassword' ;
If you want them to use a password this is also required in addition to the ability to login.
GRANT CONNECT ON DATABASE [blahdb] TO [blahuser] ;
Redundant with the default connect grant given to the public pseudo-role.
David J.
Thanks. Yeah, the ALTER fixed it.
I thought the CONNECT should have done the same thing.
Is the GRANT CONNECT not necessary then?
On Mon, Jun 10, 2024 at 12:14 PM soroush jurat <srsh.jurat@gmail.com> wrote:
To grant the role login access, you need to modify the role to have the LOGIN attribute. You can do this by running the following command:ALTER ROLE [blah] WITH LOGIN;On Sun, 9 Jun 2024 at 20:09 Edwin UY <edwin.uy@gmail.com> 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.Regards,Ed
On Sunday, June 9, 2024, Edwin UY <edwin.uy@gmail.com> wrote:
Thanks. Yeah, the ALTER fixed it.I thought the CONNECT should have done the same thing.Is the GRANT CONNECT not necessary then?
The grant is database scoped. Login is cluster-scoped.
To log into a database requires both levels of permission.
David J.
The GRANT CONNECT statement allows the role to connect to the database but does not give it login permission either.
So, you need both the ALTER ROLE and GRANT CONNECT statements to ensure the role can log in and connect to the database.
Jurat
On Sun, 9 Jun 2024 at 20:27 Edwin UY <edwin.uy@gmail.com> wrote:
Thanks. Yeah, the ALTER fixed it.I thought the CONNECT should have done the same thing.Is the GRANT CONNECT not necessary then?On Mon, Jun 10, 2024 at 12:14 PM soroush jurat <srsh.jurat@gmail.com> wrote:To grant the role login access, you need to modify the role to have the LOGIN attribute. You can do this by running the following command:ALTER ROLE [blah] WITH LOGIN;On Sun, 9 Jun 2024 at 20:09 Edwin UY <edwin.uy@gmail.com> 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.Regards,Ed
Thanks for the clarification.
On Mon, Jun 10, 2024 at 12:35 PM soroush jurat <srsh.jurat@gmail.com> wrote:
The GRANT CONNECT statement allows the role to connect to the database but does not give it login permission either.So, you need both the ALTER ROLE and GRANT CONNECT statements to ensure the role can log in and connect to the database.JuratOn Sun, 9 Jun 2024 at 20:27 Edwin UY <edwin.uy@gmail.com> wrote:Thanks. Yeah, the ALTER fixed it.I thought the CONNECT should have done the same thing.Is the GRANT CONNECT not necessary then?On Mon, Jun 10, 2024 at 12:14 PM soroush jurat <srsh.jurat@gmail.com> wrote:To grant the role login access, you need to modify the role to have the LOGIN attribute. You can do this by running the following command:ALTER ROLE [blah] WITH LOGIN;On Sun, 9 Jun 2024 at 20:09 Edwin UY <edwin.uy@gmail.com> 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.Regards,Ed
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
Don't think I can do this as this is AWS RDS?
On Mon, Jun 10, 2024 at 10:59 PM Norbert Poellmann <np@ibu.de> wrote:
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
We are using aws - rds.
All the discussions with respect to this thread are applicable to aws rds.
Thanks,
Sarwar
Sent from my Galaxy
-------- Original message --------
From: Edwin UY <edwin.uy@gmail.com>
Date: 6/10/24 8:18 AM (GMT-05:00)
To: Norbert Poellmann <np@ibu.de>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: GRANT CONNECT ON DATABASE
Don't think I can do this as this is AWS RDS?
On Mon, Jun 10, 2024 at 10:59 PM Norbert Poellmann <np@ibu.de> wrote:
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
OK, I'll check how/where to set what Norbert suggested on aws-rds.
Thanks.
On Tue, Jun 11, 2024 at 12:44 AM M Sarwar <sarwarmd02@outlook.com> wrote:
We are using aws - rds.All the discussions with respect to this thread are applicable to aws rds.Thanks,SarwarSent from my Galaxy-------- Original message --------From: Edwin UY <edwin.uy@gmail.com>Date: 6/10/24 8:18 AM (GMT-05:00)To: Norbert Poellmann <np@ibu.de>Subject: Re: GRANT CONNECT ON DATABASEDon't think I can do this as this is AWS RDS?On Mon, Jun 10, 2024 at 10:59 PM Norbert Poellmann <np@ibu.de> wrote: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