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@xxxxxxxxxxx
