At the time of this article the default PostgreSQL version for RHEL is 13.20.
PostgreSQL is a modular RHEL package. Module versions can be listed with the following command.
sudo dnf module list postgresql
To enable the PostgreSQL version 16 module, the following command can be used.
dnf module enable postgresql:16
Install PostgreSQL with the following command.
sudo dnf install postgresql-server postgresql
Initialize the database cluster.
sudo postgresql-setup --initdb
To enhance password security, edit /var/lib/pgsql/data/postgresql.conf to use scram-sha-256 instead of md5 for password hashing by adding the following line.
password_encryption = scram-sha-256
In order to start PostgreSQL now and to start it on system boot, execute the following command.
sudo systemctl enable --now postgresql
Connect to the database server and log in as the postgres user.
sudo -u postgres psql
Set the runtime value for password encryption to scram-sha-256.
set password_encryption to 'scram-sha-256';
Set a password for the postgres database user.
alter user postgres with encrypted password 'yoursecurepasswordhere';
Create the restgresql database user with a password. The restgresql provides the means by which the restgresql REST API will communicate with the database.
create user restgresql with encrypted password 'yoursecurepasswordhere';
Create a new database called mysite which will contain the content that which restgresql REST API will serve.
create database mysite;
Change database to mysite
\c mysite
Drop the public schema from mysite as it is no longer needed.
drop schema public cascade;
Create a content schema.
create schema content;
Allow the restgresql user to connect to the new mysite database.
grant connect on database mysite to restgresql;
Allow the restgresql user to use the new content schema.
grant usage on schema content to restgresql;
Allow the restgresql user to select data from any extant tables in the content schema.
grant select on all tables in schema content to restgresql;
Ensure that the restgresql user is able to select from any subsequent tables created in the content schema.
alter default privileges in schema content grant select on tables to restgresql;
Create the tables in the content schema which will store mysite data.
create table "content".games ( game_id int4 NOT NULL, game_path varchar NULL, constraint games_pkey primary key (game_id) ); create table "content".images ( image_id int4 not null, image_file_name varchar not null, image bytea not null, constraint images_image_file_name_key unique (image_file_name), constraint images_pkey primay key (image_id) ); create table "content".post_categories ( post_category_id int4 not null, post_category varchar null, constraint post_categories_pkey primary key (post_category_id) ); create table "content".posts( post_id int4 not null, game_id int4 null, post_category_id int4 null, post_uid varchar null, post_title varchar null, post_subtext varchar null, post_preview_image_url varchar null, post_datetime timestamp default now() null, post_author varchar null, post_markdown_content text null, post_js_resource_key varchar null, constraint posts_pkey primary key (post_id) );
Create a view to streamline querying post data and establish intended data relationships.
create or replace view "content".vw_posts as select p.post_id, p.game_id, p.post_category_id, p.post_uid, p.post_title, p.post_subtext, p.post_preview_image_url, p.post_datetime, p.post_author, p.post_markdown_content, p.post_js_resource_key, g.game_path, pc.post_category from content.posts p left join content.games g using (game_id) left join content.post_categories pc using (post_category_id);
Some queries executed by restgresql require parameters to narrow the result set like prm_image_id. In order to maximize safety, the following functions are used by restgresql to query data in a parameterized manner.
create or replace function "content".fn_get_image_by_id(prm_image_id integer) returns setof content.images language sql as $function$ select i.* from content.images i where i.image_id = prm_image_id $function$; create or replace function "content".fn_get_image_by_filename(prm_image_file_name character varying) returns setof content.images language sql as $function$ select i.* from content.images i where image_file_name = prm_image_file_name $function$; create or replace function "content".fn_get_post_by_id(prm_post_id integer) returns setof content.vw_posts language sql as $function$ select p.* from content.vw_posts p where p.post_id = prm_post_id $function$; create or replace function "content".fn_get_recent_posts(prm_how_many_posts integer) returns setof content.vw_posts language sql as $function$ select p.* from content.vw_posts p order by p.post_datetime desc limit prm_how_many_posts; $function$; create or replace function "content".fn_get_recent_articles(prm_how_many_articles integer) returns setof content.vw_posts language sql as $function$ select p.* from content.vw_posts p where p.post_category = 'article' order by p.post_datetime desc limit prm_how_many_articles; $function$; create or replace function "content".fn_get_recent_projects(prm_how_many_projects integer) returns setof content.vw_posts language sql as $function$ select p.* from content.vw_posts p where p.post_category = 'project' order by p.post_datetime desc limit prm_how_many_projects; $function$;
Populate the default Post Category data. post_categories enable mysite to display different articles in different ways. For instance project posts will be displayed in mysite's project tab and article posts will be displayed under mysites' article tab.
insert into "content".post_categories (post_category_id, post_category) values (1, 'page'), (2, 'resume'), (3, 'project'), (4, 'article');
Populate default posts placeholder data. The posts table contains data which is subsequently rendered into webpages by mysite.
insert into "content".posts (post_id, game_id, post_category_id, post_uid, post_title, post_subtext, post_preview_image_url, post_datetime, post_author, post_markdown_content, post_js_resource_key) values(1, 0, 1, '0', 'Home Page', '', NULL, NOW(), '', 'Home Page Content', NULL), (2, 0, 1, '0', 'Article Page', '', NULL, NOW(), '', '', NULL), (3, 0, 1, '0', 'Project Page', '', NULL, NOW(), '', '', NULL), (4, 0, 1, '0', 'Contact Page', '', NULL, NOW(), '', 'Contact Page Content', NULL), (5, 0, 3, '0', 'Project Test', '', NULL, NOW(), '', 'Project Content', NULL), (6, 0, 4, '0', 'Article Test', '', NULL, NOW(), '', 'Article Content', NULL);
Exit the database server with the following command.
\q
Edit /var/lib/pgsql/data/pg_hba.conf to disable local ident connections and enable restgresql and postgres to connect from localhost with scram-sha-256 password authentication.
# TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only #local all all peer # IPv4 local connections: host all postgres 127.0.0.1/32 scram-sha-256 host all restgresql 127.0.0.1/32 scram-sha-256 #host all all 127.0.0.1/32 ident # IPv6 local connections: host all postgres ::1/128 scram-sha-256 host all restgresql ::1/128 scram-sha-256 #host all all ::1/128 ident # Allow replication connections from localhost, by a user with the # replication privilege. #local replication all peer #host replication all 127.0.0.1/32 ident #host replication all ::1/128 ident
Restart PostgreSQL for the pb_hba.conf changes to take effect.
sudo systemctl restart postgresql.service
Test the connection to ensure that the postgres user can connect.
psql --host=127.0.0.1 --username=postgres --password
Do the same with restgresql
psql --host=127.0.0.1 --username=restgresql --password
restgresql expects SSL certificates which enable it to operate https:// connections.
Create a directory in which to generate the SSL keys.
mkdir -p $HOME/tls && cd $HOME/tls
Certificates for a development environment can be generated with the following commands.
For RSA:2048 use the following command.
openssl req -x509 -out fullchain.pem -keyout privkey.pem -newkey rsa:2048 -nodes -sha256 -subj '/CN=localhost' -extensions EXT -config <( printf "[dn]\nCN=localhost\n[req]\ndistinguished_name = dn\n[EXT]\nsubjectAltName=DNS:localhost\nkeyUsage=digitalSignature\nextendedKeyUsage=serverAuth")
For ECDSA use this command
openssl req -x509 -out fullchain.pem -keyout privkey.pem -newkey ec -pkeyopt 'ec_paramgen_curve:P-256' -nodes -sha256 -subj '/CN=localhost' -extensions EXT -config <( printf "[dn]\nCN=localhost\n[req]\ndistinguished_name = dn\n[EXT]\nsubjectAltName=DNS:localhost\nkeyUsage=digitalSignature\nextendedKeyUsage=serverAuth")
Generate a dh key. This might take awhile.
openssl dhparam -out ssl-dhparams.pem 2048
restgresql will be compiled from source for development. The following libraries and tools are required to build restgresql.
Install the following packages.
sudo dnf install git g++ gcc cmake openssl-devel libpq-devel
Create a directory to clone the restgresql git repository.
mkdir -p $HOME/git/ && cd $HOME/git
Clone the git repository.
git clone https://github.com/ademarest/restgresql.git
Create a build directory. The build directory ensures that the files generated during compilation do not cause clutter.
cd restgresql && mkdir build && cd build
Use cmake to compile restgresql.
cmake ../src/CMakeLists.txt && make -j$(nproc)
A restgresql binary is now located in the build directory. restgresql provides some context for program options with the --help flag.
./restgresql --help
restgresql requires a single configuration file, restgresql.conf to store database connection information and the location of SSL certificates. To simplify testing, use the -c flag in with the $(pwd)/restgresql.conf argument. $(pwd) evaluates to the current bash directory ensuring that the restgresql configuration file will be created with the executable. To simplify the first development test run, restgresql can be run without SSL certificates with the -d flag. -d should not be used in a production environment.
./restgresql -c $(pwd)/restgresql.conf -d
restgresql will ask for a database connection string. This connect string shares the same format as the psql PostgreSQL command line interface.
Please enter your PostgresSQL database connection string: dbname=mysite user=restgresql password=<yoursecurerestgresqlpassword> host=localhost port=5432
Enter the absolute path for the SSL certificate which was generated earlier for localhost.
Please enter the filesystem path to your server's SSL certificate file: /home/youruser/tls/fullchain.pem
Enter the absolute path for the SSL key which was generated earlier for localhost.
Please enter the filesystem path to your server's SSL key file: /home/youruser/tls/privkey.pem
Navigating to localhost:8000/api/posts in a browser will display json output from restgresql.
If restgresql is running in a headless environment it can be tested with the following curl command.
curl http://localhost:8000/api/posts
Executing restgresql without the -d flag enables SSL encryption.
./restgresql -c $(pwd)/restgresql.conf
Navigating to https://localhost:8000/api/posts in a browser will post json will be output from restgresql.
If restgresql is executed in a headless environment it can be tested with the following curl command.
curl --cacert $HOME/tls/fullchain.pem https://localhost:8000/api/posts
Copy the restgresql executable to /usr/bin
sudo cp $HOME/git/restgresql/build/restgresql /usr/bin/
Create the directory /etc/restgresql. This is the default directory where restgresql will store the restgresql.conf configuration file.
mkdir -p /etc/restgresql
A production example with letsencrypt and certbot would be as follows
sudo ./restgresql
"Please enter your PostgresSQL database connection string": dbname=mysite user=restgresql password=<yoursecurerestgresqlpassword> host=localhost port=5432
"Please enter the filesystem path to your server's SSL certificate file": /etc/letsencrypt/live/example.com/fullchain.pem
"Please enter the filesystem path to your server's SSL key file": /etc/letsencrypt/live/example.com/privkey.pem
restgresql can be started via systemd by creating the following .service file, /etc/systemd/system/restgresql.service
[Unit] Description=Restgresql Service After=network.target StartLimitIntervalSec=0 [Service] Type=simple Restart=always RestartSec=1s ExecStart=/bin/restgresql StandardOutput=append:/var/log/restgresql.log StandardError=append:/var/log/restgresql.log [Install] WantedBy=multi-user.target
Start and enable the restgresql service with the following command.
sudo systemctl enable --now restgresql
Node.js must be greater than 18.17.0
List the RHEL stream modules for node.js with the following command.
dnf module list nodejs
Select the desired stream module for node.js. In this case, version 22 was chosen.
dnf module enable nodejs:22
Install node.js with dnf.
sudo dnf install nodejs
Create a directory in which to clone the mysite repository.
mkdir -p $HOME/git/ && cd $HOME/git
Clone the mysite repository with git.
git clone https://github.com/ademarest/mysite.git
Create a config.json file for mysite.
cd mysite && touch config.json
Populate config.json with the following data.
{ "apiServerURI": "https://localhost:8000/", "homePostId": 1, "articlesPostId": 2, "projectsPostId": 3, "contactPostId": 4, "recentArticleNum": 3 }
Install the node.js packages required by mysite.
npm install
Ensure that restgresql is running on the system before proceeding.
As the server is using self-signed TLS certificates for development work, the environment variable NODE_TLS_REJECT_UNAUTHORIZED=0 will be exported. Do not use this environment variable in production.
export NODE_TLS_REJECT_UNAUTHORIZED=0
Build mysite with the following command.
npm run build
Start mysite with the following command.
npm run start
mysite should now be accessible through a web browser. Navigating to localhost:3000 in a web browser will display the about section of mysite.
Accessing via curl from the terminal can be accomplished with the following command.
curl localhost:3000
/etc/system/systemd/mysite.service
[Unit] Description=NodeJS, NextJS public frontend After=network.target Requires=restgresql.service [Service] Type=exec Restart=on-failure RestartSec=10 User=root Group=root WorkingDirectory=/var/www/mysite/ ExecStart=/usr/bin/npm run start StandardOutput=append:/var/log/mysite.log StandardError=append:/var/log/mysite.error [Install] WantedBy=multi-user.target
mysite is designed to be run behind a webserver such as nginx configured to act as a reverse proxy.
Check the available nginx versions for RHEL.
sudo dnf module list nginx
Enable a recent version such as 1.24
dnf module enable nginx:1.24
Install nginx with the following command
sudo dnf install nginx
Create a backup of the existing nginx.conf file.
sudo cp /etc/nginx/nginx.conf /etc/nginx/nginx.conf.bak
Create a directory /etc/nginx/dev_tls. This directory will contain symbolic links to the development SSL files created earlier.
Create the directory in which the SSL symbolic links will be created.
sudo mkdir /etc/nginx/dev_tls
Symbolically link all the SSL files created earlier into the /etc/nginx/dev_tls/ directory.
ln -sf /home/andrew/tls/* /etc/nginx/dev_tls/
SELinux will by default deny access to the SSL files as they are located in a user directory. Enable access with the following command.
sudo semanage boolean --modify --on httpd_read_user_content
Alternate command syntax to achieve the same result
sudo setsebool -P httpd_read_user_content 1
SELinux will by default deny reverse proxy requrests. This is changed with the following command
sudo semanage boolean --modify --on httpd_can_network_connect
Alternate command syntax to achieve the same result
sudo setsebool -P httpd_can_network_connect 1
Edit the nginx configuration file, /etc/nginx/nginx.conf
# For more information on configuration, see: # * Official English Documentation: http://nginx.org/en/docs/ # * Official Russian Documentation: http://nginx.org/ru/docs/ user nginx; worker_processes auto; error_log /var/log/nginx/error.log; pid /run/nginx.pid; # Load dynamic modules. See /usr/share/doc/nginx/README.dynamic. include /usr/share/nginx/modules/*.conf; events { worker_connections 1024; } http { log_format main '$remote_addr - $remote_user [$time_local] "$request" ' '$status $body_bytes_sent "$http_referer" ' '"$http_user_agent" "$http_x_forwarded_for"'; access_log /var/log/nginx/access.log main; sendfile on; tcp_nopush on; tcp_nodelay on; keepalive_timeout 65; types_hash_max_size 4096; ssl_protocols TLSv1.2 TLSv1.3; ssl_prefer_server_ciphers on; ssl_session_timeout 5m; ssl_ciphers "EECDH+AESGCM:EDH+AESGCM:AES256+EECDH:AES256+EDH"; ssl_prefer_server_ciphers on; ssl_certificate /etc/nginx/dev_tls/fullchain.pem; ssl_certificate_key /etc/nginx/dev_tls/privkey.pem; ssl_dhparam /etc/nginx/dev_tls/ssl-dhparams.pem; gzip on; gzip_types text/plain application/json; include /etc/nginx/mime.types; default_type application/octet-stream; # Load modular configuration files from the /etc/nginx/conf.d directory. # See http://nginx.org/en/docs/ngx_core_module.html#include # for more information. include /etc/nginx/conf.d/*.conf; }
Create a configuration file for mysite, /etc/nginx/conf.d/mysite.conf
server { server_name localhost; return 301 https://$host$request_uri; } server { server_name localhost; listen 443 ssl default_server; location / { proxy_http_version 1.1; proxy_set_header Connection ""; proxy_set_header Host $host; proxy_set_header X-Real-IP $remote_addr; proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; proxy_set_header Upgrade $http_upgrade; proxy_set_header Connection "upgrade"; proxy_pass http://127.0.0.1:3000; } location /api/ { proxy_http_version 1.1; proxy_set_header Connection ""; proxy_set_header Host $host; proxy_set_header X-Real-IP $remote_addr; proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for; proxy_set_header Upgrade $http_upgrade; proxy_set_header Connection "upgrade"; proxy_pass https://127.0.0.1:8000; } }
Start and enable the nginx server with the following command.
sudo systemctl enable --now nginx
At this point it is possible to access mysite with TLS encryption. Upon navigating to localhost with a web browser, the user will be presented with a potential security risk message due to a self-signed TLS certificate. Upon accepting the unisigned certificate, the browser will display the mysite landing page. Configuration for a development environment is complete.