Flexible Web Services Info Tech Support

I was setting up a local environment to do some design work and since I set my Postgresql server to only accept certificates that were signed, I had to set this up on my development PC as well. I already had pgAdmin working with the certificates. The environment variables were correctly set, but I kept getting this error below.

Microsoft OLE DB Provider for ODBC Drivers

could not get home directory to locate root certificate file Either provide the file or change sslmode to disable server certificate verification.

What to do... 

Because the environment variables pointed to a location on my hard drive that IIS didn't have access to, I added those permissions. When I made changes on my 2003 server, I was able to start and stop the World Wide Web Publishing service and the environment variables were loaded. However, that did not work this time. I'm not sure what service needed to be restarted, but a reboot of the system properly loaded the variables and IIS stopped complaining about the location of the certificates.

I'm using Posgtresql 9.1 on Ubuntu 13.x.x 64-bit. I actually don't remember if I installed OpenSSL on it or not, but those are the packages I used during this install. It is one of the more difficult tasks I've had to complete. A few nice things before I go into the details of installing SSL for the ODBC client on a Server 2003 IIS server for use with my web pages.

I noticed that the Postgresql 9.1 and OpenSSL already enabled SSL by default without authentication. That means that the data being transmitted between client and server was encrypted, but it was not verifying the certificate as being one that the server has approved. i.e. no key or crt was being provided by the client at all. And the client crt was not signed by the servers CA.

By default, there is a crt and key symlinked to the Posgtresql data directory (which on Ubuntu is located in \var\lib\posgtresql\9.1\main). In the data directory they are called server.crt and server.key. There was no root.crt which as I understand it contains the CA (certificate authority). Without root.crt, any client that requests an SSL connection will get one. As soon as you put a root.crt file in there, the requesting connection will be checked against that file, however, only verify-ca and verify-full will fail if it doesn't match.

The most helpful link was this one on howtoforge.com. The other link I read only explained how to make the server certificates (info on  xtuple.org). But nicely, xtuple.org also explains how to setup SSL on windows installations of Postgresql. I thought there would be a problem in creating the server certificates with the command line arguments of the xtuple.org and the command line arguments on howtoforage as I did the server ones and the clients ones using the different sites respectively. 

The only problem I ran into while setting up the server with self-signed certificates was the crt and key files already existing in the data directory. These are symlinked and provided by OpenSSL; linked from snakeoil.crt and snakeoil.key in the OpenSSL directory. Copying over the new files would not overwrite the symlinked files. I had to delete the symlinks first. I probably should have unlinked them then copied in the new certs, but I didn't have any problems.

After creating the client crts per the howtoforge.com site, I didn't know where to place them on the client server. The default location to look is the <user>\appdata\postgresql directory, but the web user IUSR does not have a profile and there for no appdata directory. This site gave a little more info, but the individual was trying to get the windows cert manager to host the certificates so it gets a little confusing. After seeing the environment keys that needed to be set the documentation on the postgresql site made a little more sense.

Basically, you need to set these environment variables:

PGSSLCERT="C:\path\to\my.crt"
PGSSLROOTCERT="C:\path\to\myrootchain.crt"
PGSSLKEY="C:\path\to\my.key" <-- modified from the web site as capi is not part of OpenSSL.

That's done by right clicking my computer and selecting properties. Then open the advanced tab and at the bottom open the Environmental Variables button and add the variables to the system set in the bottom window. I replaced the capi:My name with postgresql.key in the same directory as the other path info. I was hoping it would take path and the ODBC client would search for they that worked, as this will create problems with connections to different replicated servers. I am guessing that I should be able to use the same keys and certs but add the CA authority to the root.crt file for any other servers that might be handling connections from this client, but I haven't tested that yet.

The last part is to change pg_hba.conf so that it only accepts SSL connections. To do that, edit the pg_hba.conf file which is located in /etc/posgtresql/9.1/main on Ubuntu. Add the line:

type database user address method options <-- don't add this line, for information purposes only

hostssl  all  all  0.0.0.0/0  cert clientcert=1

I also specified the addresses these connections can connect from. Technically though, if you are using a certificate and not a password, then it should be safe to expose access to any client that has a certificate.

There is also a suggestion that hostssl be put before host lines, but I'm not sure of the reason for that. I tested this with pgadmin. You can set the certificates for the database connection properties under the SSL tab. As successful connection shows in the properties for the connection SSL encrypted.

