Part 3. Preparing PostgreSQL for Metadata use

Part 3. Preparing PostgreSQL for Metadata use

This section is going to start off with the assumption that you've already installed the PostgreSQL packages described in Step 1.  If not, please refer to that post before continuing.

At this point, the PostgreSQL server should not be running.  To check, open up a terminal, navigate to /usr/sbin, and type:

mstradm@linux-las9:/usr/sbin> sudo ./rcpostgresql status

Checking for PostgreSQL:                                             unused

Go ahead and start the server up:

mstradm@linux-las9:/usr/sbin> sudo ./rcpostgresql start

Initializing the PostgreSQL database at location /var/lib/pgsql/data done

Starting PostgreSQL                                                  done

At this point the database is running, however there's really nothing in it and no users other than 'postgres' created.  In order to log into the database for the first time, we need to switch to the 'postgres' user and type 'psql'.

mstradm@linux-las9:/usr/sbin> sudo su postgres

postgres@linux-las9:/usr/sbin> psql

psql (8.4.1)

Type "help" for help.

postgres=# 

Now you'll be sitting at a database prompt ready to type commands.  The first thing we can do is go ahead and create a password for the postgres database user.  At the prompt, type \password and you should be prompted for a new password as shown below:

postgres=# \password

Enter new password: 

Enter it again: 

postgres=# 

Now, type \quit to exit the psql prompt and go back to the main shell.

Next, let's create a database for MicroStrategy to use for the metadata that we're going to create and configure later.  While still using the 'postgres' user, type 'createdb DATABASE_NAME' and hit enter.  Should look like the following:

postgres@linux-las9:/usr/sbin> createdb METADATA

If the command completes successfully, you really shouldn't see anything on the screen.  If you'd like to check, log back into psql and execute \l (l as in L).  You'll see your new database listed in the output.

postgres=# \l

                                  List of databases

   Name    |  Owner   | Encoding |  Collation  |    Ctype    |   Access privileg

es   

-----------+----------+----------+-------------+-------------+------------------

-----

 METADATA  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | 

Before we move on to the next step, we need to "turn on" plpgsql for this new database.  If we don't, the MicroStrategy metadata configuration wizard will fail when creating the repository tables.  To do this, we will use the createlang command, passing 'plpgsql' as the second argument, and our database name as the third.

postgres@linux-las9:/usr/sbin> createlang plpgsql METADATA

Next we're going to need a database user for MicroStrategy to use when logging into the metadata.  For this, we're going to use the createuser command shown below:

postgres@linux-las9:/usr/sbin> createuser --superuser mstr

Yes, we're creating the MicroStrategy user named 'mstr' as a superuser of the database.  I'm primarily creating this user as a superuser for the sake of brevity, so if you're actually planning to deploy this into a real production environment, I'd pay some mind to properly selecting your user roles and privileges.

Launch psql again and set a password for the new 'mstr' account with ALTER USER.

postgres=# ALTER USER mstr WITH PASSWORD 'test';

ALTER ROLE

Now at this point, we have two users created for the database; postgres and mstr.  However, if you try to launch psql passing the -U (user) flag, you'll get the following error:

postgres@linux-las9:/usr/sbin> psql -U mstr

psql: FATAL:  Ident authentication failed for user "mstr"

This has to do with how we have the database authentication setup in the pg_hba.conf file.  To change, we need to navigate to the /var/lib/pgsql/data directory, and open pg_hba.conf with the text editor of your choice.  Scroll to the end of the file, and you should see connection settings listed.

Change the 'METHOD' from 'ident' to 'trust' for each line item.  After you complete the changes, the new settings should look like:

Go ahead and save your changes, however do not exit this file yet.  While we're here, let's make another change that will allow 2-tier connections to the database from non-local sources.  This could be simply connecting via a 2-tier connection in MicroStrategy Desktop, using the project duplication tool (which we will do later.), or connecting to the database with pgAdmin or another tool that isn't directly on the server machine.

What we need to do is add another line similar to those above, however specifying our outside IP address range and the connection method.  The line I'm going to add is:

host    all    all    192.168.10.100/20    md5

What this means is, any user can connect to any database, as long as the client IP address is within 192.168.10.100 - 192.168.10.120.  Also, the credentials will be encrypted since our method specifies md5.  So once I add this to my pg_hba.conf file, it now looks like:

Save the pg_hba.conf file.  One more file we need to modify to allow external access, and this file is again located in /var/lib/pgsql/data and is named postgresql.conf.  Go ahead and open this file in your text editor of choice, and find the following 'CONNECTIONS AND AUTHENTICATION' section:

Here, we need to uncomment the 'listen_addresses' line, and change 'localhost' to '*'.  Once complete, this section should look like:

Save the postgresql.conf file and exit.

We're finished!  Simply restart the PostgreSQL server and all changes should be in effect.

postgres@linux-las9:/usr/sbin> sudo ./rcpostgresql restart

Shutting down PostgreSQLserver stopped

                                                                     done

Starting PostgreSQL                                                  done