Database Security Best Practices and Solutions

# Check Installed PostgreSQL Packages for On Red Hat Based Systems
rpm -qa | grep postgresql
 
# You can check your rpm your outputs
postgresql13-libs-13.13-1PGDG.rhel9.x86_64
postgresql13-13.13-1PGDG.rhel9.x86_64
postgresql13-server-13.13-1PGDG.rhel9.x86_64
postgresql13-contrib-13.13-1PGDG.rhel9.x86_64
postgresql13-devel-13.13-1PGDG.rhel9.x86_64

/*
This command lists all RPM packages with "postgresql" in their names. 
You should see entries like above this;
postgresql-server
postgresql-libs 
and postgresql-contrib.
*/

# Check direcktly postgresql service if you do not use service that run postgresql
systemctl status postgresql-13.service 

# In case using other service such us patroni, you can check other service
systemctl status patroni.service

# Do not forget patroni default preset is disable
# so you have to enable your service due to the fact that machine boot
# Service running and active
● patroni.service - Runners to orchestrate a high-availability PostgreSQL
     Loaded: loaded (/usr/lib/systemd/system/patroni.service; enabled; preset: disabled)
     Active: active (running) since Wed 2024-04-24 14:55:46 +03; 1 month 5 days ago
   Main PID: 1151 (patroni)
      Tasks: 24 (limit: 203328)
     Memory: 16.1G
        CPU: 2d 11h 58min 56.969s
     CGroup: /system.slice/patroni.service

#directory list command for On Red Hat Based Systems. 

sudo ls -l /pg_data/data/ #you can chechk your directory with sql "SHOW data_directory;"


# You can see like this output
-rw-------  1 postgres postgres   179 Mar 25 23:03 backup_label.old
drwx------ 63 postgres postgres  4096 May 30 09:24 base
-rw-------  1 postgres postgres    52 May 30 00:00 current_logfiles
drwx------  2 postgres postgres  4096 May 30 09:31 global
drwx------  2 postgres postgres    32 Feb  7 15:30 log
-rw-------  1 postgres postgres  1896 May 27 15:53 patroni.dynamic.json
drwx------  2 postgres postgres     6 Feb  7 15:30 pg_commit_ts
drwx------  2 postgres postgres     6 Feb  7 15:30 pg_dynshmem
-rw-------  1 postgres postgres  5996 May  6 14:51 pg_hba.conf
-rw-------  1 postgres postgres  5996 May  7 11:33 pg_hba.conf.backup
-rw-------  1 postgres postgres  1636 Mar 25 23:01 pg_ident.conf
-rw-------  1 postgres postgres  1636 May  7 11:33 pg_ident.conf.backup
drwx------  4 postgres postgres    84 May 30 09:59 pg_logical
drwx------  4 postgres postgres    48 Feb  7 15:30 pg_multixact
drwx------  2 postgres postgres     6 Feb  7 15:30 pg_notify
drwx------  4 postgres postgres    50 May  8 13:43 pg_replslot
drwx------  2 postgres postgres     6 Feb 12 14:42 pg_serial
drwx------  2 postgres postgres     6 Feb 12 14:33 pg_snapshots
drwx------  2 postgres postgres     6 May  7 11:33 pg_stat
drwx------  2 postgres postgres  4096 May 30 10:20 pg_stat_tmp
drwx------  2 postgres postgres    26 May 25 11:50 pg_subtrans
drwx------  2 postgres postgres     6 Feb 27 15:42 pg_tblspc
drwx------  2 postgres postgres     6 Feb 12 14:41 pg_twophase
-rw-------  1 postgres postgres     3 Feb  7 15:30 PG_VERSION
lrwxrwxrwx  1 postgres postgres     7 Feb  7 15:30 pg_wal -> /pg_wal
drwx------  2 postgres postgres    26 Feb 12 14:37 pg_xact
-rw-------  1 postgres postgres    88 Mar 25 23:03 postgresql.auto.conf
-rw-------  1 postgres postgres 28098 Mar 25 23:03 postgresql.base.conf
-rw-------  1 postgres postgres 28098 May  7 11:33 postgresql.base.conf.backup
-rw-r--r--  1 postgres postgres  2475 May 27 15:53 postgresql.conf
-rw-r--r--  1 postgres postgres  2475 May  7 11:33 postgresql.conf.backup
-rw-------  1 postgres postgres   433 May  7 11:33 postmaster.opts
-rw-------  1 postgres postgres    99 May  8 13:43 postmaster.pid

cat /pg_data/data/PG_VERSION
# Output
13

psql --version
# Output
psql (PostgreSQL) 13.13

#Please do not forget to use postgres user
pg_controldata /pg_data/data/ | grep "Data page checksum version"

#Output

Data page checksum version:           1

# If it says 1, checksums are enabled. If it says 0, checksums are not enabled.

lvs #Logical volume size command
# Output
  LV         VG     Attr       LSize    Pool Origin Data%  Meta%  Move Log Cpy%Sync Convert
  pg_data_lv datavg -wi-ao----   <2.25t
  pg_log_lv  datavg -wi-ao----  300.00g
  pg_temp_lv datavg -wi-ao----  300.00g
  pg_wal_lv  datavg -wi-ao---- <172.00g
  homelv     rootvg -wi-ao----   10.00g
  rootlv     rootvg -wi-ao----   20.00g
  swaplv     rootvg -wi-ao----   20.00g
  varlv      rootvg -wi-ao----   10.00g


pvs #Physical volume size command
# Output
  PV         VG     Fmt  Attr PSize   PFree
  /dev/sda2  rootvg lvm2 a--  <99.00g <39.00g
  /dev/sdb1  datavg lvm2 a--   <3.00t      0


lsblk # list block devices
# Output
NAME                  MAJ:MIN RM  SIZE RO TYPE MOUNTPOINTS
sda                     8:0    0  100G  0 disk
├─sda1                  8:1    0    1G  0 part /boot
└─sda2                  8:2    0   99G  0 part
  ├─rootvg-rootlv     253:0    0   20G  0 lvm  /
  ├─rootvg-swaplv     253:1    0   20G  0 lvm  [SWAP]
  ├─rootvg-varlv      253:6    0   10G  0 lvm  /var
  └─rootvg-homelv     253:7    0   10G  0 lvm  /home
sdb                     8:16   0    3T  0 disk
└─sdb1                  8:17   0    3T  0 part
  ├─datavg-pg_wal_lv  253:2    0  172G  0 lvm  /pg_wal
  ├─datavg-pg_log_lv  253:3    0  300G  0 lvm  /pg_log
  ├─datavg-pg_temp_lv 253:4    0  300G  0 lvm  /pg_temp
  └─datavg-pg_data_lv 253:5    0  2.2T  0 lvm  /pg_data
sr0                    11:0    1 1024M  0 rom


#This example did not pass our exam due to logical volume partitioning, but not the physical one

lsblk -o NAME,FSTYPE,FSVER,LABEL,UUID,FSAVAIL,FSUSE%,MOUNTPOINT

NAME                  FSTYPE      FSVER    LABEL UUID                                   FSAVAIL FSUSE% MOUNTPOINT
sda
├─sda1                xfs                        3cd2b87c-ddbe-49b1-a770-80001f33c95d    674.2M    34% /boot
└─sda2                LVM2_member LVM2 001       dptPYc-9Hxh-GD5C-Jaa1-1zhI-E3L4-HV0rHn
  ├─rootvg-rootlv     xfs                        fdd0848b-a7fe-4754-a726-c86253f621ef     10.5G    47% /
  ├─rootvg-swaplv     swap        1              18b5352c-7a6a-4d5b-b9ac-2ad02ab7a489                  [SWAP]
  ├─rootvg-varlv      xfs                        03ea089a-1a53-48b9-b9cc-042a548c47b4      6.9G    31% /var
  └─rootvg-homelv     xfs                        dba1b3ee-c531-4701-a793-c5591569a229      9.9G     1% /home
sdb
└─sdb1                LVM2_member LVM2 001       I2xKSO-853B-eu6f-HMIc-lsbT-f5O1-eRjrma
  ├─datavg-pg_wal_lv  xfs                        03ce8167-0476-4581-9e95-633e3b63ba03     63.7G    63% /pg_wal
  ├─datavg-pg_log_lv  xfs                        e48f8a8e-75ba-41f1-93a8-1978e841a1af    296.3G     1% /pg_log
  ├─datavg-pg_temp_lv xfs                        74438755-9e98-4bfb-8baf-e837d196c545    297.7G     1% /pg_temp
  └─datavg-pg_data_lv xfs                        d8bfaded-6e81-48b2-a32a-2eb6e4f9dadb      1.8T    20% /pg_data
sr0


"""
Check the "FSTYPE" column to see the file system type of the partition. 
If it's encrypted 
it will likely be a type such as "crypto_LUKS" or "crypt" 
instead of a specific file system like "ext4" or "xfs".
"""
#This example did not pass our exam
#We may need to encrypt it manually using tools like LUKS 
#(Linux Unified Key Setup) or other encryption mechanisms supported by your Linux distribution.


