-- Create a new user (role)
CREATE USER myappuser WITH 
    PASSWORD 'your_secure_password'
    NOSUPERUSER
    CREATEDB
    NOCREATEROLE
    INHERIT
    LOGIN;

-- Create a new database owned by the user
CREATE DATABASE myappdb WITH 
    OWNER = myappuser
    ENCODING = 'UTF8'
    LC_COLLATE = 'en_US.UTF-8'
    LC_CTYPE = 'en_US.UTF-8'
    TEMPLATE = template0;

-- Connect to the new database
\c myappdb

-- Grant all privileges on the database to the user
GRANT ALL PRIVILEGES ON DATABASE myappdb TO myappuser;

-- Create schema and grant privileges
CREATE SCHEMA IF NOT EXISTS myappschema AUTHORIZATION myappuser;

-- Set default privileges for future tables
ALTER DEFAULT PRIVILEGES FOR USER myappuser IN SCHEMA myappschema
    GRANT ALL ON TABLES TO myappuser;

-- Set the search path
ALTER DATABASE myappdb SET search_path TO myappschema, public;