Thread: Libpq functions & string to obtain connection parameters

Libpq functions & string to obtain connection parameters

From
arnaud gaboury
Date:
I start an app which use a libpq function to read connection params in a URI. Until now, the app was using a tcp connection to connect to postgresql with this URI:

postgres://mmuser:mmuser_password@10.10.10.1:5432/mattermost?sslmode=disable&connect_timeout=10

I want to switch and make the app connect to the unix socket instead. I am trying to figure out what would then be the correct URI, with no luck until now.

----------------------------
1- I modified the pg_hba.conf this way:

local   mattermost          mmuser                      peer       map=mattermap
-----------------
2- I modified pg_ident.conf this way:

   mattermap      mattermost              mmuser
----------------------------------    

3- I changed the URI this way:

postgres:/mmuser:PrOOt34/mattermost?connect_timeout=10

(NB: do I have to write in the URI the mmuser postgresql password, or Unix user password?)

 I recieve an error:
Failed to ping db err:pq: SSL is not enabled on the server

sslmode is ignored for Unix domain socket communication, so why this error?
I suspect my URI is not writen properly, but after many tries, I can't find a working one.
Thank you for help.



Re: Libpq functions & string to obtain connection parameters

From
Andreas Kretschmer
Date:
arnaud gaboury <arnaud.gaboury@gmail.com> wrote:

> I start an app which use a libpq function to read connection params in a URI.
> Until now, the app was using a tcp connection to connect to postgresql with
> this URI:
>
> postgres://mmuser:mmuser_password@10.10.10.1:5432/mattermost?sslmode=disable&
> connect_timeout=10
>
> I want to switch and make the app connect to the unix socket instead. I am
> trying to figure out what would then be the correct URI, with no luck until
> now.


The general form for a connection URI is:

postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]

The URI scheme designator can be either postgresql:// or postgres://.
Each of the URI parts is optional. The following examples illustrate
valid URI syntax uses:

postgresql:// postgresql://localhost postgresql://localhost:5433
postgresql://localhost/mydb postgresql://user@localhost
postgresql://user:secret@localhost
postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp

Components of the hierarchical part of the URI can also be given as
parameters. For example:

postgresql:///mydb?host=localhost&port=5433

Percent-encoding may be used to include symbols with special meaning in
any of the URI parts.

Any connection parameters not corresponding to key words listed in
Section 31.1.2 are ignored and a warning message about them is sent to
stderr.

For improved compatibility with JDBC connection URIs, instances of
parameter ssl=true are translated into sslmode=require.

The host part may be either host name or an IP address. To specify an
IPv6 host address, enclose it in square brackets:

postgresql://[2001:db8::1234]/database

The host component is interpreted as described for the parameter host.
In particular, a Unix-domain socket connection is chosen if the host
part is either empty or starts with a slash, otherwise a TCP/IP
connection is initiated. Note, however, that the slash is a reserved
character in the hierarchical part of the URI. So, to specify a
non-standard Unix-domain socket directory, either omit the host
specification in the URI and specify the host as a parameter, or
percent-encode the path in the host component of the URI:

postgresql:///dbname?host=/var/lib/postgresql

postgresql://%2Fvar%2Flib%2Fpostgresql/dbname



does that help?