"""
To manually encrypt a partition using LUKS (Linux Unified Key Setup)
"""

sudo dnf install cryptsetup
sudo umount /mnt/data
sudo cryptsetup luksFormat /dev/sdXn
#Open the Encrypted Partition
sudo cryptsetup open /dev/sdXn cryptdata
#Create a File System on the Encrypted Partition
sudo mkfs.ext4 /dev/mapper/cryptdata
sudo mkdir /mnt/cryptdata
#Mount the Encrypted Partition
sudo mount /dev/mapper/cryptdata /mnt/cryptdata
#Update /etc/fstab and /etc/crypttab
sudo nano /etc/crypttab
#Add a line like this:
cryptdata /dev/sdXn none luks
sudo nano /etc/fstab
#Edit /etc/fstab to include the mount point:
sudo nano /etc/fstab
# Add a line like this:
/dev/mapper/cryptdata /mnt/cryptdata ext4 defaults 0 2
sudo systemctl start cryptsetup.target
sudo mount -a

if you do not have internet connection you can skip this step 

postgres=# \dx
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description
--------------------+---------+------------+------------------------------------------------------------------------
 btree_gist         | 1.5     | public     | support for indexing common datatypes in GiST
 dblink             | 1.2     | public     | connect to other PostgreSQL databases from within a database
 pg_profile         | 4.3     | profile    | PostgreSQL load profile repository and report builder
 pg_qualstats       | 2.1.0   | public     | An extension collecting statistics about quals
 pg_stat_kcache     | 2.2.2   | public     | Kernel statistics gathering
 pg_stat_statements | 1.8     | public     | track planning and execution statistics of all SQL statements executed
 pg_wait_sampling   | 1.1     | public     | sampling based statistics of wait events
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(8 rows)

postgres=# SELECT * FROM pg_extension;
  oid   |      extname       | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition
--------+--------------------+----------+--------------+----------------+------------+-----------+--------------
  13486 | plpgsql            |       10 |           11 | f              | 1.0        |           |
 707724 | dblink             |       10 |         2200 | t              | 1.2        |           |
 707770 | pg_profile         |       10 |       707723 | f              | 4.3        |           |
  17717 | btree_gist         |       10 |         2200 | t              | 1.5        |           |
  18340 | pg_stat_statements |       10 |         2200 | t              | 1.8        |           |
  18354 | pg_stat_kcache     |       10 |         2200 | t              | 2.2.2      |           |
  18367 | pg_qualstats       |       10 |         2200 | f              | 2.1.0      |           |
  18407 | pg_wait_sampling   |       10 |         2200 | t              | 1.1        |           |
(8 rows)

#you can check your extension two method. If you do not use anymore please drop your extension.

/*
Tablespaces can be used mainly for two purposes: 
(1) “extend” the capacity of the disk where the data directory was created by adding data to a separate disk device,
(2) to improve the performance of some operations and leverage the capacities of different storage devices
*/

\db+ /*Chech your tablespaces*/


CREATE TABLESPACE newspace LOCATION ‘/ssd1/postgres/newspace’;

CREATE DATABASE cbsm TABLESPACE newspace;

/*
The umask must be 0077 or more restrictive for the postgres user. 
Currently it is set to 0022.
*/

su - postgres
unmask # check your defalt umask
#output
0022
echo 'umask 077' >> .bash_profile
source .bash_profile
umask
#output
0077


/*

        Common umask Settings
umask User Access Group Access Other
0000  all         all          all
0002  all         all          read, execute
0007  all         all          none
0022  all         read,execute read, execute
0027  all         read,execute none
0077  all         none         none

*/

ls -ld /pg_data/data/

#output
drwx------ 19 postgres postgres 4096 May 30 00:00 /pg_data/data/


stat -c "%a" /pg_data/data/

#output
700

# The content of the PGDATA must be generated by PostgreSQL itself except custom configuration files.
ls -ln /pg_data/data/ 

#Output
-rw-------  1 26 26   179 Mar 25 23:03 backup_label.old
drwx------ 63 26 26  4096 May 30 09:24 base
-rw-------  1 26 26    52 May 30 00:00 current_logfiles
drwx------  2 26 26  4096 May 30 11:31 global
drwx------  2 26 26    32 Feb  7 15:30 log
-rw-------  1 26 26  1896 May 27 15:53 patroni.dynamic.json
drwx------  2 26 26     6 Feb  7 15:30 pg_commit_ts
drwx------  2 26 26     6 Feb  7 15:30 pg_dynshmem
-rw-------  1 26 26  5996 May  6 14:51 pg_hba.conf
-rw-------  1 26 26  5996 May  7 11:33 pg_hba.conf.backup
-rw-------  1 26 26  1636 Mar 25 23:01 pg_ident.conf
-rw-------  1 26 26  1636 May  7 11:33 pg_ident.conf.backup
drwx------  4 26 26    84 May 30 14:58 pg_logical
drwx------  4 26 26    48 Feb  7 15:30 pg_multixact
drwx------  2 26 26     6 Feb  7 15:30 pg_notify
drwx------  4 26 26    50 May  8 13:43 pg_replslot
drwx------  2 26 26     6 Feb 12 14:42 pg_serial
drwx------  2 26 26     6 Feb 12 14:33 pg_snapshots
drwx------  2 26 26     6 May  7 11:33 pg_stat
drwx------  2 26 26  4096 May 30 15:14 pg_stat_tmp
drwx------  2 26 26    26 May 25 11:50 pg_subtrans
drwx------  2 26 26     6 Feb 27 15:42 pg_tblspc
drwx------  2 26 26     6 Feb 12 14:41 pg_twophase
-rw-------  1 26 26     3 Feb  7 15:30 PG_VERSION
lrwxrwxrwx  1 26 26     7 Feb  7 15:30 pg_wal -> /pg_wal
drwx------  2 26 26    26 Feb 12 14:37 pg_xact
-rw-------  1 26 26    88 Mar 25 23:03 postgresql.auto.conf
-rw-------  1 26 26 28098 Mar 25 23:03 postgresql.base.conf
-rw-------  1 26 26 28098 May  7 11:33 postgresql.base.conf.backup
-rw-r--r--  1 26 26  2475 May 27 15:53 postgresql.conf
-rw-r--r--  1 26 26  2475 May  7 11:33 postgresql.conf.backup
-rw-------  1 26 26   433 May  7 11:33 postmaster.opts
-rw-------  1 26 26    99 May  8 13:43 postmaster.pid

# The pg_hba.conf UNIX permission must be 0640 or 0600, especially when it is stored outside the PGDATA
ls -ld /pg_data/data/pg_hba.conf
#output
-rw------- 1 postgres postgres 5996 May  6 14:51 /pg_data/data/pg_hba.conf

stat -c "%a" /pg_data/data/pg_hba.conf
#output
600

psql -c 'SHOW unix_socket_directories;'
#output
unix_socket_directories
---------------------------
 /var/run/postgresql, /tmp

ls -ld /var/run/postgresql
#output
drwxr-xr-x 2 postgres postgres 80 May  7 11:33 /var/run/postgresql

stat -c "%a" /var/run/postgresql
#output
755

#Remove .psql_history if it exists.

rm -f ~<user>/.psql_history || true

#Use either of the techniques below to prevent it from being created again:
#Set the HISTFILE variable to /dev/null in ~<user>/.psqlrc

cat << EOF >> ~<user>/.psqlrc
\set HISTFILE /dev/null
EOF

#Create ~<user>/.psql_history as a symbolic to /dev/null.

ln -s /dev/null $HOME/.psql_history

#Set the PSQL_HISTORY variable for all users:

sudo echo 'PSQL_HISTORY=/dev/null' >> /etc/environment

You want enough detail to describe when an event of interest has started 
and stopped, what the event is/was, the event's cause, and what the event 
did/is doing to the system. Ideally, the logged information is in a format 
permitting further analysis giving us new perspectives and insight.

show log_destination;

 log_destination
-----------------
 stderr

/* 
stderr log/postgresql.log
csvlog log/postgresql.csv
jsonlog log/postgresql.json  /*required version at least 15*/
*/

alter system set log_destination = 'csvlog';
/* Reload configuretion file */
select pg_reload_conf();

show logging_collector;

 logging_collector
 - - - - - - - - - -
 off

alter system set logging_collector = 'on';
/* Reload configuretion file */
select pg_reload_conf();

show log_directory;

 log_directory
 - - - - - - - - - -
log

alter system set log_directory='/pg_log/log';
/* Reload configuretion file */
select pg_reload_conf();

/*
Please do not forget logical volume is not enough. Also, physcihal volume is required.
*/

show log_filename;
 log_filename
-------------------
postgresql-%a.log

/* Change your file name */ 
alter system set log_filename='postgresql-%Y-%m-%d_%H%M%S.log';

/* Reload configuretion file */
select pg_reload_conf();


show log_filename;
          log_filename
--------------------------------
 postgresql-%Y-%m-%d_%H%M%S.log

show log_file_mode;
 log_file_mode
 - - - - - - - -
 0600

