Aleksey Deryagin coder4web

## debian10_initial_setup.sh
#!/bin/sh

apt-get update && apt-get upgrade

# ------------------------
# INITIAL
# ------------------------
# see https://gist.github.com/coder4web/5fe1bb155d8957558911
dpkg-reconfigure locales
date

## openssl11_build.sh
# https://github.com/openssl/openssl/blob/master/INSTALL.md
# https://github.com/openssl/openssl/blob/master/NOTES.UNIX
# https://www.openssl.org/docs/man1.1.1/

# reqs
#sudo yum groupinstall "Development Tools"
#sudo apt-get install build-essential cmake gcc libssl-dev

cd /usr/local/src
wget https://www.openssl.org/source/openssl-1.1.1g.tar.gz

## nginx_self_signed_ecc.sh
#!/bin/sh

# Private key
openssl ecparam -genkey -name secp384r1 -out nginx.ec.key
openssl ec -in nginx.ec.key -text -noout

# CSR
openssl req -new -sha256 -key nginx.ec.key -subj "/CN=devops/C=BM/ST=Bermudian/L=Bermudian/O=Org/OU=IT" -out nginx.ec.csr
#openssl req -in nginx.ec.csr -text -noout
openssl req -in nginx.ec.csr -text -noout | grep -i "Signature.*SHA256" && echo "All is well"

## pgbouncer_centos8.sh
# https://www.pgbouncer.org/install.html
# sudo dnf install pgbouncer
# Problem: package pgbouncer requires python-psycopg2, but none of the providers can be installed
# https://www.pgbouncer.org/install.html#building

sudo dnf install libevent libevent-devel

# https://www.pgbouncer.org/downloads/
wget -c https://www.pgbouncer.org/downloads/files/1.12.0/pgbouncer-1.12.0.tar.gz
tar xvfz pgbouncer-1.12.0.tar.gz

## postgresql_install.sql
# RHEL / CentOS 8
# https://www.postgresql.org/download/linux/redhat/
# https://www.postgresql.org/docs/current/runtime.html
dnf list installed |grep postgresql
dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
dnf install postgresql12 postgresql12-server

# Fedora 31
dnf install https://download.postgresql.org/pub/repos/yum/reporpms/F-31-x86_64/pgdg-fedora-repo-latest.noarch.rpm
dnf install postgresql12 postgresql12-server

## mysql_monit.sql
-- all databases sizes
SELECT table_schema AS "Database",
ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
FROM information_schema.TABLES
GROUP BY table_schema;

-- sizes of all of the tables in a specific database
SELECT table_name AS "Table",
ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
FROM information_schema.TABLES

## postgrespro.sh
# @see https://postgrespro.ru/docs/postgrespro/11/binary-installation-on-linux.html

#-------------------------------
# Debian 8/9/10:
#-------------------------------
sudo apt-get install lsb-release
sudo sh -c 'echo "deb http://repo.postgrespro.ru/pgpro-11/debian $(lsb_release -cs) main" > /etc/apt/sources.list.d/postgrespro.list'
wget --quiet -O - http://repo.postgrespro.ru/pgpro-11/keys/GPG-KEY-POSTGRESPRO | sudo apt-key add -
sudo apt-get update

## osm_tile_server_centos7.sh
# Based on next manuals:
# Ubuntu 18.04 LTS: https://switch2osm.org/manually-building-a-tile-server-18-04-lts/
# CentOS 7 - https://www.hyperlearning.ai/en/knowledgebase/blog/centos-7-open-street-map-tile-server
# Updated to latest CentOS / deps packages

# Dependencies
sudo yum install git gdal sqlite geos boost curl libcurl libicu bzip2-devel zlib-devel libxml2-devel python-setuptools proj-devel proj proj-epsg proj-nad libicu-devel gdal-devel sqlite-devel libcurl-devel geos-devel protobuf-devel protobuf-c-devel lua-devel cmake proj boost-thread proj-devel autoconf automake libtool pkgconfig ragel gtk-doc glib2 glib2-devel libtool-ltdl-devel python-devel boost-devel cabextract xorg-x11-font-utils fontconfig perl-DBD-Pg mesa-libGLU-devel
sudo yum install cairo pycairo cairo-devel pycairo-devel freetype freetype-devel harfbuzz harfbuzz-devel harfbuzz-icu libjpeg libjpeg-devel libpng libpng-devel libtiff libtiff-devel libwebp libwebp-devel

sudo mkdir -p /usr/local/src/osm_tile_server

## ubuntu_dualboot.sh
# boot - How can I reinstall GRUB to the EFI partition? - Ask Ubuntu
# https://askubuntu.com/a/831241/19938
# Note : sdX = disk | sdXX = efi partition | sdXXX = system partition
sudo mount /dev/sdXXX /mnt
sudo mount /dev/sdXX /mnt/boot/efi
for i in /dev /dev/pts /proc /sys /run; do sudo mount -B $i /mnt$i; done
sudo chroot /mnt
grub-install /dev/sdX
update-grub

## postgresql_roles.sql
\z

-- Full access
GRANT ALL privileges ON DATABASE db TO admin_user;
GRANT ALL ON schema public TO admin_user;
GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_user;
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO admin_user;

