Adding users to a PostgreSQL server

Recently one of my colleagues faced a simple and yet interesting problem. He was trying to install a new PostgreSQL database (version 9.0.1-1). The problem was that the original Postgresql DBMS was intended to be installed as part of another software package and the installer for that software package automatically did all the pre-configuration necessary in terms of users, roles, database et al. However he already had a debug environment setup on his machine and wanted to install the DBMS as a separate package.

Consider for instance that the user that the specific PostgreSQL instance that the software package installed is ‘username‘ with password ‘password‘. The database that the software package created had this user as the owner and required the same credentials for the application to access this database. Now, the PostgreSQL installer does not give an option to configure additional users (aside from the default ‘postgres’ account) during installation. This was a source of confusion since after installation, there was no option from pgAdminIII to configure any new users! We tried to create a new entry in the pgpass.conf file by opening it via pgAdminIII and adding a new entry with ‘username’ and ‘password’ as the credentials. This, of course, did not work. This was fine but the irksome bit was that the error message was quite misleading, as can be seen below:

So I did a bit of research into the PostgreSQL documentation and found that I could add a new user only via a tool called ‘createuser.exe‘ (on Windows). The documentation did not specify that clearly and only mentioned ‘CREATEUSER’ as a command with some options that could be used to assign different privileges to the new role. My first thought was that this could be achieved on the psql.exe command shell but that was a total dud! Quite expectedly so indeed. It was only when I checked the contents of the bin folder that I discovered the ‘createuser.exe‘ tool! Long story short, here’s how you add a new user with superuser privileges to your PostgreSQL server (note: this was checked on PostgreSQL 9.0.1-1 but this should work with at least as far back as PostgreSQL 8.3):

with the options explained as follows:

-d informs PostgreSQL to allow the new user to be able to create new databases
-s informs PostgreSQL to assign superuser privileges to the new user
-P intimates PostgreSQL that the new user should have a password, which will be supplied by the user

The following options are for the PostgreSQL server itself:

-h provides the hostname of the PostgreSQL server
-U provides the username of the PostgreSQL server (default user ‘postgres’ is used here)
-p supplies the port on which the PostgreSQL server is listening
-W intimates PostgreSQL that it must use the supplied password (for the default user ‘postgres’ to connect to the server)

Et voila! The database is now installed beautifully and works like a charm:

And the properties of the server (and correspondingly the database within it) also shows the new user as the owner of the server instance:

The takeaway from this exercise is that the PostgreSQL documentation sucks but given enough time, patience and mental acuity, we can yet tame this free and solid quality beast.

Adding users to a PostgreSQL server

Nifty little Christmas easter egg in VLC Player

I have been using VLC player for years now and I just noticed today (Christmas Day) that the VLC icon was a little different! Apparently VLC player has a nice easter egg in that the VLC icon at the top left corner of the player changes to the original one capped by a cute Santa hat!

How very germane of VLC! 😀
(I am using VLC player 0.9.8a Grishenko).

Nifty little Christmas easter egg in VLC Player

Changing the encoding in Eclipse to UTF-8 – howto

Eclipse on Windows chugs along fine with its default encoding of Cp1252 (Cp1252 is basically a superset of the ISO-8859-1 encoding scheme, read more here) till we reach the world of unicode. And then it crashes and burns – horribly. Arguably, the most popular and useful encoding in the programming world today is the UTF-8 encoding scheme well at least in the Web world. It is still baffling as to why Eclipse continues to have Cp1252 as its default encoding mechanism and make changing to UTF-8 such a pain! In fact there is even a bug with a very long interesting discussion on the same – Eclipse default encoding bug.

Even though the discussion in the bug history argues about ‘compatibility’ issues being the main concern dissuading the Eclipse team from shifting to UTF-8 as the default, most of us would still like to work in UTF-8. Changing to UTF-8 encoding is simple in Eclipse but is extremely buggy and the random unpredictable behavior can be really irksome. In most cases, one or more of the following changes should suffice. If you are still getting an error while changing the encoding scheme, the best bet would be to close the workspace and restart the IDE! Seriously, no kidding.

I am using Eclipse Galileo as the base IDE but these steps should work on most version of Eclipse with minor differences (by the way, I am using the JAVA EE version).

For all the various options mentioned below, the first step is to traverse to the settings page as

Windows -> Preferences

1. Changing the entire workspace’s encoding scheme:

2. Changing the Text Editor’s encoding scheme (also works for the Java editor):

3. Changing the CSS editor’s encoding scheme:

4. Changing the HTML editor’s encoding scheme:

5. Changing the JSP editor’s encoding scheme:

And there you go! Now you can start coding in your favorite encoding scheme!

Changing the encoding in Eclipse to UTF-8 – howto

