[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Feedback - postgresql howto
The PostgreSQL howto to be found at
http://www.linuxdoc.org/HOWTO/PostgreSQL-HOWTO.html
<FLAME>
is downright embarassingly bad. It fails to deliver anything resembling
a howto for much of anything, other than the structure of molecules and
the laws of physics, as delivered by a 12-year old child in a bad sci-fi
movie!
This guy clearly has the brain of a gnat, and is likely permenantly
addled by the large quantities of LSD consumed both by himself and his
mother during her pregnancy.
What the fuck is this lame excuse for documentation? It's a testament to
the imminent failure of open-sourced technologies! This guy should be
tarred and feathered!
</FLAME>
<NOFLAMES>
should be re-considered as to its suitability for publishing. In
numerous occasions, facts are interspersed with opinion, the latter
often claimed as being the former, and the document is over-all lacking
in suitable content for those attempting to learn how to setup/use
PostgreSQL.
Additionally, this "how-to" is riddled with poorly written, dis-jointed,
and often utterly inapplicable information.
I would submit that erasing this document from your site and its mirrors
would likely prompt a more clearly written, useful howto to be written
by somebody with demonstrated competence.
In short, its relevance and usefullnes are about as low as possible
while still preserving an overt, offensive tone.
</NOFLAMES>
I have attached what I feel is a far more suitable replacement, in the
form of a "mini-howto" called pg-kick.txt. (short for
PostgreSQL kick-start)
-Benjamin Smith
Kick-start for PostgreSQL
WHAT IS POSTGRESQL
Postgres is an Object-oriented RDBMS. (= Relational DataBase Management
Sysystem)It uses the internationally-recognized SQL language. (SQL=structured
query langage)
It is extremely advanced and powerful, but if you didn't already know that you
wouldn't be here, would you?
Even better, it's open-source, so its long-term viability and powerful
feature set are assured.
HOW DOES IT WORK?
You can almost think of PostgreSQL as another file system. There's information
there, and like Unix, there are multiple users, each of which can have a
verying degree of access or control over this information.
There's a master program that manages this information called "postmaster".
Postmaster is the ONLY program that has any direct access to this information.
In this, it functions somewhat like an anally-retentive librarian; the only
information going in or out of the databases is shuttled by postmaster.
Other programs, called clients, connect to postmaster, send a request (in SQL
format) and postmaster returns the answer.
This system prevents data corruption if multiple requests are made to the same
data set, ensures data integrity, and provides a high level of performance.
Users authorized by postmaster to have access to the information in the
database don't necessarily have anything to do with users set up on the system
at large.
In other words, under *nix, if you have a login account called "user", that
login account has no direct relationship with any user from the perspective of
postmaster.
PostgreSQL has its own set of authentication protocols irrespective of the
underlying operating system.
HOW DO CLIENTS CONNECT?
Clients to the PostgreSQL database can connect through one of at least two
methods: TCP/IP sockets & Unix file sockets. There may be other methods I'm
not (yet) aware of.
TCP/IP sockets allow access to the database by database clients anywhere, from
any computer system running any operating system. Unix file sockets must be
accessed from the computer postmaster is running on.
SO, WHAT ARE WE HERE FOR?
The goal of this mini-howto is to get PostgreSQL up and running, with correct
permissions, to allow you to create and access a PostgreSQL database.
Of course, now you hear the standard disclaimers... I'm VOLUNTEERING this
information, as it didn't exist when I needed it, cost me at least half a day
of my time to figure this stuff out, and this could have been cut down to less
than an hour had this little howto existed.
So, by reading this, you hold me harmless to any damage to your machine, your
data, any vulnerabilities or insecurities I don't tell you about, the health
and well being of yourself, your family, your friends, and your pets.
In short, I take NO LIABILITES OF ANY KIND, either express or implied, as a
result of this document, or any derivitive form of this document.
READ AT YOUR OWN CAUTION, as I am still a fairly inexperienced sysadmin! This
document is made available under the GNU Public License, so you can use this
document as you see fit, so long as you don't change it. If you do change it,
you have to let me know. (mcrbids@excite.com)
Also, feel free to send me more information you feel is lacking in this
mini-howto.
GETTING STARTED... GETTING POSTGRESQL
PostgreSQL is available in source-code form from http://www.postgresql.org.
If you are using Red Hat Linux (or any of its derivitives, like Mandrake) you
can download RPMS.
Most distributions of Linux come with PostgreSQL. This tutorial assumes we're
using Red Hat Linux version 6.2.
Red Hat Linux can be obtained directly from Red Hat (http://www.redhat.com) or
you can get unsupported copies from the likes of Cheap Bytes.
(http://www.cheapbytes.com)
As of the time of this writing, the most current version is 7.0.2, we'll be
working with version 6.5.3. It is generally better to work with the latest
version available.
To install an RPM, the syntax is rpm -Uvh <packagename>.
For example
rpm -Uvh postgresql-6.5.3-6
To see if the rpm(s) for postgresql are already installed, try this:
rpm -qa | grep postgres
if you see (at least) postgresql-{numbers}, postgresql-test-{numbers} and
postgresql-devel-{numbers} than you are already in business.
Many people are running Postgres on their Linux systems and don't even know
it!
SETING UP POSTGRES USER.
Postmaster is a process that runs on your system. It is a daemon, meaning it
sits in the background and manages some part of information flow within your
system. (that having to do with your databases!)
It is NEVER a good idea to run any daemon as root if you can avoid it. Any
program run as root that contains any kind of vulnerability leaves your system
open to less-than-ethical hackers and system crackers, who are then given
root-level access to your computer.
Not a good idea!
Postgres actually makes all this meaningless. Postmaster WILL NOT RUN as root.
You have to set up a user specifically for postgres, that does not have root
privileges.
If you installed from RPM, or the RPMS were already installed, it's likely
that this is already done.
To test for this, as root, run this command:
cd ~postgres
pwd
If "pwd" returns something like either /var/lib/pgsql or /home/postgres, you
are in business.
Otherwise, create a user called "postgres" (adduser postgres) give it a
password, (passwd postgres) and proceed!
GETTING POSTMASTER TO RUN
You might already have postmaster running already (again, you might not know
it). Try the following command:
ps -e u | grep post
If you see a line like this:
postgres 4812 0.0 0.7 5068 348 ? S Jun20 0:00 /usr/bin/postmast
You're almost DONE with this step. Read on...
When you installed the RPMs, you created a startup script called "postgresql".
You need to make a decision now: Do you want to allow connections to your
database via TCP/IP from other computer systems? Most people won't, at least,
I think so.
Allowing connections via TCP/IP introduces all kinds of security issues, and
this is something we don't want. Bad bad bad. Some people, however, NEED this
function, and so it's enabled. If this is the case, update your router or
gateway's packet filter to ONLY allow connections from hosts you COMPLETELY
TRUST. You need to filter out any TCP and UDP packets going to port 5432 from
ALL OTHER HOSTS. (tell sysadmin, eh?)
To turn off postgresql access via TCP/IP, you need to go to /etc/rc.d/init.d
and edit a file called "postgresql".
Try this:
pico /etc/rc.d/init.d/postgresql
Look for a line that reads like this:
su -l postgres -c '/usr/bin/postmaster -i -S -D/var/lib/pgsql'
If you don't want connections from other machines, remove the -i flag, so it
reads like this:
su -l postgres -c '/usr/bin/postmaster -S -D/var/lib/pgsql'
Save, and exit. (if you used pico, try Ctrl-O and then Ctrl-X)
Now, (as root) you want to make your changes take effect, by typing these
commands:
/etc/rc.d/init.d/postgresql stop
/etc/rc.d/init.d/postgresql start
and your changes should have taken effect. Verify this with a port-scanner,
(such as nmap - available at www.insecure.org) or go to
www.dslreports.com/scan for a free checkup. dslreports will scan the computer
you are connecting from, so you need to have your browser running on the
computer you've just installed postgres on. If you are in a fire-walled LAN,
depending on your configuration, it might scan the firewall, make sure this
isn't what happened.
A port-scan will likely show you lots of other vulnerabilities you weren't
aware of, but that's outside the scope of this document. (but inside the scope
of hackers and system security - no laughing matter - so it's a good idea to
check this out ASAP, eh?)
BATTEN THE HATCHES, ME MATEYS!
As stated previously, postgres runs in the operating system account
"postgres". There is also (by default) a postmaster user called "postgres". By
default, it has no password and connections made to postmaster are trusted
right out of the box.
In other words, your front door is wide open.
If this is OK with you, ignore this next set of steps. If not, read on.
First off, we want to create a password for user postgres:
1) Login to the system as "postgres". The easiest way is probably to login as
root and "su - postgres".
2) type in the following:
psql template1
You should see something like this:
--------------------------------------------------------------------
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66]
type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: template1
template1=>
--------------------------------------------------------------------
You're logged in to postgreSQL! (if you want to take a moment to pat yourself
on the back, feel free. I sure did!) Alot of basic functions are performed
here, in interactive mode.
psql is an interactive client that comes with the postgres package(s). If you
need help, try typing this in:
\?
If you need help with a command, try
\h alter table
This will give help on the "alter table" command. Really, it's almost like
have a manual w/o having to fire up netscape! Get to know these "\" backslash
commands, they are your friends.
Now, back to what we're here doing; a password for user postgres:
ALTER USER postgres WITH PASSWORD "new_password";
Enter the above statement in your interactive psql session, replace
new_password (duh!) with your password. Do include the quotes.
Once you've done this, remote hosts can no longer connect to your local
database server as "postgres" using TCP/IP, without entering the password
you've just specified. (You closed the screen door)
Exit out of psql with \q.
Change back to root. If you used "su" (as I mentioned above) use "exit" to
return to root.
Now, you need to edit an important file, pg_hba.conf. This is your access
control file, the options that are available with respect to security policies
are listed here. Find this file (likely to be found at
/var/lib/pgsql/pg_hba.conf. Edit it.
(perhaps with pico /var/lib/pgsql/pg_hba.conf)
Go to the bottom of the file, and change the last two lines that read
local all trust
host all 127.0.0.1 255.255.255.255 trust
to read:
local all password
host all 127.0.0.1 255.255.255.255 password
What this means is that any postgres client on the local machine has to
identify itself with a password.
Save and exit this file.
Restart postgres (see above) to make these changes take effect.
Now, we want to create a "normal" user, (which I recommend using instead of
user postgres) so here's what you do.
run psql as user "postgres".
psql -u template1
psql will try to use database "template1" (which comes with postgreSQL) and
prompt you for login/password. (-u) When you are logged in, we want to create
a new user. (There's a table that postmaster keeps with this information in
it)
So, without further ado, type:
CREATE USER user_name WITH PASSWORD "password";
This will create a new user, but it won't necessarily give this new user the
permission we want.
To get more information on this stuff, type \h alter user.
You can give this newuser permission to create databases
(CREATEDB/NOCREATEDB), create other users (CREATEUSER/NOCREATEUSER) and also
specify with group(s) user is in. (group information is beyond the scope of
this document and my current level of understanding!) You can even give a
timeout period for how long this user is allowed!
CONCLUSION
Well, this document is meant to act like a sling-shot, to get you moving
quickly. What you do here is up to you.
I strongly recommend that you read up as much as is possible on securing your
new installation of postgres, and system security in general, if you are on
the Internet or a large local LAN. When in doubt, READ THE MANUAL.
The documentation for Postgres is good, generally laid out logically,
inclusive, and freely available at http://www.postgresql.org, under "info
central".
-Benjamin Smith