• Home
  • Services
    • HR Services
      • HR Advisory Services
      • Contract Staffing
      • HR Outsourcing Services
      • Manpower Supply Services
      • Payroll Processing
      • Permanent Placement
      • Recruitment and Placement Services
      • Recruitment Process Outsourcing
      • Staffing Agency Services
    • DBA Support
      • DBA Consultancy Services
      • PostgreSQL Support
    • Website Maintenance
    • Company Registration Services
    • Virtual Office Space Address
  • Company
    • FAQs
    • About Us
    • Contact
  • Locations
  • Blogs
    • Blog
    • Blog – PostgreSQL Support
    • Blog – PostgreSQL Migration
    • Blog – All DB’s
    • Blog – Linux
  • Courses

    About Courses

    • List Of Courses
    • Become an Instructor
    Greenplum

    Greenplum

    $1,500.00
    Read More
    Have any question?
    (+91)8838953252
    ITsupport@rayafeel.com
    Login
    RayaFeeL
    • Home
    • Services
      • HR Services
        • HR Advisory Services
        • Contract Staffing
        • HR Outsourcing Services
        • Manpower Supply Services
        • Payroll Processing
        • Permanent Placement
        • Recruitment and Placement Services
        • Recruitment Process Outsourcing
        • Staffing Agency Services
      • DBA Support
        • DBA Consultancy Services
        • PostgreSQL Support
      • Website Maintenance
      • Company Registration Services
      • Virtual Office Space Address
    • Company
      • FAQs
      • About Us
      • Contact
    • Locations
    • Blogs
      • Blog
      • Blog – PostgreSQL Support
      • Blog – PostgreSQL Migration
      • Blog – All DB’s
      • Blog – Linux
    • Courses

      About Courses

      • List Of Courses
      • Become an Instructor
      Greenplum

      Greenplum

      $1,500.00
      Read More

      Blog

      • Home
      • Blog
      • Blog
      • PostgreSQL SSL Setup

      PostgreSQL SSL Setup

      • Posted by Nijamutheen J
      • Categories Blog
      • Date September 7, 2019
      • Comments 0 comment

      PREREQUEST :
      ============

      1. OpenSSL should be install on both client and server systems
      2. Confirm whether openssl is installed or not .

      nijam@2ndquadrant.in:~ # rpm -qa|grep -i openssl
      openssl-1.0.2j-60.52.1.x86_64
      libopenssl1_0_0-1.0.2j-60.52.1.x86_64
      python-pyOpenSSL-16.0.0-4.17.1.noarch

      3. Checking openSSL version  :

      nijam@2ndquadrant.in:~ # openssl version
      OpenSSL 1.0.2j-fips  26 Sep 2016

      4. We can print the OpenSSL directory with version -d option like below.

      nijam@2ndquadrant.in:~ # openssl version -d
      OPENSSLDIR: "/etc/ssl"
      
      nijam@2ndquadrant.in:~ # openssl version -a
      OpenSSL 1.0.2j-fips  26 Sep 2016
      built on: reproducible build, date unspecified
      platform:
      options:  bn(64,64) rc4(16x,int) des(idx,cisc,16,int) blowfish(idx)
      compiler: cc -I. -I.. -I../include  -g
      OPENSSLDIR: "/etc/ssl"

      5. Collecting the postgres Server details:

      nijam@2ndquadrant.in:/home/nijam> ps -ef|grep -i postgres|grep -v -edle
      nijam   2693  2641  0 21:12 pts/1    00:00:00 grep --color=auto -i postgres
      nijam  19150     1  0 Jun11 ?        00:10:54 /ins_path/10.5/bin/postgres -D /data/nijam
      nijam  19151 19150  0 Jun11 ?        00:00:00 postgres: logger process
      nijam  19153 19150  0 Jun11 ?        00:00:31 postgres: checkpointer process
      nijam  19154 19150  0 Jun11 ?        00:07:15 postgres: writer process
      nijam  19155 19150  0 Jun11 ?        00:03:48 postgres: wal writer process
      nijam  19156 19150  0 Jun11 ?        00:01:19 postgres: autovacuum launcher process
      nijam  19157 19150  0 Jun11 ?        00:00:04 postgres: archiver process   last was 000000010000000100000027
      nijam  19158 19150  0 Jun11 ?        00:03:45 postgres: stats collector process
      nijam  19159 19150  0 Jun11 ?        00:00:01 postgres: bgworker: logical replication launcher

      CONFIGURATION :
      ===============

      This describes how to set up ssl certificates to enable encrypted connections from PgAdmin on some client machine to postgresql on a server machine. The assumption is that postgresql (compiled with ssl support) and openssl are already installed and functional on the server (Linux). PgAdmin is already installed on the client (either Windows or Linux).

      On the server, three certificates are required in the data directory. CentOS default is /var/lib/pgsql/data/:

      1.root.crt (trusted root certificate)
      2.server.crt (server certificate)
      3.server.key (private key)

      connect as root user and go to postgres data directory path then generate SSL certificate form there only .

      Step 1.
      =====

      $ sudo su -
      # cd /data/nijam

      Step 2. Generate a private key (you must provide a passphrase):
      =====

      2ndquadrant.in:/data/nijam # openssl genrsa -des3 -passout pass:gsahdg -out server.key 1024
      Generating RSA private key, 1024 bit long modulus
      ........++++++
      ...++++++
      e is 65537 (0x10001)

      Step 3. Remove the passphrase.
      =======

      2ndquadrant.in:/data/nijam # openssl rsa -in server.key -out server.key
      Enter pass phrase for server.key:
      writing RSA key
      
      Note : Give below inputs if prompt 
      gsahdg   (it is like password you can give whatever you want).

      Step 4. Set appropriate permission and owner on the private key file.
      =====

      # chmod 400 server.key
      # chown nijam.nijam server.key

       

      Step 5. Create the server certificate.
      =====
      -subj is a shortcut to avoid prompting for the info.
      -x509 produces a self signed certificate rather than a certificate request.

      2ndquadrant.in:/data/nijam # openssl req  -nodes -new -x509 -days 3650  -keyout server.key -out server.crt
      Generating a 2048 bit RSA private key
      .....................................................+++
      .......................................+++
      writing new private key to 'server.key'
      -----
      You are about to be asked to enter information that will be incorporated
      into your certificate request.
      What you are about to enter is what is called a Distinguished Name or a DN.
      There are quite a few fields but you can leave some blank
      For some fields there will be a default value,
      If you enter '.', the field will be left blank.
      -----
      Country Name (2 letter code) [AU]:
      State or Province Name (full name) [Some-State]:
      Locality Name (eg, city) []:
      Organization Name (eg, company) [Internet Widgits Pty Ltd]:
      Organizational Unit Name (eg, section) []:
      Common Name (e.g. server FQDN or YOUR name) []:
      Email Address []:
      
      Step 6. Since we are self-signing, we use the server certificate as the trusted root certificate. 
      =====
      2ndquadrant.in:/data/nijam # cp server.crt root.crt

      You’ll need to edit pg_hba.conf. For example:

      # TYPE  DATABASE        USER            ADDRESS                 METHOD
      host    all             all             127.0.0.1/32            trust
      # IPv6 local connections:
      host    all             all             ::1/128                 trust
      #hostssl    all             all            0.0.0.0/0             trust  clientcert=1
      # "local" is for Unix domain socket connections only
      local   all             all                                     trust
      # IPv4 local connections:
      # host    all             all             127.0.0.1/32            pam
      # IPv6 local connections:
      # host    all             all             ::1/128                 pam
      # Allow replication connections from localhost, by a user with the
      # replication privilege.
      local   replication     all                                     peer
      host    replication     all             127.0.0.1/32            pam
      host    replication     all             ::1/128                 pam
      hostssl         DB      DB_writer 53.126.142.211/32        trust clientcert=1
      hostssl         DB      DB_reader 53.126.142.222/32      trust clientcert=1

      ==================(OR)=================
      if you don’t want to use client certificate means you can ignore clientcert=1 , so connection will not authenticate by using client certifcate , its network encryption-decryption only. below is the sample configuration setup.

      # TYPE  DATABASE        USER            ADDRESS                 METHOD
      host    all             all             127.0.0.1/32            trust
      # IPv6 local connections:
      host    all             all             ::1/128                 trust
      
      # "local" is for Unix domain socket connections only
       local   all             all                                     trust
      # IPv4 local connections:
      # host    all             all             127.0.0.1/32            pam
      # IPv6 local connections:
      # host    all             all             ::1/128                 pam
      # Allow replication connections from localhost, by a user with the
      # replication privilege.
      local   replication     all                                     peer
      host    replication     all             127.0.0.1/32            pam
      host    replication     all             ::1/128                 pam
      hostssl         DB      DB_writer 53.126.142.211/32        password
      hostssl         DB      DB_reader 53.126.142.222/32        password
      
      Step 7. You need to edit postgresql.conf to actually activate ssl. 
      ====
      ssl = on
      #ssl_cert_file = 'server.crt'
      #ssl_key_file = 'server.key'
      ssl_ca_file = 'root.crt'

      Step 8. Postgresql server must be restarted.
      =====

      nijam@2ndquadrant.in:/data/nijam> pg_ctl restart $DATA
      waiting for server to shut down.... done
      server stopped
      waiting for server to start....2019-07-03 21:45:14.785 CST [5594] LOG:  listening on IPv4 address "0.0.0.0", port 25084
      2019-07-03 21:45:14.785 CST [5594] LOG:  listening on IPv6 address "::", port 25084
      2019-07-03 21:45:14.792 CST [5594] LOG:  listening on Unix socket "/var/run/postgresql/.s.PGSQL.25084"
      2019-07-03 21:45:14.804 CST [5594] LOG:  listening on Unix socket "/tmp/.s.PGSQL.25084"
      2019-07-03 21:45:14.815 CST [5594] LOG:  redirecting log output to logging collector process
      2019-07-03 21:45:14.815 CST [5594] HINT:  Future log output will appear in directory "log".
       done
      server started

      POSTREQUEST SERVER SIDE :
      ==========================

      Step 1 . Checking postgres SSL .
      =====

      nijam@2ndquadrant.in:/data/nijam> psql -h localhost
      psql (10.5)
      SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
      Type "help" for help.
      
      nijam=#

      Step 2 . Verifying certificate.
      =====

      nijam@2ndquadrant.in:/data/nijam>  openssl verify -CAfile root.crt server.crt
      server.crt: OK
      
      nijam=# show ssl_cert_file;
       ssl_cert_file
      ---------------
       server.crt
      (1 row)
      
      nijam=# show ssl_key_file;
       ssl_key_file
      --------------
       server.key
      (1 row)
      
      nijam=# show ssl_ca_file;
       ssl_ca_file
      -------------
       root.crt
      (1 row)
      
      nijam=# show ssl_crl_file;
       ssl_crl_file
      --------------
      
      (1 row)

      Step 3. Checking SSL mode with Require .
      =====

      nijam@2ndquadrant.in:/data/nijam> psql "postgresql://localhost/?sslmode=require"
      psql (10.5)
      SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
      Type "help" for help.
      
      nijam=#

       

      CLIENT SIDE CONFIGURATION :
      ==========================

      If the server fails to (re)start, look in the postgresql startup log, /var/lib/pgsql/pgstartup.log default for CentOS, for the reason.

      On the client, we need three files. For Windows, these files must be in %appdata%\postgresql\ directory.
      You need to create the directory on the client machine if linux ( mkdir ~/.postgresql )

      • root.crt (trusted root certificate)
      • postgresql.crt (client certificate)
      • postgresql.key (private key)

      Step 1.
      ====
      Generate the the needed files on the server machine, and then copy them to the client. We’ll generate the needed files in the /tmp/directory.
      First create the private key postgresql.key for the client machine, and remove the passphrase.

      2ndquadrant.in:~ # openssl genrsa -des3 -passout pass:gsahdg -out /tmp/postgresql.key 1024
      Generating RSA private key, 1024 bit long modulus
      .++++++
      ...++++++
      e is 65537 (0x10001)
      
      2ndquadrant.in:~ # openssl rsa -in /tmp/postgresql.key -passout pass:gsahdg -out /tmp/postgresql.key
      Enter pass phrase for /tmp/postgresql.key:
      writing RSA key
      
      Note : Give gsahdg while prompting

      Step 2.
      =====
      Then create the certificate postgresql.crt. It must be signed by our trusted root (which is using the private key file on the server machine). Also, the certificate common name (CN) must be set to the database user name we’ll connect as.

      2ndquadrant.in:~ # openssl req -new -key /tmp/postgresql.key -out /tmp/postgresql.csr
      You are about to be asked to enter information that will be incorporated
      into your certificate request.
      What you are about to enter is what is called a Distinguished Name or a DN.
      There are quite a few fields but you can leave some blank
      For some fields there will be a default value,
      If you enter '.', the field will be left blank.
      -----
      Country Name (2 letter code) [AU]:
      State or Province Name (full name) [Some-State]:
      Locality Name (eg, city) []:
      Organization Name (eg, company) [Internet Widgits Pty Ltd]:
      Organizational Unit Name (eg, section) []:
      Common Name (e.g. server FQDN or YOUR name) []:
      Email Address []:
      
      Please enter the following 'extra' attributes
      to be sent with your certificate request
      A challenge password []:
      An optional company name []:

      Step 3. Extending SSL validation expiry.
      =====

      2ndquadrant.in:~ # openssl x509 -days 3650 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial
      Signature ok
      
      subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd
      Error opening CA Certificate root.crt
      139936769549968:error:02001002:system library:fopen:No such file or directory:bss_file.c:407:fopen('root.crt','re')
      139936769549968:error:20074002:BIO routines:FILE_CTRL:system lib:bss_file.c:409:
      unable to load certificate

      Reason:
      Reroot.crt file path is missing

      Solution :

      2ndquadrant.in:~ # cd /data/nijam/
      2ndquadrant.in:/data/nijam # openssl x509 -days 3650 -req -in /tmp/postgresql.csr -CA root.crt -CAkey server.key -out /tmp/postgresql.crt -CAcreateserial
      Signature ok
      subject=/C=AU/ST=Some-State/O=Internet Widgits Pty Ltd
      Getting CA Private Key

      Copy the three files we created from the server /tmp/ directory to the client machine.
      Copy the trusted root certificate root.crt from the server machine to the client machine (for Windows pgadmin %appdata%\postgresql\ or for Linux pgadmin ~/.postgresql/). Change the file permission of postgresql.key to restrict access to just you (probably not needed on Windows as the restricted access is already inherited). Remove the files from the server /tmp/ directory.
      (After copying the three files from the server (/tmp/{postgresql.key,postgresql.crt,root.crt}) to the client machine (into directory ~/.postgresql/), you’ll need to set the permission of the key to not world readable: chmod 0400 ~/.postgresql/postgresql.key
      On windows, permissions in the are handled automatically for you.)

      ==========================END==================

      Basic Issues while configuring SSL certificate :

      ISSUES-1 :

      $ psql "postgresql://localhost/?sslmode=verify-ca"
      psql: root certificate file "/home/nijam/.postgresql/root.crt" does not exist
      Either provide the file or change sslmode to disable server certificate verification.

      Solution :

      $ cp /data/nijam/root.crt /home/nijam/.postgresql/
      nijam@2ndquadrant.in:/home/nijam> mkdir /home/nijam/.postgresql/
      
      $ psql "postgresql://localhost/?sslmode=verify-ca"
      psql (10.5)
      SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
      Type "help" for help.

       

      ISSUES-2 :

      In client side should be change permissions as

      chmod 0600 .postgresql/postgresql.key
      
      from client 53.126.142.211 :
      password_encryption = md5 
      psql -h 2ndquadrant.in -p 5432 -U DB_writer -d DB

      check localdomain account.

      For server side :

      chmod 400 server.crt
      chown nijam.nijam server.crt
      chmod 400 root.crt
      chown nijam.nijam root.crt

      Some basic notes of SSL :
      https://discuss.tutorialdba.com/1158/enable-openssl-postgressql-server

      Tag:postgres, postgresql, ssl

      • Share:
      Nijamutheen J
      Nijamutheen J

      Nijamutheen J 7+ years of experience in PostgreSQL, Linux admin , web hosting - apache server , Oracle ,mySQL, Mriadb, vertica DB & Server security administrator

      My updated resume is https://www.tutorialdba.com/p/hi-i-have-4.html

      Previous post

      How to DELETE current XLOG / WAL LOG in postgresql database ?
      September 7, 2019

      Next post

      Postgres user creation and restrict DDL & database access
      September 13, 2019

      Leave A Reply

      You must be logged in to post a comment.

      Connect with



      Search

      ADVERTISEMENT

      Latest Posts

      Tips to Choose the Best Serviced Office for Your Business
      24May2022
      What key considerations do you need to keep in mind when choosing a new serviced office to house your business?
      24May2022
      The Benefits of Coworking
      24May2022
      The Long-Term Impact of Coworking
      24May2022
      Are you spending more money than you earn? Outsource with Ease and Move When You’re Ready
      24May2022
      PostgreSQL

      PostgreSQL

      $800.00 $500.00
      Greenplum

      Greenplum

      $1,500.00
      Oracle Database

      Oracle Database

      $350.00
      2ndquadrant.in

      (+91) 8838953252

      ITsupport@rayafeel.com

      Company

      • About Us
      • Our Team

      COURSES

      • List Of Course
      • Become An Instructor

      Support

      • DBA Support
      • Consultancy Services

      Recommend

      • Login
      • Contact Us

      IT Services by rayafeel.com. Powered by Rayafeel Technologies Pvt Ltd.

      • Privacy
      • Terms

      Become An Instructor?

      Join thousand of instructors and earn money hassle free!

      Get Started Now

      Connect with

      Login with Google Login with Twitter Login with Linkedin Login with Windowslive Login with Yahoo

      Login with your site account

      Connect with



      Lost your password?