sql - liquibase.exception.DatabaseException: ERROR: permission denied for schema public - Stack Overflow

admin管理员组

文章数量:1122832

I created jhipster project using postgres for database. Here is application-prod.yml:

# ===================================================================
# Spring Boot configuration for the "prod" profile.
#
# This configuration overrides the application.yml file.
#
# More information on profiles: /profiles/
# More information on configuration properties: /common-application-properties/
# ===================================================================

# ===================================================================
# Standard Spring Boot properties.
# Full reference is available at:
# .html
# ===================================================================

logging:
  level:
    ROOT: INFO
    tech.jhipster: INFO
    com.mycompany.myapp: INFO

management:
  prometheus:
    metrics:
      export:
        enabled: false

spring:
  devtools:
    restart:
      enabled: false
    livereload:
      enabled: false
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:postgresql://localhost:5432/app1
    username: app1
    password: abc123
    hikari:
      poolName: Hikari
      auto-commit: false
  # Replace by 'prod, faker' to add the faker context and have sample data loaded in production
  liquibase:
    contexts: prod
  mail:
    host: localhost
    port: 25
    username:
    password:
  thymeleaf:
    cache: true

# ===================================================================
# To enable TLS in production, generate a certificate using:
# keytool -genkey -alias app1 -storetype PKCS12 -keyalg RSA -keysize 2048 -keystore keystore.p12 -validity 3650
#
# You can also use Let's Encrypt:
# See details in topic "Create a Java Keystore (.JKS) from Let's Encrypt Certificates" on 
#
# Then, modify the server.ssl properties so your "server" configuration looks like:
#
# server:
#   port: 443
#   ssl:
#     key-store: classpath:config/tls/keystore.p12
#     key-store-password: password
#     key-store-type: PKCS12
#     key-alias: selfsigned
#     # The ciphers suite enforce the security by deactivating some old and deprecated SSL cipher, this list was tested against SSL Labs (/)
#     ciphers: TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA,TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA,TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256,TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 ,TLS_DHE_RSA_WITH_AES_128_GCM_SHA256 ,TLS_DHE_RSA_WITH_AES_256_GCM_SHA384 ,TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256,TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384,TLS_DHE_RSA_WITH_AES_128_CBC_SHA256,TLS_DHE_RSA_WITH_AES_128_CBC_SHA,TLS_DHE_RSA_WITH_AES_256_CBC_SHA256,TLS_DHE_RSA_WITH_AES_256_CBC_SHA,TLS_RSA_WITH_AES_128_GCM_SHA256,TLS_RSA_WITH_AES_256_GCM_SHA384,TLS_RSA_WITH_AES_128_CBC_SHA256,TLS_RSA_WITH_AES_256_CBC_SHA256,TLS_RSA_WITH_AES_128_CBC_SHA,TLS_RSA_WITH_AES_256_CBC_SHA,TLS_DHE_RSA_WITH_CAMELLIA_256_CBC_SHA,TLS_RSA_WITH_CAMELLIA_256_CBC_SHA,TLS_DHE_RSA_WITH_CAMELLIA_128_CBC_SHA,TLS_RSA_WITH_CAMELLIA_128_CBC_SHA
# ===================================================================
server:
  port: 8080
  shutdown: graceful # see .html#boot-features-graceful-shutdown
  compression:
    enabled: true
    mime-types: text/html,text/xml,text/plain,text/css,application/javascript,application/json,image/svg+xml
    min-response-size: 1024

# ===================================================================
# JHipster specific properties
#
# Full reference is available at: /common-application-properties/
# ===================================================================

