How to install, configure, create user and database with permissions – PostgreSQL

postgresql

PostgreSQL: The World’s Most Advanced Open Source Relational Database

PostgreSQL is arguably the most advance and powerful opensource enterprise class relational database system. It is the object relational database system and provides the most standard compliant system for the Database designers. It provides the complete support for reliable transactions that is (ACID complaint) where ACID stands for Atomicity, Consistency, Isolation and Durability.

Its advance underlying technology makes it extremely powerful and programmable. Support for concurrency is one of its key feature. It is one of the most important technology you will learn and will greatly affect the way you work with Databases. It is the ultimate RDBM system which will allow you to create complex web apps which works flawlessly even for very large number of users.

1. Download and Install Postgresql from the following link

https://www.postgresql.org/download/

2. Configure

service postgresql initdb
systemctl enable postgresql
systemctl start postgresql

Edit the file /etc/postgresql/8.4/main/pg_hba.conf and replace ident or peer by either md5 or trust, depending on whether you want it to ask for a password on your own computer or not. Then reload the configuration file with:

/etc/init.d/postgresql reload

pg_hba.conf

local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            password  
# IPv6 local connections:
host    all             all             ::1/128                 password

3. Create user and database with permissions

sudo -u postgres psql
postgres=# create database mydb;
postgres=# create user myuser with encrypted password 'mypass';
postgres=# grant all privileges on database mydb to myuser;

How to repair grub bootloader on a dual boot machine with Windows and Linux

Grub 2 typically gets overridden when you install Windows or another Operating System. To make Linux control the boot process, you need Reinstall (Repair/Restore) Grub using a Linux Live CD.

ROOT_DISK='/dev/sda2'
BOOT_DISK='/dev/sda1' # optional, only for EFI
DISK='/dev/sda'

mount $ROOT_DISK /mnt
mount $BOOT_DISK /mnt/boot/efi # optional

for i in /dev /dev/pts /proc /sys /sys/firmware/efi/efivars /run; do sudo mount -B $i /mnt$i; done

chroot /mnt
grub-install $DISK
update-grub

# don't forget to update UUID in /mnt/etc/fstab using blkid

Restricting Access with HTTP Basic Authentication in Apache and Nginx

You can restrict access to your website or some parts of it by implementing a username/password authentication. Usernames and passwords are taken from a file created and populated by a password file creation tool, for example, apache2-utils.

Creating a Password File

sudo htpasswd -c /etc/httpd/.htpasswd admin
or
sudo htpasswd -c /etc/nginx/.htpasswd admin

Create additional user-password pairs. Omit the -c flag because the file already exists

Nginx configuration

server {
    ...
    auth_basic           "Administrator’s Area";
    auth_basic_user_file /etc/nginx/.htpasswd;

    location /public/ {
        auth_basic off;
    }
}

Apache/httpd basic configuration

<VirtualHost *:80>
    ServerAdmin webmaster@localhost
    DocumentRoot /var/www/html
    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined

    <Directory "/var/www/html">
        AuthType Basic
        AuthName "Restricted Content"
        AuthUserFile /etc/httpd/.htpasswd
        Require valid-user
    </Directory>
</VirtualHost>

Apache/httpd with proxypass

<VirtualHost *:80>
    ServerAdmin webmaster@localhost
    ProxyPass / http://localhost:990/
    ErrorLog ${APACHE_LOG_DIR}/error.log
    CustomLog ${APACHE_LOG_DIR}/access.log combined

    <Location />
        AuthType Basic
        AuthName "Restricted Content"
        AuthUserFile /etc/httpd/.htpasswd
        Require valid-user
    </Location>
</VirtualHost>

IP based restriction using Nginx

You can restrict access to certain parts of your website using Nginx’s inbuilt authentication and authorization mechanism based either on your client’s I.P, by prompting for a login prompt or both.

A sample I.P. based authorization configuration would be like:

location /private/ {
allow 192.168.1.1/24;
allow 172.16.0.1/16;
allow 127.0.0.1;
deny all;
}

Use NGINX as a Reverse Proxy

A reverse proxy is a server that sits between internal applications and external clients, forwarding client requests to the appropriate server. While many common applications, such as Node.js, are able to function as servers on their own, NGINX has a number of advanced load balancing, security, and acceleration features that most specialized applications lack. Using NGINX as a reverse proxy enables you to add these features to any application.

Basic Configuration for an NGINX Reverse Proxy

server {
  listen 80;
  listen [::]:80;

  server_name example.com;

  location / {
      proxy_pass http://localhost:3000/;
      proxy_buffering off; # optional
      proxy_set_header X-Real-IP $remote_addr; # optional
      proxy_set_header Host $host; # optional
    
  }
}
sudo nginx -t
sudo nginx -s reload

How to Redirect www URL to non-www and non-www URL to www with Nginx

This tutorial will show you how to redirect a www URL to non-www, e.g. www.example.com to example.com, with Nginx. We will also show you how to redirect in the other direction, from a non-www URL to www.

Configure DNS Records

In order to set up the desired redirect, www.example.com to example.com or vice versa, you must have an A record for each name.

Option 1: Redirect www to non-www

server {
    server_name www.example.com;
    return 301 $scheme://example.com$request_uri;
}
sudo systemctl restart nginx

Option 2: Redirect non-www to www

server {
    server_name example.com;
    return 301 $scheme://www.example.com$request_uri;
}
sudo systemctl restart nginx

How to Redirect HTTP to HTTPS in Nginx

All login credentials transferred over plain HTTP can easily be sniffed by a MITM attacker, but it is not enough to encrypt the login forms. If you are visiting plain HTTP pages while logged in, your session can be hijacked, and not even two-factor authentication will protect you. To protect all info sent between your visitors – which includes you – and your web server, we will redirect all requests that are coming over plain HTTP to the HTTPS equivalent.

Redirect All Sites

server {
    listen 80 default_server;

    server_name _;

    return 301 https://$host$request_uri;
}

Redirect Specific Sites

server {
    listen 80;

    server_name foo.com;
    return 301 https://foo.com$request_uri;
}

Optional: App Configuration

server {
    listen 443 ssl default_server;
    server_name foo.com;
}

server {
    listen 443 ssl;
    server_name bar.com;
}

# and so on...

named bind server – adding multiple TXT records for the same domain

named bind server – adding multiple TXT records for the same domain

_acme-challenge IN      TXT "JoM6ibdd4vWEwiYNj4XRSftAL1XD1w5WNrHFiRs_Hn-U"
_acme-challenge IN      TXT "6EmvVKhLSn40QKDtGFZXyVPYnSIe1OiD1TSyYkE_yd8g"

apache2/httpd – IP based restriction to a virtual host

The Require provides a variety of different ways to allow or deny access to resources. In conjunction with the RequireAll, RequireAny, and RequireNone directives, these requirements may be combined in arbitrarily complex ways, to enforce whatever your access policy happens to be.

example:

<VirtualHost *:80>

ServerName example.net
Documentroot /var/www/html/

<Location />
      Require ip 192.168.0.0/24  10.0.0.2
</Location>

</VirtualHost>