Packetmon by AnalogX claims the ability to monitor packets through RAW winsock connects (though Windows does place some restrictions on the data that can be monitored). It's a free program and still available as of this writing, though the author of the site does not appear to have made any updates since 2009.

When I installed packetmon, I immediately got an error message: Unable to open raw socket, packet monitoring canceled. After some search, I found that even as far back as Windows XP, RAW sockets needed to be enabled, but I could find no where that explained how to enable RAW sockets in Windows 7. Then I stumbled across a similar problem in Vista. The question was Can a regular user be allowed access to RAW sockets. The answer was no. In Windows 7, even if the user has an administrator account, programs not signed will not open in administrator mode. You have to right click the icon and select Run as Administrator, which gives the program access to admin level functions such as RAW sockets. So I tried it with Packetmon and it ran without a problem using RAW sockets.

I moved my sites to a 2003 server I've been using mostly for email and ran into a problem that the FLV file was not found. The site owner hasn't noticed it since the move 2 months ago, but I'm working on the SEO organic reports and found several 404 errors.

I use the flowplayer-3.1.5. The swf files the flowplayer uses to display the video worked fine, but the actual video kept showing: 200, Stream not found, NetStream.Play.StreamNotFound, clip: '[Clip] in the flowplayer screen. When I tried to access the file directly, I got a 404 error, file not found. When I researched that. I found this for Server 2003 issues of this type 

Basically, the steps to fix it, because windows 2003 doesn't know what to do with that file, you have to tell it, though I don't know why it doesn't just open as a text file or attempt to download it.

In IIS, open the properties for the IIS server

1. Select the MIME Types (button on my server)

2. Click New and enter the following information:

Associated Extension box: .FLV
MIME Type box:flv-application/octet-stream
Click OK.

3. Restart the World Wide Web Publishing service.
 

I just purchased the love factory plugin. When I bought, it was version 4.0.0 for Joomla 3.1. Right away, it was not ready for Postgresql. I made several modifications myself and got it installed. This is the normal double-quote and accent marks issue that I've been running into. While I got it installed in a couple of days, I will say the tech support from the guys at thefactory.ro revamped the entire plugin and made it Postgresql ready in an amazing 4 days.

I didn't agree with all the field mods and defaults they made in the process of converting, but I was very impressed, the system installed cleanly and all the items have been working so far.

Some issues that I wasn't aware of in the process of building my PHP server. I needed to install GD Library which is required  to create thumbnails from uploaded files. In fact, the photos wouldn't finish uploading. I got a bar across the screen showing it completed, but then it just stopped without an error message.

I'm on a Ubuntu server. Installation of the Library was easy: apt-get install php5-gd

I also needed the CURL PHP plug in for Google maps. that was also easy to install: apt-get install php5-curl.

