Paul's Resources

Search

SearchSearch

NCAE-Database

Apr 22, 2024, 3 min read

MOCs

Box hardening

  • Change default given users password immediately (passwd)

  • Disable unnecessary users (need one sudo user for team and scoring
    users (BEST ALL OF THESE FIRST) )

    • usermod -L -e 1970-01-01 INSERT_USER_HERE

  • Update firewall for minimum allowed (need ssh, 22, and whatever port
    that postgresql is running on)

    • Can see the postgresql port with a sudo ss -tunap or sudo netstat -tunap

  • Can check open ports with ss -tunap or netstat -tunap - look
    for open

  • Check what version of postgresis using (if its not setup for us)

    • postgres --version

Firewall setup

sudo ufw default deny incoming
sudo ufw default allow outgoing
sudo ufw allow 22
sudo ufw allow 3306

See setup

sudo ufw status numbered # for rule numbers
sudo ufw status verbose # for all information, can then use like `sudo
ufw delete 2` to delete a rule number

Install postgres

Set username and make new sudo user (may be wheel for redhat)

sudo hostnamectl set-hostname "db01-oliver"
sudo adduser olivermustoe
sudo usermod -aG sudo olivermustoe

Lock default user

usermod -L -e 1970-01-01 champuser

Disabled root login in /etc/ssh/sshd_config:

Setup my netplan (use nmtui if not a ubuntu)

Install postgres (swap apt with dnf or yum for redhat):

sudo apt update -y
sudo apt install postgresql

Source:

Setup postgres

sudo postgresql-setup --initdb
sudo systemctl start postgresql

Basic SQL commands

Assumed to be logged in with a superuser such as:

sudo -i -u postgres
psql

Create new user

If need be here is how to create a user

create user myuser with encrypted password 'mypass';
# could also createuser --interactive
\du

Create another superuser (recommended if SSHing)

CREATE ROLE username WITH LOGIN SUPERUSER PASSWORD 'password';

Change user password

Have to be logged in

\password postgres

Give user permissions

For all permissions on an entire DB:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

For all permissions on a table in a DB:

GRANT ALL PRIVILEGES ON books.authors TO 'tolkien'@'localhost';
FLUSH PRIVILEGES;

Show all databases

\l

Create Database

CREATE DATABASE databasename;

Select a database

\c testdb;

See all tables

\dt

See all users

See just users

\du

See all super users

\d+

\dg

Create a table

Common texts would be INT, DATE,TEXT

CREATE TABLE table_name (
column1 datatype,
column2 datatype,
column3 datatype,
);

Create data in tables

INSERT INTO table_name (column1, column2, column3) VALUES (value1,
value2, value3);

Lock a user

Use \du to see user and host

ALTER USER joseph NOLOGIN;
FLUSH PRIVILEGES;

Unlock a user

Use \du \d+ to see user and host

ALTER USER joseph NOLOGIN;
FLUSH PRIVILEGES;

Sources

Backing up database

pg_dump db > dumpfile

Sources:

SQL securing

postgres_history

Regularly clean out the ~/.postgres_history file:

shred -n 10 ~/.postgres_history
cat /dev/null > ~/.postgres_history

Proper postgresuser permissions

postgresuser should exist and it and be in a group of the same name:

ALSO SHOULD NOT HAVE A PASSWORD/be on sbin/nologin!

Postgres and its group should own /var/lib/postgres (at leasy on a Rocky
9 box)/wherever it stores the databases:

And ensure that user and group is used to run it:

If this is not the case follow these steps:

sudo systemctl stop postgres
sudo groupadd postgres
sudo useradd -r -g postgres -s /sbin/nologin postgres
sudo chown -R postgres:postgres/var/lib/postgres
# Edit /usr/lib/systemd/system/postgres.server to have the user and
group directives like above
sudo systemctl restart postgres

Machine securing

AllowUsers

{width=“6.5in”
height=“2.763888888888889in”}

# In /etc/ssh/sshd_config
AllowUsers llama

Blacklists in package managers

Yum

Apt


Sources:

Graph View

Backlinks