jhipster:
  http:
    cache: # Used by the CachingHttpHeadersFilter
      timeToLiveInDays: 1461
  cache: # Cache configuration
    ehcache: # Ehcache configuration
      time-to-live-seconds: 3600 # By default objects stay 1 hour in the cache
      max-entries: 1000 # Number of objects in each cache entry
  security:
    authentication:
      jwt:
        # This token must be encoded using Base64 and be at least 256 bits long (you can type `openssl rand -base64 64` on your command line to generate a 512 bits one)
        # As this is the PRODUCTION configuration, you MUST change the default key, and store it securely:
        # - In the Consul configserver
        # - In a separate `application-prod.yml` file, in the same folder as your executable JAR file
        # - In the `JHIPSTER_SECURITY_AUTHENTICATION_JWT_BASE64_SECRET` environment variable
        base64-secret: MmRmNzMzN2M5MGFiMmE5NzZlYmUyYzE2ZmYwYTFkMmU5N2Y0NmYxZmU0M2EyNTdjNDRmZjM0NDNjZjQ4YzUwYzFmM2U4OGI0Y2I2YTQ4NzNjNzZhYzIyYjMxOGEzMzFiMGIwM2Y1MmJmMGRlYWY0OGQxODY5NTZmZDdmZDBiNDU=
        # Token is valid 24 hours
        token-validity-in-seconds: 86400
        token-validity-in-seconds-for-remember-me: 2592000
  mail: # specific JHipster mail property, for standard properties see MailProperties
    base-url: http://my-server-url-to-change # Modify according to your server's URL
  logging:
    use-json-format: false # By default, logs are not in Json format
    logstash: # Forward logs to logstash over a socket, used by LoggingConfiguration
      enabled: false
      host: localhost
      port: 5000
      ring-buffer-size: 512
# ===================================================================
# Application specific properties
# Add your own application properties here, see the ApplicationProperties class
# to have type-safe configuration, like in the JHipsterProperties above
#
# More documentation is available at:
# /common-application-properties/
# ===================================================================

# application:

and I also created the app1 database and app1 user in psql:

postgres=# create database app1;
CREATE DATABASE
postgres=# create user app1 with password 'abc123';
CREATE ROLE
postgres=# grant all privileges on app1 to app1;
ERROR:  relation "app1" does not exist
postgres=# grant all privileges on database app1 to app1;
GRANT
postgres=# grant all on schema public to app1;
GRANT
postgres=#

chatgpt says I needed to grant schema public, which I did as seen above. But when I run the jhipster project as .\mvnw -Pprod, I will get:

Error creating bean with name 'liquibase' defined in class path resource [com/mycomp
any/myapp/config/LiquibaseConfiguration.class]: liquibase.exception.CommandExecutionException: liquibase.exception.DatabaseException: liquibase.exce
ption.DatabaseException: ERROR: permission denied for schema public_  Position: 14 [Failed SQL: (0) CREATE TABLE public.databasechangelog (ID VARCHA
R(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP WITHOUT TIME ZONE NOT NULL, ORDEREXECUTED INTE
GER NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))]

so it says liquibase does not have permission, but I granted everything I thought is required. What else to do?

I created jhipster project using postgres for database. Here is application-prod.yml:

# ===================================================================
# Spring Boot configuration for the "prod" profile.
#
# This configuration overrides the application.yml file.
#
# More information on profiles: https://www.jhipster.tech/profiles/
# More information on configuration properties: https://www.jhipster.tech/common-application-properties/
# ===================================================================

# ===================================================================
# Standard Spring Boot properties.
# Full reference is available at:
# http://docs.spring.io/spring-boot/docs/current/reference/html/common-application-properties.html
# ===================================================================

logging:
  level:
    ROOT: INFO
    tech.jhipster: INFO
    com.mycompany.myapp: INFO

management:
  prometheus:
    metrics:
      export:
        enabled: false

spring:
  devtools:
    restart:
      enabled: false
    livereload:
      enabled: false
  datasource:
    type: com.zaxxer.hikari.HikariDataSource
    url: jdbc:postgresql://localhost:5432/app1
    username: app1
    password: abc123
    hikari:
      poolName: Hikari
      auto-commit: false
  # Replace by 'prod, faker' to add the faker context and have sample data loaded in production
  liquibase:
    contexts: prod
  mail:
    host: localhost
    port: 25
    username:
    password:
  thymeleaf:
    cache: true