At this point I am working through setting up the templates. I did a search on Google because there is not documentation sent with the module (note: love-factory is a module not a template. It plugs into whatever template you have running and I'm still using the one that initially came with Joomla without sample data). The documentation on the thefactory.ro's web site talks about using Smarty Templates. It states that it must be enabled in the settings General tab, however; there is no such setting in my version of this module. I'm still trying to find it.

This one is a big fail for use with Postgresql. On the initial install, none of the web page would load due to the use of double quotes. I was able to find and fix that. One odd thing. I wasn't able to find the location in the template I downloaded that was causing the problem. I can only assume that they downloaded additional files during the installation of the template that I uploaded. I had to modify the PHP files on the server itself and I could only find the problem but using the trace file after setting debug on. There was only one helper.PHP file in the template and no other zip files. That helper.PHP file didn't have the lines that were causing the problem.

Once the problem was fixed, the web site displayed fine, but there was no data. When it came to installing the sample data, there is just no hope if getting the site up and running. In fact, it was broken beyond the ability to uninstall the template. I had to scrape the entire site and reinstall Joomla from scratch.

Installing the sample data is done from the template manager in Joomla using a java script totally designed to function outside of the Joomla template world. You can install the sample data from a button which, from what I can tell only downloads a xml file. This means there is a library of files installed already which know how to process the xml file. Any attempt to fix the xml file only results in being overwritten when try to install again, since the file is downloaded... fresh with all the broken items in it.

This template suffers from interchanging using double quotes to delimit fields and using single quotes. It also uses the WHERE 1 clause. And the xml file queries all used accent marks to delimit field names which doesn't work in Postgresql.

I would like to add that this is a very sleek and nice template. I wish I could have used it. They claim the template is Joomla 2.5 and 3.1 ready, but i don't think you should be able to claim that if your template is not database independent.

This template can be found on Joomlashine's web site. I used the free template. If the free template doesn't work then the Pro template won't work either.

Well, I didn't mean to get into this, but every Joomla template addon I have checked so far is not ready for Postgresql. I not sure how this will develop but for now, I will make comment on each designer I test. I will also test various templates from each designer and if I find one that works, I'll list it. Otherwise, I will just list the failures.

My biggest issues today is the interchangeability of the designers work. One where clause with open with double quotes and use signal quotes to delimit the fields. This works fine with Postgresql. But in the same file, the next time I see a where clause, the start the where clause with a single quote and delimit the fields with double quotes. This does not work with Postgresql. It seems that it doesn't matter which way they do this and it will work fine with MySQL.

The other issue I just ran across is the use of WHERE 1 and a where clause. This makes not sense at all. One comment I read is that it makes it easier to build conditional where clause. I won't comment on that, but any where clause should always return a true or a false. I could understand doing this in the case mentioned above, but are we trying to save space? Could the clause just say WHERE true? or WHERE 1 = 1.

So far, any addon that has to create new tables has failed and I mean horribly failed. As in, no tables even come close to being built. Where I found this to be an issue is in the installer.xml file where they specify the database to build the tables from. Joomla doesn't care that you don't have the one they want to install. It just pretends that the job was done and the failures starting showing up with exceptions that say such and such a relationship doesn't exist. In the xml file, it specifies that the tables to be built and the language to use is MySQL or Postgresql where you can have both in the same file, but they point to different SQL files to actually build those files. Then you can build tables using specific syntax for that database manager. That is not the case in the normal run. Instead, the designers of Joomla built the query interface to be generic on the one hand and they will build the query (using JFactory and JDatabase), though they say queries can still be submitted via a string as it has worked the past, but I can almost guarantee that any designer who does that will be formatting the query for MySQL alone which is guaranteed to fail.

I will make more specific comments on other templates as I go along. 

My Carbonite backup software stopped backing up on July 1, 2013. Checking the settings in the Carbonite software, gave every indication it was working okay, except an odd backup counter that said my last backup was 49,675 days ago. If it wasn't for the notice from Carbonite that our backup hasn't been performed in 2 weeks, I would have never known to look into the problem. After I went into services and restarted Carbonite, I finally got the message that an update was available (actually required ... apparently).

Downloading and installing the update file only wrecked havoc. The service changed to disabled, the icon on the desktop and the in the service tray were both removed, but the installation kept breaking... Error extracting installation program, retry or cancel. It might have said unpacking instead of extracting. I couldn't uninstall Carbonite because there was no uninstall in add and remove programs. So, I went about it manually and couldn't remove the program files folder for Carbonite because the files were still being used. It turns out the program that was using the files was explorer.exe. So I killed the process to see if it would let go of the DLL file, but every time explorer started, those files started also.

Killing the explorer process and restarting it again, which you can do in the task manager by going to File, run and entering explorer in the process you want to run and it'll start again, is about as close to rebooting as you can get without rebooting. So, after I killed and restarted the explorer process, I tried to do the Carbonite install again and it worked this time.

A reboot would have worked just fine, but this runs on a shared machine and everyone would be using it until late at night. I didn't have the luxury of rebooting at the moment and had already missed a day. now the number of days since last backup correctly reflects 16 days or since July 1.

It's really annoying that calls are forwarded outside the United States for support. Many have experienced a language barrier in the worst case and best case an accent problem. Though the call center personnel are usually polite, they simply cannot help in many situations. The first time I discovered this with Network Solutions, the person who answered the phone had no accent at all, but then forwarded me to an "extension" and then I was talking to a person in the Philippines. My question... I needed to renew a domain. The account holder had passed away and I needed to get that domain activated again. She told me only the domain holder can reactivate the domain. This is actually a new concept for Network Solutions. It use to be that anyone could pay to have the domain registered. Now that apparently isn't so. I told her that I'm on the account as a technical contact and still she insisted that I could not renew the domain. She temporarily activated the domain which helped some. By activating the domain, I should have been able to get the password to the domain sent to the registered users account, but the the email to reset his password still had not come through. Very frustrating.

I ended up logging in with my own account and guess what... she was wrong. I was able to renew the domain as a technical contact.

This time, I called about an account where I received an email that I have been removed from an account as technical contact. The only problem is, I don't know which account. The email only give the account number, but not the domain. I called customer service and the friendly Philippine person who answered the phone could only tell me their systems were going through maintenance and to call back in 1 or 2 hours.

Network Solutions domains are the most expensive of any registrar. It doesn't seem that we are getting our money's worth.

I've been working on several projects involving Joomla, all of them with Postgresql as the database backend. The projects include a construction contractor web site, a blog, a dating web site and a model's portfolio web site. None of them are working.

It might not be surprising, but most of the Joomla component and template makers have no interest in addressing the database backend issue. So far, 100% say, use the MySQL backend. No one (and I mean NO ONE) has requested anything to do with the database backend. I've spent several days now working out issues with accent marks and double quotes, not to mention the database types that simply don't exist in Postgresql.

I stayed away from Joomla because of the MySQL backend. I didn't want to use it and I didn't want to learn it. When Joomla 2.5 came out addressing MS SQL and Postgresql, I thought I could finally dive in. I didn't do so until 3.1 thinking at least some providers would be addressing Postgresql by now. Not happening. 

First, I'm just going to say that HP's MFC (Multifunction) printers have way too much overhead. Taking almost an hour to install is just ridiculous.

I would like to complain about the network diagnostics, but the problem after much research as actually that the printer went sleep or turned itself off and I was installing the driver remotely. This is an MFC-8500 909a Officejet printer. Once the printer was back on, the installation almost finished... Almost. It got all the way to the end and decided that something was wrong and started uninstalling.... I wasn't going to let that happen, so I went into the task manager, found the running program and ended the task. Now there are many things that don't work correctly, but it can print and scan. I can't use the control center.

I believe the entire problem is that the printer keeps going off line and I'm researching that now.

As it turns out, that printer was just burned out. I believe the problem revolved mostly around the power supply, but I can't be sure. My recommendation was to get rid of it. The customer bought a new one before asking me (I wanted to suggest a different brand, i.e. Brother). But he got a newer model of the same printer. This one was a HP 8600. The old printer had been in service for about 6 years, so I guess we can't complain too much... 

I just completed a Joomla 3.1.1 fresh install and had some issues with installing to Postgresql. First, I was running my Postgres on a old Linux box Fedora 11 which is no longer supported. Joomla requires Postgres 8.3.14 or above and I was running 8.3.x not 14 on that box and couldn't install the Joomla files. So I am updating the server and the database, however; before that goes into production, I am testing so as to resolve problems before it goes live.

So my test environment is completely clean. Ubuntu 12.0.4T 32 bit and Postgresql 9.1 (I couldn't get 9.2 installed on that box, but 9.1 meets the requirements). 

At this point, I have managed to get a working version of Joomla installed on the Ubuntu box. In the production environment, the Postgres database will be on a different server from the web site. I have been trying to add templates and haven't been successful. I initially ignored the warning in the extension manager because until I tried to add a template, I had no problems at all. The error message i was getting was on on the *_associations table stating the field id was not type INT. That isn't a type in Postgresql that I know of. I found the query with debug turned on and cut and paste the query to see what info was actually being presented. Here is the query that was failing: 

SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name='*_associations'  - note: the * is a replacement for the actual value.
AND column_name='id'
AND data_type='INT'

I took out the data_type line. The resulting query is: id, integer. Postgres is very particular about case and length matching. The command to alter the table in 3.0.3.sql statement kept failing also. After reading up on others who had similar problems at this link, I was able to locate the file and removed it. After that, the databases all checked out, but I'm still unable to add the particular template I'm working with. 

The issue was actually the id numbering. After the fresh install of Joomla went in, the id in the template_styles table was left at 8 and the increment control file was also at 8. It should have incremented to 9 and added, but the error message in the Postgresql error log was that the unique id 8 already existed. I could see no reason why it would insist on that number. To fix the problem, I added a dummy record to that table manually and then deleted it. Adding the template from within Joomla worked with the new id being 10.