#36 Error on first postgres file access

Runtime (7)

I think this is the only part of the error file that is useful:

Aug 5, 2010 9:52:53 PM org.jclarion.clarion.ClarionSQLFile setError
WARNING: SQLException : ERROR: permission denied for relation ingredients 42501
org.postgresql.util.PSQLException: ERROR: permission denied for relation ingredients
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)

Happens on all three of the cookbook tables.

My db.properties file looks like this:


which is the only way I could get past the database page, but:

a) it should not be necessary to give the root user password to access the files (which are in piblic)
b) the password should not be stored in a plain text file
c) the java version seems (I think) to be looking for the tables in the cookbook database (which I do have set up properly), while for the clarion version, cookbook is the DSN name and it actually uses the data tables in the postgres/public schema (which also exist)
d) in the Database Manager: select cookbook screen, if I look in expert mode after giveing it the root user password, it shows two databases under the node: cookbook and phpwiki. It does not show the default postgres database.

Any ideas?




  • Andrew Barnham

    Andrew Barnham - 2010-08-06

    Sounds like something setup from default in your postgres installation.

    I need to see full stack trace to order to properly diagnose. I need to determine precisely what clarion2java was doing when the error occured. Was it trying to scan meta data? Was it reading data from an actual table?

    Please provide full stack trace.

    On points above

    a) root password is required to create new database only. It is not required to access existing database. in password manager can you provide details of a non privileged user in order to get user/db listing.

    b) I agree, yet most systems I have worked on do this, including systems that manage financial services accounts. Generally security vector analysis of DBs focuses on preventing network access to the DB and should not rely on password controls to prevent access. (i.e. correct setup of pg_hba.conf). Also passwords stored in encrypted form can be decrypted by someone smart enough and motivated enough, unless db.properties itself is protected with a passphrase which user has to type in everytime they start the software. It is an intractable problem associated with auto-attended software. Yet if you want some basic protection, I can easily include basic 3DES cipher on the db.properties file for password component at least.

    c) java logs in with the user/password you specify. I does a meta data scan for table with matching name. schema, i.e. "public." is actually stripped before the search.

    d) this is normal. It deliberately filters out 'postgres'. The system select databases by user. With query "SELECT usename FROM pg_user". The 'postgres' user is explicitly filtered out.

  • Andrew Barnham

    Andrew Barnham - 2010-08-22

    Can we close this ticket?

  • Jon Waterhouse

    Jon Waterhouse - 2010-08-25

    I think you can close the ticket, but I'm still sort of confused.

    When you specify "cookbook" in the owner string in the Clarion dictionary it will be looking for a DSN with that name and will take login credentials from there.

    I'm not sure that the owner string does anything at all in the java version?

    Anyway, after creating a postgres login role called cookbook, making cookbook the owner of the tables in the cookbook database, manually adding a
    line to the db.properties file, I managed to connect. In one of the earlier tests using the postgres user I must have been able to select the login role or database user (not sure which) that gave me the source.cookbook line. There was no node selectable below the "Postgres is available" in the Select database dialog now, though.

    All a bit painful, but more of a familiarity/understanding problem than anything else I think.



Log in to post a comment.