# ===================================================================
# To enable TLS in production, generate a certificate using:
# keytool -genkey -alias app1 -storetype PKCS12 -keyalg RSA -keysize 2048 -keystore keystore.p12 -validity 3650
#
# You can also use Let's Encrypt:
# See details in topic "Create a Java Keystore (.JKS) from Let's Encrypt Certificates" on https://maximilian-boehm.com/en-gb/blog
#
# Then, modify the server.ssl properties so your "server" configuration looks like:
#
# server:
#   port: 443
#   ssl:
#     key-store: classpath:config/tls/keystore.p12
#     key-store-password: password
#     key-store-type: PKCS12
#     key-alias: selfsigned
#     # The ciphers suite enforce the security by deactivating some old and deprecated SSL cipher, this list was tested against SSL Labs (https://www.ssllabs.com/ssltest/)
#     ciphers: TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA,TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA,TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256,TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 ,TLS_DHE_RSA_WITH_AES_128_GCM_SHA256 ,TLS_DHE_RSA_WITH_AES_256_GCM_SHA384 ,TLS_ECDHE_RSA_WITH_AES_128_CBC_SHA256,TLS_ECDHE_RSA_WITH_AES_256_CBC_SHA384,TLS_DHE_RSA_WITH_AES_128_CBC_SHA256,TLS_DHE_RSA_WITH_AES_128_CBC_SHA,TLS_DHE_RSA_WITH_AES_256_CBC_SHA256,TLS_DHE_RSA_WITH_AES_256_CBC_SHA,TLS_RSA_WITH_AES_128_GCM_SHA256,TLS_RSA_WITH_AES_256_GCM_SHA384,TLS_RSA_WITH_AES_128_CBC_SHA256,TLS_RSA_WITH_AES_256_CBC_SHA256,TLS_RSA_WITH_AES_128_CBC_SHA,TLS_RSA_WITH_AES_256_CBC_SHA,TLS_DHE_RSA_WITH_CAMELLIA_256_CBC_SHA,TLS_RSA_WITH_CAMELLIA_256_CBC_SHA,TLS_DHE_RSA_WITH_CAMELLIA_128_CBC_SHA,TLS_RSA_WITH_CAMELLIA_128_CBC_SHA
# ===================================================================
server:
  port: 8080
  shutdown: graceful # see https://docs.spring.io/spring-boot/docs/current/reference/html/spring-boot-features.html#boot-features-graceful-shutdown
  compression:
    enabled: true
    mime-types: text/html,text/xml,text/plain,text/css,application/javascript,application/json,image/svg+xml
    min-response-size: 1024

# ===================================================================
# JHipster specific properties
#
# Full reference is available at: https://www.jhipster.tech/common-application-properties/
# ===================================================================

jhipster:
  http:
    cache: # Used by the CachingHttpHeadersFilter
      timeToLiveInDays: 1461
  cache: # Cache configuration
    ehcache: # Ehcache configuration
      time-to-live-seconds: 3600 # By default objects stay 1 hour in the cache
      max-entries: 1000 # Number of objects in each cache entry
  security:
    authentication:
      jwt:
        # This token must be encoded using Base64 and be at least 256 bits long (you can type `openssl rand -base64 64` on your command line to generate a 512 bits one)
        # As this is the PRODUCTION configuration, you MUST change the default key, and store it securely:
        # - In the Consul configserver
        # - In a separate `application-prod.yml` file, in the same folder as your executable JAR file
        # - In the `JHIPSTER_SECURITY_AUTHENTICATION_JWT_BASE64_SECRET` environment variable
        base64-secret: MmRmNzMzN2M5MGFiMmE5NzZlYmUyYzE2ZmYwYTFkMmU5N2Y0NmYxZmU0M2EyNTdjNDRmZjM0NDNjZjQ4YzUwYzFmM2U4OGI0Y2I2YTQ4NzNjNzZhYzIyYjMxOGEzMzFiMGIwM2Y1MmJmMGRlYWY0OGQxODY5NTZmZDdmZDBiNDU=
        # Token is valid 24 hours
        token-validity-in-seconds: 86400
        token-validity-in-seconds-for-remember-me: 2592000
  mail: # specific JHipster mail property, for standard properties see MailProperties
    base-url: http://my-server-url-to-change # Modify according to your server's URL
  logging:
    use-json-format: false # By default, logs are not in Json format
    logstash: # Forward logs to logstash over a socket, used by LoggingConfiguration
      enabled: false
      host: localhost
      port: 5000
      ring-buffer-size: 512
