Thread: Fwd: How to connect PostgreSQL (9.6.17) database from Macbook Pro Terminal using JumpCloud password?

Fwd: How to connect PostgreSQL (9.6.17) database from Macbook Pro Terminal using JumpCloud password?

From

Devraj B

Date:

16 July 2020, 22:04:13

I login to my Macbook Pro using my JumpCloud password. My userid is firstname.lastname

/Users/firstname.lastname > whoami
firstname.lastname

After connecting to VPN, from my Macbook Pro Terminal, I connect to PostgreSQL server staging.mycompany.com having CentOS 6.10
/Users/firstname.lastname > ssh staging.mycompany.com
[firstname.lastname@staging] $

Than I connect to root using my JumpCloud password
[firstname.lastname@staging] $ sudo su -
[sudo] password for firstname.lastname: < I enter my JumpCloud Password here >
[root@staging] #

(Above, I could connect to root using my JumpCloud Password. I don't know what setup would have been done for that.)

Checked the O/S
[root@staging] # uname -a
Linux staging.local 2.6.32-696.16.1.el6.x86_64 #1 SMP Wed Nov 15 16:51:15 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
[root@staging] # lsb_release -d | awk -F"\t" '{print $2}'
CentOS release 6.10 (Final)

Connected to PostgreSQL (9.6.17) to create a user exactly SAME as of my Macbook Pro (or JumpCloud) username.
[root@staging] # psql -h localhost -U postgres
postgres=#

postgres=# select version();
PostgreSQL 9.6.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit

postgres=# create user "firstname.lastname";
CREATE ROLE
postgres=# grant connect on database my_test_db to "firstname.lastname";
GRANT
postgres=# \q

From server staging.mycompany.com I can connect to the database using user firstname.lastname successfully.
[root@staging] # psql -h localhost -U firstname.lastname -d my_test_db
my_test_db=>

But I want to setup JumpCloud or LDAP or any other authentication so that I can connect PostgreSQL user "firstname.lastname" directly from my Macbook Pro Terminal using my JumpCloud Password. Like:-

/Users/firstname.lastname > psql -h staging.mycompany.com -U firstname.lastname -d my_test_db
Password for user firstname.lastname: < I have Not given any password when I created PostgreSQL user firstname.lastname above and trying to authenticate here using my JumpCloud password, May be the same way as I could connect above to root. But when I enter my JumpCloud Password here there is an error. >
psql: error: could not connect to server: FATAL: password authentication failed for user "firstname.lastname"

What more to set up so that I can connect PostgreSQL (9.6.17) database using PostgreSQL user ( with the Same name as of my JumpCloud/ Macbook Pro user) directly from my Macbook Pro terminal using my JumpCloud Password authentication?
What do I need to set up in pg_hba.conf ?
Please suggest in detail.

Thks

Re: Fwd: How to connect PostgreSQL (9.6.17) database from Macbook Pro Terminal using JumpCloud password?

From

Adrian Klaver

Date:

16 July 2020, 22:55:20

On 7/16/20 12:04 PM, Devraj B wrote:
> I login to my Macbook Pro using my JumpCloud password. My userid is 
> firstname.lastname
> /Users/firstname.lastname > whoami
> firstname.lastname
> 
> After connecting to VPN, from my Macbook Pro Terminal, I connect to 
> PostgreSQL server staging.mycompany.com <http://staging.mycompany.com> 
> having CentOS 6.10
> /Users/firstname.lastname > ssh staging.mycompany.com 
> <http://staging.mycompany.com>
> [firstname.lastname@staging] $
> 
> Than I connect to root using my JumpCloud password
> [firstname.lastname@staging] $ sudo su -
> [sudo] password for firstname.lastname:  < I enter my JumpCloud Password 
> here >
> [root@staging] #
> (Above, I could connect to root using my JumpCloud Password. I don't 
> know what setup would have been done for that.)
> 
> Checked the O/S
> [root@staging] # uname -a
> Linux  staging.local 2.6.32-696.16.1.el6.x86_64 #1 SMP Wed Nov 15 
> 16:51:15 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
> [root@staging] # lsb_release -d | awk -F"\t" '{print $2}'
> CentOS release 6.10 (Final)
> 
> Connected to PostgreSQL (9.6.17) to create a user exactly SAME as of my 
> Macbook Pro (or JumpCloud) username.
> [root@staging] # psql -h localhost -U postgres
> postgres=#
> postgres=# select version();
> PostgreSQL 9.6.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 
> 20120313 (Red Hat 4.4.7-23), 64-bit
> postgres=# create user "firstname.lastname";
> CREATE ROLE
> postgres=# grant connect on database my_test_db to "firstname.lastname";
> GRANT
> postgres=# \q
> 
>  From server staging.mycompany.com <http://staging.mycompany.com> I can 
> connect to the database using user  firstname.lastname successfully.
> [root@staging] #  psql -h localhost -U firstname.lastname -d my_test_db
> my_test_db=>
> 
> But I want to setup JumpCloud or LDAP or any other authentication so 
> that I can connect PostgreSQL user  "firstname.lastname" directly from 
> my Macbook Pro Terminal using my JumpCloud Password. Like:-
> 
> /Users/firstname.lastname >  psql -h staging.mycompany.com 
> <http://staging.mycompany.com> -U firstname.lastname -d my_test_db
> Password for user firstname.lastname:  < I have Not given any password 
> when I created PostgreSQL user firstname.lastname above and trying to 
> authenticate here using my JumpCloud password, May be the same way as I 
> could connect above to root. But when I enter my JumpCloud Password here 
> there is an error. >
> psql: error: could not connect to server: FATAL:  password 
> authentication failed for user "firstname.lastname"

Well I would say the Postgres server has pg_hba.conf setup to require 
password authentication for host(ssl) connections. A good idea.

You need to back to part above where you shelled into psql prompt and
use ALTER ROLE:

https://www.postgresql.org/docs/12/sql-alterrole.html

and do

ALTER ROLE firstname.lastname WITH LOGIN PASSWORD 'the_password';

You need the LOGIN if you want that role to connect directly to database.

FYI, the above can be done as part of CREATE ROLE also:

https://www.postgresql.org/docs/12/sql-createrole.html

You probably want to read through above anyway to see what the options 
are and they do.

> 
> What more to set up so that I can connect PostgreSQL (9.6.17) database 
> using PostgreSQL user ( with the Same name as of my JumpCloud/ Macbook 
> Pro user) directly from my Macbook Pro terminal using my JumpCloud 
> Password authentication?
> What do I need to set up in pg_hba.conf ?
> Please suggest in detail.
> Thks
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Fwd: How to connect PostgreSQL (9.6.17) database from Macbook Pro Terminal using JumpCloud password?

From

Adrian Klaver

Date:

16 July 2020, 23:24:27

On 7/16/20 1:17 PM, Devraj B wrote:

Please reply to list also.
Ccing list.
> Thanks Adrian,
> 
> I had granted LOGIN to  PostgreSQL user  firstname.lastname but do Not 
> want to provide a database password,
> rather I wanna access the database using my JumpCloud password directly 
> from my Macbook Pro using LDAP authentication or any other authentication.
> 
> Please suggest me following:
> 
>> But I want to setup JumpCloud or LDAP or any other authentication so
>> that I can connect PostgreSQL user  "firstname.lastname" directly from
>> my Macbook Pro Terminal using my JumpCloud Password. Like:-

That's outside my knowledge, so someone else is going to have to jump in 
on this.

>>
>> /Users/firstname.lastname >  psql -h staging.mycompany.com <http://staging.mycompany.com/>
>  > <http://staging.mycompany.com <http://staging.mycompany.com/>> -U 
> firstname.lastname -d my_test_db
>> Password for user firstname.lastname:  < I have Not given any password
>> when I created PostgreSQL user firstname.lastname above and trying to
>> authenticate here using my JumpCloud password, May be the same way as I
>> could connect above to root. But when I enter my JumpCloud Password here
>> there is an error. >
>> psql: error: could not connect to server: FATAL:  password
>> authentication failed for user "firstname.lastname"
> 
> 
> On Thu, Jul 16, 2020 at 2:55 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 7/16/20 12:04 PM, Devraj B wrote:
>      > I login to my Macbook Pro using my JumpCloud password. My userid is
>      > firstname.lastname
>      > /Users/firstname.lastname > whoami
>      > firstname.lastname
>      >
>      > After connecting to VPN, from my Macbook Pro Terminal, I connect to
>      > PostgreSQL server staging.mycompany.com
>     <http://staging.mycompany.com> <http://staging.mycompany.com>
>      > having CentOS 6.10
>      > /Users/firstname.lastname > ssh staging.mycompany.com
>     <http://staging.mycompany.com>
>      > <http://staging.mycompany.com>
>      > [firstname.lastname@staging] $
>      >
>      > Than I connect to root using my JumpCloud password
>      > [firstname.lastname@staging] $ sudo su -
>      > [sudo] password for firstname.lastname:  < I enter my JumpCloud
>     Password
>      > here >
>      > [root@staging] #
>      > (Above, I could connect to root using my JumpCloud Password. I don't
>      > know what setup would have been done for that.)
>      >
>      > Checked the O/S
>      > [root@staging] # uname -a
>      > Linux  staging.local 2.6.32-696.16.1.el6.x86_64 #1 SMP Wed Nov 15
>      > 16:51:15 UTC 2017 x86_64 x86_64 x86_64 GNU/Linux
>      > [root@staging] # lsb_release -d | awk -F"\t" '{print $2}'
>      > CentOS release 6.10 (Final)
>      >
>      > Connected to PostgreSQL (9.6.17) to create a user exactly SAME as
>     of my
>      > Macbook Pro (or JumpCloud) username.
>      > [root@staging] # psql -h localhost -U postgres
>      > postgres=#
>      > postgres=# select version();
>      > PostgreSQL 9.6.17 on x86_64-pc-linux-gnu, compiled by gcc (GCC)
>     4.4.7
>      > 20120313 (Red Hat 4.4.7-23), 64-bit
>      > postgres=# create user "firstname.lastname";
>      > CREATE ROLE
>      > postgres=# grant connect on database my_test_db to
>     "firstname.lastname";
>      > GRANT
>      > postgres=# \q
>      >
>      >  From server staging.mycompany.com <http://staging.mycompany.com>
>     <http://staging.mycompany.com> I can
>      > connect to the database using user  firstname.lastname successfully.
>      > [root@staging] #  psql -h localhost -U firstname.lastname -d
>     my_test_db
>      > my_test_db=>
>      >
>      > But I want to setup JumpCloud or LDAP or any other authentication so
>      > that I can connect PostgreSQL user  "firstname.lastname" directly
>     from
>      > my Macbook Pro Terminal using my JumpCloud Password. Like:-
>      >
>      > /Users/firstname.lastname >  psql -h staging.mycompany.com
>     <http://staging.mycompany.com>
>      > <http://staging.mycompany.com> -U firstname.lastname -d my_test_db
>      > Password for user firstname.lastname:  < I have Not given any
>     password
>      > when I created PostgreSQL user firstname.lastname above and
>     trying to
>      > authenticate here using my JumpCloud password, May be the same
>     way as I
>      > could connect above to root. But when I enter my JumpCloud
>     Password here
>      > there is an error. >
>      > psql: error: could not connect to server: FATAL:  password
>      > authentication failed for user "firstname.lastname"
> 
>     Well I would say the Postgres server has pg_hba.conf setup to require
>     password authentication for host(ssl) connections. A good idea.
> 
>     You need to back to part above where you shelled into psql prompt and
>     use ALTER ROLE:
> 
>     https://www.postgresql.org/docs/12/sql-alterrole.html
> 
>     and do
> 
>     ALTER ROLE firstname.lastname WITH LOGIN PASSWORD 'the_password';
> 
>     You need the LOGIN if you want that role to connect directly to
>     database.
> 
>     FYI, the above can be done as part of CREATE ROLE also:
> 
>     https://www.postgresql.org/docs/12/sql-createrole.html
> 
>     You probably want to read through above anyway to see what the options
>     are and they do.
> 
>      >
>      > What more to set up so that I can connect PostgreSQL (9.6.17)
>     database
>      > using PostgreSQL user ( with the Same name as of my JumpCloud/
>     Macbook
>      > Pro user) directly from my Macbook Pro terminal using my JumpCloud
>      > Password authentication?
>      > What do I need to set up in pg_hba.conf ?
>      > Please suggest in detail.
>      > Thks
>      >
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

Re: Fwd: How to connect PostgreSQL (9.6.17) database from Macbook Pro Terminal using JumpCloud password?

From

"David G. Johnston"

Date:

16 July 2020, 23:43:24

On Thu, Jul 16, 2020 at 1:24 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:

On 7/16/20 1:17 PM, Devraj B wrote:

Please reply to list also.
Ccing list.
> Thanks Adrian,
>
> I had granted LOGIN to PostgreSQL user firstname.lastname but do Not
> want to provide a database password,
> rather I wanna access the database using my JumpCloud password directly
> from my Macbook Pro using LDAP authentication or any other authentication.
>
> Please suggest me following:
>
>> But I want to setup JumpCloud or LDAP or any other authentication so
>> that I can connect PostgreSQL user "firstname.lastname" directly from
>> my Macbook Pro Terminal using my JumpCloud Password. Like:-

That's outside my knowledge, so someone else is going to have to jump in
on this.

It is quite possible this can be done. I mean, there are 11 different authentication methods mentioned in the documentation. But the thing is, actually setting up the link between the external authentication method and PostgreSQL takes knowledge and skills outside the normal DBA's skillset and outside the responsibility of PostgreSQL. It may be that there is information on the Internet, or someone chimes in (though 2 business days without a response means I wouldn't hold my breath), but the documentation is only going to be of limited use - but is important none-the-less.

My suggestion to the OP is to just get password or other PostgreSQL-only authentication working, possibly with the help of PostgreSQL people, and then decide whether the convenience of what is basically single-sign-on is worth the learning curve.

David J.