Procedures to generate CA certificate, server and client certificates signed by CA using Openssl

By Posted in Uncategorized

1. Generate CA Certificate
1.1 Generate private Key
> openssl genrsa -out CA.key 2048

1.2 Generate CA CSR
> openssl req -new -sha256 -key CA.key -out CA.csr -subj “/C=US/ST=Massachusetts/L=Boston/O=My Company/CN=CA CERTIFICATE”

1.3 Generate CA Certificate (10 years)
> openssl x509 -signkey CA.key -in CA.csr -req -days 3650 -out CA.pem

2. Generate Server Certificate signed by CA
2.1 Generate private Key
> openssl genrsa -out ServerCert_signedByCA.key 2048

2.2 Create Server CSR
> openssl req -new -sha256 -key ServerCert_signedByCA.key -out ServerCert_signedByCA.csr -subj “/C=US/ST=Massachusetts/L=Boston/O=My  Company/CN=lab.mycompany$

2.3 Generate Server Certificate
> openssl x509 -req -in ServerCert_signedByCA.csr -CA CA.pem -CAkey CA.key -CAcreateserial -out ServerCert_signedByCA.crt -days 3650 -sha256

2.4 View Certificate
> openssl x509 -text -noout -in ServerCert_signedByCA.crt

3. Generate Client Certificate signed by CA
3.1 Generate private Key
> openssl genrsa -out ClientCert_signedByCA.key 2048

3.2 Create Client CSR
> openssl req -new -sha256 -key ClientCert_signedByCA.key -out ClientCert_signedByCA.csr -subj “/C=US/ST=Massachusetts/L=Boston/O=My Company/CN=client”

3.3 Generate Client Certificate
> openssl x509 -req -in ClientCert_signedByCA.csr -CA CA.pem -CAkey CA.key -CAcreateserial -out ClientCert_signedByCA.crt -days 3650 -sha256

3.4 #View Certificate
> openssl x509 -text -noout -in ClientCert_signedByCA.crt

 

Upgrade Ubuntu

By Posted in Uncategorized
  1. Using Ubuntu “Software-upgrader” tool
    • start the tool from Ubuntu search
    • set the update/upgrade policy
    • when alerted, do upgrade.
    • unfortunately, if there are software in the upgrade list not upgraded for whatever reasons, you won’t get to the place to upgrade the system.
  2. Using shell commands 
    • $ sudo apt-get update
    • $ sudo apt-get upgrade
    • $ sudo do-release-upgrade

Install PostgreSQL, PostGIS amd PgAdmin — A verified procedure

By Posted in PostGIS, PostgreDB, Uncategorized

Installing PostgreSQL, PostGIS and PgAdmin bundle is very typical and popular way of using these database tools. In the past years of work involving these tools, I have repeatedly gone through the whole process a number of times, initially installed them partially from source codes and partially from package distribution and then went through the relatively well described and accurate procedures in the PostGIS official user WiKi. To really make every component installed, configured and finally working, this procedure still missed some key steps. Below is a procedure I finally figured out based on my study. The most of part of the procedure are directly copied from the PostGIS official user WiKi and therefore I’d like to greatly thank and credit the original authors.

1. Verify your Ubuntu version

sudo lsb_release -a

2. Add Respository to sources.list

a. For vivid (15.04) PostgreSQL 9.4 is already latest on launchpad / regular distro so no need to add apt.postgresql.org to list

b. For trusty (14.04)

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt trusty-pgdg main" >> /etc/apt/sources.list'

c. For precise (12.04)

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ precise-pgdg main" >> /etc/apt/sources.list'

3. Add Keys

wget --quiet -O - http://apt.postgresql.org/pub/repos/apt/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update

4. Install

a. For 14.04 / 15.04 (Trusty, Vivid)

sudo apt-get install postgresql-9.4-postgis-2.1 pgadmin3 postgresql-contrib-9.4

b. For 12.04 thru 13.02

sudo apt-get install postgresql-9.4-postgis pgadmin3 postgresql-contrib

5. If you want to install and use pgRouting

sudo apt-add-repository -y ppa:georepublic/pgrouting
sudo apt-get update
# Install pgRouting package (for Ubuntu 14.04)
sudo apt-get install postgresql-9.4-pgrouting

6. Enable Adminpack
This is needed for pgAdminIII or other admin tools to manage the database. While in terminal, log in to the psql console as “postgres” user:

sudo -u postgres psql

Under the psql console

CREATE EXTENSION adminpack;

You can also enable the PostGIS extension here (or use pgAdminIII as described later):

CREATE EXTENSION postgis;

quite from psql

\q

7. Create new PGSQL user
This is for creating a new database super user to use instead of the default “postgres” user. While in terminal, run:

sudo su - postgres
createuser -d -E -i -l -P -r -s yourUserName