(Copy&Paste from
http://stackoverflow.com/questions/27037990/connecting-to-postgres-via-database-url-and-unix-socket-in-rails)


Regards, Andreas Kretschmer
--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: Libpq functions & string to obtain connection parameters

From
arnaud gaboury
Date:


On Fri, Oct 7, 2016 at 3:15 PM Andreas Kretschmer <akretschmer@spamfence.net> wrote:
arnaud gaboury <arnaud.gaboury@gmail.com> wrote:

> I start an app which use a libpq function to read connection params in a URI.
> Until now, the app was using a tcp connection to connect to postgresql with
> this URI:
>
> postgres://mmuser:mmuser_password@10.10.10.1:5432/mattermost?sslmode=disable&
> connect_timeout=10
>
> I want to switch and make the app connect to the unix socket instead. I am
> trying to figure out what would then be the correct URI, with no luck until
> now.


The general form for a connection URI is:

postgresql://[user[:password]@][netloc][:port][/dbname][?param1=value1&...]

The URI scheme designator can be either postgresql:// or postgres://.
Each of the URI parts is optional. The following examples illustrate
valid URI syntax uses:

postgresql:// postgresql://localhost postgresql://localhost:5433
postgresql://localhost/mydb postgresql://user@localhost
postgresql://user:secret@localhost
postgresql://other@localhost/otherdb?connect_timeout=10&application_name=myapp

Components of the hierarchical part of the URI can also be given as
parameters. For example:

postgresql:///mydb?host=localhost&port=5433

Percent-encoding may be used to include symbols with special meaning in
any of the URI parts.

Any connection parameters not corresponding to key words listed in
Section 31.1.2 are ignored and a warning message about them is sent to
stderr.

For improved compatibility with JDBC connection URIs, instances of
parameter ssl=true are translated into sslmode=require.

The host part may be either host name or an IP address. To specify an
IPv6 host address, enclose it in square brackets:

postgresql://[2001:db8::1234]/database

The host component is interpreted as described for the parameter host.
In particular, a Unix-domain socket connection is chosen if the host
part is either empty or starts with a slash, otherwise a TCP/IP
connection is initiated. Note, however, that the slash is a reserved
character in the hierarchical part of the URI. So, to specify a
non-standard Unix-domain socket directory, either omit the host
specification in the URI and specify the host as a parameter, or
percent-encode the path in the host component of the URI:

postgresql:///dbname?host=/var/lib/postgresql

postgresql://%2Fvar%2Flib%2Fpostgresql/dbname



does that help?

Honestly not so much, as it is a nearly perfect Copy/past of postgresql official doc[0].
My postgresql socket is in the usual place:
 /run/postgresql/.s.PGSQL.5432=
So no need to specify it. But for the rest, I am still not sure of my URI


(Copy&Paste from
http://stackoverflow.com/questions/27037990/connecting-to-postgres-via-database-url-and-unix-socket-in-rails)


Regards, Andreas Kretschmer
--
Andreas Kretschmer
http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services



Re: Libpq functions & string to obtain connection parameters

From
Adrian Klaver
Date:
On 10/07/2016 06:42 AM, arnaud gaboury wrote:
>
>

>
>     does that help?
>
>
> Honestly not so much, as it is a nearly perfect Copy/past of postgresql
> official doc[0].
> My postgresql socket is in the usual place:
>  /run/postgresql/.s.PGSQL.5432=
> So no need to specify it. But for the rest, I am still not sure of my URI

Something like:

psql postgresql:///test?connect_timeout=10

 From ps:

4228 pts/1    S+     0:00 psql postgresql:///test?connect_timeout=10
4229 ?        Ss     0:00 postgres: aklaver test [local] idle


>
> [0]https://www.postgresql.org/docs/9.5/static/libpq-connect.html
>
>
>
>     (Copy&Paste from
>     http://stackoverflow.com/questions/27037990/connecting-to-postgres-via-database-url-and-unix-socket-in-rails)
>
>
>     Regards, Andreas Kretschmer
>     --
>     Andreas Kretschmer
>     http://www.2ndQuadrant.com/
>     PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Libpq functions & string to obtain connection parameters

From
Adrian Klaver
Date:
On 10/07/2016 06:42 AM, arnaud gaboury wrote:
>
>

>
> Honestly not so much, as it is a nearly perfect Copy/past of postgresql
> official doc[0].
> My postgresql socket is in the usual place:
>  /run/postgresql/.s.PGSQL.5432=
> So no need to specify it. But for the rest, I am still not sure of my URI
>
> [0]https://www.postgresql.org/docs/9.5/static/libpq-connect.html
>
>

Aah, just remembered you wanted username and password, so:

psql postgresql://aklaver:pwd@/test?connect_timeout=10

4408 pts/1    S+     0:00 psql postgresql://aklaver:pwd@/test?connect_timeout=10
4409 ?        Ss     0:00 postgres: aklaver test [local] idle


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Libpq functions & string to obtain connection parameters

From
arnaud gaboury
Date:


On Fri, Oct 7, 2016 at 3:56 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/07/2016 06:42 AM, arnaud gaboury wrote:
>
>

>
> Honestly not so much, as it is a nearly perfect Copy/past of postgresql
> official doc[0].
> My postgresql socket is in the usual place:
>  /run/postgresql/.s.PGSQL.5432=
> So no need to specify it. But for the rest, I am still not sure of my URI
>
> [0]https://www.postgresql.org/docs/9.5/static/libpq-connect.html
>
>

Aah, just remembered you wanted username and password, so:

psql postgresql://aklaver:pwd@/test?connect_timeout=10

It helps, but I must have something else misconfigured then:

-------------------------------------
 # su postgres
postgres@thetradinghall ➤➤ mattermost/bin % bash
bash-4.3$ psql postgresql:///mattermost?connect_timeout=10
psql (9.5.4)
Type "help" for help.

mattermost=# \q
=====> OK
-----------------------------------------
Now with the Unix user I want to use:

--------------------------------------------------------
% su mattermost
bash-4.3$ psql postgresql:///mattermost?connect_timeout=10
psql: FATAL:  no pg_hba.conf entry for host "[local]", user "mattermost", database "mattermost", SSL off
--------------------------------------------

As a reminder, I made an entry in pg_ident.conf this way:
 mattermap      mattermost              mmuser

and add this line in pg_hba.conf
local   mattermost          mmuser                      peer       map=mattermap

What is wromg in my  UNIX/postgresql user mapping?




4408 pts/1    S+     0:00 psql postgresql://aklaver:pwd@/test?connect_timeout=10
4409 ?        Ss     0:00 postgres: aklaver test [local] idle


--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Libpq functions & string to obtain connection parameters

From
Adrian Klaver
Date:
On 10/07/2016 07:28 AM, arnaud gaboury wrote:
>
>
> On Fri, Oct 7, 2016 at 3:56 PM Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 10/07/2016 06:42 AM, arnaud gaboury wrote:
>     >
>     >
>
>     >
>     > Honestly not so much, as it is a nearly perfect Copy/past of
>     postgresql
>     > official doc[0].
>     > My postgresql socket is in the usual place:
>     >  /run/postgresql/.s.PGSQL.5432=
>     > So no need to specify it. But for the rest, I am still not sure of
>     my URI
>     >
>     > [0]https://www.postgresql.org/docs/9.5/static/libpq-connect.html
>     >
>     >
>
>     Aah, just remembered you wanted username and password, so:
>
>     psql postgresql://aklaver:pwd@/test?connect_timeout=10
>
>
> It helps, but I must have something else misconfigured then:
>
> -------------------------------------
>  # su postgres
> postgres@thetradinghall ➤➤ mattermost/bin % bash
> bash-4.3$ psql postgresql:///mattermost?connect_timeout=10
> psql (9.5.4)
> Type "help" for help.
>
> mattermost=# \q
> =====> OK
> -----------------------------------------
> Now with the Unix user I want to use:
>
> --------------------------------------------------------
> % su mattermost
> bash-4.3$ psql postgresql:///mattermost?connect_timeout=10
> psql: FATAL:  no pg_hba.conf entry for host "[local]", user
> "mattermost", database "mattermost", SSL off
> --------------------------------------------
>
> As a reminder, I made an entry in pg_ident.conf this way:
>  mattermap      mattermost              mmuser
>
> and add this line in pg_hba.conf
> local   mattermost          mmuser                      peer
> map=mattermap
>
> What is wromg in my  UNIX/postgresql user mapping?
>

Did you reload the server after making the changes?:

https://www.postgresql.org/docs/9.6/static/auth-pg-hba-conf.html

The pg_hba.conf file is read on start-up and when the main server
process receives a SIGHUP signal. If you edit the file on an active
system, you will need to signal the postmaster (using pg_ctl reload or
kill -HUP) to make it re-read the file.

https://www.postgresql.org/docs/9.6/static/auth-username-maps.html

The pg_ident.conf file is read on start-up and when the main server
process receives a SIGHUP signal. If you edit the file on an active
system, you will need to signal the postmaster (using pg_ctl reload or
kill -HUP) to make it re-read the file.



--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Libpq functions & string to obtain connection parameters

From
arnaud gaboury
Date:


On Fri, Oct 7, 2016 at 4:42 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/07/2016 07:28 AM, arnaud gaboury wrote:
>
>
> On Fri, Oct 7, 2016 at 3:56 PM Adrian Klaver <adrian.klaver@aklaver.com
> <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 10/07/2016 06:42 AM, arnaud gaboury wrote:
>     >
>     >
>
>     >
>     > Honestly not so much, as it is a nearly perfect Copy/past of
>     postgresql
>     > official doc[0].
>     > My postgresql socket is in the usual place:
>     >  /run/postgresql/.s.PGSQL.5432=
>     > So no need to specify it. But for the rest, I am still not sure of
>     my URI
>     >
>     > [0]https://www.postgresql.org/docs/9.5/static/libpq-connect.html
>     >
>     >
>
>     Aah, just remembered you wanted username and password, so:
>
>     psql postgresql://aklaver:pwd@/test?connect_timeout=10
>
>
> It helps, but I must have something else misconfigured then:
>
> -------------------------------------
>  # su postgres
> postgres@thetradinghall ➤➤ mattermost/bin % bash
> bash-4.3$ psql postgresql:///mattermost?connect_timeout=10
> psql (9.5.4)
> Type "help" for help.
>
> mattermost=# \q
> =====> OK
> -----------------------------------------
> Now with the Unix user I want to use:
>
> --------------------------------------------------------
> % su mattermost
> bash-4.3$ psql postgresql:///mattermost?connect_timeout=10
> psql: FATAL:  no pg_hba.conf entry for host "[local]", user
> "mattermost", database "mattermost", SSL off
> --------------------------------------------
>
> As a reminder, I made an entry in pg_ident.conf this way:
>  mattermap      mattermost              mmuser
>
> and add this line in pg_hba.conf
> local   mattermost          mmuser                      peer
> map=mattermap
>
> What is wromg in my  UNIX/postgresql user mapping?
>

Did you reload the server after making the changes?:


GGGrrrhhhhh

-------------------------------
# systemctl restart postgresql
 # su mattermost
bash-4.3$ psql postgresql://mmuser:XXXYYYY@/mattermost?
psql (9.5.4)
Type "help" for help.

mattermost=> \q
-----------------------------------------

OK. Now I know unix user mattermost can connect to mattermost Db via unix socket. BUT when trying to write this in my URI:

postgresql://mmuser:MyPwd@/mattermost?connect_timeout=10

I get this from my app :
Failed to ping db err:pq: SSL is not enabled on the server

Is it normal to still have this error ? Adding  sslmode param will not change anything as it is ignored for Unix domain socket communication.



https://www.postgresql.org/docs/9.6/static/auth-pg-hba-conf.html

The pg_hba.conf file is read on start-up and when the main server
process receives a SIGHUP signal. If you edit the file on an active
system, you will need to signal the postmaster (using pg_ctl reload or
kill -HUP) to make it re-read the file.

https://www.postgresql.org/docs/9.6/static/auth-username-maps.html

The pg_ident.conf file is read on start-up and when the main server
process receives a SIGHUP signal. If you edit the file on an active
system, you will need to signal the postmaster (using pg_ctl reload or
kill -HUP) to make it re-read the file.



--
Adrian Klaver
adrian.klaver@aklaver.com

Re: Libpq functions & string to obtain connection parameters

From
Adrian Klaver
Date:
On 10/07/2016 07:58 AM, arnaud gaboury wrote:
>
>

>
> GGGrrrhhhhh
>
> -------------------------------
> # systemctl restart postgresql
>  # su mattermost
> bash-4.3$ psql postgresql://mmuser:XXXYYYY@/mattermost?
> psql (9.5.4)
> Type "help" for help.
>
> mattermost=> \q
> -----------------------------------------
>
> OK. Now I know unix user mattermost can connect to mattermost Db via
> unix socket. BUT when trying to write this in my URI:
>
> postgresql://mmuser:MyPwd@/mattermost?connect_timeout=10

So what app/framework/code is processing the above?

>
> I get this from my app :
> Failed to ping db err:pq: SSL is not enabled on the server

That does not look like something coming from Postgres, but from
whatever is initiating the connection.

>
> Is it normal to still have this error ? Adding  sslmode param will not
> change anything as it is ignored for Unix domain socket communication.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com


Re: Libpq functions & string to obtain connection parameters

From
Tom Lane
Date:
arnaud gaboury <arnaud.gaboury@gmail.com> writes:
> OK. Now I know unix user mattermost can connect to mattermost Db via unix
> socket. BUT when trying to write this in my URI:

> postgresql://mmuser:MyPwd@/mattermost?connect_timeout=10

> I get this from my app :
> Failed to ping db err:pq: SSL is not enabled on the server

> Is it normal to still have this error ? Adding  sslmode param will not
> change anything as it is ignored for Unix domain socket communication.

That message isn't coming out of libpq; I can't find any such string
in the community git repo.

            regards, tom lane


Re: Libpq functions & string to obtain connection parameters

From
arnaud gaboury
Date:


On Fri, Oct 7, 2016 at 5:01 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote:
On 10/07/2016 07:58 AM, arnaud gaboury wrote:
>
>

>
> GGGrrrhhhhh
>
> -------------------------------
> # systemctl restart postgresql
>  # su mattermost
> bash-4.3$ psql postgresql://mmuser:XXXYYYY@/mattermost?
> psql (9.5.4)
> Type "help" for help.
>
> mattermost=> \q
> -----------------------------------------
>
> OK. Now I know unix user mattermost can connect to mattermost Db via
> unix socket. BUT when trying to write this in my URI:
>
> postgresql://mmuser:MyPwd@/mattermost?connect_timeout=10

So what app/framework/code is processing the above?

>
> I get this from my app :
> Failed to ping db err:pq: SSL is not enabled on the server

That does not look like something coming from Postgres, but from
whatever is initiating the connection.

Yes. I will contact the app maintener and see how we can deal with this issue.

Thank you for your time and help. Have a good weekend.
 

>
> Is it normal to still have this error ? Adding  sslmode param will not
> change anything as it is ignored for Unix domain socket communication.
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com