Ubuntu under Postgres installation and configuration

postgres8.4 Installation configuration:
1. Installing postgres8.4
~$ sudo apt-get install PostgreSQL

2. Modify the Super admin Postgres Password:
Run Psql as a system user
~$ sudo-u postgres psql postgres
Modify Postgres password (123456)
postgres=#/password Postgres

3. Back up the modified configuration file:
Backing Up pg_hba.conf files
~$ sudo cp/etc/postgresql/8.4/main/pg_hba.conf/etc/postgresql/8.4/main/pg_hba.conf.original
Backing Up postgresql.conf files
~$ sudo cp/etc/postgresql/8.4/main/postgresql.conf/etc/postgresql/8.4/main/postgresql.conf.original

4. Create a test user (password: 123456):
Set up test user Usertest1,usertest2
Createuser-a-d-p-R usertest1
Createuser-a-d-p-R usertest2

-A does not allow other users to be created
-D does not allow database creation
-P Create password
-R does not allow roles to be created

5. Create a Test database:
Build Dbtest1 owner for Usertest1
Build Dbtest2 owner for Usertest2

6. Modify the configuration file:
Modify Postgresql.conf
Change line #listen_addresses = ' localhost ' modified to listen_addresses = ' * ' To listen on all ports
Change line #password_encryption = on modified to password_encryption = on allow password access

Modify Pg_hba.conf
Host All 127.0.0.1/32 MD5
Host All 192.168.10.0/24 MD5
Host Dbtest1 Usertest1 172.18.67.107/32 MD5
Host Dbtest2 Usertest2 172.18.67.109/32 MD5

Reference Documentation:

Http://wiki.ubuntu.org.cn/PostgreSQL

Http://wiki.ubuntu.org.cn/UbuntuHelp:PostgreSQL

Http://community.itbbs.cn/topic/7011-postgresql%E5%AE%89%E8%A3%85%E5%92%8C%E7%AE%80%E5%8D%95%E4%BD%BF%E7%94%A8 /?s=27253e5f5998aa7f7f10e86e74fa6bf2

To install PostgreSQL:

sudo apt-get install PostgreSQL

Installing pgAdmin3

sudo apt-get install pgadmin3

Start & Close:

sudo/etc/init.d/postgresql-8.4 start

sudo/etc/init.d/postgresql-8.4 stop

To run Psql as a system user:

Sudo-u Postgres psql Postgres

Set Password:

/password Postgres

Create User (Postgres):

Createuser-a-d-p-R user1

-A does not allow other users to be created

-D does not allow database creation

-P Create password

-R does not allow roles to be created

To create a database:

Sudo-u Postgres Createdb MyDB

To activate the database:

Sudo-u Postgres Psql MyDB

Using the Pgadmin graphics client:

Input in Terminal: pdadmin3

Restricting IP access

Restricting user names and databases

To modify a configuration file:

The configuration file is placed in:/etc/postgresql/8.4/main

Manage Users and permissions:

Edit the/etc/postgresql/8.4/main/pg_hba.conf and modify its default configuration

/etc/postgresql/8.4/main/postgresql

sudo gedit/etc/postgresql/8.4/main/postgresql.conf

Change line: #listen_addresses = ' localhost ' modified to: listen_addresses = ' * '

Change line: #password_encryption = on modified to: Password_encryption = On

Reference documents

Http://www.pgsqldb.org/pgsqldoc-cvs/client-authentication.html

http://www.linuxsir.org/main/node/254

Log in with Psql

Sudo-u postgres psql-u user1-d test_user1-h 192.168.10.251

Reference Documentation:

http://bbs.chinaunix.net/viewthread.php?tid=809390

http://blog.csdn.net/wyswlp/article/details/5723327

Ubuntu under Postgres installation and configuration