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

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