1

In my spring application, I have the following Hibernate class to access my postgresql database:

@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");
         }
      };
   }
}

my persitence.properties file is:

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

My database have three tables, they are:

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;

Table autorizacoes:

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;

and table 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;

My problem is that when I run the project, the database can't be accessed by this user (only the postgres user, who is the admin user).

How I can modify the application/database to permit other user access the tables?

3
  • 1
    And what is the error message you get? Commented Mar 31, 2014 at 12:18
  • the error is Caused by: org.postgresql.util.PSQLException: FATAL: password authentication failed for user "klebermo", but i am sure the password is correct (i even change to 'pwd' for test purposes) Commented Mar 31, 2014 at 12:23
  • Well, the password isn't correct, or you're connecting to a different DB than you think you are. Check the PostgreSQL server error logs for a more detailed message. Commented Apr 1, 2014 at 3:57

1 Answer 1

1

You need to create a user for the application and GRANT permissions to connect and access tables.

I would not recommend using the admin credentials for your application.

Sign up to request clarification or add additional context in comments.

7 Comments

but it was what i did. the user 'klebermo', the one i want use, have all the proper permissions to access the database.
Apparently not. You must have missed something, because PostgreSQL disagrees with you.
Maybe you right, but when I change back the user to postgres in my persistence.properties, no error is displayed, but I still get a invalid login page.
Make sure you're connecting to the server where you created the klebermo user.
I just create a new database from scratch with owner 'klebermo', with the same table from my previous one (who was created with owner 'postgres' and after changed to 'klebermo'), and still don't work
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.