terrymanu commented on issue #35110:
URL:
https://github.com/apache/shardingsphere/issues/35110#issuecomment-3500226937
Root Cause
This is a PostgreSQL-specific permission synchronization issue.
Core Problem
The PostgreSQL metadata loader in PostgreSQLMetaDataLoader.java implements
a unique permission checking mechanism:
// Line 149
Collection<String> roleTableGrants = loadRoleTableGrants(connection,
tables);
// Lines 156-158
if (!roleTableGrants.contains(tableName)) {
continue; // Skip tables without permissions
}
Root Cause: When a new table is just created, permission records in the
information_schema.role_table_grants system table may not be synchronized in
time, causing:
1. Some data sources: Permission records synchronized, can load metadata
normally
2. Some data sources: Permission records not synchronized, permission
check fails, skipping metadata loading
3. Metadata consistency check finds inconsistent metadata across data
sources, throws exception
Why other databases don't have this problem
- MySQL/Oracle/SQL Server: Directly query system tables/views, the query
results themselves are filtered by user permissions, no additional permission
checking steps
- PostgreSQL: Has an additional layer of loadRoleTableGrants() permission
checking, which has permission synchronization delay issues
Solutions
Recommended Solution: Ensure Correct Permission Configuration
1. Check and ensure database permission configuration is correct
Execute the following SQL in all PostgreSQL instances to confirm
permissions:
-- Check current user's table permissions
SELECT * FROM information_schema.role_table_grants
WHERE grantee = 'your_username' AND table_schema = 'public';
-- Grant necessary permissions if insufficient
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_username;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO your_username;
-- Ensure future tables can automatically get permissions
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON TABLES TO
your_username;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL ON SEQUENCES TO
your_username;
2. Recommended Operating Procedure
- Complete permission configuration for all storage units first
- Ensure users have appropriate permissions in all databases
- Then create sharding table rules
3. Temporary Solution
If the problem still exists after correct permission configuration,
consider:
props:
check-table-metadata-enabled: false # Temporarily disable
Then enable this configuration after all tables are created.
Need More Information for Confirmation
If the above permission configuration solution cannot solve the problem,
please provide the following information for further confirmation:
1. Permission Query Results
-- Execute in each PostgreSQL instance and provide results
SELECT * FROM information_schema.role_table_grants WHERE grantee =
'your_username';
2. Detailed Logs
Complete error logs with DEBUG mode enabled
3. Environment Information
- Specific user permission configuration for each data source
- Whether the problem can be reproduced in all PostgreSQL instances
- Database version and connection configuration details
Conclusion
This problem is likely caused by PostgreSQL permission system
synchronization delays when creating tables. It is recommended to first solve
it through correct permission configuration. Only if the problem still
reproduces
after confirming that permission configuration is completely correct,
should code-level fixes be considered.
--
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]
