Advanced Postgis Topic

http://www.bostongis.com/PrinterFriendly.aspx?content_name=postgis_tut01

PG SCHEMA

http://www.mail-archive.com/postgis-users@postgis.refractions.net/msg02169.html
http://www.paolocorti.net/2008/01/30/installing-postgis-on-ubuntu/


http://postgis.refractions.net/pipermail/postgis-users/2007-April/015206.html

Enabling Remote Access pgsql

If you wish to enable remote access, modify the cluster settings. The configuration files for each cluster are in the directory /etc/postgresql/version/clustername/, e.g. /etc/postgresql/8.1/main/.
Edit the file postgresql.conf, and remove the comment marker on the line for the listen_addresses setting, so that it reads:

listen_addresses = '*'

Open the file pg_hba.conf:

# nano /etc/postgresql/8.1/main/pg_hba.conf

To permit users to connect from remote systems on your network with any role, add this line:

host all all 192.168.1.0/24  md5

Replace 192.168.1.0/24 with the appropriate subnet definition for your network.
This enables md5 authentication, which means that login roles are secured with passwords that PostgreSQL itself stores in an encrypted form, and that PostgreSQL will require a valid password for any remote connection to use a role. After you make this change, ident authentication remains enabled for local logins.
You may, of course, change the local line in pg_hba.conf to disable ident authentication. Make sure that you can actually login to your PostgreSQL cluster with the postgres role and a password first!
To make your changes take effect, restart the service:

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

Once the service restarts, you may access your PostgreSQL service from remote systems, either using tools such as psql or pgAdmin III, or with applications that support SQL. The Debian version of PostgreSQL is configured to automatically protect network connections with SSL, so that all communication between the server and remote clients is encrypted.
By default, the postgres system account on Debian is locked, and you should not unlock it. Cracking tools now try to use postgres, root, and other well-known system account names when they attempt to gain access to UNIX-like operating systems.
Under no circumstance should you enable PostgreSQL ident authentication for any remote access. The ident system cannot safely verify or guarantee the identity of any user on a remote system.

Apache2 et proxypass

On se connecte sous root.
On commence par installer apache

apt-get install apache2

On active le mode proxy et les dépendances nécessaires, puis on recharge :

a2enmod proxy
a2enmod proxy_http
a2enmod proxy_connect
/etc/initd/apache2 force-reload

On ouvre les permissions sur notre domaine

nano /etc/apache2/mods-enabled/proxy.conf
Allow from nc.run

PostGIS in Action is the first book devoted entirely to PostGIS. It will help both new and experienced users write spatial queries to solve real-world problems. For those with experience in more traditional relational databases, this book provides a background in vector-based GIS so you can quickly move to analyzing, viewing, and mapping data. Advanced users will learn how to optimize queries for maximum speed, simplify geometries for greater efficiency, and create custom functions suited specifically to their applications. It also discusses the new features available in PostgreSQL 8.4 and provides tutorials on using additional open source GIS tools in conjunction with PostGIS.

PuTTY for SSH Tunneling to PostgreSQL Server

What SSH Tunneling allows you to do is to tunnel all your traffic to the server thru your SSH connection. It is basically a Virtual Private Network (VPN) using SSH. The basic idea is you map local ports on your pc to remote service ports on the server. When you launch your SSH session, you can then connect with any application e.g. PgAdmin III, MS Access whatever to this remote port via the local port. Instead of specifying the remote server port when setting up your PgAdmin III or MS Access connection, you specify the ip as localhost and port as whatever port you configured to receive traffic via the Tunnel.
For more info clik here