---

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100),
    price DECIMAL(10,2)
);

CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    product_id INT,
    quantity INT
);

CREATE TABLE admin_log (
    id SERIAL PRIMARY KEY,
    action TEXT,
    created_at TIMESTAMP DEFAULT NOW()
);


CREATE ROLE app_role;
CREATE ROLE manager_role;
CREATE ROLE admin_role;


GRANT SELECT ON products TO PUBLIC;


GRANT SELECT ON orders TO manager_role;


GRANT INSERT, UPDATE, DELETE ON orders TO app_role;


REVOKE ALL ON admin_log FROM PUBLIC;
GRANT ALL ON admin_log TO admin_role;


CREATE USER app_user WITH PASSWORD 'app_pass';
CREATE USER manager_user WITH PASSWORD 'manager_pass';
GRANT app_role TO app_user;
GRANT manager_role TO manager_user;

=============================================================================================

\c shop_db;


CREATE USER user1 WITH PASSWORD 'password1';

ALTER USER user1 WITH SUPERUSER;


CREATE USER user2 WITH PASSWORD 'password2';
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO user2;


CREATE USER user3 WITH PASSWORD 'password3';
GRANT ALL PRIVILEGES ON DATABASE shop_db TO user3;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO user3;
GRANT CREATE ON SCHEMA public TO user3;


CREATE ROLE role_all_privs;
CREATE ROLE role_select_only;


GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO role_all_privs;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO role_select_only;

CREATE USER admin_user WITH PASSWORD 'admin_pass';
CREATE USER manager1 WITH PASSWORD 'manager1_pass';
CREATE USER manager2 WITH PASSWORD 'manager2_pass';

GRANT role_all_privs TO admin_user;

GRANT role_select_only TO manager1;
GRANT role_select_only TO manager2;


ALTER USER user1 WITH PASSWORD 'new_secure_password';


DROP USER user2;