Wow, I really must be out of touch – wordpress source code highlighting feature

I just got done with my previous blog and then I realized, as always, WordPress seemed not to like my source code and was chunking off arbitrary portions off of it! A little bit of Googling gave me the solution – simple and elegant and supports a wide range of languages. So now I get not just automatic HTML escaping but also syntax highlighting and a nifty format for the source code display! Here is the link with the details – WordPress Source Code Highlighting support.

I guess I need to make out some time to update my previous blogs now. It’s well worth it.

Wow, I really must be out of touch – wordpress source code highlighting feature

Java’s HttpURLConnection is broken!

Continuing from the last post, I was experimenting on some prototypes for attaching documents (PDF, CSV, XML etc.) using the JavaMail 1.4 API. The basic modus operandi of the whole process is quite simple:

1. Obtain the URL of the document as served out by Tomcat server.

2. Use this URL as the DataSource for the JavaMail API.

3. Munge the URL for the file name and insert the URL as a link in the message body and, finally

4. Attach the document (if below the user-configured attachment limit) to the mail and e-mail it to the recipients.

All in all pretty simple, right? Wrong. The code was working smoothly until the document was either UTF-8 encoded and/or the document name contained white-spaces as well as a smorgasbord of other characters (as allowed by the Windows file system) such as the tilde character(~) or the backquote character (`) or even something such as the addition symbol (+). This caused the whole mechanism to come crashing down…and bad! This interesting development led me to a whole afternoon of researching and hacking which was quite tiring but enjoyable. I can honestly say that I learnt more about encoding and the way Java’s API’s conform (or not) to the standards, through this simple error, than I could have by devouring the RFC’s and API documentation!

So, first off, the problem description –

1. The URI specification (which is basically a superset of the URL specification) is drafted in a variety of RFC’s – RFC 2396, 3986, 5785 et al. However, most modern browsers conform to the new IRI (Internationalized Resource Identifiers) as specified by RFC 3987. This means that a whole variety of previously disallowed characters are now kosher for URI’s/URL’s/IRI’s. However, Java has failed to keep up with the trend and their IRI support is broken. See. This opens a whole can of worms.

2. In my code, the main “context path” of the URL that my module receives for a specified document is basically localized/internationalized. For instance, consider as URL of the form:

http://localhost:9999/examples/servlets/`~!@^()_-+={[ }] ‘,. test 1 サンプルファイル 999.CSV.PDF

In the URL above, the context portion is “http://localhost:9999/examples/servlets/”. So I just had to resolve the encoding problems with the file name portion (i.e., “`~!@^()_-+={[ }] ‘,. test 1 サンプルファイル 999.CSV.PDF”). In this respect, a variety of tools are purportedly provided by the JDK suite itself – URLEncoder, URI and the URL class. For instance, the following were the options that I tried, in order:

a). Use the java.net.URLEncoder class’ encode(String s, String enc) method to try and encode the complete URL. Bad idea. For the sample URL above, it returns a wholly encoded form as follows:

http%3A%2F%2Flocalhost%3A9999%2Fexamples%2Fservlets%2F%60%7E%21%40%5E%28%29_-%2B%3D%7B%5B+++%7D%5D+%27%2C.+test+1+%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB++++++999.CSV.PDF

Whoa! This cannot be parsed by either the browser or by JavaMail’s URLDataSource handler (or even by JDK’s HttpURLConnection/HttpsURLConnection classes). Trashed.

b). Okay, so now I just try and encode the filename portion (since the context path is guaranteed by contract to be properly encoded). The output?

http://localhost:9999/examples/servlets/%60%7E%21%40%5E%28%29_-%2B%3D%7B%5B+++%7D%5D+%27%2C.+test+1+%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB++++++999.CSV.PDF

Hmmm. Slightly better but still completely non-functional as before. At this point, I decided to dig into the JDK source code (using the OpenJDK Java 7 codebase). There was the problem! It was basically encoding whitespaces as ‘+’ instead of encoding it into ‘%20’ as expected. Plus a whole bunch of other characters were being ignored when they should have been encoded. At this point I was almost decided in my mind to implement my own UTF-8 encoder.

c). Upon suggestion by an acquaintance, I decided to try the java.net.URI class constructor to generate a proper URI and then invoke the toURL() method to obtain a properly encoded URL. Sounds exquisitely standards-conformant but this totally bombed when it encountered UTF-8 characters (though it handled whitespace just fine). Firefox Opera and Opera seemed to like this just fine.The URL generated was not usable by JavaMail’s URLDataSource handler (or HttpURLConnection/HttpsURLConnection classes). Plus on some e-mail clients (Gmail), the links were pretty much broken and the user would have to manually copy-and-paste the link into the browser to access the document.

