Can't Acess DB even with sslmode=disable

[gcavalcante8808]

Hello My Friend,

I'm trying to test the pgdiff solution on a linux machine, but I'm receiving the following error:

2016/08/19 12:11:24 Error running querypq: SSL is not enabled on the server

But looks like the option is correctly set as stated:

-- schemaType: TABLE
-- db1: {dbmon IP1 5432 zabbix sslmode=disable}
-- db2: {dbmon IP2 5432 zabbix sslmode=disable}
-- Run the following SQL against db2:

Can you give me some advice on how can I get rid of this error (without the need to implement SSL on servers)?

[gcavalcante8808]

Looks Like the error is returned when the user doesn't provide a password too ...

[joncrlsn]

Gabriel, Since you wrote this, I've fixed my email to highlight issues people open on this project. I'm sorry you got lost in the shuffle. I'm hoping you got a solution figured out.

[mikeball]

I am having same issue on windows. Using postgres 9.6.1 installed locally attempting to compare 2 databases within the same local postgres instance.

Any workaround?

[joncrlsn]

Hopefully someone else will comment. I don't have any ideas right now.

[mikeball]

Ok, originally was attempting to run pgdiff from a script because there are so many options. In continuing to test, when pgdiff run directly on the command line, it works fine.

I tried a variety of methods of starting pgdiff from a script and all failed with message "SSL not enabled on server". Very weird, perhaps this is a situation where when run as script network permissions are blocked or something of that nature. This was on windows 10, I didn't test on linux.

As a suggestion that might avoid people putting the commands into scripts, it might be nice to have a configuration file with all the options in TOML format (https://github.com/pelletier/go-toml) and just pass a single path to config file as a single argument to pgdiff.

[hstadler]

Hi,

I encountered the same problem on Windows with PostgreSQL 9.6.1 having a database user that does not need a password (setting connection method to trust in pg_hba.conf).
For me I could solve this using a user with a (not empty) password.

Did not try it as I don't have a Go SDK installed, but maybe the password must not be part of the connection string, if it is empty?

[joncrlsn]

hstadler, Thanks for your insight. I'm at work, but I can check if the password is included or not when it is empty on my way home on the bus.

[hstadler]

Thx for your quick response!

This is only a guess, so not sure if this would solve the problem...

[cyberyoung]

I have same problem, and it'll be fixed when I change 'trust' to 'md5', and use password in the command line.

[vjpr]

Same problem macOS.

[ivoras]

Same, Linux.

[gustavosantos1990]

Same (Linux CentOS 7, PostgreSQL 9.6.6)...

-bash-4.2$ ./pgdiff ROLE -U user1 -H localhost -P 5432 -D myDb -S developmentSchema -O "sslmode=disable" -u user1 -h localhost -p 5432 -d myDb -s productionSchema -o "sslmode=disable"
-- schemaType: ROLE
-- db1: {myDb localhost 5432 user1 developmentSchema sslmode=disable}
-- db2: {myDb localhost 5432 user2 productionSchema sslmode=disable}
-- Run the following SQL against db2:
2018/01/04 13:33:55 Error running querypq: SSL is not enabled on the server

I'm trying to compare two schemas on a same database (production and development schemas), my connections at pg_hba.conf are set to trust.

[joncrlsn]

I'd love to figure out what people have done to make this work. I noticed this problem before (more than a year ago) but it was only temporary and I never figured out what was different.

[neoneye]

@joncrlsn it seems to be password related. When the password is an empty string then one error. When the password is a non-empty string then another error.

My localhost database doesn't have password.

PROMPT> pgdiff -H 127.0.0.1 -D test1  -O "sslmode=disable" -S myschema -P 5432 -W "x" -h 127.0.0.1 -d test1b -o "sslmode=disable" -s myschema -p 5432 -w "x" COLUMN
-- schemaType: COLUMN
-- db1: {test1 127.0.0.1 5432  x myschema sslmode=disable}
-- db2: {test1b 127.0.0.1 5432  x myschema sslmode=disable}
-- Run the following SQL against db2:
2018/04/01 13:10:34 Error running querypq: role "host=127.0.0.1" does not exist
PROMPT> pgdiff -H 127.0.0.1 -D test1  -O "sslmode=disable" -S myschema -P 5432 -W "" -h 127.0.0.1 -d test1b -o "sslmode=disable" -s myschema -p 5432 -w "" COLUMN
-- schemaType: COLUMN
-- db1: {test1 127.0.0.1 5432   myschema sslmode=disable}
-- db2: {test1b 127.0.0.1 5432   myschema sslmode=disable}
-- Run the following SQL against db2:
2018/04/01 13:17:48 Error running querypq: SSL is not enabled on the server
PROMPT>

[ibigpapa]

I can also confirm adding the -w and -W with the password fixes this.

[joncrlsn]

I might be able to fix this. When the connection string is built, it includes the 'password=' part of the string even if there is no password specified. It might be as simple as not including 'password=' in that case.

[ivoras]

I made it work with a patch to pgutil:

diff --git a/pgutil.go b/pgutil.go
index f25aa25..f0d6383 100644
--- a/pgutil.go
+++ b/pgutil.go
@@ -118,7 +118,12 @@ func (dbInfo *DbInfo) Populate() (verFlag, helpFlag bool) {

 // ConnectionString returns the string needed by the postgres driver library to connect
 func (dbInfo *DbInfo) ConnectionString() string {
-       connString := fmt.Sprintf("user=%s host=%s port=%v dbname=%s password=%s", dbInfo.DbUser, dbInfo.DbHost, dbInfo.DbPort, dbInfo.DbName, dbInfo.DbPass)
+       var connString string
+       if dbInfo.DbPass != "" {
+               connString = fmt.Sprintf("user=%s host=%s port=%v dbname=%s password=%s", dbInfo.DbUser, dbInfo.DbHost, dbInfo.DbPort, dbInfo.DbName, dbInfo.DbPass)
+       } else {
+               connString = fmt.Sprintf("user=%s host=%s port=%v dbname=%s", dbInfo.DbUser, dbInfo.DbHost, dbInfo.DbPort, dbInfo.DbName)
+       }
        if len(dbInfo.DbOptions) > 0 {
                connString += " " + dbInfo.DbOptions
        }

[tbarbugli]

same problem here; I don't use a password for my local env and this is not supported. Since pretty much all PG tools know how to use it wouldn't it much easier to accept a connection string for the two databases?

Any reason the patch from @ivoras was not applied?