/* if you have correct mode, please alter your mode. Default Value: 0600*/
alter system set log_file_mode = '0600';

/* Reload configuretion file */
select pg_reload_conf();

show log_truncate_on_rotation;

log_truncate_on_rotation
--------------------------
on

/* Default Value:on */ 
alter system set log_truncate_on_rotation = 'on';

/* Reload configuretion file */
select pg_reload_conf();

 show log_rotation_age;
 log_rotation_age
------------------
 1d

/* Default Value: 1d (one day) */ 
alter system set log_rotation_age='1h';

/* Reload configuretion file */
select pg_reload_conf();

show log_rotation_size;
 log_rotation_size
-------------------
 0


/* Default Value: 0 */ 
alter system set log_rotation_size = '1GB';

/* Reload configuretion file */
select pg_reload_conf();

show syslog_facility;
syslog_facility
-----------------
local0


/* Default Value: LOCAL0 */ 
alter system set syslog_facility = 'LOCAL1';

/* Reload configuretion file */
select pg_reload_conf();

show syslog_sequence_numbers;

syslog_sequence_numbers
-------------------------
on

/* Default Value: on */ 
alter system set syslog_sequence_numbers = 'on';

/* Reload configuretion file */
select pg_reload_conf();

show syslog_split_messages;

syslog_split_messages
-----------------------
on

/* Default Value: on */ 
alter system set syslog_split_messages = 'on';

/* Reload configuretion file */
select pg_reload_conf();

show syslog_ident;

 syslog_ident
--------------
 postgres

/* Default Value: postgres */ 
alter system set syslog_ident = 'proddb';

/* Reload configuretion file */
select pg_reload_conf();

/*
• DEBUG5 <-- exceedingly chatty
• DEBUG4
• DEBUG3
• DEBUG2
• DEBUG1
• INFO
• NOTICE
• WARNING <-- default
• ERROR
• LOG
• FATAL
• PANIC <-- practically muteshow syslog_ident;

WARNING is considered the best practice unless indicated otherwise by your
organization's logging policy.
*/

show log_min_messages;

log_min_messages
------------------
warning

/* Default Value: warning */ 
alter system set log_min_messages = 'warning';

/* Reload configuretion file */
select pg_reload_conf();

show log_min_error_statement;

log_min_error_statement
-------------------------
error

/* Default Value: error */ 
alter system set log_min_error_statement = 'error';

/* Reload configuretion file */
select pg_reload_conf();

show debug_print_parse;

debug_print_parse
-------------------
off

/* Default Value: off */ 
alter system set debug_print_parse='off';

/* Reload configuretion file */
select pg_reload_conf();

show debug_print_rewritten;

debug_print_rewritten
-----------------------
off

/* Default Value: off */ 
alter system set debug_print_rewritten = 'off';

/* Reload configuretion file */
select pg_reload_conf();

show debug_print_plan;

debug_print_plan
------------------
off

/* Default Value: off */ 
alter system set debug_print_plan = 'off';

/* Reload configuretion file */
select pg_reload_conf();

show debug_pretty_print;

debug_pretty_print
--------------------
on

/* Default Value: on */ 
alter system set debug_pretty_print = 'on';

/* Reload configuretion file */
select pg_reload_conf();

show log_connections;

log_connections
-----------------
on

/* Default Value: on */ 
alter system set log_connections = 'on';

/* Reload configuretion file */
select pg_reload_conf();

show log_disconnections;

log_disconnections
--------------------
on

/* Default Value: on */ 
alter system set log_disconnections = 'on';

/* Reload configuretion file */
select pg_reload_conf();

Valid values are:
• TERSE
• DEFAULT
• VERBOSE

show log_error_verbosity;

 log_error_verbosity
---------------------
 default

/* Default Value: default */ 
alter system set log_error_verbosity = 'verbose';

/* Reload configuretion file */
select pg_reload_conf();

show log_hostname;

log_hostname
--------------
off

/* Default Value: off */ 
 alter system set log_hostname='off';

/* Reload configuretion file */
select pg_reload_conf();

%a = application name
%u = user name
%d = database name
%r = remote host and port
%h = remote host
%b = backend type
%p = process ID
%P = process ID of parallel group leader
%t = timestamp without milliseconds
%m = timestamp with milliseconds
%n = timestamp with milliseconds (as a Unix epoch)
%Q = query ID (0 if none or not computed)
%i = command tag
%e = SQL state
%c = session ID
%l = session line number
%s = session start timestamp
%v = virtual transaction ID
%x = transaction ID (0 if none)
%q = stop here in non-session processes
%% = '%'

show log_line_prefix;

 log_line_prefix
-----------------
 %m [%p]

/* Default Value: %m [%p]*/ 
alter system set log_line_prefix = '%m [%p]: [%l-1] db=%d,user=%u,app=%a,client=%h ';

/* Reload configuretion file */
select pg_reload_conf();

/*
The log_statement setting specifies the types of SQL statements that are logged. 
Valid values are:
• none (off)
• ddl
• mod
• all (all statements)

ddl logs all data definition statements:
• CREATE
• ALTER
• DROP

mod logs all ddl statements, plus data-modifying statements:
• INSERT
• UPDATE
• DELETE
• TRUNCATE
• COPY FROM
*/

show log_statement;

 log_statement
---------------
 none

/* Default Value: none */
alter system set log_statement='ddl';

/* Reload configuretion file */
select pg_reload_conf();

show log_timezone;

  log_timezone
-----------------
 Europe/Istanbul

/* Default Value: By default, the PGDG packages will set this to match the server's timezone in the
Operating System.*/
alter system set log_timezone = 'GMT +3';

/* Reload configuretion file */
select pg_reload_conf();

show data_directory;

 data_directory
----------------
 /pg_data/data


show log_directory;

 log_directory
---------------
 /pg_log/log

#Firstly, you can instal pgauidit package

dnf -y install pgaudit15_13

alter system set shared_preload_libraries = 'pgaudit';

ALTER SYSTEM SET pgaudit.log TO 'ddl, write';

#Reload configuretion file
select pg_reload_conf();


show pgaudit.log;

 pgaudit.log
-------------
 ddl, write


• READ: SELECT and COPY when the source is a relation or a query.
• WRITE: INSERT, UPDATE, DELETE, TRUNCATE, and COPY when the destination is a relation.
• FUNCTION: Function calls and DO blocks.
• ROLE: Statements related to roles and privileges: GRANT, REVOKE, CREATE/ALTER/DROP ROLE.
• DDL: All DDL that is not included in the ROLE class.
• MISC: Miscellaneous commands, e.g. DISCARD, FETCH, CHECKPOINT, VACUUM

sudo grep postgres /etc/shadow | cut -d: -f1-2
# If this output is not postgres:!<something> then this is a failure.

# Execute the following command:
sudo passwd -l postgres

sudo groupadd dba

useradd -m -G dba kemal

echo '%dba ALL=(postgres) PASSWD: ALL' > /etc/sudoers.d/postgres

chmod 600 /etc/sudoers.d/postgres

su - kemal

sudo -iu postgres

psql -c "\du+ kemal*"

                                    List of roles
 Role name | Attributes |Member of | Description
-----------+------------+-------------------------+-------------
 kemal.oz  | Superuser  | {"DBA"}  |
 

psql -c "ALTER ROLE kemal.oz NOSUPERUSER;"


ALTER ROLE "kemal.oz" NOSUPERUSER;


psql -c "\du+ kemal*"
                                    List of roles
 Role name | Attributes |Member of | Description
-----------+------------+-------------------------+-------------
 kemal.oz  |            | {"DBA"}  |


# Lock Out Accounts if Not Currently in Use (Manual)
ALTER ROLE "kemal.oz" NOLOGIN;

SELECT nspname, proname, proargtypes, prosecdef, rolname, proconfig FROM pg_proc p JOIN pg_namespace n ON p.pronamespace = n.oid JOIN pg_authid a ON a.oid = p.proowner WHERE proname NOT LIKE 'pgaudit%' AND (prosecdef OR NOT proconfig IS NULL);
 nspname |                   proname                   |       proargtypes        | prosecdef | rolname  |                 proconfig
---------+---------------------------------------------+--------------------------+-----------+----------+-------------------------------------------
 public  | dblink_connect_u                            | 25 25                    | t         | postgres |
 public  | dblink_connect_u                            | 25                       | t         | postgres |
 profile | get_diffreport                              | 19 3910 1043 25 16       | f         | postgres | {search_path=profile}
 profile | get_diffreport                              | 19 1043 3910 25 16       | f         | postgres | {search_path=profile}
 profile | get_diffreport                              | 19 3910 3910 25 16       | f         | postgres | {search_path=profile}

/*In the query results, a prosecdef value of 't' on a row indicates that that
function uses privilege elevation. If elevation privileges are utilized which 
are not required or are expressly forbidden by organizational guidance, 
this is a fail.
*/
psql -c "ALTER FUNCTION dblink_connect_u SECURITY INVOKER;"

