agora inbox for pgsql-admin@postgresql.org
help / color / mirror / Atom feedGRANT CONNECT ON DATABASE
11+ messages / 5 participants
[nested] [flat]
* GRANT CONNECT ON DATABASE
@ 2024-06-10 00:09 Edwin UY <edwin.uy@gmail.com>
0 siblings, 3 replies; 11+ messages in thread
From: Edwin UY @ 2024-06-10 00:09 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: GRANT CONNECT ON DATABASE
@ 2024-06-10 00:14 soroush jurat <srsh.jurat@gmail.com>
parent: Edwin UY <edwin.uy@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: soroush jurat @ 2024-06-10 00:14 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: GRANT CONNECT ON DATABASE
@ 2024-06-10 00:27 David G. Johnston <david.g.johnston@gmail.com>
parent: Edwin UY <edwin.uy@gmail.com>
2 siblings, 0 replies; 11+ messages in thread
From: David G. Johnston @ 2024-06-10 00:27 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: GRANT CONNECT ON DATABASE
@ 2024-06-10 00:27 Edwin UY <edwin.uy@gmail.com>
parent: soroush jurat <srsh.jurat@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Edwin UY @ 2024-06-10 00:27 UTC (permalink / raw)
To: soroush jurat <srsh.jurat@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>>
>>
>>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: GRANT CONNECT ON DATABASE
@ 2024-06-10 00:30 David G. Johnston <david.g.johnston@gmail.com>
parent: Edwin UY <edwin.uy@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: David G. Johnston @ 2024-06-10 00:30 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; +Cc: soroush jurat <srsh.jurat@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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.
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: GRANT CONNECT ON DATABASE
@ 2024-06-10 00:34 soroush jurat <srsh.jurat@gmail.com>
parent: Edwin UY <edwin.uy@gmail.com>
1 sibling, 1 reply; 11+ messages in thread
From: soroush jurat @ 2024-06-10 00:34 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>>>
>>>
>>>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: GRANT CONNECT ON DATABASE
@ 2024-06-10 01:02 Edwin UY <edwin.uy@gmail.com>
parent: soroush jurat <srsh.jurat@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Edwin UY @ 2024-06-10 01:02 UTC (permalink / raw)
To: soroush jurat <srsh.jurat@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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.
>
> 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
>>>>
>>>>
>>>>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: GRANT CONNECT ON DATABASE
@ 2024-06-10 10:59 Norbert Poellmann <np@ibu.de>
parent: Edwin UY <edwin.uy@gmail.com>
2 siblings, 1 reply; 11+ messages in thread
From: Norbert Poellmann @ 2024-06-10 10:59 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: GRANT CONNECT ON DATABASE
@ 2024-06-10 12:17 Edwin UY <edwin.uy@gmail.com>
parent: Norbert Poellmann <np@ibu.de>
0 siblings, 1 reply; 11+ messages in thread
From: Edwin UY @ 2024-06-10 12:17 UTC (permalink / raw)
To: Norbert Poellmann <np@ibu.de>; +Cc: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* RE: GRANT CONNECT ON DATABASE
@ 2024-06-10 12:44 M Sarwar <sarwarmd02@outlook.com>
parent: Edwin UY <edwin.uy@gmail.com>
0 siblings, 1 reply; 11+ messages in thread
From: M Sarwar @ 2024-06-10 12:44 UTC (permalink / raw)
To: Edwin UY <edwin.uy@gmail.com>; Norbert Poellmann <np@ibu.de>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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<mailto: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<http://1.2.3.4/32; scram-sha-256
changes followed by a server reload.
cheers
Norbert Poellmann
>
> Regards,
> Ed
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: GRANT CONNECT ON DATABASE
@ 2024-06-10 13:41 Edwin UY <edwin.uy@gmail.com>
parent: M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 11+ messages in thread
From: Edwin UY @ 2024-06-10 13:41 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: Norbert Poellmann <np@ibu.de>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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,
> 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
>>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2024-06-10 13:41 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-10 00:09 GRANT CONNECT ON DATABASE Edwin UY <edwin.uy@gmail.com>
2024-06-10 00:14 ` soroush jurat <srsh.jurat@gmail.com>
2024-06-10 00:27 ` Edwin UY <edwin.uy@gmail.com>
2024-06-10 00:30 ` David G. Johnston <david.g.johnston@gmail.com>
2024-06-10 00:34 ` soroush jurat <srsh.jurat@gmail.com>
2024-06-10 01:02 ` Edwin UY <edwin.uy@gmail.com>
2024-06-10 00:27 ` David G. Johnston <david.g.johnston@gmail.com>
2024-06-10 10:59 ` Norbert Poellmann <np@ibu.de>
2024-06-10 12:17 ` Edwin UY <edwin.uy@gmail.com>
2024-06-10 12:44 ` M Sarwar <sarwarmd02@outlook.com>
2024-06-10 13:41 ` Edwin UY <edwin.uy@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox