Difference between revisions of "Postgres SQL"

From Blue-IT.org Wiki

(Initial registration with pgAdminIII)
Line 63: Line 63:
 
  sudo /etc/init.d/postgresql-8.1 restart
 
  sudo /etc/init.d/postgresql-8.1 restart
  
===Initial registration with pgAdminIII===
+
==Initial registration with pgAdminIII==
  
 
[[Image:PdAdminIII_initial_registration.png|left|600px|Working with pgAdminIII ]]
 
[[Image:PdAdminIII_initial_registration.png|left|600px|Working with pgAdminIII ]]

Revision as of 18:38, 6 September 2007

General

General documentation you will find here: postgresql.org - interactive (!) manual with comments.

You should read this in advance to make shure the concepts behind postgresql configuration, startup and security (e.g. 'roles).

User Management

postgresql-org - user management

Ubuntu Feisty

Thanks to the article on hocuspok.us, it was very easy to set up postgresql-8.2 running on ubuntu.

Installation

Install postgresql

sudo apt-get install postgresql-8.2 postgresql-client-8.2

For administration we install pgadmin3

sudo apt-get install pgadmin3 pgadmin3-data

Set database admin account

Alter the password for the standard postgres user account (as root)

sudo su
postgres -c psql template1

Inside the database:

template1=# ALTER USER postgres WITH PASSWORD 'new_password';
template1=# \q

/etc/postgresql/8.2/main/postgresql.conf

Assure that the server is accessible on localhost

sudo gedit /etc/postgresql/8.2/main/postgresql.conf
listen_addresses = 'localhost'
password_encryption = on

/etc/postgresql/8.2/main/pg_hba.conf

Security settings

sudo gedit /etc/postgresql/8.2/main/pg_hba.conf
# Allow any user on the local system to connect to any database under
# any user name using Unix-domain sockets (the default for local
# connections).
#
# Database administrative login by UNIX sockets
local all all trust

# TYPE DATABASE USER CIDR-ADDRESS METHOD

# "local" is for Unix domain socket connections only
local all all md5

# IPv4 local connections:
host all all 127.0.0.1/32 md5
# IPv6 local connections:
host all all ::1/128 md5

# Connections for all PCs on the subnet
#
# TYPE DATABASE USER IP-ADDRESS IP-MASK METHOD
# e.g. host all all 192.168.0.0/24 md5
# e.g. host all all 192.168.0.10 255.255.255.0 md5
host all all [ip address] [subnet mask] md5

Restart postgresql

sudo /etc/init.d/postgresql-8.1 restart

Initial registration with pgAdminIII

Working with pgAdminIII