Thursday, 9 October 2025

MYSQL , restrict user from hosts, change restriction, restrict users from docker container hosts

 MYSQL Docker container created user root is root@%, so it can connect from any host


During MYSQL init set up , in init.sql, you might have


CREATE USER 'test'@'%' IDENTIFIED BY 'gitea';

GRANT ALL PRIVILEGES ON `gitea`.* TO 'test'@'%';


thats allowing user test to connect from any host, to enforce restriction for the user from a docker container service from any docker container within a docker subnet, its more safer to do :


CREATE USER 'test'@'169.255.255.%' IDENTIFIED BY 'gitea';

GRANT ALL PRIVILEGES ON `gitea`.* TO 'test'@'169.255.255.%';



Or if your init.sql already ran, you can do :

RENAME USER 'test'@'%' TO 'test'@'169.255.255.%';



You can do this with root user as well, 


If root@'localhost' already exists, drop the wide one:


sql

Copy code

DROP USER 'root'@'%';


you can use SQL statement to check:

SELECT user, host, plugin FROM mysql.user WHERE user='root';




Friday, 26 September 2025

Docker Compose MYSQL init, MYSQL port 3306

 docker mysql service has to use port 3306 to start multiple :

version: '3.8'services: mysql_db1: image: mysql:8 environment: MYSQL_ROOT_PASSWORD: password1 MYSQL_DATABASE: db1 ports: - "3306:3306" # Maps host port 3306 to container port 3306 volumes: - db_data1:/var/lib/mysql mysql_db2: image: mysql:8 environment: MYSQL_ROOT_PASSWORD: password2 MYSQL_DATABASE: db2 ports: - "3307:3306" # Maps host port 3307 to container port 3306 volumes: - db_data2:/var/lib/mysqlvolumes: db_data1: db_data2:


-----------------------------------------------------------------------------------------

To add an init.sql script to your MySQL Docker Compose setup to create the gitea and keycloak databases, follow these steps: Create the init.sql file.
Create a file named init.sql (or any other name ending in .sql.sh, or .sql.gz) in a directory accessible to your docker-compose.yml file. For example, create a subdirectory named mysql-init and place init.sql inside it.
Code
    -- mysql-init/init.sql    CREATE DATABASE IF NOT EXISTS `gitea`;    CREATE DATABASE IF NOT EXISTS `keycloak`;    -- You might also want to create dedicated users and grant privileges here    -- For example:    -- CREATE USER 'gitea_user'@'%' IDENTIFIED BY 'gitea_password';    -- GRANT ALL PRIVILEGES ON `gitea`.* TO 'gitea_user'@'%';    -- CREATE USER 'keycloak_user'@'%' IDENTIFIED BY 'keycloak_password';    -- GRANT ALL PRIVILEGES ON `keycloak`.* TO 'keycloak_user'@'%';    -- FLUSH PRIVILEGES;
Modify your docker-compose.yml.
In your docker-compose.yml file, add a volumes entry to your MySQL service to mount your mysql-init directory into the /docker-entrypoint-initdb.d directory within the MySQL container.
Code
    version: '3.8'    services:      mysql:        image: mysql:8.0 # Or your desired MySQL version        environment:          MYSQL_ROOT_PASSWORD: your_root_password          # Optional: Define default database, user, and password for initial setup          # MYSQL_DATABASE: default_database          # MYSQL_USER: default_user          # MYSQL_PASSWORD: default_password        volumes:          - ./mysql-init:/docker-entrypoint-initdb.d          - mysql_data:/var/lib/mysql # For persistent data        ports:          - "3306:3306"        networks:          - my_network      gitea:        image: gitea/gitea:latest        environment:          DB_TYPE: mysql          DB_HOST: mysql:3306          DB_NAME: gitea          DB_USER: gitea_user # If you created a dedicated user          DB_PASSWD: gitea_password # If you created a dedicated user          # ... other Gitea configurations        depends_on:          - mysql        networks:          - my_network      keycloak:        image: quay.io/keycloak/keycloak:latest        environment:          KC_DB: mysql          KC_DB_URL: jdbc:mysql://mysql:3306/keycloak          KC_DB_USERNAME: keycloak_user # If you created a dedicated user          KC_DB_PASSWORD: keycloak_password # If you created a dedicated user          # ... other Keycloak configurations        depends_on:          - mysql        networks:          - my_network    volumes:      mysql_data:    networks:      my_network:
Run Docker Compose.
Navigate to the directory containing your docker-compose.yml file and run:
Code
    docker-compose up -d
The init.sql script will execute automatically when the MySQL container starts for the first time (when the /var/lib/mysql data directory is empty), creating the gitea and keycloak databases. Subsequent restarts will not re-run the init.sql script unless the mysql_data volume is cleared.




Friday, 5 September 2025

docker expose, docker-compose expose

 

1. Dockerfile → EXPOSE

  • Purely documentation/metadata.

  • Doesn’t actually open or bind anything.

  • Other containers on the same network can still reach your app via container_name:port even if you don’t EXPOSE it.

  • Example: you can still curl http://web:8080 inside another container, even if the Dockerfile doesn’t have EXPOSE 8080.


2. docker-compose.yml → expose:

  • Same as Dockerfile’s EXPOSE, but defined in Compose instead.

  • Makes the port visible to other containers in the same Compose network.

  • Does not publish it to your host machine.


3. docker-compose.yml → ports:

  • This is the one that really matters if you want to access the container from outside Docker (e.g., browser, Postman, curl from host).

  • Maps container port → host port.
    Example:

    ports: - "8080:80" # host:container

Nginx http server, terminate TLS, proxy http, Nginx TLS proxy

 docker-compose

networks:

  backend:

    ipam:

      driver: default

      config:

        - subnet: 169.254.6.0/28


services:

 go-service:

  ..............

    networks:

      backend:

        ipv4_address: 169.254.6.2

.................

  nginx:

    image: nginx:1.27-alpine

    restart: unless-stopped

    networks:

      backend:

        ipv4_address: 169.254.6.4

    ports:

      - "443:443"

      - "9586:9586"

    volumes:

      - ./nginx/nginx.conf:/etc/nginx/nginx.conf:ro

      - ./nginx/certs/myserver:/etc/nginx/certs:ro

      - ./nginx/logs:/var/log/nginx

    extra_hosts:

      - test.com:169.254.6.2

!!!!!!!!!!!!!!! then whenever u use test.com in nginx container it will go to 169.254.6.2 the docker service


nginx.conf

# /etc/nginx/nginx.conf

worker_processes auto;

events { worker_connections 1024; }

http {
    include       /etc/nginx/mime.types;
    default_type  application/octet-stream;
    server_tokens off;
    sendfile      on;
    access_log /var/log/nginx/access.log;
    error_log  /var/log/nginx/error.log;

    server {
        listen 443 ssl;
        http2 on;
        server_name site_a.com;

        ssl_certificate     /etc/nginx/certs/site_a.crt;
        ssl_certificate_key /etc/nginx/certs/site_a.key;

        root /usr/share/nginx/html;

        location = /docs {
            return 301 /docs/;
        }

        access_log /var/log/nginx/site_a-ssl.access.log;
        error_log  /var/log/nginx/site_a-ssl.error.log;
    }

#teminates TLS at nginx, reverse proxy http to go service 
    server {
        listen                  443 ssl;
        server_name             siteb.com;
        #charset koi8-r;
        access_log /var/log/nginx/siteb-ssl.access.log;
        error_log  /var/log/nginx/siteb-ssl.error.log;

        ssl_certificate     /etc/nginx/certs/siteb.crt;
        ssl_certificate_key /etc/nginx/certs/siteb.key;

        proxy_set_header    X-Real-IP        $remote_addr;
        proxy_set_header    X-Forwarded-For  $proxy_add_x_forwarded_for;

#specified in docker
        location / {
            proxy_pass https:// test.com;
        }


        #error_page  404              /404.html;
        # redirect server error pages to the static page /50x.html
        #
        error_page   500 502 503 504  /50x.html;
        location = /50x.html {
            root   /usr/share/nginx/html;
        }
    }

}