The solution?

I implemented a simple UTF-8 encoder which basically encodes every non-alphanumeric character into UTF-8, shown belowpackage com.z0ltan.mail.encoder;

package com.z0ltan.mail.encoder;

import java.nio.charset.Charset;

public class MyUTFEncoder {
	private static final String HEX_CHARS = "0123456789ABCDEF";

	public static String encode(String data) {

		if (data != null) {
			StringBuffer buffer = new StringBuffer();

			byte[] dataBytes = MyUTFEncoder.getUTFBytes(data);

			for (int i = 0; i < dataBytes.length; i++) {
				char c = (char) dataBytes[i];

				if ((c >= 'a' && c <= 'z')
					|| (c >= 'A' && c <= 'Z')
					|| (c >= '0' && c <= '9')) {
					buffer.append(c);
				} else {
					buffer
					 .append('%');
					buffer
					 .append(HEX_CHARS.charAt((c & 0xF0) >> 4));
					buffer
					 .append(HEX_CHARS.charAt((c & 0x0F)));
				}
			}

			return buffer.toString();
		}
		return null;
	}

	private static byte[] getUTFBytes(String data) {
		if (data != null) {
			return data.getBytes(Charset.forName("UTF-8"));
		}
		return null;
	}
}

The code is pretty self-explanatory. The URL’s generated may not be pretty to look at but they are equally liked by the browser, Java’s URLConnection API’s, JavaMail’s URLDataSource handler as well as E-mail clients such as MS Outlook and Gmail (no more broken links, yay!).

For the representative URL shown at the beginning of this blog, the output is as follows:

http://localhost:9999/examples/servlets/%60%7E%21%40%5E%28%29%5F%2D%2B%3D%7B%5B%20%20%20%7D%5D%20%27%2C%2E%20test%201%20%E3%82%B5%E3%83%B3%E3%83%97%E3%83%AB%E3%83%95%E3%82%A1%E3%82%A4%E3%83%AB%20%20%20%20%20%20999%2ECSV%2EPDF

Yes, pretty ugly, but in most browsers that I tested it on, the browser automatically displays it in a more user-friendly format (with no encoding). The best part? This URL works well with JDK, JavaMail, e-mail clients as well as the browser! Plus, to rid it of some ugliness, I actually insert the attached document’s name without the encoding – such as `~!@^()_-+={[ }] ‘,. test 1 サンプルファイル 999.CSV.PDF. This saves the user some confusion about the veracity of the attached document (Note: The file name may still be encoded into weird forms by the e-mail client, if not properly configured but that is beyond the purview of my application).

All in all, an afternoon well spent. Slainte!

PostScript

JDK 1.6, JavaMail 1.4, Firefox 8.0.1, IE 7 and Opera 11.52, MS Outlook and Gmail client used for testing.

Now I am poring over the RFC’s to gain a wholesome understanding of the whole wonderfully twisted domain of encoding/decoding!

Java’s HttpURLConnection is broken!

How to read the file size of a document served at an HTTP/S URL using Java

Recently I was assigned some work where I had to read reports from a custom HTTP (development) or HTTPS (production) URL and e-mail them accordingly, attaching the report to the e-mail if it did not exceed the user configured maximum SMTP attachment size.

The problem I had was that even though the reports were actually stored on the web server’s local file system, I did not have access or permission to access the reports from the file system. All I had was the web URL of the report document. Thus I had no choice but to read the file size somehow from the URL , check it against the configured maximum SMTP attachment size and then e-mail the report. The prototype that I developed is posted at this location – http://pastebin.com/KnsUuAhG

As can be seen, I have used JavaMail for the actual e-mail dispatch. The interesting bit is in the isAttachmentSmallerThanMaxSmtpSize  method:

private static boolean isAttachmentSmallerThanMaxSmtpSize(
                        String attachmentLink) {
                boolean status = false;

                try {
                        HttpURLConnection connection = (HttpURLConnection) new URL(
                                        attachmentLink).openConnection();
                        int sizeInMB = connection.getContentLength() / (1024 * 1024);

                        if (sizeInMB > MAX_SMTP_SIZE) {
                                status = false;
                        } else {
                                status = true;
                        }

                } catch (Exception ex) {
                        status = false;
                }

                return status;
        }

where the actual file size can be obtained by simply obtaining the content length of the HTTP (or HTTPS) Connection object.

For my prototype, for HTTPS connections, I actually disabled the HTTPS certificate check by using the method described in this nifty blog: http://www.nakov.com/blog/2009/07/16/disable-certificate-validation-in-java-ssl-connections/. In actual production use, however, HTTPS URLs are handled by using the actual certificates installed with the product.

How to read the file size of a document served at an HTTP/S URL using Java