postgres initial DB

p12 in docker

keytool -genkeypair -alias myalias -keyalg RSA -keysize 2048 -storetype PKCS12 -keystore mykeystore.p12 -validity 3650


openssl pkcs12 -export -in cert.pem -inkey key.pem -out mykeystore.p12 -name myalias


# Copy the .p12 file into the Docker image
COPY mykeystore.p12 /path/to/keystore/mykeystore.p12


keytool -list -v -keystore mykeystore.p12 -storetype PKCS12

shell

CREATE SCHEMA IF NOT EXISTS schema_name;

GRANT ALL ON SCHEMA sales TO sales_user;

GRANT SELECT ON TABLE sales.orders TO sales_user;

-- Step 1: Create the schema if it doesn't exist
CREATE SCHEMA IF NOT EXISTS sales;

-- Step 2: Create the table if it doesn't exist in the 'sales' schema
CREATE TABLE IF NOT EXISTS sales.orders (
    order_id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total_amount NUMERIC(10, 2) NOT NULL
);

DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'your_role_name') THEN
        -- Grant schema-level rights
        GRANT USAGE ON SCHEMA your_schema_name TO your_role_name;
        -- Grant database-level rights
        GRANT CONNECT ON DATABASE your_database_name TO your_role_name;
    END IF;
END $$;


BEGIN;

-- Step 1: Create table
CREATE TABLE IF NOT EXISTS employees (
    employee_id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE
);

-- Step 2: Grant all privileges to two users
GRANT ALL PRIVILEGES ON TABLE employees TO user1, user2;

COMMIT;

GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE employees TO user1, user2;

trigger

CREATE TRIGGER my_table_audit_trigger
AFTER INSERT OR UPDATE ON my_table
FOR EACH ROW
EXECUTE FUNCTION audit_my_table();

trigger function

CREATE OR REPLACE FUNCTION audit_my_table()
RETURNS TRIGGER AS $$
BEGIN
    -- Log the old and new values into an audit table
    INSERT INTO audit_log (action_type, old_value, new_value, action_time)
    VALUES (TG_OP, ROW(OLD.*)::TEXT, ROW(NEW.*)::TEXT, CURRENT_TIMESTAMP);
    RETURN NEW;  -- Return the new row for AFTER triggers
END;
$$ LANGUAGE plpgsql;