java - 如何使用不同于 super 用户(postgres)的用户访问postgresql数据库？

Question

在我的 Spring 应用程序中，我有以下 Hibernate 类来访问我的 postgresql 数据库:

@Configuration
@EnableTransactionManagement
@PropertySource({ "classpath:persistence.properties" })
@ComponentScan({ "org.webapp.persistence" })
public class HibernateConfig {

   @Autowired
   private Environment env;

   @Bean
   public LocalSessionFactoryBean sessionFactory() {
      LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
      sessionFactory.setDataSource(restDataSource());
      sessionFactory.setPackagesToScan(new String[] { "org.webapp.persistence.model" });
      sessionFactory.setHibernateProperties(hibernateProperties());

      return sessionFactory;
   }

   @Bean
   public DataSource restDataSource() {
      BasicDataSource dataSource = new BasicDataSource();
      dataSource.setDriverClassName(env.getProperty("jdbc.driverClassname"));
      dataSource.setUrl(env.getProperty("jdbc.url"));
      dataSource.setUsername(env.getProperty("jdbc.user"));
      dataSource.setPassword(env.getProperty("jdbc.pass"));

      return dataSource;
   }

   @Bean
   @Autowired
   public HibernateTransactionManager transactionManager(SessionFactory sessionFactory) {
      HibernateTransactionManager txManager = new HibernateTransactionManager();
      txManager.setSessionFactory(sessionFactory);

      return txManager;
   }

   @Bean
   public PersistenceExceptionTranslationPostProcessor exceptionTranslation() {
      return new PersistenceExceptionTranslationPostProcessor();
   }

   Properties hibernateProperties() {
      return new Properties() {
         /**
         * 
         */
        private static final long serialVersionUID = 1L;

        {
            setProperty("hibernate.hbm2ddl.auto", env.getProperty("hibernate.hbm2ddl.auto"));
            setProperty("hibernate.dialect", env.getProperty("hibernate.dialect"));
            setProperty("hibernate.globally_quoted_identifiers", "true");
         }
      };
   }
}

我的 persistence.properties 文件是:

jdbc.driverClassname=org.postgresql.Driver
jdbc.url=jdbc:postgresql://localhost:5432/wehavescience?charSet=LATIN1
jdbc.user=klebermo
jdbc.pass=123
hibernate.dialect=org.hibernate.dialect.PostgreSQLDialect
hibernate.show_sql=true
hibernate.hbm2ddl.auto=update

我的数据库有三个表，它们是:

CREATE TABLE usuario
(
  id serial NOT NULL,
  login character varying(100),
  senha character varying(100),
  CONSTRAINT pf_usuario PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE usuario
  OWNER TO klebermo;
GRANT ALL ON TABLE usuario TO klebermo;
GRANT SELECT ON TABLE usuario TO public;

表自动:

CREATE TABLE autorizacao
(
  id serial NOT NULL,
  nome character varying(100),
  CONSTRAINT pf_autorizacao PRIMARY KEY (id)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE autorizacao
  OWNER TO klebermo;
GRANT ALL ON TABLE autorizacao TO klebermo;
GRANT SELECT ON TABLE autorizacao TO public;

和表autorizacao_usuario

CREATE TABLE autorizacao_usuario
(
  id serial NOT NULL,
  usuario integer,
  autorizacao integer,
  CONSTRAINT pf_autorizacao_usuario PRIMARY KEY (id),
  CONSTRAINT fk_autorizacao FOREIGN KEY (autorizacao)
      REFERENCES autorizacao (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT fk_usuario FOREIGN KEY (usuario)
      REFERENCES usuario (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE autorizacao_usuario
  OWNER TO klebermo;
GRANT ALL ON TABLE autorizacao_usuario TO klebermo;
GRANT SELECT ON TABLE autorizacao_usuario TO public;

我的问题是，当我运行项目时，该用户无法访问数据库(只有postgres用户，即管理员用户)。

如何修改应用程序/数据库以允许其他用户访问表？

Answer 1

您需要create a user对于应用程序和 GRANT permissions连接和访问表。

我不建议您的应用程序使用管理员凭据。