You will be prompted to enter and confirm the password for the newly created user. If you have opened access to clients as described in step 9, you will be asked to enter the password (as set in step 8 below) of super user “postgres” to proceed the process.

exit

to logout as “postgres” user.

8. Set password for super user “postgres”
Initially, password for super user “postgres” is not set and will not be required for psql console access if step 9 below has not be executed. After executing step 9, you will be asked to provide password for “postgres” when using any psql functions such as create new user as described in step 7. So you need to set password for “postgres” before executing step 9 below or otherwise you will not be able to access psql any more (you need to alter the change as described in step 9 to restore psql access via super user “postgres” without password). To set password for default super user “postgres”, in terminal

sudo -u postgres psql postgres

If you want to set the password to “postgres”, for example, do the follow in psql console.

\password postgres
\q

to quit when done.

9. Open Access to Clients
The default installation of postgreSQL only allows direct psql console access via super user “postgres” as described in step 6 (no password is required initially). If you want to allow any client access, such as from pgAdminIII, JDBC client and so on, you need to open the access by modifying the configure file.

sudo nano /etc/postgresql/9.4/main/pg_hba.conf

Modify

local all postgres  peer

to

local all postgres  md5

This will disable the local psql access without password and enable external client access using password (including super user “postgres”). If you want to restore to local psql access without password (you need to do so if you forget your user password and want to change the password), you will then need to alter the above change.

Add

host all all 0.0.0.0/0 md5

to allow access from any hosts, or you can specify a specific address to restrict access to the specific host only.

You may also need to add

hostssl all all 0.0.0.0/0 md5

to allow secured access from any hosts.

You need to restart the postgres to allow above changes to be applied.

sudo service postgresql restart

10. Configure pgAdminIII to connect to the database
Find and execute pgAdminIII in Ubuntu. Click on the “plug” icon to set connection parameters as below

- Name: nickname you want to give your connection
- host: localhost (or the IP address of the server that runs the postgres)
- port: 5432 by default
- maintenance DB: postgres by default
- username: super user "postgres" or whatever you created in step 7 above
- password: super user "postgres" password ("postgres" in the example setting in step 8) or whatever password you chose for the created user

11. Enable postgis extensions on new database using pgAdminIII
The default database created at installation time is “postgres”. You can create new databases using pgAdminIII database creation function (right click on “Databases” and “New Database…” or using the pgAdminIII query window to issue create query.

To create extensions, right-click the extensions node, and click “new extension” and then enable Enable the “postgis” extension, as well as “postgis_topology”, and “postgis_tiger_geocoder”. Alternatively from psql console or pgAdminIII query window, you can do

CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;
CREATE EXTENSION fuzzystrmatch;
CREATE EXTENSION postgis_tiger_geocoder;
#if you installed pgRouting extension, you can do
CREATE EXTENSION pgrouting;

12. Import SHP files using shp2pgsql-gui
Another handy piece of software shp2pgsql-gui tool. This will allow you to quickly connect to your new PostGIS database and import a Shapefile.

Open terminal, and type:

sudo apt-get install postgis

Open the SHP2PGSQL application:

shp2pgsql-gui

Follow the on-screen prompts to load your data.

The above procedures have been verified multiple times and proving working smoothly. Again, I’d like to give thanks and credit to the original authors of the great post that I referenced a lot.

13. Import Database data

In terminal

psql -U database-name -p 5432 -W -h database-server < path/to/database-file.sql

 

Insatll Packages not yet available in Ubuntu Central Repository

By Posted in Linux, Ubuntu, Uncategorized

Often times you need to install the latest version of a package which is not yet available in the Ubuntu central repository. Thus, you will not be able to install the package via “apt-get”, “Ubuntu Software Center”, “Synaptic” etc. methods without customizing your local Ubuntu resource list via adding the so called Personal Package Archives(‘PPAs’) path. Here is a procedure of adding / modifying such this.

Before adding a PPA you should be aware of some of the risks involved. Always remember that PPAs are provided by the community, you should be aware of the possible risks before just adding a PPA.

1. First open the dash by either clicking on the Home button (On the top-left Corner) or pressing the Super Key.
2. Search for ‘Software Center’ and launch the Ubuntu software center.
3. Move the mouse to the top panel where the name of the application is written.
4. Go to the Edit menu and select Software Sources.
5. Switch to the ‘Other Software’ tab.
6. click on add / edit / remove to add / modify / remove a specific PPA path
7. uncheck a PPA path also disable it from all apt operations.
8. you can also add PPA via adding the package specific source list in “/etc/apt/sources.list.d/”. Create the source list file “xxx.list” if not exist. However, you still need to go through above procedure to make sure there is no duplicate / conflict PPA paths for the same package as Ubuntu may already included some default PPA paths.

Example of adding postgres PPA:

PPA path: deb http://apt.postgresql.org/pub/repos/apt/ wily-pgdg main