SELECT proname, proacl FROM pg_proc WHERE proname ='dblink_connect_u';
REVOKE EXECUTE ON FUNCTION dblink_connect_ (integer,boolean) FROM appreader;

select t.schemaname, t.tablename, u.usename,
 has_table_privilege(u.usename, t.tablename, 'select') as select,
 has_table_privilege(u.usename, t.tablename, 'insert') as insert,
 has_table_privilege(u.usename, t.tablename, 'update') as update,
 has_table_privilege(u.usename, t.tablename, 'delete') as delete
from pg_tables t, pg_user u
where t.schemaname not in ('information_schema','pg_catalog');
 schemaname | tablename |   usename   | select | insert | update | delete
------------+-----------+-------------+--------+--------+--------+--------
 public     | a         | repuser     | f      | f      | f      | f
 public     | b         | repuser     | f      | f      | f      | f
 public     | a         | admin       | f      | f      | f      | f
 public     | b         | admin       | f      | f      | f      | f
 public     | a         | replication | f      | f      | f      | f
 public     | b         | replication | f      | f      | f      | f
 public     | a         | postgres    | t      | t      | t      | t
 public     | b         | postgres    | t      | t      | t      | t
 public     | a         | kemal.oz    | f      | t      | t      | f
 public     | b         | kemal.oz    | f      | f      | f      | f



select t.tablename, u.usename,
 has_table_privilege(u.usename, t.tablename, 'select') as select,
 has_table_privilege(u.usename, t.tablename, 'insert') as insert,
 has_table_privilege(u.usename, t.tablename, 'update') as update,
 has_table_privilege(u.usename, t.tablename, 'delete') as delete
from pg_tables t, pg_user u
where t.tablename = 'a'
and u.usename in ('kemal.oz');
 tablename | usename  | select | insert | update | delete
-----------+----------+--------+--------+--------+--------
 a         | kemal.oz | f      | t      | t      | f


REVOKE update, insert  ON TABLE a FROM "kemal.oz";


select t.tablename, u.usename,
 has_table_privilege(u.usename, t.tablename, 'select') as select,
 has_table_privilege(u.usename, t.tablename, 'insert') as insert,
 has_table_privilege(u.usename, t.tablename, 'update') as update,
 has_table_privilege(u.usename, t.tablename, 'delete') as delete
from pg_tables t, pg_user u
where t.tablename = 'a'
and u.usename in ('kemal.oz');
 tablename | usename  | select | insert | update | delete
-----------+----------+--------+--------+--------+--------
 a         | kemal.oz | f      | f      | f      | f


/*
Note: For versions of PostgreSQL prior to version 15, CVE-2018-1058 is applicable and
it is recommended that all privileges be revoked from the public schema for all users on
all databases. If you have upgraded from one of these earlier releases, this CVE is not
fixed for you during an upgrade. You can correct this CVE by issuing:

https://nvd.nist.gov/vuln/detail/CVE-2018-1058
*/

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

SELECT oid, relname, relrowsecurity FROM pg_class WHERE relrowsecurity IS TRUE;
 oid | relname | relrowsecurity
-----+---------+----------------


CREATE TABLE passwd1 (
 user_name text UNIQUE NOT NULL,
 pwhash text,
 uid int PRIMARY KEY,
 gid int NOT NULL,
 real_name text NOT NULL,
 home_phone text,
 extra_info text,
 home_dir text NOT NULL,
 shell text NOT NULL
);


SELECT oid, relname, relrowsecurity FROM pg_class WHERE relname = 'passwd';
  oid  | relname | relrowsecurity
-------+---------+----------------
 26300 | passwd  | f


CREATE USER admin;
CREATE USER bob;
CREATE USER alice;

 INSERT INTO passwd VALUES
 ('admin','xxx',0,0,'Admin1','111-222-3333',null,'/root','/bin/dash');

INSERT INTO passwd VALUES 
('bob','xxx',1,1,'Bob','123-456-7890',null,'/home/bob','/bin/zsh');

INSERT INTO passwd VALUES 
('alice','xxx',2,1,'Alice','098-765-4321',null,'/home/alice','/bin/zsh');

ALTER TABLE passwd ENABLE ROW LEVEL SECURITY;

SELECT oid, relname, relrowsecurity FROM pg_class WHERE relname = 'passwd';
  oid  | relname | relrowsecurity
-------+---------+----------------
 26300 | passwd  | t

CREATE POLICY admin_all ON passwd TO admin USING (true) WITH CHECK (true);

CREATE POLICY user_mod ON passwd FOR UPDATE
 USING (current_user = user_name)
 WITH CHECK (
 current_user = user_name AND
 shell IN ('/bin/bash','/bin/sh','/bin/dash','/bin/zsh','/bin/tcsh')
 );


GRANT SELECT, INSERT, UPDATE, DELETE ON passwd TO admin;

GRANT SELECT
 (user_name, uid, gid, real_name, home_phone, extra_info, home_dir, shell)
 ON passwd TO public;

GRANT UPDATE
 (pwhash, real_name, home_phone, extra_info, shell)
 ON passwd TO public;

set role admin;

table passwd;
 user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info |  home_dir   |   shell
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
 admin     | xxx    |   0 |   0 | Admin1    | 111-222-3333 |            | /root       | /bin/dash
 bob       | xxx    |   1 |   1 | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | xxx    |   2 |   1 | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh


set role alice;

table passwd;
ERROR:  permission denied for table passwd

select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name |  home_phone  | extra_info |  home_dir   |   shell
-----------+-----------+--------------+------------+-------------+-----------
 admin     | Admin1    | 111-222-3333 |            | /root       | /bin/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | Alice     | 098-765-4321 |            | /home/alice | /bin/zsh


update passwd set user_name = 'joe';
ERROR:  permission denied for table passwd

update passwd set real_name = 'Alice Doe';
UPDATE 1

update passwd set real_name = 'John Doe' where user_name = 'admin';
UPDATE 0

select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name |  home_phone  | extra_info |  home_dir   |   shell
-----------+-----------+--------------+------------+-------------+-----------
 admin     | Admin1    | 111-222-3333 |            | /root       | /bin/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | Alice Doe | 098-765-4321 |            | /home/alice | /bin/zsh

update passwd set real_name = 'John Doe' where user_name = 'admin1';
UPDATE 0

 select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name |  home_phone  | extra_info |  home_dir   |   shell
-----------+-----------+--------------+------------+-------------+-----------
 admin     | Admin1    | 111-222-3333 |            | /root       | /bin/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | Alice Doe | 098-765-4321 |            | /home/alice | /bin/zsh


update passwd set shell = '/bin/xx';
ERROR:  new row violates row-level security policy for table "passwd"

delete from passwd;
ERROR:  permission denied for table passwd

insert into passwd (user_name) values ('xxx');
ERROR:  permission denied for table passwd

update passwd set pwhash = 'abc';
UPDATE 1

select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name |  home_phone  | extra_info |  home_dir   |   shell
-----------+-----------+--------------+------------+-------------+-----------
 admin     | Admin1    | 111-222-3333 |            | /root       | /bin/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | Alice Doe | 098-765-4321 |            | /home/alice | /bin/zsh

update passwd set pwhash = 'abc';

select user_name,real_name,home_phone,extra_info,home_dir,shell from passwd;
 user_name | real_name |  home_phone  | extra_info |  home_dir   |   shell
-----------+-----------+--------------+------------+-------------+-----------
 admin     | Admin1    | 111-222-3333 |            | /root       | /bin/dash
 bob       | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | Alice Doe | 098-765-4321 |            | /home/alice | /bin/zsh

# Authorized user
table passwd;
 user_name | pwhash | uid | gid | real_name |  home_phone  | extra_info |  home_dir   |   shell
-----------+--------+-----+-----+-----------+--------------+------------+-------------+-----------
 admin     | xxx    |   0 |   0 | Admin1    | 111-222-3333 |            | /root       | /bin/dash
 bob       | xxx    |   1 |   1 | Bob       | 123-456-7890 |            | /home/bob   | /bin/zsh
 alice     | abc    |   2 |   1 | Alice Doe | 098-765-4321 |            | /home/alice | /bin/zsh

CREATE OR REPLACE VIEW roletree AS
WITH RECURSIVE
roltree AS (
 SELECT u.rolname AS rolname,
        u.oid AS roloid,
        u.rolcanlogin,
        u.rolsuper,
        '{}'::name[] AS rolparents,
        NULL::oid AS parent_roloid,
        NULL::name AS parent_rolname
 FROM pg_catalog.pg_authid u
 LEFT JOIN pg_catalog.pg_auth_members m ON u.oid = m.member
 LEFT JOIN pg_catalog.pg_authid g ON m.roleid = g.oid
 WHERE g.oid IS NULL
 UNION ALL
 SELECT u.rolname AS rolname,
        u.oid AS roloid,
        u.rolcanlogin,
        u.rolsuper,
        t.rolparents || g.rolname AS rolparents,
        g.oid AS parent_roloid,
        g.rolname AS parent_rolname
 FROM pg_catalog.pg_authid u
 JOIN pg_catalog.pg_auth_members m ON u.oid = m.member
 JOIN pg_catalog.pg_authid g ON m.roleid = g.oid
 JOIN roltree t ON t.roloid = g.oid
)
SELECT rolname,
       roloid,
       rolcanlogin,
       rolsuper,
       rolparents,
       parent_roloid,
       parent_rolname
