CREATE SCHEMA api;

CREATE TABLE api.users(
    last_name TEXT,
    first_name TEXT,
    user_name TEXT UNIQUE,
    email TEXT UNIQUE,
    has_premium BOOLEAN DEFAULT FALSE,
    date_created TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    user_id BIGINT GENERATED ALWAYS AS IDENTITY 
    (START WITH 10000000 INCREMENT BY 1) PRIMARY KEY
    );

CREATE TABLE api.passwords(
    encrypted_password TEXT,
    ref_user_id BIGINT,
    FOREIGN KEY(ref_user_id) REFERENCES api.users(user_id) ON DELETE CASCADE ON UPDATE CASCADE
    );

CREATE INDEX idx_user_id ON api.users(user_id);
CREATE INDEX idx_user_name ON api.users(user_name);

CREATE ROLE web_anon NOLOGIN;
GRANT web_anon TO postgres;

GRANT USAGE ON schema api TO web_anon;
GRANT SELECT ON api.users TO web_anon;

CREATE ROLE todo_user NOLOGIN;
GRANT todo_user TO postgres;
GRANT USAGE ON schema api To todo_user;
GRANT ALL ON api.users TO todo_user;
GRANT ALL ON api.passwords TO todo_user;

sync def create_user(self, first_name, last_name, username, email, password):
        """Adds a user to the StockTools database"""
        print('receiver end')
        async with postgrest.Client(self.host, default_headers={"role": "todo_user"}) as client:
            try:
                user_insert = await client.insert("users", {'last_name': last_name, 'first_name': first_name,
                                                            'user_name': username, 'email': email},
                                                  returning='representation')
                print('user insert complete')
                print(user_insert)
                await client.insert('passwords', {'encrypted_password': hashlib.sha3_512(password).hexdigest(),
                                                  'ref_user_id': user_insert})
                print('inserted passwords')
            except Exception as err:
                print(err)
                raise CannotManageUserData(err)
        print('user created')

401, message='permission denied for table users', url=URL('http://localhost:3000/users')