# ===================================================================
# Application specific properties
# Add your own application properties here, see the ApplicationProperties class
# to have type-safe configuration, like in the JHipsterProperties above
#
# More documentation is available at:
# https://www.jhipster.tech/common-application-properties/
# ===================================================================

# application:

and I also created the app1 database and app1 user in psql:

postgres=# create database app1;
CREATE DATABASE
postgres=# create user app1 with password 'abc123';
CREATE ROLE
postgres=# grant all privileges on app1 to app1;
ERROR:  relation "app1" does not exist
postgres=# grant all privileges on database app1 to app1;
GRANT
postgres=# grant all on schema public to app1;
GRANT
postgres=#

chatgpt says I needed to grant schema public, which I did as seen above. But when I run the jhipster project as .\mvnw -Pprod, I will get:

Error creating bean with name 'liquibase' defined in class path resource [com/mycomp
any/myapp/config/LiquibaseConfiguration.class]: liquibase.exception.CommandExecutionException: liquibase.exception.DatabaseException: liquibase.exce
ption.DatabaseException: ERROR: permission denied for schema public_  Position: 14 [Failed SQL: (0) CREATE TABLE public.databasechangelog (ID VARCHA
R(255) NOT NULL, AUTHOR VARCHAR(255) NOT NULL, FILENAME VARCHAR(255) NOT NULL, DATEEXECUTED TIMESTAMP WITHOUT TIME ZONE NOT NULL, ORDEREXECUTED INTE
GER NOT NULL, EXECTYPE VARCHAR(10) NOT NULL, MD5SUM VARCHAR(35), DESCRIPTION VARCHAR(255), COMMENTS VARCHAR(255), TAG VARCHAR(255), LIQUIBASE VARCHAR(20), CONTEXTS VARCHAR(255), LABELS VARCHAR(255), DEPLOYMENT_ID VARCHAR(10))]

so it says liquibase does not have permission, but I granted everything I thought is required. What else to do?

• sql
• postgresql
• jhipster
• liquibase
• liquibase-hibernate

Share Improve this question Follow asked Nov 22, 2024 at 21:26 milanHrabosmilanHrabos 1,95544 gold badges2020 silver badges6060 bronze badges 7

• 1 Do you have a public_ schema in it? That is with that "_" ? From your error message ...permission denied for schema public_ .... – Jorge Campos Commented Nov 22, 2024 at 21:36
• I dont think so, why does liquibase uses public_ schema by default? – milanHrabos Commented Nov 22, 2024 at 21:45
• try this: spring.liquibase.default-schema=public and see how it goes – Jorge Campos Commented Nov 22, 2024 at 21:50
• and for your question, not sure what happened there – Jorge Campos Commented Nov 22, 2024 at 21:51
• If that doesn't work, try adding logging.level.liquibase=DEBUG and check the logs to see if anything stands out – Jorge Campos Commented Nov 22, 2024 at 21:52

 |  Show 2 more comments

1 Answer 1

Sorted by: Reset to default 0

This issue with public_ it's strange, as liquibase is using public . Also liquibase doesn't change the search_path, it will use the one provided by Postgresql.

So try to reset the search path for this user and change the db owner so it will have full access to it:

ALTER ROLE app1 RESET search_path;
GRANT ALL ON DATABASE app1 TO app1;
ALTER DATABASE app1 OWNER TO app1;
GRANT USAGE, CREATE ON SCHEMA PUBLIC TO app1;

本文标签： sqlliquibaseexceptionDatabaseException ERROR permission denied for schema publicStack Overflow