|
14.9. Setting Up PostgreSQL for BudgetProLet's turn our attention to installing and starting PostgreSQL to support the BudgetPro application. 14.9.1. Installing PostgreSQLThe simplest way to install PostgreSQL is to use precompiled binary packages. RedHat and Fedora have RPM packages for installing the database and client software (although neither distributes the client libraries for Java due to licensing issues with Java itself). The PostgreSQL project produces RPM packages of their own, including the Java JDBC class library. Those might be your easiest course. Debian packages for PostgreSQL exist, but again, they do not provide the JDBC library. For our purposes, we are going to assume that you have downloaded and installed the following RPM packages[9] from the PostgreSQL Download page:[10]
14.9.2. Creating a postgres UserMore than likely, installing a PostgreSQL package will create a new Linux user called postgres on your system. This is not a login account; it will be used by the database server process daemon[11] (called "postmaster," lest you think that it might be an e-mail server or something). Furthermore, only the postgres user is able to create additional database users or any databases.
Obviously, we won't want it to be like this forever. It has the same problem as the root user on the OS itself: There's one magic user with all the power. You'll need to create additional users and you'll want to limit what they can do. How to do it? First off, you can't log in as postgres, so you will have to become root and then su to the postgres user: [mschwarz@cassidy mschwarz]$ su - Password: [root@cassidy root]# su - postgres -bash-2.05b$ Note that postgres didn't have any profile or rc script to set up prompts or anything. All PostgreSQL databases have owning users, in much the same way that all Linux files have owning users. But PostgreSQL users are not necessarily the same as Linux users.[12] The only PostgreSQL user that exists "out of the box" is postgres. You must use the createuser command-line utility (or the equivalent SQL) to create a user. Here's an example:
-bash-2.05b$ createuser mschwarz Shall the new user be allowed to create databases? (y/n) y Shall the new user be allowed to create more new users? (y/n) y CREATE USER -bash-2.05b$ Here, we created the PostgreSQL user mschwarz and gave him the ability to create databases and new users. 14.9.3. Creating Our DatabaseNow that our username, mschwarz, has been created and is authorized to create databases, we use the createdb command to create our database: [mschwarz@cassidy mschwarz]$ createdb budgetPro CREATE DATABASE Notice that we did this as the Linux user mschwarz, so the createdb command used that username when it created the budgetPro database. What would have happened if we had used a Linux user that did not have a matching PostgreSQL user? Let's see: [albing@cassidy albing]$ createdb budgetPro createdb: could not connect to database template1: \ FATAL: user "albing" does not exist Warning Note that we have not implemented any security yet. The user mschwarz exists, but it does not have a password. Depending on how PostgreSQL security is configured, it may be possible for any user to use the -U option with the PostgreSQL command-line utilities to impersonate mschwarz. As packaged for Fedora Core 2, PostgreSQL uses ident to check authentication, so when albing attempts this, he gets this error: [albing@cassidy albing]$ createdb -U mschwarz budgetPro createdb: could not connect to database template1: \ FATAL: IDENT authentication failed for user "mschwarz" Just remember that this behavior is dependent on the local configuration of PostgreSQL. You cannot assume this security is in place just because you are in a PostgreSQL environment. Always be aware of the security configuration of your production environment! 14.9.4. Straight JDBCOur application is quite simple. We're going to directly integrate database access by simply calling the JDBC interface at startup and making use of that connection again and again for data changes. Our solution is more than sufficient for the standalone command-line and GUI versions of the application, but it will be left as an exercise for the reader to implement a better solution for the EJB implementation. What will be lacking? Well, the solution we will implement here will get a single database connection and use it throughout the life of the application. It will be assumed that a single thread is accessing the database. These are all bad assumptions for a multiuser and multithreaded environment such as an application server. 14.9.4.1 Static Account MembersMost of the database code is in the Account class. It consists of a static method, getTopAccount(), which will establish a static connection to the database, create the Account table if need be, and load the top level account (defined as the account record with a null parent) if present or create it if not present. The username is passed in as an argument. The username must exist in the User table. If it does not, an exception is thrown. 14.9.4.2 Joining the UserTwo static methods are added that take a JDBC Connection and a String as arguments. They are getUserIdByName() and getUserByName(). The String is the username to look for. The first method returns the id column for that user. It returns zero (0) if the user doesn't exist. The other returns a User object, or null if the user doesn't exist. |
|