Transcript CS 491 Linux Administration and Security

CS 491/591 Linux
Administration and Security
PostgreSQL (database)
•Installation
•Configuration
•Basic Security
Gulsen ilgaz:[email protected]
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”