FROM roltree;

SELECT
 r.rolname,
 r.roloid,
 r.rolcanlogin,
 r.rolsuper,
 r.rolparents
FROM roletree r
ORDER BY 1;
          rolname          | roloid | rolcanlogin | rolsuper |       rolparents
---------------------------+--------+-------------+----------+------------------------
 admin                     |  16385 | t           | f        | {}
 admin1                    |  26309 | t           | f        | {}
 alice                     |  26311 | t           | f        | {}
 bob                       |  26310 | t           | f        | {}
 kemal.oz                  |  26299 | t           | f        | {}
 pg_database_owner         |   6171 | f           | f        | {}
 pg_execute_server_program |   4571 | f           | f        | {}
 pg_monitor                |   3373 | f           | f        | {pg_stat_scan_tables}
 pg_monitor                |   3373 | f           | f        | {pg_read_all_settings}
 pg_monitor                |   3373 | f           | f        | {pg_read_all_stats}
 pg_read_all_data          |   6181 | f           | f        | {}
 pg_read_all_settings      |   3374 | f           | f        | {}
 pg_read_all_stats         |   3375 | f           | f        | {}
 pg_read_server_files      |   4569 | f           | f        | {}
 pg_signal_backend         |   4200 | f           | f        | {}
 pg_stat_scan_tables       |   3377 | f           | f        | {}
 pg_write_all_data         |   6182 | f           | f        | {}
 pg_write_server_files     |   4570 | f           | f        | {}
 postgres                  |     10 | t           | t        | {}
 replication               |  16386 | t           | f        | {}
 repuser                   |  16384 | t           | f        | {}
(21 rows)

SELECT
 ro.rolname,
 ro.roloid,
 ro.rolcanlogin,
 ro.rolsuper,
 ro.rolparents
FROM roletree ro
WHERE (ro.rolcanlogin AND ro.rolsuper)
OR
(
 ro.rolcanlogin AND EXISTS
 (
 SELECT TRUE FROM roletree ri
 WHERE ri.rolname = ANY (ro.rolparents)
 AND ri.rolsuper
 )
);
 rolname  | roloid | rolcanlogin | rolsuper | rolparents
----------+--------+-------------+----------+------------
 postgres |     10 | t           | t        | {}

rpm -ivh /a/set_user_14-4.0.1-2.rhel9.x86_64.rpm

patronictl -c /etc/patroni/patroni.yml edit-config

shared_preload_libraries = 'set_user,pgaudit,somethingelse' --Required restart

select * from pg_available_extensions where name = 'set_user';
   name   | default_version | installed_version |                  comment
----------+-----------------+-------------------+--------------------------------------------
 set_user | 4.0.1           |                   | similar to SET ROLE but with added logging


create extension set_user;

select * from pg_available_extensions where name = 'set_user';
   name   | default_version | installed_version |                  comment
----------+-----------------+-------------------+--------------------------------------------
 set_user | 4.0.1           | 4.0.1             | similar to SET ROLE but with added logging

/*
Now, we use GRANT to configure each DBA role to allow it to use the set_user functions.
In the example below, we will configure my db user kemal.oz. (You would do this for each
DBA's normal user role.)
*/
grant execute on function set_user(text) to "kemal.oz","alı.oz"; 
-- "kemal.oz" is an unprivileged user that can run as "alı.oz" through calls to set_user.
-- "alı.oz" is an unprivileged user that can run as "kemal.oz" through calls to set_user.

grant execute on function set_user_u(text) to "kemal.oz";
-- kemal.oz is the privileged (non-superuser) role, which is able to escalate privileges to superuser with Enhanced Logging.

set role "kemal.oz";

select set_user('postgres');
ERROR: switching to superuser not allowed

 select set_user_u('postgres');
 set_user_u
------------
 OK

 select current_user, session_user;
 current_user | session_user
--------------+--------------
 postgres     | postgres

select reset_user();
 reset_user
------------
 OK

select current_user, session_user;
 current_user | session_user
--------------+--------------
 kemal.oz     | kemal.oz


ALTER USER postgres NOLOGIN;
ERROR:  must be superuser to alter superuser roles or change superuser attribute

alter user "kemal.oz" SUPERUSER;

ALTER USER postgres NOLOGIN;

REVOKE name_of_granting_role FROM kemal.oz; -- an example only REVOKE ROLE

select rolname from pg_roles where rolsuper is true;

  rolname
------------
 kemal.oz
 db_monitor
 postgres

GRANT pg_monitor TO "kemal.oz";

Default Value:
The following predefined roles exist in PostgreSQL 13.x:
• pg_read_all_data
Read all data (tables, views, sequences), as if having SELECT rights on those
objects, and USAGE rights on all schemas, even without having it explicitly. This
role does not have the role attribute BYPASSRLS set. If RLS is being used, an
administrator may wish to set BYPASSRLS on roles which this role is GRANTed to.
• pg_write_all_data
Write all data (tables, views, sequences), as if having INSERT, UPDATE, and DELETE
rights on those objects, and USAGE rights on all schemas, even without having it
explicitly. This role does not have the role attribute BYPASSRLS set. If RLS is being
used, an administrator may wish to set BYPASSRLS on roles which this role is
GRANTed to.
• pg_read_all_settings
Read all configuration variables, even those normally visible only to superusers.
• pg_read_all_stats
Read all pg_stat_* views and use various statistics related extensions, even
those normally visible only to superusers.
• pg_stat_scan_tables
Execute monitoring functions that may take ACCESS SHARE locks on tables,
potentially for a long time.
• pg_monitor
Read/execute various monitoring views and functions. This role is a member of
pg_read_all_settings, pg_read_all_stats and pg_stat_scan_tables.
• pg_database_owner
None. Membership consists, implicitly, of the current database owner.
• pg_signal_backend
Signal another backend to cancel a query or terminate its session.
• pg_read_server_files
Allow reading files from any location the database can access on the server with
COPY and other file-access functions.
pg_write_server_files
Allow writing to files in any location the database can access on the server with
COPY and other file-access functions.
• pg_execute_server_program
Allow executing programs on the database server as the user the database runs
as with COPY and other functions which allow executing a server-side program.
• pg_checkpoint
Allow executing the CHECKPOINT command.
• pg_use_reserved_connections
Allow use of connection slots reserved via reserved_connections.
• pg_create_subscription
Allow users with CREATE permission on the database to issue CREATE SUBSCRIPTION.

/*
Note: For versions of PostgreSQL prior to version 15, CVE-2018-1058 is applicable and
it is recommended that all privileges be revoked from the public schema for all users on
all databases. If you have upgraded from one of these earlier releases, this CVE is not
fixed for you during an upgrade. You can correct this CVE by issuing:

https://nvd.nist.gov/vuln/detail/CVE-2018-1058
*/

REVOKE CREATE ON SCHEMA public FROM PUBLIC;

GRANT CREATE ON SCHEMA public TO "kemal.oz"; -- Grant necessary privileges to specific users or roles

/*
Periodically, you should review the current permissions on the public schema 
to ensure that there are no unauthorized changes.
*/

SELECT grantee, privilege_type 
FROM information_schema.role_table_grants 
WHERE table_schema = 'public';

SELECT nspname, usename, has_schema_privilege(usename, nspname, 'CREATE') AS create_priv
FROM pg_namespace, pg_user
WHERE nspname = 'public';

postgresql://postgres:PASSWORD@host

# Check the process or task list if the password is visible.
sudo ps -few

# Check the shell or command history if the password is visible.
History

#Remediation: Use the --password or -W terminal parameter without directly specifying thepassword and then enter the password when prompted.
psql -u <user> --password

SHOW listen_addresses;
-- If * or 0.0.0.0 is returned, this is a failure.

-- allow only postgres user logins locally via UNIX socket
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             postgres                                peer

-- allow all local users via UNIX socket
local   all             all                                     peer

-- allow all local users, via UNIX socket, only if they are connecting to a db named the same as their username
local   samerole        all                                     peer

-- allow only local users, via UNIX socket, who are members of the 'rw' role in the db
local   all             +rw                                     peer

# The use of the "md5" authentication method is vulnerable to packet replay attacks. See line ** in file /pg_data/data/pg_hba.conf.
host    all        all             10.6.128.238/32        md5
# This is fail

host    all        all             10.10.80.238/32        scram-sha-256
# This can be pass our exam

SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%';;
-- Default Value: -1 

   rolname   | rolconnlimit
-------------+--------------
 repuser     |           -1
 admin       |           -1
 replication |           -1
 admin1      |           -1
 bob         |           -1
 alice       |           -1
 kemal.oz    |           -1
 db_monitor  |           -1
 postgres    |           -1
(9 rows)

ALTER USER "kemal.oz" CONNECTION LIMIT 4; -- please set reasonable concurrent connection count

