PostgreSQL on Fedora

Jumaat, 19 Februari 2010, 1:44 am0

PostgreSQL (Update: Using Fedora 14)

Install PostgreSQL & phpPgAdmin (web-based Postgres administration)

yum install postgresql postgresql-server phpPgAdmin

Initialize database

service postgresql initdb

Start PostgreSQL service

service postgresql start

Create password for user ‘postgres’ (in the system)

passwd postgres

Login as user ‘postgres’

su - postgres

Create password for user ‘postgres’ (in the database)

psql -d template1
alter user postgres with password 'password';

Exit psql shell:

q

Let Postgres allow authentication using password: (/var/lib/pgsql/data/pg_hba.conf)

# TYPE  DATABASE    USER        CIDR-ADDRESS          METHOD
# "local" is for Unix domain socket connections only
local   all         all                               md5
local   all         all                               ident
# IPv4 local connections:
host    all         all         127.0.0.1/32          md5
host    all         all         127.0.0.1/32          ident
# IPv6 local connections:
host    all         all         ::1/128               md5
host    all         all         ::1/128               ident

Note:
md5 – allow authentication using password
ident – authentication as system user

Restart PostgreSQL

service postgresql restart

Enable service:

chkconfig postgresql on

phpPgAdmin

Create phpPgAdmin by copying from original config file: (/usr/share/phpPgAdmin/conf)

cp config.inc.php-dist config.inc.php

Allow login from phpPgAdmin: (/usr/share/phpPgAdmin/conf/config.inc.php)

$conf['extra_login_security'] = false;

Apache setting, add your IP address to allow access to the application (/etc/httpd/conf.d/phpPgAdmin.conf)

Allow from 192.168.56.1

Reload httpd. Open phpPgAdmin (http://localhost/phpPgAdmin). Login as ‘postgres’

Add new user: (PostgreSQL > Roles > Create role)

Create role (user) ‘webmaster’, inherits privileges from user ‘postgres’ & allow login

Create database: (PostgreSQL > Database > Create database)

Create database ‘helloworld’ using Unicode encoding

Grant permission on database: (PostgreSQL > helloworld > Privileges > Grant)

User ‘webmaster’ is granted ‘All privileges’

Check connection & configuration

psql -h localhost helloworld webmaster