View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000410 | Pgpool-II | Bug | public | 2018-07-04 17:30 | 2019-04-04 08:07 |
| Reporter | nagata | Assigned To | t-ishii | ||
| Priority | normal | Severity | major | Reproducibility | have not tried |
| Status | closed | Resolution | open | ||
| Product Version | 3.7.4 | ||||
| Summary | 0000410: Session is closed abnormally when reading tables to whose schema the user doesn't have privilege | ||||
| Description | When reading a table specified with a schema name (e.g. SELECT * from s1.t1;) , Pgpool-II calls to_regclass('schema_name.table_name') using do_query() to create relcache info . However, if the user doesn't have a privilege on the schema, the query fails with the following error. ERROR permission denied for schema schema_name This leads the FATAL error in do_query(). FATAL: Backend throw an error message Then, the session is closed immediately. This is a problem. Pgpool-II should return the permission denied error on the schema to the client without closing the session | ||||
| Tags | No tags attached. | ||||
|
|
It's a known issue. See comments in pool_process_query.c. So far we haven't found any solution for this. /* * This is fatal. Because: If we operate extended * query, backend would not accept subsequent commands * until "sync" message issued. However, if sync * message is issued, unnamed statement/unnamed portal * will disappear and will cause lots of problems. If * we do not operate extended query, ongoing * transaction is aborted, and subsequent query would * not accepted. In summary there's no transparent * way for frontend to handle error case. The only way * is closing this session. */ |
|
|
So, is it a restriction of Pgpool-II that users must not access to any tables to whose schema they don't have privilege because this causes FATAL error? Is there any plan to resolve this in future? One idea to resolve is checking the privilege by pg_namespace.nspacl before getting relifno. |
|
|
I'm not excited by such a band-aid: just the application would get ERROR (and transaction abort) instead of FATAL. Such an application is broken anyway (or the system configuration is broken) and they need to be fixed first if they are used in a production environment. |
|
|
Returning ERROR is right behavior because PostgreSQL does like it. Anyway, I understand you don't have any plan to resolve this for now. I think you should state this restriction in the documentation for user covenient because this is Pgpool-II's incompatibility with PostgreSQL. |
|
|
Everybody agrees that Pgpool-II wants to behave like PostgreSQL. No question. But how you do solve the technical difficulty written in the comment? Without an idea to solve it, it's just an Irresponsible wish. > One idea to resolve is checking the privilege by pg_namespace.nspacl before getting relifno. So how do you deal with it if you find the query do_query is executing will be prohibited because of lacking of privilege? For example, Pgpool-II needs to know the table in question is an unlogged one or not. What Pgpool should do after finding do_query cannot execute the query because there's no enough privilege? do_query should pretend as if it's not an unlogged table (or vice versa)? Once that is cached in either way, next session will use the cache and the stupid admin notices that he needs to give a privilege to the schema and gives it. Then the session will run based on the wrong cache data, which may result in data corruption. For me this is much more scarily story than current behavior. |
|
|
> Everybody agrees that Pgpool-II wants to behave like PostgreSQL. No question. But how you do solve the technical difficulty written in the comment? Without an idea to solve it, it's just an Irresponsible wish. I just wanted to know if you have some plan to resolve this or not because our customer are facing this problem. I won't criticize you for being careful to fix this, because I understand it's difficulty. > So how do you deal with it if you find the query do_query is executing will be prohibited because of lacking of privilege? For example, Pgpool-II needs to know the table in question is an unlogged one or not. What Pgpool should do after finding do_query cannot execute the query because there's no enough privilege? do_query should pretend as if it's not an unlogged table (or vice versa)? Once that is cached in either way, next session will use the cache and the stupid admin notices that he needs to give a privilege to the schema and gives it. Then the session will run based on the wrong cache data, which may result in data corruption. For me this is much more scarily story than current behavior. SELECT for tables that user has not privilege on ends up with an error at last, so nothing will be cached even if this is regarded as a regular table right? It seems to me that this is similar that when the table does not exist. If a table doesn't exist is_unlogged_table() returns false, so this is considered as a regular table, but no problem has occur. The privilege for schema can be examined by using has_sequence_privilege(). For example, select has_schema_privilege(to_regnamespace('s1'), 'usage'); or select has_schema_privilege(nspname,'usage') from pg_namespace where nspname ='schema_name'; Of course, this is still unmature idea, so we will have to be careful and consider all other conditions where relcache is needed. By the way, there may be a bug of to_regclass, since this is designed to not return errors if the table is not found, although I'm not sure that this is the case when the user is unprivileged for the schema. test=> select * from s1.t1; ERROR: permission denied for schema s1 test=> select * from to_regclass('s1.t1'); ERROR: permission denied for schema s1 |
|
|
I am going to close this issue since Hoshiai-san has already proposed enhancement for to_regclass and friends. If there's objection, please say so now. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2018-07-04 17:30 | nagata | New Issue | |
| 2018-07-04 17:31 | nagata | Description Updated | |
| 2018-07-04 17:57 | t-ishii | Note Added: 0002087 | |
| 2018-07-04 18:21 | nagata | Note Added: 0002088 | |
| 2018-07-04 22:08 | t-ishii | Note Added: 0002089 | |
| 2018-07-05 10:38 | nagata | Note Added: 0002093 | |
| 2018-07-05 11:14 | t-ishii | Note Added: 0002094 | |
| 2018-07-06 15:12 | nagata | Note Added: 0002105 | |
| 2018-07-06 15:15 | nagata | Note Edited: 0002105 | |
| 2018-07-06 15:27 | nagata | Note Edited: 0002105 | |
| 2019-01-30 10:02 | pengbo | Assigned To | => t-ishii |
| 2019-01-30 10:02 | pengbo | Status | new => assigned |
| 2019-04-03 13:18 | t-ishii | Note Added: 0002502 | |
| 2019-04-03 15:40 | t-ishii | Status | assigned => feedback |
| 2019-04-04 08:07 | t-ishii | Status | feedback => closed |
