Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.

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?

share|improve this question
1  
And what is the error message you get? –  a_horse_with_no_name Mar 31 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) –  Kleber Mota Mar 31 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. –  Craig Ringer Apr 1 at 3:57

1 Answer 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.

share|improve this answer
    
but it was what i did. the user 'klebermo', the one i want use, have all the proper permissions to access the database. –  Kleber Mota Mar 31 at 12:20
    
Apparently not. You must have missed something, because PostgreSQL disagrees with you. –  duffymo Mar 31 at 12:22
    
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. –  Kleber Mota Mar 31 at 12:50
    
Make sure you're connecting to the server where you created the klebermo user. –  Milen A. Radev Mar 31 at 13:18
    
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 –  Kleber Mota Mar 31 at 13:18

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.