Using PostGIS as databasePlatform

[capooti]

I am trying to use PostGIS as the GeoFence database.

This is what I have in my geofence-datasource-ovr.properties

geofenceVendorAdapter.databasePlatform=org.hibernatespatial.postgis.PostgisDialect
geofenceDataSource.driverClassName=org.postgresql.Driver
geofenceDataSource.url=jdbc:postgresql://localhost:5432/geofence
geofenceDataSource.username=me
geofenceDataSource.password=***
geofenceEntityManagerFactory.jpaPropertyMap[hibernate.default_schema]=gfschema

The user is the owner of the database and has full privileges. If I go to the GeoFence Data Rules page, the following error is raised:

... Caused by: org.postgresql.util.PSQLException: ERROR: relation "gfschema.gf_rule" does not exist ...

How can I generate the schema? thanks a lot!

[etj]

Try adding a line like this:

geofenceEntityManagerFactory.jpaPropertyMap[hibernate.hbm2ddl.auto]=update

in your property file. It's needed in the first run only, in order to create the schema, then you can either remove it, replace update with validate or leave it blank after the equal sign.

You may also want to refer to these lines

[capooti]

@etj I am now on GeoServer 2.12 (which comes with GeoNode master). Apparently in this case the geofence-datasource-ovr.properties is ignored, and GeoFence keeps using h2

[capooti]

@etj I cannot make GeoFence (the one integrated with GeoNode master) working with PostGIS.

This is the way I start the container:

-Dgeofence.dir=/home/vagrant/geonode.git/geoserver/data/geofence -Dgeofence-ovr=file:/home/vagrant/geonode.git/geoserver/data/geofence/geofence-datasource-ovr.properties

This is what I have in geofence-datasource-ovr.properties:

geofenceVendorAdapter.databasePlatform=org.hibernatespatial.postgis.PostgisDialect
geofenceDataSource.driverClassName=org.postgresql.Driver
geofenceDataSource.url=jdbc:postgresql://localhost:5432/geofence
geofenceDataSource.username=myuser
geofenceDataSource.password=***
geofenceEntityManagerFactory.jpaPropertyMap[hibernate.default_schema]=gfschema
geofenceEntityManagerFactory.jpaPropertyMap[hibernate.hbm2ddl.auto]=update

[ewsterrenburg]

@capooti I experience something similar (Geoserver 2.13 from Geonode). I have seen messages in the PostgreSQL log about trying to actually create the tables (yet at that time there was a non-existing schemaname in my config). Using create-drop, I have also once (of many attempts) seen messages about not being able to drop these tables (which makes sense, since they are not there). It seems to me as if the update / create-drop is not always triggered when it should be (i.e. is almost never triggered). I could however not find any logic when GeoFence did attempt to execute this process...

What I did observe:

• If I change the password to an incorrect one, I get consistently messages about not being able to connect to Postgres, changes in the GeoFence datastore properties are correctly picked up on a tomcat restart.
• If I create the schema manually using the SQL files supplied in the docs folder, GeoFence is working fine, geofence is able to connect to PostgreSQL and use this for its rules.

[NyakudyaA]

I have tried to use the PostGIS database as a store and nothing seems to be working.

Here is my config

And the jars

Error logs

23/08/2019 08:44:55Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'cachedRuleReader' defined in URL [jar:file:/usr/local/tomcat/webapps/geoserver/WEB-INF/lib/gs-geofence-2.13-20180610.113735-711.jar!/applicationContext.xml]: Cannot resolve reference to bean 'ruleReaderService' while setting bean property 'realRuleReaderService'; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'ruleReaderService' defined in URL [jar:file:/usr/local/tomcat/webapps/geoserver/WEB-INF/lib/geofence-services-impl-3.2-20180503.181323-25.jar!/applicationContext.xml]: Initialization of bean failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'adminRuleDAO': Injection of persistence dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'geofenceEntityManagerFactory' defined in URL [jar:file:/usr/local/tomcat/webapps/geoserver/WEB-INF/lib/geofence-persistence-3.2-20180503.181323-25.jar!/applicationContext-geofenceDatasource.xml]: Invocation of init method failed; nested exception is java.lang.NoClassDefFoundError: org/postgis/Point

[freddy34]

• GeoServer version: 2.16.1
• GeoFence version: 2.16.1
• hibernate-spatial: 1.1.3.1
• hibernate-spatial-postgis: 1.1.3.1 (dowload it from here after remove h2 one)
• jdbc-postgis: first run with version 1.3.3 and then with 2.3.0
• file geofence-datasource-ovr.properties written according your documentation here

What's wrong?

---

RESOLVED: geofence-datasource-ovr.properties was empty cause Docker COPY did not replace it with my local configuration entries