Subject: Re: Fwd: How to connect PostgreSQL (9.6.17) database from Macbook Pro
 Terminal using JumpCloud password?
To: Devraj B <drb3644@gmail.com>, pgsql-general@postgresql.org
References: 
 <CACL8z1T=2BAJBo7ZZfAp3OmbFn_+UW4thAmLq+SLuNQi_bUHoQ@mail.gmail.com>
 <CACL8z1SzCdYuf2=C-zLFH=z3BuX65iWnMspUOatyjTTDy9ozDw@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <0c66c0ed-087f-d60d-dccd-3e94b9cf227c@aklaver.com>
Date: Thu, 16 Jul 2020 12:55:20 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.10.0
MIME-Version: 1.0
In-Reply-To: 
 <CACL8z1SzCdYuf2=C-zLFH=z3BuX65iWnMspUOatyjTTDy9ozDw@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

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