SELECT rolname, rolconnlimit
FROM pg_roles
WHERE rolname NOT LIKE 'pg_%';;
   rolname   | rolconnlimit
-------------+--------------
 repuser     |           -1
 admin       |           -1
 replication |           -1
 admin1      |           -1
 bob         |           -1
 alice       |           -1
 db_monitor  |           -1
 postgres    |           -1
 kemal.oz    |            4

SHOW shared_preload_libraries;
shared_preload_libraries
--------------------------
set_user,pgaudit

SHOW dynamic_library_path;
dynamic_library_path
----------------------
$libdir

vi ${PGDATA}/postgresql.conf
shared_preload_libraries = 'pgaudit,$libdir/passwordcheck,somethingelse'
-- This changes need to restart

create user deneme1 password 'test123';
ERROR:  password is too short
-- password required at least 8 charackter 

connection_timeout = 3s  # example timeout of 3 seconds

#/etc/haproxy/haproxy.cfg
 
timeout connect 3s

psql 'host=10.10.80.68 user=postgres sslmode=require'
# output
psql: error: connection to server at "10.5.56.67", port 5432 failed: server does not support SSL, but SSL was required
# If output like this then this is a failure. Please read article above

psql 'host=10.10.80.68 user=postgres sslmode=require'
# output
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, bits: 256, compression: off)

1. Use TYPE hostssl when administrating the database cluster as a superuser.
2. Use TYPE hostnossl for performance purposes and when DML operations are deemed safe without SSL connections.

Below is a table providing typical subnets for IPv4.

Prefix size Network mask Usable hosts per subnet
/1           128.0.0.0      2,147,483,646
/2           192.0.0.0      1,073,741,822
/3           224.0.0.0      536,870,910
/4           240.0.0.0      268,435,454
/5           248.0.0.0      134,217,726
/6           252.0.0.0      67,108,862
/7           254.0.0.0      33,554,430
Class A                     
/8          255.0.0.0       16,777,214
/9          255.128.0.0     8,388,606
/10         255.192.0.0     4,194,302
/11         255.224.0.0     2,097,150
/12         255.240.0.0     1,048,574
/13         255.248.0.0     524,286
/14         255.252.0.0     262,142
/15         255.254.0.0     131,070
Class B                     
/16         255.255.0.0     65,534
/17         255.255.128.0   32,766
/18         255.255.192.0   16,382
/19         255.255.224.0   8,190
/20         255.255.240.0   4,094
/21         255.255.248.0   2,046
/22         255.255.252.0   1,022
/23         255.255.254.0   510
Class C
/24         255.255.255.0   254
/25         255.255.255.128 126
/26         255.255.255.192 62
/27         255.255.255.224 30
/28         255.255.255.240 14
/29         255.255.255.248 6
/30         255.255.255.252 2
/31         255.255.255.254 0
/32         255.255.255.255 0

\h create role
Command:     CREATE ROLE
Description: define a new database role
Syntax:
CREATE ROLE name [ [ WITH ] option [ ... ] ]

where option can be:

      SUPERUSER | NOSUPERUSER
    | CREATEDB | NOCREATEDB
    | CREATEROLE | NOCREATEROLE
    | INHERIT | NOINHERIT
...

A good practice when creating roles is to add a role with the CREATEROLE and
CREATEDB attributes but not the SUPERUSER. And use this role to add any other
role and database you need to create.

CREATE ROLE admin WITH
  LOGIN
  NOSUPERUSER
  NOINHERIT
  CREATEDB
  CREATEROLE
  NOREPLICATION
  CONNECTION LIMIT 10
  PASSWORD 'test123'
  VALID UNTIL '2024-04-19 17:00:00+03';

create role "user1" in role "admin";
alter user "user1" login;

"""
The role attributes LOGIN, SUPERUSER, CREATEDB, and CREATEROLE can be thought 
of as special privileges,  but they are never inherited as ordinary privileges 
on database objects are.  You must actually SET ROLE to a specific role having 
one of these attributes in order to make use of the attribute. 
"""

SELECT * FROM pg_roles ;
\du

"""
you can manage your pg_hba configuration file your role. Below passed example 
admin role can be login to clusterdb. You can create a lot of role your requriments.
"""


# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             10.80.208.0/24          scram-sha-256
""" Like tihs configurations fail our exam"""

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    clusterdb       admin           10.80.208./21           scram-sha-256
# Like tihs configurations pass our exam

show listen_addresses;
 listen_addresses
------------------
 10.70.12.23
(1 row)
**10.70.12.23: This is server ip4. Please avoid to use * or 0.0.0.0

/*
If You are admin you can edit conf file. Please do not forget other parameter
user cannot be all. If you give this permission. This is fail.
*/

TYPE    DATABASE   USER                 ADDRESS                 METHOD
Host    all        postgres             10.10.120.120/32        scram-sha-256

**10.10.120.120: Admin

TYPE    DATABASE   USER                 ADDRESS                 METHOD
Host    all        postgres             10.10.120.0/24          trust
Host    all        postgres             10.10.120.0/24          md5
-- This is failed

TYPE    DATABASE   USER                 ADDRESS                 METHOD
Host    all        postgres             10.10.120.0/24          scram-sha-256
-- This is passed

Examples;
--------------
1. Weak Authentication
2. SQL injection
3. Privilege abuse
4. Excessive privileges
5. Phishing
6. Inadequate logging and weak auditing
7. Denial of service
8. Exploiting unpatched services
9. Insecure system architecture
10. Inadequate Backup

SELECT name, setting FROM pg_settings WHERE context IN
('backend','superuser-backend') ORDER BY 1;

         name          | setting
-----------------------+---------
 ignore_system_indexes | off
 jit_debugging_support | off
 jit_profiling_support | off
 log_connections       | on
 log_disconnections    | on
 post_auth_delay       | 0

ps -few | grep -E -- '[p]ost.*-[D]'

""" Output"""
postgres 2744612       1  0 Jun10 ?        
00:00:04 /usr/pgsql-14/bin/postgres -D /var/data/ 
--config-file=/var/data/postgresql.conf 
--listen_addresses=10.5.56.67 --port=5432 
--cluster_name=denemek --wal_level=replica 
--hot_standby=on 
--max_connections=100 
--max_wal_senders=10 
--max_prepared_transactions=0 
--max_locks_per_transaction=64 
--track_commit_timestamp=off 
--max_replication_slots=10 
--max_worker_processes=8 
--wal_log_hints=on

SELECT name, setting FROM pg_settings WHERE context = 'postmaster'
ORDER BY 1;
                name                 |            setting
-------------------------------------+--------------------------------
 archive_mode                        | on
 autovacuum_freeze_max_age           | 200000000
 autovacuum_max_workers              | 3
 autovacuum_multixact_freeze_max_age | 400000000
 bonjour                             | off
 bonjour_name                        |
 cluster_name                        | denemek
 config_file                         | /var/data/postgresql.conf
 data_directory                      | /var/data
 data_sync_retry                     | off
 dynamic_shared_memory_type          | posix
 event_source                        | PostgreSQL
 external_pid_file                   |
 hba_file                            | /var/data/pg_hba.conf
 hot_standby                         | on
 huge_pages                          | try
 huge_page_size                      | 0
 ident_file                          | /var/data/pg_ident.conf
 ignore_invalid_pages                | off
 jit_provider                        | llvmjit
 listen_addresses                    | 10.20.23.12
 logging_collector                   | on
 max_connections                     | 100
 max_files_per_process               | 1000
 max_locks_per_transaction           | 64
 max_logical_replication_workers     | 4
 max_pred_locks_per_transaction      | 64
 max_prepared_transactions           | 0
 max_replication_slots               | 10
 max_wal_senders                     | 10
 max_worker_processes                | 8
 min_dynamic_shared_memory           | 0
 old_snapshot_threshold              | -1
 port                                | 5432
 recovery_target                     |
 recovery_target_action              | pause
 recovery_target_inclusive           | on
 recovery_target_lsn                 |
 recovery_target_name                |
 recovery_target_time                |
 recovery_target_timeline            | latest
 recovery_target_xid                 |
 shared_buffers                      | 16384
 shared_memory_type                  | mmap
 shared_preload_libraries            | set_user,$libdir/passwordcheck
 superuser_reserved_connections      | 3
 track_activity_query_size           | 1024
 track_commit_timestamp              | off
 unix_socket_directories             | /var/run/postgresql, /tmp
 unix_socket_group                   |
 unix_socket_permissions             | 0777
 wal_buffers                         | 512
 wal_level                           | replica
 wal_log_hints                       | on
(54 rows)


ps -few | grep -E -- '[p]ost.*-[D]'

""" Output"""
postgres 2744612       1  0 Jun10 ?        
00:00:04 /usr/pgsql-14/bin/postgres -D /var/data/ 
--config-file=/var/data/postgresql.conf 
--listen_addresses=10.5.56.67 --port=5432 
--cluster_name=denemek --wal_level=replica 
--hot_standby=on 
--max_connections=100 
--max_wal_senders=10 
--max_prepared_transactions=0 
--max_locks_per_transaction=64 
--track_commit_timestamp=off 
--max_replication_slots=10 
--max_worker_processes=8 
--wal_log_hints=on

