Postgresql – SymmetricDS fails to start for PostgreSQL 9.2

authenticationpostgresqlreplicationusers

I'm trying to get SymmetricDS up and running with PostgreSQL.
I've followed the tutorial (almost) exactly.
(I have not set up a separate node yet since, for my purposes, I need that separate node to be truly separate and on a different VM.)
Unfortunately, I am not able to get the database import step to function, as SymmetricDS will not connect to the database.

Following advice from Connecting to local instance of PostgreSQL from JDBC, I ensured that the second SLOC in pg_hba.conf was sensible;
PostgreSQL will (should) accept all connections made over TCP/IP over lo using client-side identification.
(The linked does call for md5 as opposed to ident; this has no visible effect and, according to the stack trace, is probably not what JDBC is expecting.)

I've ensured that symmetricds is a system user and is a user registered with PostgreSQL.
If memory serves, I did this with something like

ADD USER symmetricds WITH PASSWORD sds-pass;
GRANT ALL PRIVILEGES TO test FOR USER symmetricds;

(or something to this effect? I'm very new to databases.)
If I had to guess where I went wrong, it'd be here.

I edited the engine file corp-000.properties to use the PostgreSQL versions of connection details (the file comes set for MySQL) and
I filled in the appropriate credentials.


As far as I know, this is all that is needed to get SymmetricDS up and running (at least for the import step).
Obviously, something went wrong; a stack trace is included below.
What did I miss?

Shell log:

[root@dbrepa samples]# cat /var/lib/pgsql/9.2/data/pg_hba.conf
# PostgreSQL Client Authentication Configuration File
# ===================================================
# ...

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident
# Allow replication connections from localhost, by a user with the
# replication privilege.
#local   replication     postgres                                peer
#host    replication     postgres        127.0.0.1/32            ident
#host    replication     postgres        ::1/128                 ident


[root@dbrepa samples]# grep symmetricds /etc/passwd
symmetricds:x:501:501::/home/symmetricds:/bin/bash


[root@dbrepa samples]# service psql start
Starting psql service:                                     [  OK  ]


[root@dbrepa samples]# su - symmetricds
[symmetricds@dbrepa ~]$ psql test
psql (9.2.4)
Type "help" for help.

test=> \l
                                   List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |    Access privileges
-----------+----------+----------+-------------+-------------+--------------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres             +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres             +
           |          |          |             |             | postgres=CTc/postgres
 test      | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =Tc/postgres            +
           |          |          |             |             | postgres=CTc/postgres   +
           |          |          |             |             | symmetricds=CTc/postgres
(4 rows)

test=> \q
[symmetricds@dbrepa ~]$ exit
logout


[root@dbrepa samples]# cat ../engines/corp-000.properties
#
# Licensed to JumpMind Inc under one or more contributor
# ...
#
# You should have received a copy of the GNU General Public License,
# version 3.0 (GPLv3) along with this library; if not, see
# <http://www.gnu.org/licenses/>.
#
# ...
#

engine.name=corp-000

# The class name for the JDBC Driver
db.driver=org.postgresql.Driver

# The JDBC URL used to connect to the database
db.url=jdbc:postgresql://localhost/corp?stringtype=unspecified

# The user to login as who can create and update tables
db.user=symmetricds

# The password for the user to login as
db.password=sds-pass

registration.url=
sync.url=http://localhost:8080/sync/corp-000

# Do not change these for running the demo
group.id=corp
external.id=000

# Don't muddy the waters with purge logging
job.purge.period.time.ms=7200000

# This is how often the routing job will be run in milliseconds
job.routing.period.time.ms=5000
# This is how often the push job will be run.
job.push.period.time.ms=10000
# This is how often the pull job will be run.
job.pull.period.time.ms=10000


[root@dbrepa samples]# ../bin/dbimport --engine corp-000 --format XML create_sample.xml
Log output will be written to ../logs/symmetric.log
[] - AbstractCommandLauncher - Option: name=engine, value={corp-000}
[] - AbstractCommandLauncher - Option: name=format, value={XML}
-------------------------------------------------------------------------------
An exception occurred.  Please see the following for details:
-------------------------------------------------------------------------------
org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user "symmetricds"
        at org.postgresql.core.v3.ConnectionFactoryImpl.doAuthentication(ConnectionFactoryImpl.java:398)
        at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:173)
        at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:64)
        at org.postgresql.jdbc2.AbstractJdbc2Connection.<init>(AbstractJdbc2Connection.java:136)
        at org.postgresql.jdbc3.AbstractJdbc3Connection.<init>(AbstractJdbc3Connection.java:29)
        at org.postgresql.jdbc3g.AbstractJdbc3gConnection.<init>(AbstractJdbc3gConnection.java:21)
        at org.postgresql.jdbc4.AbstractJdbc4Connection.<init>(AbstractJdbc4Connection.java:31)
        at org.postgresql.jdbc4.Jdbc4Connection.<init>(Jdbc4Connection.java:24)
        at org.postgresql.Driver.makeConnection(Driver.java:393)
        at org.postgresql.Driver.connect(Driver.java:267)
        at org.apache.commons.dbcp.DriverConnectionFactory.createConnection(DriverConnectionFactory.java:38)
        at org.apache.commons.dbcp.PoolableConnectionFactory.makeObject(PoolableConnectionFactory.java:582)
        at org.apache.commons.dbcp.BasicDataSource.validateConnectionFactory(BasicDataSource.java:1556)
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1545)
 [wrapped] org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (FATAL: Ident authentication failed for user "symmetricds")
        at org.apache.commons.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
        at org.apache.commons.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
        at org.apache.commons.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)
        at org.jumpmind.symmetric.AbstractCommandLauncher.testConnection(AbstractCommandLauncher.java:325)
 [wrapped] java.lang.RuntimeException: org.apache.commons.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (FATAL: Ident authentication failed for user "symmetricds")
        at org.jumpmind.symmetric.AbstractCommandLauncher.testConnection(AbstractCommandLauncher.java:329)
        at org.jumpmind.symmetric.AbstractCommandLauncher.getDatabasePlatform(AbstractCommandLauncher.java:336)
        at org.jumpmind.symmetric.DbImportCommand.executeWithOptions(DbImportCommand.java:113)
        at org.jumpmind.symmetric.AbstractCommandLauncher.execute(AbstractCommandLauncher.java:130)
        at org.jumpmind.symmetric.DbImportCommand.main(DbImportCommand.java:72)
-------------------------------------------------------------------------------

Some more resources as I find them in my sea of open tabs:

Best Answer

org.postgresql.util.PSQLException: FATAL: Ident authentication failed for user "symmetricds"

You're using ident authentication and it's failing because:

  • identd isn't running; or
  • the running user's ID doens't match the database user they're trying to connect to

In general, ident is a bit of a weird and legacy choice. I'd recommend just using md5 authentication with a password.