Note |
---|
This article can be enhanced by adding instructions for letting just any user create a database. |
During the time of this article, the instructions on the Ubuntu wiki were pretty light and from what I can see not quite complete. These instructions are currently written for PostgreSQL 8.4x. and 9.x.
Installing PostgreSQL
Ensure UTF-8 Encoding is Set
Make If you did not follow the Bonsai Framework to setup Ubuntu yourself, make sure that your operating system has the right encoding set as explained in the "Select Locale" part of my Ubuntu Linux Base Server article. In the case of my Ubuntu server it was installed with the minimal configuration and was missing the utf-8 locale.
Install
The Ubuntu install is simply,
Code Block | ||
---|---|---|
| ||
sudo apt-get install postgresql # installs postgresql |
...
You will now have a postgresql user on your system. This account is reserved for running Postgresql in a secure manner. This account neither has a password or a home directory. This account also has a role in the database for administration purposes. I will show you how to use it this account as we go forward.
The postgresql service is also added to the system to start automatically.
...
That's all there is for the installation. Ubuntu apt-get makes it easy and does all the hard work for you.
Further Details
You might also be interested to know that Ubuntu's apt-get installer does a number of other things you would have had to manually configure if you installed manually,
- Initialize a database storage area on disk called the database cluster.
- ...
Warning In order to complete this section I need to figure out how to do a configured portable setup of PostgreSQL.
Initial Setup
Secure Master Account
By default, the master account in the database postgres does not have a password. We want to set this up so that we can provide access to a dba without the dba requiring sudo access to the operating system. Also, leaving at is makes postgres open to any user with sudo privileges.
Change the Default postgres Database User Account
If you want to manage postgresql outside of the command line, you must change the default password in postgresql.
The first step, is to step into the postgresql shell launching the program psql as the user postgres.
The first step, is to launch the postgresql shell.
Code Block | ||
---|---|---|
| ||
sudo -u postgres psql postgres |
The command is confusing so here is a break down,
Code Block | ||
---|---|---|
| ||
sudo -u postgres # Run as the operating system user postgres which also has a role in the database.
psql # Launch the postgres shell.
postgres # Name of the database to load (not the user). |
Info |
---|
Sometimes the last parameter is not used. This is short-hand. If no database name is specified, psql will try to load the database matching the user name. |
Here is what it looks like once you are in,
Code Block | ||
---|---|---|
| ||
psql (8.4.1)
Type "help" for help.
postgres=# |
All commands are prefixed by the the \. So to change the postgres account password. In the Bonsai Framework, we use the selected password algorithm derived from the server name.
Code Block | ||
---|---|---|
| ||
postgres=# \password postgres |
Note that this changes the account inside of postgres, not the operating system account postgres. Finally exit the postgres shell,
Code Block | ||
---|---|---|
| ||
postgres=# \quit |
With this change you can now use a GUI client software such as pgAdmin III. Note that be by default, postgres is very secure. As such with this change you will be able to use pgAdmin III in only the following 2 scenarios,
- Using the pgAdmin III installed on the same system as Postgresql.
- From another machine using an SSH Tunnel.
Further Reading
Page Tree | ||
---|---|---|
|