// TCP reverse proxy
stream {
    server {
        listen 9888 reuseport;
        proxy_connect_timeout 10s;
        proxy_timeout 600s;

        proxy_pass test.com:9888;
        #log/inspect SNI if needed
        ssl_preread on;
    }
}


MYSQL important remark, how to listen to server IP, how to enforce TLS

 Create user xxx@IP


means create a user and only allows to connect if user is from this IP


it is different than MYSQL connection string -H (host IP) 

this host IP means where the MYSQL server is 

----------------------------------


GRANT ALL PRIVILEGES ON `db1`.* TO `xxxx`@`IP`;



 

FLUSH PRIVILEGES;


means grant all privlege of db 1 to user

--------------------------------------------------------------------

sudo netstat -tulnp | grep 3306

tcp        0      0 127.0.0.1:33060         0.0.0.0:*               LISTEN      2985660/mysqld

tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      2985660/mysqld


This means MYSQL only listen to localhost, to make it listen to server IP


do 

/etc/mysql/mysql.conf.d

Bind-address 0.0.0.0 so it listens on serverip or it will only listen to local host



---------------------------

Enforce SSL


MYSQL TLS cert location and specificaiton

check where config file is

cat /etc/mysql/my.cnf

go to /etc/mysql/conf.d/ if u found the config is above

check custom-mysqld-ssl.cnf (make your own custom file for TLS)

need chmod 644 this file

 

[mysqld]

# Use server.chain for "requires SSL" authentication

ssl_ca=myca.chain

# Use combined.chain for "requires x509" authentication

#ssl_ca=combined.chain

ssl_cert=myserver.cert

ssl_key=myserver.key

#require_secure_transport=ON

 !! this will make mysql to look cert at /var/lib/mysql/

·         Need 644 on all certs, need mysql:mysql on certs

Check:

SHOW VARIABLES LIKE 'ssl%';      

SHOW VARIABLES LIKE 'tls_version'; 

ALTER USER 'user'@'%' REQUIRE SSL

------------------------------------------------------------------------------------------------------------------------

sudo systemctl restart mysql

sudo systemctl status mysql



Thursday, 28 August 2025

MYSQL show user permission and user@host

 MYSQL cmds to show user privileges

SELECT CONCAT('SHOW GRANTS FOR \'', user, '\'@\'', host, '\';') FROM mysql.user;

this produce a list of :

SHOW GRANTS FOR 'john'@'localhost';


then execute individual command to get permission of user


To create a new user in MySQL, execute the following SQL command within your MySQL client (e.g., MySQL Shell, phpMyAdmin, or a command-line interface):
Code
CREATE USER 'your_username'@'localhost' IDENTIFIED BY 'your_password';
  • Replace 'your_username' with the desired username.
  • Replace 'localhost' with the hostname or IP address from which the user will connect. Use '%' for any host.
  • Replace 'your_password' with a strong, secure password for the new user.

This host measn the source IP of the user that can use to connect to 

if its test@123.456.789.123

if test is connecting from 155.155.155.231 test will be rejected


MY SQL grant permission 

GRANT ALL PRIVILEGES ON `<mydatabase>`.* TO `test`@`10.121.150.150`;

FLUSH PRIVILEGES;




  • Host Restriction:
    host specifies the allowed source(s) from which the user can connect. This can be:
    • localhostAllows connections only from the same machine where the MySQL server is running (often using a Unix socket, or TCP/IP on 127.0.0.1).
    • A specific IP address (e.g., 192.168.1.100): Limits connections to that exact IP address.
    • A hostname (e.g., myclient.example.com): Restricts connections to that specific hostname.
    • A wildcard character (%): Allows connections from any host. This is often used for broad access but can pose security risks if not managed carefully.
    • An IP address range with wildcards (e.g., 10.0.%): Allows connections from any IP address within that specific range.
    ------------------------------------------------------------------------------------
    --------------------------------------------------------------------------------------
    What if mysql is running on server 172.168.11.110, the docker services running bridge network also in that machine, can docker user test@172.168.11.110 to conenct assume such user is created :




