We start by firing up a root shell and installing the PostgresSQL server.
ares:~$sudo bash [sudo] password for f: ares:~#whoami root ares:~#apt-get install postgresql
It needs a y to install and start the PostgreSQL server and bind it on port 5432 on localhost
Let 's make sure that it started
ares:~#netstat -putan|grep postgres tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 22705/postgres tcp6 0 0 ::1:5432 :::* LISTEN 22705/postgres udp6 0 0 ::1:48884 ::1:48884 ESTABLISHED 22705/postgres
Cool, now we need to login to our PostgreSQL server to reset the postgres user password using the psql --the PostgreSQL interactive terminal.
ares:~#sudo -u postgres psql postgres could not change directory to "/root" psql (8.4.8) Type "help" for help. postgres=#
To set the password for the user postgres we use the following command
postgres=# \password postgres Enter new password: Enter it again: postgres=#
We can list the databases in our PostgreSQL server using the command \l
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
: postgres=CTc/postgres
(3 rows)
We can list the users using the command \du
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+-------------+-----------
postgres | Superuser | {}
: Create role
: Create DB
The key sequence Ctrl,d or the command \q exits psql
postgres=# \q ares:~#
OK, let's add a user and a database to our freshly installed PostgreSQL server.
Let's add a user named pguser0.
ares:~#sudo -u postgres createuser -P pguser0 could not change directory to "/root" Enter password for new role: Enter it again: Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) n Shall the new role be allowed to create more new roles? (y/n) n ares:~#
Let's add a database named pgdb0 that belongs to the user pguser0.
ares:~#sudo -u postgres createdb -O pguser0 pgdb0
Let's list all the users and all the databases in our PostgreSQL server.
ares:~#sudo -u postgres psql postgres
could not change directory to "/root"
psql (8.4.8)
Type "help" for help.
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+-------------+-----------
pguser0 | | {}
postgres | Superuser | {}
: Create role
: Create DB
postgres=# \l
List of databases
Name | Owner | Encoding | Collation | Ctype | Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
pgdb0 | pguser0 | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 |
template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
: postgres=CTc/postgres
template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres
: postgres=CTc/postgres
(4 rows)
postgres=# \q
ares:~#
looks good, now lets try to connect to pgdb0 as pguser0.
ares:~#psql pgdb0 -U pguser0 -h localhost Password for user pguser0: psql (8.4.8) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. pgdb0=> pgdb0=> \q ares:~#
it works! , let's configure our PostgreSQL server to listen in 192.168.2.250 by adding it to listen_addresses in
/etc/postgresql/8.4/main/postgresql.conf like
ares:~#grep listen_addresses /etc/postgresql/8.4/main/postgresql.conf listen_addresses = '192.168.2.250,localhost' # what IP address(es) to listen on;
Now, we need to restart PostgreSQL
ares:~#/etc/init.d/postgresql-8.4 restart * Restarting PostgreSQL 8.4 database server ...done.
and make sure it listens where we told him to listen
ares:~#netstat -punta|grep postgre tcp 0 0 127.0.0.1:5432 0.0.0.0:* LISTEN 23271/postgres tcp 0 0 192.168.2.250:5432 0.0.0.0:* LISTEN 23271/postgres tcp6 0 0 ::1:5432 :::* LISTEN 23271/postgres udp6 0 0 ::1:50521 ::1:50521 ESTABLISHED 23271/postgres
OK, the PostgreSQL server listens on 192.168.2.250.
Now, let's allow access to our PostgreSQL server from the network 192.168.2.0/24.
For that, we will have to edit /etc/postgresql/8.4/main/pg_hba.conf
We add the line "host all all 192.168.2.0/24 md5" in the
"# TYPE DATABASE USER CIDR-ADDRESS METHOD" section.
The last 11 lines of /etc/postgresql/8.4/main/pg_hba.conf look like
ares:~#tail -11 /etc/postgresql/8.4/main/pg_hba.conf # TYPE DATABASE USER CIDR-ADDRESS METHOD # "local" is for Unix domain socket connections only local all all ident # IPv4 local connections: host all all 127.0.0.1/32 md5 #allow access from 192.168.2.0/24 host all all 192.168.2.0/24 md5 # IPv6 local connections: host all all ::1/128 md5
OK, let's restart the PostgreSQL server.
ares:~#/etc/init.d/postgresql-8.4 restart * Restarting PostgreSQL 8.4 database server ...done.
And, attempt to connect to the pgdb0 database from another host in 192.168.2.0/24.
g0:~$psql pgdb0 -U pguser0 -h 192.168.2.250 Password for user pguser0: psql (8.4.8) SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Type "help" for help. pgdb0=>
It works!
