2

I am trying out Forena Reports and in connecting to a Postgres table I set the the Driver Type to Postgres Database and the URI to postgresql://username:[email protected]:5432/database.

The problem is when try to connect to the database username and password are correctly sent as the database log produces this message.

 FATAL:  database "database password=password" does not exist

I have created a temporary workaround by creating a user with the same name as the target database, by using the URI and omitting database at the end, as in

postgresql://database:[email protected]:5432/

What is the correct syntax for this URI postgresql://username:[email protected]:5432/database which allows a user without the same name as the database?

2
  • 2
    I didn't get the opportunity to try out Pierre's suggestions before it was put on hold a again. Can it be reopened for me to test them? This is definitely a Drupal question and the only way for me to check for a bug is try out the suggestions
    – vfclists
    Jun 11, 2016 at 4:28
  • 1
    now that your question is opened again (via the reopen-voting queue with 4 votes to reopen your question), I've undeleted my (updated) answer also (to make it visible again for users with <15K rep). I hope that my answer will help anybody who wonders "what to do" for defining a database connection to Postgres, for which one needs to know more then just what the uri syntax is like. That was also my major point why this question should not have "hold" status. Because of the 2nd hold, I assumed "the community considered my answer worthless", therefor I deleted it. Jun 11, 2016 at 13:54

2 Answers 2

0

I think your uri is not correct, read on for more details, and the correct syntax (near the end) ...

To use Forena for connecting to a database (such as Postgres, Oracle, MS SQL, etc), you need to perform the steps described in the tutorial that comes with Forena, i.e. within the Data Sources Settings). These are the supported methods for defining Data Sources:

  • use the Forena configuration data (located within admin/config/content/forena/data), and then use the "add data source" link (=admin/config/content/forena/data/add). There you can specify relevant options. After you select "Postgress Database" as the driver there, you'll be able to specify the remaining options within "Connection info", such as the uri.
  • use a settings.php file which allows for including PHP logic. Have a look at the settings.php files for each of the samples included in Forena's subfolder repos.
  • from within a custom module, use a forena_alter_repos hook (documented within Available Hooks in Forena's Development Guide).

Specific to Postgres, you can connect to it in one of two ways:

  1. Using the Postgres native driver, connect using a uri that has a connection string similar to what is described in http://php.net/manual/en/function.pg-connect.php .
  2. Using PDO Postgres driver use a syntax that is similar to http://php.net/manual/en/ref.pdo-pgsql.connection.php .

Looking at the (first) uri included in your question, I would specify a uri like so:

pgsql:host=127.0.0.1;port=5432;dbname=database;user=username;password=password

Disclosure: I'm a co-maintainer of Forena.

5
  • So I was right. The original comment I wrote, which you told me was, quote, "completely wrong", was correct, and this has come down to the OP not using a proper PDO connection string. "Your" new version of the answer is a copy/paste of the connection string I gave the OP originally. Think before you act next time, don't tell people they're wrong unless you actually have the knowledge to back it up.
    – Clive
    Jun 10, 2016 at 8:55
  • Sorry @clive, but in your deleted "answer" I still see "pgsql://database:[email protected]:5432/", which at least did not include anything for the "user" to be used ("without the same name as the database", as in the question. And my prior comments were deleted (not by me), so I'm not sure anymore what exactly I wrote there. But I seem to remember that my comment then was related to your "put on hold reason" then. Jun 10, 2016 at 9:16
  • You might not be aware but that connection string is a PDO thing, nothing to do with Drupal. The docs are here, this is an example: pgsql:host=localhost;port=5432;dbname=testdb;user=bruce;password=mypass
    – Clive
    Jun 10, 2016 at 9:16
  • That was my original comment. To which you replied: Moreover, I hate to correct you in your prior comment, but it is completely wrong (no worries: to error is human) Like I said, don't presume to "correct" people when you don't know yourself.
    – Clive
    Jun 10, 2016 at 9:17
  • That's right, delete the answer. Make sure no-one else sees the comments. Keep up the charade that you know what you're talking about.
    – Clive
    Jun 10, 2016 at 9:19
3

This worked for me:

host='somesite.comOR_IP' dbname='YourDBName' user='DBUsername'
2
  • I don't know why this was down voted. It works if this is Drupal 7 and is entered in the URI line for one of the data sources on this page: Home » Administration » Configuration » Content authoring » Forena Reports Jun 11, 2016 at 14:45
  • This is indeed the alternative way to make such connection to work, and a sample of such connection string as in bullet "1." of my answer (the uri near the end of my answer is a sample of bullet "2" of my answer). Jun 11, 2016 at 19:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.