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(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)lastname(at)staging] $
> >
> > Than I connect to root using my JumpCloud password
> > [firstname(dot)lastname(at)staging] $ sudo su -
> > [sudo] password for firstname.lastname:  < I enter my JumpCloud
> Password
> > here >
> > [root(at)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(at)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(at)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(at)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(at)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com