Migration after 5.1.0 release

[plejik]

Steps to Reproduce

Hey!
Run prowler on k8s eks + rds as database
Update v 5.0.5 to 5.1.0 ( and 5.1.3 as well)
Face problem when run command
poetry run python manage.py migrate --database admin

I got error
django.db.utils.ProgrammingError: new row violates row-level security policy for table "roles"

What i findout that we have policy

                WHEN current_setting('%(tenant_setting)s', True) IS NULL THEN FALSE
                ELSE %(field_column)s = current_setting('%(tenant_setting)s')::uuid
            END
tenant_settings == api.tenant_id

after migration 0004 we run migration 0005 but i do not see middlware which ( i may not find out proper code sorry ) set tenant_id.
But find out something similar in

src/backend/api/decorators.py: cursor.execute(SET_CONFIG_QUERY, [POSTGRES_TENANT_VAR, tenant_id])

Could it be related issue for migration
Thx for help

Expected behavior

Migration should run without error

Actual Result with Screenshots or Logs

  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/models/query.py", line 948, in get_or_create
    return self.get(**kwargs), False
           ^^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/models/query.py", line 649, in get
    raise self.model.DoesNotExist(
__fake__.Role.DoesNotExist: Role matching query does not exist.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/backends/utils.py", line 105, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
psycopg2.errors.InsufficientPrivilege: new row violates row-level security policy for table "roles"


The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/home/prowler/backend_test/manage.py", line 24, in <module>
    main()
  File "/home/prowler/backend_test/manage.py", line 20, in main
    execute_from_command_line(sys.argv)
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/core/management/__init__.py", line 442, in execute_from_command_line
    utility.execute()
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/core/management/__init__.py", line 436, in execute
    self.fetch_command(subcommand).run_from_argv(self.argv)
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/core/management/base.py", line 413, in run_from_argv
    self.execute(*args, **cmd_options)
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/core/management/base.py", line 459, in execute
    output = self.handle(*args, **options)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/core/management/base.py", line 107, in wrapper
    res = handle_func(*args, **kwargs)
          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/core/management/commands/migrate.py", line 357, in handle
    post_migrate_state = executor.migrate(
                         ^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/migrations/executor.py", line 135, in migrate
    state = self._migrate_all_forwards(
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/migrations/executor.py", line 167, in _migrate_all_forwards
    state = self.apply_migration(
            ^^^^^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/migrations/executor.py", line 255, in apply_migration
    state = migration.apply(state, schema_editor)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/migrations/migration.py", line 132, in apply
    operation.database_forwards(
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/migrations/operations/special.py", line 196, in database_forwards
    self.code(from_state.apps, schema_editor)
  File "/home/prowler/backend_test/api/migrations/0005_rbac_missing_admin_roles.py", line 13, in create_admin_role
    admin_role, _ = Role.objects.using(MainRouter.admin_db).get_or_create(
                    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/models/query.py", line 955, in get_or_create
    return self.create(**params), True
           ^^^^^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/models/query.py", line 679, in create
    obj.save(force_insert=True, using=self.db)
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/models/base.py", line 892, in save
    self.save_base(
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/models/base.py", line 998, in save_base
    updated = self._save_table(
              ^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/models/base.py", line 1161, in _save_table
    results = self._do_insert(
              ^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/models/base.py", line 1202, in _do_insert
    return manager._insert(
           ^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/models/manager.py", line 87, in manager_method
    return getattr(self.get_queryset(), name)(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/models/query.py", line 1847, in _insert
    return query.get_compiler(using=using).execute_sql(returning_fields)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/models/sql/compiler.py", line 1836, in execute_sql
    cursor.execute(sql, params)
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/backends/utils.py", line 79, in execute
    return self._execute_with_wrappers(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/backends/utils.py", line 92, in _execute_with_wrappers
    return executor(sql, params, many, context)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/backends/utils.py", line 100, in _execute
    with self.db.wrap_database_errors:
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/utils.py", line 91, in __exit__
    raise dj_exc_value.with_traceback(traceback) from exc_value
  File "/home/prowler/.cache/pypoetry/virtualenvs/prowler-api-NnJNioq7-py3.12/lib/python3.12/site-packages/django/db/backends/utils.py", line 105, in _execute
    return self.cursor.execute(sql, params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
django.db.utils.ProgrammingError: new row violates row-level security policy for table "roles"```

### How did you install Prowler?

Docker (docker pull toniblyx/prowler) + k8s 

### Environment Resource

1. eks  ( aws k8s ) 
2. rds 

### OS used

1. Amazon Linux 

### Prowler version

5.1.3

### Pip version

docker 5.1.3

### Context

_No response_

[plejik]

Okay i find out issue
I run rds instance on aws with postgres
Create 2 role prowler and proler_admin , assign prowler_admin as owner of db.
Since ( i only guess ) something like src/backend/api/decorators.py: cursor.execute(SET_CONFIG_QUERY, [POSTGRES_TENANT_VAR, tenant_id]) not used for migration ( or other way how tenatn used ) i can run migration only with postgres admin user ( which is not preferable ) .

Setup work fine on docker compose since it uses postgres admin by default. And look like ( from docs ) postgres admin has permission bypass rls stuff =\ same as admin on aws rds. BYPASSRLS as role not available in aws rds ( do not test in local setup ) but for security purpose may i ask to have a look on this problem :)

[AdriiiPRodri]

Hi @plejik,

Thank you very much for all the description and the steps followed, we are going to investigate this more in depth and we will test with RDS for possible problems like this one, thank you very much for the help, as soon as possible we will start testing.

[plejik]

I would add some additional description, latest user config ( i just give up at some point ) i provide similar access to prowler and prowler_admin ( which is incorect but try to figure out what i am do wrong )

   GRANT ALL PRIVILEGES ON DATABASE prowler TO prowler;
    GRANT ALL PRIVILEGES ON DATABASE prowler TO prowler_admin;
    \c prowler;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO prowler;
    GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO prowler_admin;
   and also 
  ALTER DATABASE prowler OWNER TO prowler_admin;

And for some reason in dbviewer i see completly diff output for select ( for example roles table)
And then i start dig up to policy ( that where i get my conclusion )
Config for psql

POSTGRES_ADMIN_USER=prowler_admin
POSTGRES_USER=prowler

and admin can select less info that prowler ( for example for prowler_admin table empty but for prowler user i see role there )
Sorry for this config mess but i could not find out proper db permission, so go backward and give all and wanna remove what will not required, but stuck on migration :D

[plejik]

Okay i find out temp solution until main fix will be figured out. So main issue still appear - prowler_admin user do not use RLS postgresql stuff that why it failed.?
To bypass id you can create role

create role prowler_role with  BYPASSRLS; 
grant prowler_role to prowler_admin; <- assign this role to user you create 

I do not work recently with postgresql and forgot some stuff with user and roles here :)
Anyway this is temp fix, i guess need to figure out how handle it in code - or just have one user who operated db or make proper separation between prowler_admin and prowler user

[AdriiiPRodri]

Thank you very much for the temporary solution, this will be very useful to solve this problem in future versions, as soon as possible we will start testing to implement a solution to this problem with the 0005 migration.