Yes. MySQL decides which account to use based on the source IP it sees, and Docker’s NAT usually makes your container’s traffic look like it’s coming from the host’s IP (10.0.0.205) when it connects to a service on the host. So the server accepts test@10.0.0.205.

Why this happens

  • Your container lives on a bridge subnet (e.g., 172.17.0.0/16).

  • When the container connects to the host’s MySQL at 10.0.0.205:3306, Docker’s iptables rules do MASQUERADE/SNAT for packets leaving the bridge.

  • Result: MySQL receives a connection from 10.0.0.205 → 10.0.0.205:3306 (hairpin NAT), which matches the MySQL user test@10.0.0.205.

How to confirm exactly what MySQL sees
Run this in your session after connecting from the container:

SELECT USER() AS client_says, CURRENT_USER() AS mysql_matched_account, SUBSTRING_INDEX(HOST, ':', 1) AS source_ip FROM information_schema.processlist WHERE ID = CONNECTION_ID();
  • source_ip shows what IP MySQL thinks you’re coming from.

  • CURRENT_USER() shows the precise user@host entry MySQL matched.

Wednesday, 27 August 2025

Apache configuration to listen and server multiple port of same domain, virtualhost *:443 vs virtualhost _default: 443

 # Enable needed modules (once)

# a2enmod ssl proxy proxy_http headers

# (optional for HTTP/2) a2enmod http2


# Tell Apache to listen on both ports

Listen 443

Listen 9888

//!!!!!!!!!!!! the above is usually in httpd.conf

# --- :443 -> https://serverA/ ---

<VirtualHost *:443>

  ServerName testme.com


  SSLEngine On

  # Reuse the same cert for both ports (or different, your choice)

  SSLCertificateFile      /etc/ssl/certs/testme.crt

  SSLCertificateKeyFile   /etc/ssl/private/testme.key

  # SSLCertificateChainFile /etc/ssl/certs/chain.pem   # if needed

  # Protocols h2 http/1.1   # if you use mod_http2


  ProxyPreserveHost On

  SSLProxyEngine On            # because backend is https

  # (Optional if backend is self-signed)

  # SSLProxyVerify none

  # SSLProxyCheckPeerName off

  # SSLProxyCheckPeerExpire off


  ProxyPass        "/"  "https://serverA/"

  ProxyPassReverse "/"  "https://serverA/"

</VirtualHost>


# --- :9888 -> https://serverB:9586/ ---

<VirtualHost *:9888>

  ServerName testme.com


  # If clients connect with https://testme.com:9888 then you MUST enable SSL here too

  SSLEngine On

  SSLCertificateFile      /etc/ssl/certs/testme.crt

  SSLCertificateKeyFile   /etc/ssl/private/testme.key

  # Protocols h2 http/1.1


  ProxyPreserveHost On

  SSLProxyEngine On


  ProxyPass        "/"  "https://serverB:9586/"

  ProxyPassReverse "/"  "https://serverB:9586/"

</VirtualHost>



VirtualHost *:443

  • Means: this vhost will respond on all IPs bound to the server, on port 443.

  • Typical form used in almost all modern Apache configs.

  • Can be matched by ServerName or ServerAlias for name-based virtual hosting.

  • If multiple vhosts on the same port exist, Apache picks the one with the best ServerName match (or the first defined as fallback).

This is the recommended style when you’re hosting multiple domains on the same server/port (which is your case — one domain, two ports).


For same domain multiple ports this is also recommended


VirtualHost _default_:443

  • Means: this vhost is the “catch-all” for port 443 if no other vhost matches.

  • It’s not tied to ServerName or ServerAlias matching — it’s just the fallback.

  • Useful if you want a safety net for requests that don’t match any defined ServerName. For example, sending them to a default “Not Found / Wrong Host” site.

  • Only one _default_ vhost per port can exist.