cansjt opened a new issue #17374:
URL: https://github.com/apache/airflow/issues/17374
The new UI does not seem to care about the AIRFLOW__CORE__SQL_ALCHEMY_SCHEMA
configuration directive.
**Apache Airflow version**: 2.1.2
**Kubernetes version (if you are using kubernetes)** (use `kubectl
version`): not relevant
**Environment**:
- **Cloud provider or hardware configuration**:
- **OS** (e.g. from /etc/os-release):
- **Kernel** (e.g. `uname -a`):
- **Install tools**:
- **Others**:
**What happened**:
The permissions table were create by the webserver user in the public schema:
```
airflow=> \dt+ public.*
Liste des relations
Schéma | Nom | Type | Propriétaire | Persistence |
Taille | Description
--------+-------------------------+-------+--------------+-------------+------------+-------------
public | ab_permission | table | webserver | permanent |
8192 bytes |
public | ab_permission_view | table | webserver | permanent | 40
kB |
public | ab_permission_view_role | table | webserver | permanent | 56
kB |
public | ab_register_user | table | webserver | permanent |
8192 bytes |
public | ab_role | table | webserver | permanent |
8192 bytes |
public | ab_user | table | webserver | permanent | 16
kB |
public | ab_user_role | table | webserver | permanent |
8192 bytes |
public | ab_view_menu | table | webserver | permanent |
8192 bytes |
(8 lines)
```
**What you expected to happen**:
The webserver should not create table (not require such high privileges on
the database), look for and use the table create by the migrations in the
schema specified by the `AIRFLOW__CORE__SQL_ALCHEMY_SCHEMA`. The tables were,
indeed, properly created by the migrations. But then remain used (empty).
```
airflow=> \dt+ airflow.ab_*
Liste des relations
Schéma | Nom | Type | Propriétaire |
Persistence | Taille | Description
---------+-------------------------------+-------+--------------+-------------+------------+-------------
airflow | ab_permission | table | schema-admin | permanent
| 8192 bytes |
airflow | ab_permission_view | table | schema-admin | permanent
| 8192 bytes |
airflow | ab_permission_view_role | table | schema-admin | permanent
| 8192 bytes |
airflow | ab_register_user | table | schema-admin | permanent
| 8192 bytes |
airflow | ab_role | table | schema-admin | permanent
| 8192 bytes |
airflow | ab_user | table | schema-admin | permanent
| 8192 bytes |
airflow | ab_user_role | table | schema-admin | permanent
| 0 bytes |
airflow | ab_view_menu | table | schema-admin | permanent
| 8192 bytes |
(8 lines)
```
This also causes permission issue as the tables, not being created by the
migrations and in the wrong schema, do not inherit the permissions they should.
The generated SQL statements leave no doubts, on the fact that table names
are not properly qualified with a schema name:
```
Broken DAG: [/opt/airflow/share/dags/test/test_dag.py] Traceback (most
recent call last):
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py",
line 1276, in _execute_context
self.dialect.do_execute(
File
"/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line
608, in do_execute
cursor.execute(statement, parameters)
psycopg2.errors.InsufficientPrivilege: permission denied for table
ab_permission_view
The above exception was the direct cause of the following exception:
Traceback (most recent call last):
File "/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/base.py",
line 1276, in _execute_context
self.dialect.do_execute(
File
"/usr/local/lib/python3.8/site-packages/sqlalchemy/engine/default.py", line
608, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.InsufficientPrivilege)
permission denied for table ab_permission_view
[SQL: SELECT ab_permission_view.id AS ab_permission_view_id,
ab_permission_view.permission_id AS ab_permission_view_permission_id,
ab_permission_view.view_menu_id AS ab_permission_view_view_menu_id
FROM ab_permission_view JOIN ab_permission ON ab_permission.id =
ab_permission_view.permission_id JOIN ab_view_menu ON ab_view_menu.id =
ab_permission_view.view_menu_id
WHERE ab_permission.name = %(name_1)s AND ab_view_menu.name = %(name_2)s]
[parameters: {'name_1': 'can_read', 'name_2': 'DAG:TEST_DAG'}]
(Background on this error at: http://sqlalche.me/e/13/f405)
```
**How to reproduce it**:
Airflow test suite should generate a random schema name and use it. Any
access to a table not qualified with a schema name would be immediately
detected.
**Anything else we need to know**:
Remediations (with postgresql):
- change tables permissions (not really secure)
- change user search path (not recommended though
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: [email protected]
For queries about this service, please contact Infrastructure at:
[email protected]
