CS 491 Linux Administration and Security

arghtalentData Management

Jan 31, 2013 (4 years and 6 months ago)

117 views

CS 491/591 Linux
Administration and Security


PostgreSQL (database)


Installation


Configuration


Basic Security



Gulsen ilgaz:musgul@siu.edu

What is PostgreSQL?

PostgreSQL is an Object
-
Relational Database

Management System (ORDBMS) that has

been developed in various forms since 1977.

It’s an open source project. It means that

you can obtain the source code, use the

program, and modify it freely without the

confines of proprietary software.

PostgreSQL provides a wealth of features
that are usually only found in commercial
databases such as Oracle. The following is a
brief listing of some of these core features:


It supports user
-
defined operators, functions,
access methods, and data types.


It supports the core SQL99 specification.


It supports referential integrity, which is used
to insure the validity of a database's data.


The flexibility of the PostgreSQL API has
allowed vendors to provide development
support easily for the PostgreSQL RDBMS.
These interfaces include Object Pascal, Python,
Perl, PHP, ODBC, Java/JDBC, Ruby, TCL,
C/C++, and Pike.



Conti….


It uses a process
-
per
-
user client/server
architecture.


The PostgreSQL feature known as
Write Ahead
Logging

increases the reliability of the database
by logging changes before they are written to
the database. This ensures that, in the unlikely
occurrence of a database crash, there will be a
record of transactions from which to restore.
This can be greatly beneficial in the event of a
crash, as any changes that were not written to
the database can be recovered by using the data
that was previously logged. Once the system is
restored, a user can then continue to work from
the point that they were at before the crash
occurred.

PostgreSQL Installation

First, you can check if you have PostgreSQL
database server already installed. Type the
following command in your terminal.

rpm
-
qa | grep postgresql

If you get postgresql
-
8.0.3
-
x

or something alike when the command is
executed, it means that you already have
PostgreSQL database software installed.


Conti…

If you don't have PostgreSQL installed, you will

need to download PostgreSQL from binary RPM

distribution from

www.postgresql.org

or its

mirror sites.

You can download the latest version of PostgreSQL

from the following address:

ftp://ftp3.us.postgresql.org/pub/postgr

esql/binary/v8.0.3/linux/rpms/


Conti…

You will need to install postgresql
-
libs first.

If you want to run server, install postgresql
-

server and postgresql
-
rpms. The other packages

are for developing applications, docs, tcl, perl, etc

support.

Run the following commands to install:

rpm
-
ivh postgresql
-
libs
-
8.0.3
-
1PGDG.i686.rpm

rpm
-
ivh postgresql
-
server
-
8.0.3

1PGDG.i686.rpm

rpm
-
ivh postgresql
-
8.0.3
-
1PGDG.i686.rpm

PostgreSQL Configuration


First check whether PostgreSQL is running:
"/etc/rc.d/init.d/postgresql status”

you
should

get some information back saying that is it
running like this "postmaster (pid 1156 790) is
running...”


The user "postgres" should have already been
configured by the installation of the RPMs. A
password will be missing. So, we need to assign a
password for user postgres:
“passwd postgres”

Conti…


Login as a user postgres:

“su

postgres”


This will execute the profile:
“/var/lib/pgsql/.bash_profile ”


To initialize PostgreSQL database server:


“initdb
--
pgdata=/var/lib/pgsql/data”


This creates a bunch of directories, a template directory
and sets up the postgres configuration in the user
directory
“/var/lib/pgsql/”


To start the database server. As root:


“service postgresql start”

or
“/etc/rc.d/init.d/postgresql start”

Conti…


To create a database:
“createdb magic”


To connect to database:
“psql magic”


Now, you will be at the PostgreSQL command
line prompt:



psql magic


Welcome to psql, the PostgreSQL interactive terminal.



Type:
\
copyright for distribution terms


\
h for help with SQL commands


\
? for help on internal slash commands


\
g or terminate with semicolon to execute query


\
q to quit



magic=#


Basic Network and Security
Configuration


To allow PostgreSQL to accept TCP/IP
connections from the JDBC driver:


“/var/lib/pgsql/data/postgresql.conf”


change

“tcpip_socket=false”
to
“tcpip_socket=true”


PostgreSQL allows anyone from the local
machine to connect to the databases


without a password challenge. So, if other users
are using your computer

Conti…

“/var/lib/pgsql/data/pg_hba.conf ”
go to the


end of the file, and change the authorization


checks from
“trust” to “password”


Start server again:


“service postgresql start”