...
During
...
the
...
time
...
of
...
this
...
article,
...
the
...
...
...
...
...
...
were
...
pretty
...
light
...
and
...
from
...
what
...
I
...
can
...
see
...
not
...
quite
...
complete.
...
These
...
instructions
...
are
...
currently
...
written
...
for
...
PostgreSQL
...
8.x.
...
and 9.x.
Installing PostgreSQL
Ensure UTF-8
...
Encoding
...
is
...
Set
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.
Install
The Ubuntu install is simply,
| Code Block | ||
|---|---|---|
| ||
sudo apt-get install postgresql # installs postgresql{code}
|
Similar
...
to
...
how
...
Apache
...
is
...
installed,
...
Ubuntu
...
has
...
it's
...
own
...
standard.
...
Here
...
are
...
the
...
keynotes.
...
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 this account as we go forward.
The postgresql service is also added to the system to start automatically.
Finally here are some directories to be aware of,
- /var/lib/postgresql/8.4/main
...
- -
...
- location
...
- of
...
- the
...
- main
...
- database
...
- that
...
- can
...
- only
...
- be
...
- viewed
...
- and
...
- modified
...
- by
...
- the
...
- user
...
- postgresql
...
- .
...
- /etc/postgresql/8.4/main
...
- -
...
- location
...
- of
...
- all
...
- configuration
...
- files.
...
- /var/log/postgresql/
...
- -
...
- location
...
- where
...
- Ubuntu
...
- keeps
...
- all
...
- the
...
- logs
...
- and
...
- automatically
...
- is
...
- set
...
- to
...
- archive
...
- and
...
- roll
...
- over.
...
That's
...
all
...
there
...
is
...
for
...
the
...
installation.
...
Ubuntu
...
apt-get
...
makes
...
it
...
easy
...
and
...
does
...
all
...
the
...
hard
...
work
...
for
...
you.
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
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=#{code}
|
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{code} |
Note
...
that
...
this
...
changes
...
the
...
account
...
inside
...
of
...
postgres,
...
not
...
the
...
operating
...
system
...
account
...
postgres.
...
Finally
...
exit
...
the
...
postgres
...
shell,
...
| Code Block | ||
|---|---|---|
| ||
postgres=# \quit{code} |
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 | ||
|---|---|---|
|