SELECT name, setting FROM pg_settings WHERE context = 'sighup'
ORDER BY 1;
                  name                  |                                                                    setting
----------------------------------------+-----------------------------------------------------------------------------------------------------------------------------------------------
 archive_cleanup_command                |
 archive_command                        | pgbackrest --stanza=cbs_backup archive-push %p
 archive_timeout                        | 0
 authentication_timeout                 | 60
 autovacuum                             | on
 autovacuum_analyze_scale_factor        | 0.1
 autovacuum_analyze_threshold           | 50
 autovacuum_naptime                     | 60
 autovacuum_vacuum_cost_delay           | 2
 autovacuum_vacuum_cost_limit           | -1
 autovacuum_vacuum_insert_scale_factor  | 0.2
 autovacuum_vacuum_insert_threshold     | 1000
 autovacuum_vacuum_scale_factor         | 0.2
 autovacuum_vacuum_threshold            | 50
 autovacuum_work_mem                    | -1
 bgwriter_delay                         | 200
 bgwriter_flush_after                   | 64
 bgwriter_lru_maxpages                  | 100
 bgwriter_lru_multiplier                | 2
 checkpoint_completion_target           | 0.9
 checkpoint_flush_after                 | 32
 checkpoint_timeout                     | 300
 checkpoint_warning                     | 30
 db_user_namespace                      | off
 fsync                                  | on
 full_page_writes                       | on
 hot_standby_feedback                   | off
 krb_caseins_users                      | off
 krb_server_keyfile                     | FILE:/etc/sysconfig/pgsql/krb5.keytab
 log_autovacuum_min_duration            | -1
 log_checkpoints                        | off
 log_destination                        | stderr
 log_directory                          | log
 log_file_mode                          | 0600
 log_filename                           | postgresql-%a.log
 log_hostname                           | off
 log_line_prefix                        | %m [%p]
 log_recovery_conflict_waits            | off
 log_rotation_age                       | 1440
 log_rotation_size                      | 0
 log_timezone                           | US/Eastern
 log_truncate_on_rotation               | on
 max_pred_locks_per_page                | 2
 max_pred_locks_per_relation            | -2
 max_slot_wal_keep_size                 | -1
 max_standby_archive_delay              | 30000
 max_standby_streaming_delay            | 30000
 max_sync_workers_per_subscription      | 2
 max_wal_size                           | 1024
 min_wal_size                           | 80
 pre_auth_delay                         | 0
 primary_conninfo                       | user=repuser passfile=/tmp/pgpass host=10.5.56.67 port=5432 sslmode=prefer application_name=pg_node2 gssencmode=prefer channel_binding=prefer
 primary_slot_name                      | pg_node2
 promote_trigger_file                   |
 recovery_end_command                   |
 recovery_init_sync_method              | fsync
 recovery_min_apply_delay               | 0
 remove_temp_files_after_crash          | on
 restart_after_crash                    | on
 restore_command                        | pgbackrest --stanza=cbs_backup archive-get %f "%p"
 set_user.block_alter_system            | on
 set_user.block_copy_program            | on
 set_user.block_log_statement           | on
 set_user.exit_on_error                 | on
 set_user.nosuperuser_target_allowlist  | *
 set_user.superuser_allowlist           | *
 set_user.superuser_audit_tag           | AUDIT
 ssl                                    | on
 ssl_ca_file                            | /var/data/root.crt
 ssl_cert_file                          | /var/data/server.crt
 ssl_ciphers                            | HIGH:MEDIUM:+3DES:!aNULL
 ssl_crl_dir                            |
 ssl_crl_file                           |
 ssl_dh_params_file                     |
 ssl_ecdh_curve                         | prime256v1
 ssl_key_file                           | server.key
 ssl_max_protocol_version               |
 ssl_min_protocol_version               | TLSv1.2
 ssl_passphrase_command                 |
 ssl_passphrase_command_supports_reload | off
 ssl_prefer_server_ciphers              | on
 stats_temp_directory                   | pg_stat_tmp
 synchronous_standby_names              |
 syslog_facility                        | local0
 syslog_ident                           | postgres
 syslog_sequence_numbers                | on
 syslog_split_messages                  | on
 trace_recovery_messages                | log
 vacuum_defer_cleanup_age               | 0
 wal_keep_size                          | 128
 wal_receiver_create_temp_slot          | off
 wal_receiver_status_interval           | 10
 wal_receiver_timeout                   | 60000
 wal_retrieve_retry_interval            | 5000
 wal_sync_method                        | fdatasync
 wal_writer_delay                       | 200
 wal_writer_flush_after                 | 128
(97 rows)

SELECT name, setting FROM pg_settings WHERE context = 'superuser' ORDER BY 1;
            name             |   setting
-----------------------------+-------------
 allow_in_place_tablespaces  | off
 allow_system_table_mods     | off
 backtrace_functions         |
 commit_delay                | 0
 compute_query_id            | auto
 deadlock_timeout            | 1000
 debug_discard_caches        | 0
 dynamic_library_path        | $libdir
 ignore_checksum_failure     | off
 jit_dump_bitcode            | off
 lc_messages                 | en_US.UTF-8
 lo_compat_privileges        | off
 log_duration                | off
 log_error_verbosity         | default
 log_executor_stats          | off
 log_lock_waits              | off
 log_min_duration_sample     | -1
 log_min_duration_statement  | -1
 log_min_error_statement     | error
 log_min_messages            | warning
 log_parameter_max_length    | -1
 log_parser_stats            | off
 log_planner_stats           | off
 log_replication_commands    | off
 log_statement               | none
 log_statement_sample_rate   | 1
 log_statement_stats         | off
 log_temp_files              | -1
 log_transaction_sample_rate | 0
 max_stack_depth             | 2048
 session_preload_libraries   |
 session_replication_role    | origin
 temp_file_limit             | -1
 track_activities            | on
 track_counts                | on
 track_functions             | none
 track_io_timing             | off
 track_wal_io_timing         | off
 update_process_title        | on
 wal_compression             | off
 wal_consistency_checking    |
 wal_init_zero               | on
 wal_recycle                 | on
 zero_damaged_pages          | off
(44 rows)

SELECT name, setting FROM pg_settings WHERE context = 'user' ORDER BY 1;
                name                 |      setting
-------------------------------------+--------------------
 application_name                    | psql
 array_nulls                         | on
 backend_flush_after                 | 0
 backslash_quote                     | safe_encoding
 bytea_output                        | hex
 check_function_bodies               | on
 client_connection_check_interval    | 0
 client_encoding                     | UTF8
 client_min_messages                 | notice
 commit_siblings                     | 5
 constraint_exclusion                | partition
 cpu_index_tuple_cost                | 0.005
 cpu_operator_cost                   | 0.0025
 cpu_tuple_cost                      | 0.01
 cursor_tuple_fraction               | 0.1
 DateStyle                           | ISO, MDY
 debug_pretty_print                  | on
 debug_print_parse                   | off
 debug_print_plan                    | off
 debug_print_rewritten               | off
 default_statistics_target           | 100
 default_table_access_method         | heap
 default_tablespace                  |
 default_text_search_config          | pg_catalog.english
 default_toast_compression           | pglz
 default_transaction_deferrable      | off
 default_transaction_isolation       | read committed
 default_transaction_read_only       | off
 effective_cache_size                | 524288
 effective_io_concurrency            | 1
 enable_async_append                 | on
 enable_bitmapscan                   | on
 enable_gathermerge                  | on
 enable_hashagg                      | on
 enable_hashjoin                     | on
 enable_incremental_sort             | on
 enable_indexonlyscan                | on
 enable_indexscan                    | on
 enable_material                     | on
 enable_memoize                      | on
 enable_mergejoin                    | on
 enable_nestloop                     | on
 enable_parallel_append              | on
 enable_parallel_hash                | on
 enable_partition_pruning            | on
 enable_partitionwise_aggregate      | off
 enable_partitionwise_join           | off
 enable_seqscan                      | on
 enable_sort                         | on
 enable_tidscan                      | on
 escape_string_warning               | on
 exit_on_error                       | off
 extra_float_digits                  | 1
 force_parallel_mode                 | off
 from_collapse_limit                 | 8
 geqo                                | on
 geqo_effort                         | 5
 geqo_generations                    | 0
 geqo_pool_size                      | 0
 geqo_seed                           | 0
 geqo_selection_bias                 | 2
 geqo_threshold                      | 12
 gin_fuzzy_search_limit              | 0
 gin_pending_list_limit              | 4096
 hash_mem_multiplier                 | 1
 idle_in_transaction_session_timeout | 0
 idle_session_timeout                | 0
 IntervalStyle                       | postgres
 jit                                 | on
 jit_above_cost                      | 100000
 jit_expressions                     | on
 jit_inline_above_cost               | 500000
 jit_optimize_above_cost             | 500000
 jit_tuple_deforming                 | on
 join_collapse_limit                 | 8
 lc_monetary                         | en_US.UTF-8
 lc_numeric                          | en_US.UTF-8
 lc_time                             | en_US.UTF-8
 local_preload_libraries             |
 lock_timeout                        | 0
 logical_decoding_work_mem           | 65536
 log_parameter_max_length_on_error   | 0
 maintenance_io_concurrency          | 10
 maintenance_work_mem                | 65536
 max_parallel_maintenance_workers    | 2
 max_parallel_workers                | 8
 max_parallel_workers_per_gather     | 2
 min_parallel_index_scan_size        | 64
 min_parallel_table_scan_size        | 1024
 parallel_leader_participation       | on
 parallel_setup_cost                 | 1000
 parallel_tuple_cost                 | 0.1
 password_encryption                 | scram-sha-256
 plan_cache_mode                     | auto
 quote_all_identifiers               | off
 random_page_cost                    | 4
 row_security                        | on
 search_path                         | "$user", public
 seq_page_cost                       | 1
 standard_conforming_strings         | on
 statement_timeout                   | 0
 synchronize_seqscans                | on
 synchronous_commit                  | on
 tcp_keepalives_count                | 0
 tcp_keepalives_idle                 | 0
 tcp_keepalives_interval             | 0
 tcp_user_timeout                    | 0
 temp_buffers                        | 1024
 temp_tablespaces                    |
 TimeZone                            | Europe/Istanbul
 timezone_abbreviations              | Default
 trace_notify                        | off
 trace_sort                          | off
 transaction_deferrable              | off
 transaction_isolation               | read committed
 transaction_read_only               | on
 transform_null_equals               | off
 vacuum_cost_delay                   | 0
 vacuum_cost_limit                   | 200
 vacuum_cost_page_dirty              | 20
 vacuum_cost_page_hit                | 1
 vacuum_cost_page_miss               | 2
 vacuum_failsafe_age                 | 1600000000
 vacuum_freeze_min_age               | 50000000
 vacuum_freeze_table_age             | 150000000
 vacuum_multixact_failsafe_age       | 1600000000
 vacuum_multixact_freeze_min_age     | 5000000
 vacuum_multixact_freeze_table_age   | 150000000
 wal_sender_timeout                  | 60000
 wal_skip_threshold                  | 2048
 work_mem                            | 4096
 xmlbinary                           | base64
 xmloption                           | content
