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.

Advertisements
Adding users to a PostgreSQL server

Speak your mind!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s