-- Read-only access
CREATE ROLE readonly;
	#!/bin/sh

	apt-get update && apt-get upgrade

	# ------------------------
	# INITIAL
	# ------------------------
	# see https://gist.github.com/coder4web/5fe1bb155d8957558911
	dpkg-reconfigure locales
	date
	# https://github.com/openssl/openssl/blob/master/INSTALL.md
	# https://github.com/openssl/openssl/blob/master/NOTES.UNIX
	# https://www.openssl.org/docs/man1.1.1/

	# reqs
	#sudo yum groupinstall "Development Tools"
	#sudo apt-get install build-essential cmake gcc libssl-dev

	cd /usr/local/src
	wget https://www.openssl.org/source/openssl-1.1.1g.tar.gz
	#!/bin/sh

	# Private key
	openssl ecparam -genkey -name secp384r1 -out nginx.ec.key
	openssl ec -in nginx.ec.key -text -noout

	# CSR
	openssl req -new -sha256 -key nginx.ec.key -subj "/CN=devops/C=BM/ST=Bermudian/L=Bermudian/O=Org/OU=IT" -out nginx.ec.csr
	#openssl req -in nginx.ec.csr -text -noout
	openssl req -in nginx.ec.csr -text -noout \| grep -i "Signature.*SHA256" && echo "All is well"
	# https://www.pgbouncer.org/install.html
	# sudo dnf install pgbouncer
	# Problem: package pgbouncer requires python-psycopg2, but none of the providers can be installed
	# https://www.pgbouncer.org/install.html#building

	sudo dnf install libevent libevent-devel

	# https://www.pgbouncer.org/downloads/
	wget -c https://www.pgbouncer.org/downloads/files/1.12.0/pgbouncer-1.12.0.tar.gz
	tar xvfz pgbouncer-1.12.0.tar.gz
	# RHEL / CentOS 8
	# https://www.postgresql.org/download/linux/redhat/
	# https://www.postgresql.org/docs/current/runtime.html
	dnf list installed \|grep postgresql
	dnf install https://download.postgresql.org/pub/repos/yum/reporpms/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm
	dnf install postgresql12 postgresql12-server

	# Fedora 31
	dnf install https://download.postgresql.org/pub/repos/yum/reporpms/F-31-x86_64/pgdg-fedora-repo-latest.noarch.rpm
	dnf install postgresql12 postgresql12-server
	-- all databases sizes
	SELECT table_schema AS "Database",
	ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)"
	FROM information_schema.TABLES
	GROUP BY table_schema;

	-- sizes of all of the tables in a specific database
	SELECT table_name AS "Table",
	ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
	FROM information_schema.TABLES
	# @see https://postgrespro.ru/docs/postgrespro/11/binary-installation-on-linux.html

	#-------------------------------
	# Debian 8/9/10:
	#-------------------------------
	sudo apt-get install lsb-release
	sudo sh -c 'echo "deb http://repo.postgrespro.ru/pgpro-11/debian $(lsb_release -cs) main" > /etc/apt/sources.list.d/postgrespro.list'
	wget --quiet -O - http://repo.postgrespro.ru/pgpro-11/keys/GPG-KEY-POSTGRESPRO \| sudo apt-key add -
	sudo apt-get update
	# Based on next manuals:
	# Ubuntu 18.04 LTS: https://switch2osm.org/manually-building-a-tile-server-18-04-lts/
	# CentOS 7 - https://www.hyperlearning.ai/en/knowledgebase/blog/centos-7-open-street-map-tile-server
	# Updated to latest CentOS / deps packages

	# Dependencies
	sudo yum install git gdal sqlite geos boost curl libcurl libicu bzip2-devel zlib-devel libxml2-devel python-setuptools proj-devel proj proj-epsg proj-nad libicu-devel gdal-devel sqlite-devel libcurl-devel geos-devel protobuf-devel protobuf-c-devel lua-devel cmake proj boost-thread proj-devel autoconf automake libtool pkgconfig ragel gtk-doc glib2 glib2-devel libtool-ltdl-devel python-devel boost-devel cabextract xorg-x11-font-utils fontconfig perl-DBD-Pg mesa-libGLU-devel
	sudo yum install cairo pycairo cairo-devel pycairo-devel freetype freetype-devel harfbuzz harfbuzz-devel harfbuzz-icu libjpeg libjpeg-devel libpng libpng-devel libtiff libtiff-devel libwebp libwebp-devel

	sudo mkdir -p /usr/local/src/osm_tile_server
	# boot - How can I reinstall GRUB to the EFI partition? - Ask Ubuntu
	# https://askubuntu.com/a/831241/19938
	# Note : sdX = disk \| sdXX = efi partition \| sdXXX = system partition
	sudo mount /dev/sdXXX /mnt
	sudo mount /dev/sdXX /mnt/boot/efi
	for i in /dev /dev/pts /proc /sys /run; do sudo mount -B $i /mnt$i; done
	sudo chroot /mnt
	grub-install /dev/sdX
	update-grub
	\z

	-- Full access
	GRANT ALL privileges ON DATABASE db TO admin_user;
	GRANT ALL ON schema public TO admin_user;
	GRANT ALL ON ALL TABLES IN SCHEMA public TO admin_user;
	GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO admin_user;

	-- Read-only access
	CREATE ROLE readonly;