(133 rows)

fips-mode-setup --check

#Output
Installation of FIPS modules is not completed.
FIPS mode is disabled.

fips-mode-setup --enable
#Output
Kernel initramdisks are being regenerated. This might take some time.
Setting system policy to FIPS
Note: System-wide crypto policies are applied on application start-up.
It is recommended to restart the system for the change of policies
to fully take place.
FIPS mode will be enabled.
Please reboot the system for the setting to take effect.

fips-mode-setup --check
#Output
FIPS mode is enabled.

openssl version
#Output
OpenSSL 3.0.7 1 Nov 2022 (Library: OpenSSL 3.0.7 1 Nov 2022)

SHOW ssl; 
ssl - - - off (1 row) 

SELECT name, setting, source FROM pg_settings WHERE name = 'ssl';

 name | setting | source 
- - -+ - - - - -+ - - - - - - - - - - 
ssl | off | default 
(1 row)

SELECT * FROM pg_available_extensions WHERE name='pgcrypto';
   name   | default_version | installed_version |         comment
----------+-----------------+-------------------+-------------------------
 pgcrypto | 1.3             |                   | cryptographic functions


CREATE EXTENSION pgcrypto;
--The pgcrypto extension is included with the PostgreSQL 'contrib' package. Although included, it needs to be created in the database

SELECT * FROM pg_available_extensions WHERE name='pgcrypto';
   name   | default_version | installed_version |         comment
----------+-----------------+-------------------+-------------------------
 pgcrypto | 1.3             | 1.3               | cryptographic functions

dependencies:
-------------
ddlx_14
python3-faker

rpm -iv ddlx_14-0.27-1PGDG.rhel9.noarch.rpm
rpm -iv python3-faker-13.3.3-1.el9.noarch.rpm
rpm -iv postgresql_anonymizer_14-1.1.0-1.rhel9.x86_64.rpm

create database test;
ALTER DATABASE test SET session_preload_libraries = 'anon';  -- if this is not apllyied your database please configure global preload libaries
\c test;
CREATE EXTENSION anon CASCADE;
SELECT anon.init();


create database employee;

\c employee;

CREATE TABLE people (
    id INT,
    firstname VARCHAR(10),
    lastname VARCHAR(10),
    phone VARCHAR(15)
);

INSERT INTO people (id, firstname, lastname, phone) VALUES (1, 'Kemal', 'Oz', '9012345678');

create role hr LOGIN;

GRANT SELECT ON people TO hr;

select * from people;
 id | firstname | lastname |   phone
----+-----------+----------+------------
  1 | Kemal     | Oz       | 9012345678


CREATE EXTENSION IF NOT EXISTS anon CASCADE;
SELECT anon.start_dynamic_masking();

CREATE ROLE hr LOGIN;
GRANT SELECT ON people TO hr;
SECURITY LABEL FOR anon ON ROLE hr IS 'MASKED';

SECURITY LABEL FOR anon ON COLUMN people.lastname IS 'MASKED WITH FUNCTION anon.fake_last_name()';
SECURITY LABEL FOR anon ON COLUMN people.phone IS 'MASKED WITH FUNCTION anon.partial(phone,2,$$******$$,2)';


employee=> select * from people;
 id | firstname |  lastname  |   phone
----+-----------+------------+------------
  1 | Kemal     | Wintheiser | 90******78

select * from people;
 id | firstname | lastname |   phone
----+-----------+----------+------------
  1 | Kemal     | Kshlerin | 90******78

CREATE ROLE replication_user WITH REPLICATION PASSWORD 'test123';

    log_autovacuum_min_duration: 5000
    log_checkpoints: true
    log_connections: true
    logging_collector: true 
    log_directory: /pg_log/log
    log_disconnections: true
    log_filename: postgresql-%Y-%m-%d_%H%M%S.log
    log_line_prefix: '%t [%p]: [%l-1] user=%u,db=%d,host=%h,app=%a '
    log_rotation_age: 1d
    log_lock_waits: true
    log_min_duration_statement: 5000 # Logs all statements, including those with 5000s duration 
    log_statement: ddl
    log_temp_files: 0

archive_command: pgbackrest --stanza=cbs_backup archive-push %p
archive_mode: true
archive_timeout: 60

/pg_data
/pg_temp
/pg_log
/pg_wal

### 8.3 Ensure Miscellaneous Configuration Settings Are Correct (Manual)

1. **Review Security Policies and Procedures**:
   - Ensure your organization's security policies and procedures are up-to-date.
   - Verify that all security policies are being enforced consistently across all systems.

2. **Check for Default Settings**:
   - Identify and change any default settings, especially those related to security, to customized and secure values.
   - Disable or remove any unnecessary default accounts or services.

3. **Audit and Logging**:
   - Ensure that logging is enabled for all critical systems and applications.
   - Verify that log data is being monitored, analyzed, and stored securely.
   - Implement log rotation and retention policies.

4. **Review User Accounts and Permissions**:
   - Conduct regular audits of user accounts and permissions.
   - Ensure that users have the minimum necessary permissions (principle of least privilege).
   - Remove or disable inactive or unnecessary accounts promptly.

5. **Network Configuration**:
   - Review firewall rules and access control lists (ACLs) to ensure they are configured correctly and are up-to-date.
   - Check for any open ports and services that are not required and close them.
   - Ensure that network devices have strong, unique passwords and that management interfaces are secured.

6. **Update and Patch Management**:
   - Verify that all systems and applications are running the latest security patches and updates.
   - Implement a regular patch management process.

7. **Application Security**:
   - Review application configurations to ensure they are secure.
   - Ensure that applications are using secure protocols (e.g., HTTPS instead of HTTP).
   - Conduct regular security assessments and code reviews.

8. **Data Protection**:
   - Ensure that sensitive data is encrypted at rest and in transit.
   - Verify that data backup processes are in place and that backups are stored securely.
   - Implement data loss prevention (DLP) measures.

9. **Incident Response**:
   - Review and update the incident response plan regularly.
   - Conduct regular incident response drills and ensure that the incident response team is trained and ready.

10. **Physical Security**:
    - Ensure that physical access to critical systems and data is restricted and monitored.
    - Implement measures like access cards, biometric scanners, and surveillance cameras.

11. **Mobile Device Management**:
    - Ensure that all mobile devices accessing company data are managed and secured.
    - Implement policies for device encryption, remote wipe, and regular updates.

12. **Review Security Settings in Cloud Services**:
    - Verify that cloud services are configured according to best security practices.
    - Ensure that cloud storage, computing, and network configurations follow your organization's security policies.

13. **Third-party and Vendor Management**:
    - Ensure that third-party vendors comply with your security requirements.
    - Review third-party agreements and conduct regular security assessments of their systems and practices.