You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "surukonda (via GitHub)" <gi...@apache.org> on 2023/02/08 04:38:10 UTC
[GitHub] [shardingsphere] surukonda opened a new issue, #24055: Support for Row level security feature of postgres
surukonda opened a new issue, #24055:
URL: https://github.com/apache/shardingsphere/issues/24055
## Feature Request
Provide support for row level feature of postgres.
https://www.postgresql.org/docs/current/ddl-rowsecurity.htmlhttps://www.postgresql.org/docs/current/ddl-rowsecurity.html
**For English only**, other languages will not accept.
Please pay attention on issues you submitted, because we maybe need more details.
If no response anymore and we cannot make decision by current information, we will **close it**.
Please answer these questions before submitting your issue. Thanks!
### Is your feature request related to a problem?
### Describe the feature you would like.
--
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: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #24055: Support for Row level security feature of postgres
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #24055:
URL: https://github.com/apache/shardingsphere/issues/24055#issuecomment-1423607756
> You mentioned shardingsphere proxy fails to insert data. Is there any exception message?
```
SET LOCAL tenant.id = 'test'
INSERT INTO codes (code, name, col3, col4, col5) VALUES ('AZC', 'test5', true, true, NULL) RETURNING carriers.id
[INFO ] 2023-02-07 16:01:43.787 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Logic SQL: INSERT INTO codes (code, name, col3, col4, col5) VALUES ('AZC', 'test5', true, true, NULL) RETURNING carriers.id
[INFO ] 2023-02-07 16:01:43.787 [Connection-1-ThreadExecutor] ShardingSphere-SQL - Actual SQL: yi_app_db ::: INSERT INTO codes (code, name, col3, col4, col5) VALUES ('AZC', 'test5', true, true, NULL) RETURNING carriers.id
[ERROR] 2023-02-07 16:01:43.790 [Connection-1-ThreadExecutor] o.a.s.p.f.c.CommandExecutorTask - Exception occur:
org.postgresql.util.PSQLException: ERROR: null value in column "tenant" violates not-null constraint
Detail: Failing row contains (12, 2023-02-07 16:01:43.419364+05:30, AZC, test5, t, t, f, null, null).
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2676)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2366)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:356)
at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:490)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:408)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:329)
at org.postgresql.jdbc.PgStatement.executeCachedSql(PgStatement.java:315)
at org.postgresql.jdbc.PgStatement.executeWithFlags(PgStatement.java:291)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:286)
at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:1256)
at com.zaxxer.hikari.pool.ProxyStatement.execute(ProxyStatement.java:102)
at com.zaxxer.hikari.pool.HikariProxyStatement.execute(HikariProxyStatement.java)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.impl.ProxyStatementExecutorCallback.execute(ProxyStatementExecutorCallback.java:42)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:77)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:70)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.callback.ProxyJDBCExecutorCallback.executeSQL(ProxyJDBCExecutorCallback.java:47)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:86)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutorCallback.execute(JDBCExecutorCallback.java:65)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.syncExecute(ExecutorEngine.java:133)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.serialExecute(ExecutorEngine.java:119)
at org.apache.shardingsphere.infra.executor.kernel.ExecutorEngine.execute(ExecutorEngine.java:113)
at org.apache.shardingsphere.infra.executor.sql.execute.engine.driver.jdbc.JDBCExecutor.execute(JDBCExecutor.java:67)
at org.apache.shardingsphere.proxy.backend.communication.jdbc.executor.ProxyJDBCExecutor.execute(ProxyJDBCExecutor.java:74)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.useDriverToExecute(ProxySQLExecutor.java:222)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.doExecute(ProxySQLExecutor.java:184)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:149)
at org.apache.shardingsphere.proxy.backend.communication.DatabaseCommunicationEngine.execute(DatabaseCommunicationEngine.java:178)
at org.apache.shardingsphere.proxy.frontend.postgresql.command.query.simple.PostgreSQLComQueryExecutor.execute(PostgreSQLComQueryExecutor.java:77)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:110)
at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1144)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:642)
at java.base/java.lang.Thread.run(Thread.java:1589)
ROLLBACK
```
`tenant` is a column with has row level security implemented.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support for Row level security feature of postgres [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #24055: Support for Row level security feature of postgres
URL: https://github.com/apache/shardingsphere/issues/24055
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #24055: Support for Row level security feature of postgres
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #24055:
URL: https://github.com/apache/shardingsphere/issues/24055#issuecomment-1422905714
> > There are 2 parts to the problems. On an existing table which has row level security enabled, shardingsphere proxy fails to insert data.
>
> What is the performance?
Sorry, didn't get you, are you referring to performance of the insert statement?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #24055: Support for Row level security feature of postgres
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #24055:
URL: https://github.com/apache/shardingsphere/issues/24055#issuecomment-1423576550
You mentioned shardingsphere proxy fails to insert data. Is there any exception message?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
Re: [I] Support for Row level security feature of postgres [shardingsphere]
Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #24055:
URL: https://github.com/apache/shardingsphere/issues/24055#issuecomment-3661064726
## Understanding
When using PostgreSQL Row Level Security (RLS) via ShardingSphere Proxy, the SET LOCAL tenant variable doesn’t take effect, and inserts hit the tenant column NOT NULL constraint.
## Root Cause
ShardingSphere focuses on cross-database traffic routing and governance, and does not plan to cover database-specific permission features like PostgreSQL RLS. Session-level GUC propagation and consistency across multiple connections/shards are not implemented.
## Analysis
The RLS session variable may not be applied to the actual physical connection executing the INSERT, so current_setting returns no value and the NOT NULL constraint is triggered. This is a missing feature rather than a SQL usage issue.
## Conclusion
ShardingSphere currently does not plan to support PostgreSQL RLS.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] surukonda commented on issue #24055: Support for Row level security feature of postgres
Posted by "surukonda (via GitHub)" <gi...@apache.org>.
surukonda commented on issue #24055:
URL: https://github.com/apache/shardingsphere/issues/24055#issuecomment-1422030806
> ShardingSphere is a gateway that supports heterogeneous databases. It cannot only consider the row level security of postgres, so this feature may not be well supported.
>
> In addition, when executing statements such as `ALTER TABLE ... ENABLE ROW LEVEL SECURITY`, special circumstances in scenarios such as sharding and encryption need to be considered.
>
> But it's a good idea and we can discuss it.
There are 2 parts to the problems. On an existing table which has row level security enabled, shardingsphere proxy fails to insert data.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #24055: Support for Row level security feature of postgres
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #24055:
URL: https://github.com/apache/shardingsphere/issues/24055#issuecomment-1422028165
ShardingSphere is a gateway that supports heterogeneous databases. It cannot only consider the row level security of postgres, so this feature may not be well supported.
In addition, when executing statements such as `ALTER TABLE ... ENABLE ROW LEVEL SECURITY`, special circumstances in scenarios such as sharding and encryption need to be considered.
But it's a good idea and we can discuss it.
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] RaigorJiang commented on issue #24055: Support for Row level security feature of postgres
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #24055:
URL: https://github.com/apache/shardingsphere/issues/24055#issuecomment-1422162386
> There are 2 parts to the problems. On an existing table which has row level security enabled, shardingsphere proxy fails to insert data.
What is the performance?
--
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: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org