From IOSN-InWent FOSS GIS & Sahana Training

Contents 1 PostgreSQL Superuser 2 Setup User 3 Create GIS Database 4 Some Troubleshooting

PostgreSQL Superuser

1. In Ubuntu PostgreSQL runs by "postgres" user with blank "password", let'sprotect this username by a password first for security purpose
2. Login tu PostgreSQL terminal as user "postgres":
   $> sudo -u postgres psql template1
3. Add some password for user "postgres":
   alter user postgres with password 'ristek';
4. Close the PostgreSQL terminal:
   \q

Setup User

1. We'll set GRASS using PostgreSQL so we have to set the user
   
2. Open pgadmin from Ubuntu menu: Applications > System Tools > PgAdmin III or from terminal:
   $> pgadmin3
3. Create new server connection with File > Add Server, login as "postgres"
   
4. Select our new server, right click on the "Login Roles"
   
5. Create new roles with superuser previlage
   
6. Disconnect our connection (or we can alo delete). Create new server connection (like we've done before with "postgres" user) using our new user login ("fossgiss" or "instruktur").

Create GIS Database

1. Right click on the Database icon and create new database named "mentawai"
   
2. Right click on the "mentawai" database's language icon, add new language. Add language "plpgsql"
   
3. Still in "mentawai database" add PostGIS functions by loading and executing some queries. Click the "Execute Arbitary SQL Files" icon on the navigation bar (the one with pencil on it). There'll be "PgAdmin Query" window showed off.
4. On "PgAdmin Query" window, access menu: File > Open, load the file "/usr/share/postgresql-8.2-postgis/lwpostgis.sql" and after it has been loaded to the query window.. runs the query using the "Execute Query" button (the one with green play/arrow icon)
5. Do the previous step for "/usr/share/postgresql-8.2-postgis/lwpostgis_upgrade.sql" and "/usr/share/postgresql-8.2-postgis/spatial_ref_sys.sql".
6. Check the Mentawai Database's Table tree, if there were two additional geometry and spatial tables then our database is ready for GIS.
   

Some Troubleshooting

In Debian / Ubuntu sometimes we'll got a problem with this:

psql: FATAL: IDENT authentication failed for user "fossgis"

Why? It happens when we use password authentication than the default IDENT-based auth (it's default in Debian distro of postgres anyway). IDENT will only let you in when you do *not* use -U, ie, your postgres user name is the same as your Unix user name. Change this in pg_hba.conf, and don't forget to SIGHUP or restart the postgres afterwards.

Solution:
Just modify /etc/postgresql/8.2/main/postgrespg_hba.conf like this (change to trust, don't forget the tab)

local all postgres trust
lcoal all fossgis trust
host all all 127.0.0.1/32 trust

On to Next Step: Creating GRASS Database