Need Help : Cant connect to postgres db from trino on docker

[sainath13]

Hello,
I am trying to connect to a postgres db using trino but there seems to be an error. I am not able to find any explanation for the error.

error is
SQL Error [67108864]: Query failed (#20240405_125903_00010_mzaiw):
Error listing tables for catalog bob: FATAL: role "root" does not exist
Query failed (#20240405_125903_00010_mzaiw): Error listing tables for catalog bob: FATAL: role "root" does not exist
Query failed (#20240405_125903_00010_mzaiw): Error listing tables for catalog bob: FATAL: role "root" does not exist
io.trino.spi.TrinoException: Error listing tables for catalog bob: FATAL: role "root" does not exist
Error listing tables for catalog bob: FATAL: role "root" does not exist
org.postgresql.util.PSQLException: FATAL: role "root" does not exist
FATAL: role "root" does not exist

I think I have followed the documentation correctly. Here are the details
compose.yml

catalog -> bob.properties

If i connect to the postgres hosted in docker directly using dbeaver, I can connect successfully and run sql queries.

But I am not able to query in dbeaver through trino

may be im missing some configuration. I tried creating role using cli but that too fails
trino> SET ROLE ALL;
SET ROLE
trino> CREATE ROLE root;
Query 20240405_122842_00004_k2yh4 failed: System roles are not enabled

I tried with postgres role as well. It generates same error. Where exactly I am making a mistake? Do we need to create roles in the catalog?

Stack trace:

➜ executable ./trino http://localhost:8080/bob --debug
trino> SELECT TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE, REMARKS,
-> TYPE_CAT, TYPE_SCHEM, TYPE_NAME, SELF_REFERENCING_COL_NAME, REF_GENERATION
-> FROM system.jdbc.tables
-> WHERE TABLE_CAT = 'bob' AND TABLE_NAME LIKE '%' ESCAPE ''
-> ORDER BY TABLE_TYPE, TABLE_CAT, TABLE_SCHEM, TABLE_NAME;

Query 20240405_123228_00007_k2yh4, FAILED, 1 node
http://localhost:8080/ui/query.html?20240405_123228_00007_k2yh4
Splits: 11 total, 1 done (9.09%)
CPU Time: 0.0s total, 0 rows/s, 0B/s, 20% active
Per Node: 0.1 parallelism, 0 rows/s, 0B/s
Parallelism: 0.1
Peak Memory: 632B
0.16 [0 rows, 0B] [0 rows/s, 0B/s]

Query 20240405_123228_00007_k2yh4 failed: Error listing tables for catalog bob: FATAL: role "root" does not exist
io.trino.spi.TrinoException: Error listing tables for catalog bob: FATAL: role "root" does not exist
at io.trino.metadata.MetadataListing.handleListingException(MetadataListing.java:358)
at io.trino.metadata.MetadataListing.getRelationTypes(MetadataListing.java:144)
at io.trino.connector.system.jdbc.TableJdbcTable.cursor(TableJdbcTable.java:106)
at io.trino.spi.connector.SystemTable.cursor(SystemTable.java:47)
at io.trino.connector.system.SystemPageSourceProvider$1.cursor(SystemPageSourceProvider.java:140)
at io.trino.plugin.base.MappedRecordSet.cursor(MappedRecordSet.java:53)
at io.trino.spi.connector.RecordPageSource.(RecordPageSource.java:37)
at io.trino.connector.system.SystemPageSourceProvider.createPageSource(SystemPageSourceProvider.java:119)
at io.trino.split.PageSourceManager.createPageSource(PageSourceManager.java:61)
at io.trino.operator.ScanFilterAndProjectOperator$SplitToPages.process(ScanFilterAndProjectOperator.java:259)
at io.trino.operator.ScanFilterAndProjectOperator$SplitToPages.process(ScanFilterAndProjectOperator.java:190)
at io.trino.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:359)
at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:412)
at io.trino.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:346)
at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:412)
at io.trino.operator.WorkProcessorUtils$3.process(WorkProcessorUtils.java:346)
at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:412)
at io.trino.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:261)
at io.trino.operator.WorkProcessorUtils.lambda$processStateMonitor$2(WorkProcessorUtils.java:240)
at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:412)
at io.trino.operator.WorkProcessorUtils.getNextState(WorkProcessorUtils.java:261)
at io.trino.operator.WorkProcessorUtils.lambda$finishWhen$3(WorkProcessorUtils.java:255)
at io.trino.operator.WorkProcessorUtils$ProcessWorkProcessor.process(WorkProcessorUtils.java:412)
at io.trino.operator.WorkProcessorSourceOperatorAdapter.getOutput(WorkProcessorSourceOperatorAdapter.java:132)
at io.trino.operator.Driver.processInternal(Driver.java:398)
at io.trino.operator.Driver.lambda$process$8(Driver.java:301)
at io.trino.operator.Driver.tryWithLock(Driver.java:704)
at io.trino.operator.Driver.process(Driver.java:293)
at io.trino.operator.Driver.processForDuration(Driver.java:264)
at io.trino.execution.SqlTaskExecution$DriverSplitRunner.processFor(SqlTaskExecution.java:887)
at io.trino.execution.executor.dedicated.SplitProcessor.run(SplitProcessor.java:76)
at io.trino.execution.executor.dedicated.TaskEntry$VersionEmbedderBridge.lambda$run$0(TaskEntry.java:191)
at io.trino.$gen.Trino_443____20240405_122650_2.run(Unknown Source)
at io.trino.execution.executor.dedicated.TaskEntry$VersionEmbedderBridge.run(TaskEntry.java:192)
at io.trino.execution.executor.scheduler.FairScheduler.runTask(FairScheduler.java:174)
at io.trino.execution.executor.scheduler.FairScheduler.lambda$submit$0(FairScheduler.java:161)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:572)
at com.google.common.util.concurrent.TrustedListenableFutureTask$TrustedFutureInterruptibleTask.runInterruptibly(TrustedListenableFutureTask.java:131)
at com.google.common.util.concurrent.InterruptibleTask.run(InterruptibleTask.java:76)
at com.google.common.util.concurrent.TrustedListenableFutureTask.run(TrustedListenableFutureTask.java:82)
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:1583)
Caused by: io.trino.spi.TrinoException: FATAL: role "root" does not exist
at io.trino.plugin.jdbc.BaseJdbcClient.getTableNames(BaseJdbcClient.java:204)
at io.trino.plugin.jdbc.ForwardingJdbcClient.getTableNames(ForwardingJdbcClient.java:81)
at io.trino.plugin.jdbc.jmx.StatisticsAwareJdbcClient.lambda$getTableNames$2(StatisticsAwareJdbcClient.java:102)
at io.trino.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:34)
at io.trino.plugin.jdbc.jmx.StatisticsAwareJdbcClient.getTableNames(StatisticsAwareJdbcClient.java:102)
at io.trino.plugin.jdbc.CachingJdbcClient.lambda$getTableNames$2(CachingJdbcClient.java:167)
at io.trino.cache.EmptyCache.get(EmptyCache.java:94)
at io.trino.plugin.jdbc.CachingJdbcClient.get(CachingJdbcClient.java:758)
at io.trino.plugin.jdbc.CachingJdbcClient.getTableNames(CachingJdbcClient.java:167)
at io.trino.plugin.jdbc.CachingJdbcClient.lambda$getTableNames$2(CachingJdbcClient.java:167)
at com.google.common.cache.LocalCache$LocalManualCache$1.load(LocalCache.java:4938)
at com.google.common.cache.LocalCache$LoadingValueReference.loadFuture(LocalCache.java:3576)
at com.google.common.cache.LocalCache$Segment.loadSync(LocalCache.java:2318)
at com.google.common.cache.LocalCache$Segment.lockedGetOrLoad(LocalCache.java:2191)
at com.google.common.cache.LocalCache$Segment.get(LocalCache.java:2081)
at com.google.common.cache.LocalCache.get(LocalCache.java:4019)
at com.google.common.cache.LocalCache$LocalManualCache.get(LocalCache.java:4933)
at io.trino.cache.EvictableCache.get(EvictableCache.java:112)
at io.trino.plugin.jdbc.CachingJdbcClient.get(CachingJdbcClient.java:758)
at io.trino.plugin.jdbc.CachingJdbcClient.getTableNames(CachingJdbcClient.java:167)
at io.trino.plugin.jdbc.DefaultJdbcMetadata.listTables(DefaultJdbcMetadata.java:879)
at io.trino.spi.connector.ConnectorMetadata.getRelationTypes(ConnectorMetadata.java:294)
at io.trino.plugin.base.classloader.ClassLoaderSafeConnectorMetadata.getRelationTypes(ClassLoaderSafeConnectorMetadata.java:302)
at io.trino.tracing.TracingConnectorMetadata.getRelationTypes(TracingConnectorMetadata.java:275)
at io.trino.metadata.MetadataManager.getRelationTypes(MetadataManager.java:567)
at io.trino.tracing.TracingMetadata.getRelationTypes(TracingMetadata.java:328)
at io.trino.metadata.MetadataListing.doGetRelationTypes(MetadataListing.java:150)
at io.trino.metadata.MetadataListing.getRelationTypes(MetadataListing.java:141)
... 41 more
Caused by: org.postgresql.util.PSQLException: FATAL: role "root" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2837)
at org.postgresql.core.v3.QueryExecutorImpl.(QueryExecutorImpl.java:175)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:317)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:273)
at org.postgresql.Driver.makeConnection(Driver.java:446)
at org.postgresql.Driver.connect(Driver.java:298)
at io.trino.plugin.jdbc.TracingDataSource$JdbcDataSource.getConnection(TracingDataSource.java:69)
at io.opentelemetry.instrumentation.jdbc.datasource.OpenTelemetryDataSource.wrapCall(OpenTelemetryDataSource.java:156)
at io.opentelemetry.instrumentation.jdbc.datasource.OpenTelemetryDataSource.getConnection(OpenTelemetryDataSource.java:94)
at io.trino.plugin.jdbc.TracingDataSource.getConnection(TracingDataSource.java:48)
at io.trino.plugin.jdbc.DriverConnectionFactory.openConnection(DriverConnectionFactory.java:90)
at io.trino.plugin.jdbc.jmx.StatisticsAwareConnectionFactory.lambda$openConnection$0(StatisticsAwareConnectionFactory.java:45)
at io.trino.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:34)
at io.trino.plugin.jdbc.jmx.StatisticsAwareConnectionFactory.openConnection(StatisticsAwareConnectionFactory.java:45)
at io.trino.plugin.jdbc.RetryingConnectionFactory.lambda$openConnection$2(RetryingConnectionFactory.java:67)
at dev.failsafe.Functions.lambda$toCtxSupplier$11(Functions.java:243)
at dev.failsafe.Functions.lambda$get$0(Functions.java:46)
at dev.failsafe.internal.RetryPolicyExecutor.lambda$apply$0(RetryPolicyExecutor.java:74)
at dev.failsafe.SyncExecutionImpl.executeSync(SyncExecutionImpl.java:187)
at dev.failsafe.FailsafeExecutor.call(FailsafeExecutor.java:376)
at dev.failsafe.FailsafeExecutor.get(FailsafeExecutor.java:112)
at io.trino.plugin.jdbc.RetryingConnectionFactory.openConnection(RetryingConnectionFactory.java:67)
at io.trino.plugin.jdbc.LazyConnectionFactory.lambda$openConnection$0(LazyConnectionFactory.java:44)
at io.trino.plugin.jdbc.LazyConnectionFactory$LazyConnection.delegate(LazyConnectionFactory.java:75)
at io.trino.plugin.jdbc.ForwardingConnection.getMetaData(ForwardingConnection.java:117)
at io.trino.plugin.jdbc.ForwardingConnection.getMetaData(ForwardingConnection.java:117)
at io.trino.plugin.jdbc.BaseJdbcClient.getTables(BaseJdbcClient.java:1201)
at io.trino.plugin.jdbc.BaseJdbcClient.getTableNames(BaseJdbcClient.java:190)
... 68 more
Suppressed: org.postgresql.util.PSQLException: FATAL: role "root" does not exist
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2725)
at org.postgresql.core.v3.QueryExecutorImpl.readStartupMessages(QueryExecutorImpl.java:2837)
at org.postgresql.core.v3.QueryExecutorImpl.(QueryExecutorImpl.java:175)
at org.postgresql.core.v3.ConnectionFactoryImpl.openConnectionImpl(ConnectionFactoryImpl.java:317)
at org.postgresql.core.ConnectionFactory.openConnection(ConnectionFactory.java:54)
at org.postgresql.jdbc.PgConnection.(PgConnection.java:273)
at org.postgresql.Driver.makeConnection(Driver.java:446)
at org.postgresql.Driver.connect(Driver.java:298)
at io.trino.plugin.jdbc.TracingDataSource$JdbcDataSource.getConnection(TracingDataSource.java:69)
at io.opentelemetry.instrumentation.jdbc.datasource.OpenTelemetryDataSource.wrapCall(OpenTelemetryDataSource.java:156)
at io.opentelemetry.instrumentation.jdbc.datasource.OpenTelemetryDataSource.getConnection(OpenTelemetryDataSource.java:94)
at io.trino.plugin.jdbc.TracingDataSource.getConnection(TracingDataSource.java:48)
at io.trino.plugin.jdbc.DriverConnectionFactory.openConnection(DriverConnectionFactory.java:90)
at io.trino.plugin.jdbc.jmx.StatisticsAwareConnectionFactory.lambda$openConnection$0(StatisticsAwareConnectionFactory.java:45)
at io.trino.plugin.jdbc.jmx.JdbcApiStats.wrap(JdbcApiStats.java:34)
at io.trino.plugin.jdbc.jmx.StatisticsAwareConnectionFactory.openConnection(StatisticsAwareConnectionFactory.java:45)
at io.trino.plugin.jdbc.RetryingConnectionFactory.lambda$openConnection$2(RetryingConnectionFactory.java:67)
at dev.failsafe.Functions.lambda$toCtxSupplier$11(Functions.java:243)
at dev.failsafe.Functions.lambda$get$0(Functions.java:46)
at dev.failsafe.internal.RetryPolicyExecutor.lambda$apply$0(RetryPolicyExecutor.java:74)
at dev.failsafe.SyncExecutionImpl.executeSync(SyncExecutionImpl.java:187)
at dev.failsafe.FailsafeExecutor.call(FailsafeExecutor.java:376)
at dev.failsafe.FailsafeExecutor.get(FailsafeExecutor.java:112)
at io.trino.plugin.jdbc.RetryingConnectionFactory.openConnection(RetryingConnectionFactory.java:67)
at io.trino.plugin.jdbc.LazyConnectionFactory.lambda$openConnection$0(LazyConnectionFactory.java:44)
at io.trino.plugin.jdbc.LazyConnectionFactory$LazyConnection.delegate(LazyConnectionFactory.java:75)
at io.trino.plugin.jdbc.ForwardingConnection.isClosed(ForwardingConnection.java:110)
at io.trino.plugin.jdbc.ReusableConnectionFactory$CachedConnection.close(ReusableConnectionFactory.java:178)
at io.trino.plugin.jdbc.BaseJdbcClient.getTableNames(BaseJdbcClient.java:183)
... 68 more