CODEWITHSUNDEEP
springspring-securityh2
Anders Persson
Anders Persson

Reputation: 337

Spring Security creates a table named USERS

When starting my application Spring Security is creating two tables "users" and "authorities" in my h2 database. My problem is that when I start the application a second time the database is already populated with these two tables but Spring Security till wants to create them. How can I tell Spring Secutiry to only create the tables if they do not exists? Or should I change something else to solve this problem?

That Spring Secutiry is creating the two tables I got from the blog describing some of the code that I have looked at. It says:"Some notes on the data model. No real username nor password is stored in the USERS-table. The table is defined by Spring Security for general purpose usage and not specific for social login use-cases."

Link to the blog

The error messages I get,some of the java stack trace, are:

Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource [org/springframework/security/core/userdetails/jdbc/users.ddl]: 
create table users(username varchar_ignorecase(50) not null primary key,password varchar_ignorecase(500) not null,enabled boolean not null); nested exception is org.h2.jdbc.JdbcSQLException: Table "USERS" already exists; 
SQL statement: create table users(username varchar_ignorecase(50) not null primary key,password varchar_ignorecase(500) not null,enabled boolean not null) [42101-175]

and

Caused by: org.springframework.jdbc.datasource.init.ScriptStatementFailedException: Failed to execute SQL script statement at line 1 of resource class path resource [org/springframework/security/core/userdetails/jdbc/users.ddl]:    
create table users(username varchar_ignorecase(50) not null primary key,password varchar_ignorecase(500) not null,enabled boolean not null); nested exception is org.h2.jdbc.JdbcSQLException: Table "USERS" already exists;
SQL statement: create table users(username varchar_ignorecase(50) not null primary key,password varchar_ignorecase(500) not null,enabled boolean not null) [42101-175]

I am not sure about where this happens in the code. I have an entityManageFactory that looks like this

@Autowired DataSource dataSource;
@Bean
public EntityManagerFactory entityManagerFactory() {
    HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
    vendorAdapter.setGenerateDdl(true);
    Properties props = new Properties();
    props.put("hibernate.dialect", H2Dialect.class.getName());
    props.put("hibernate.format_sql", "true");
    props.put("spring.jpa.properties.hibernate.hbm2ddl.auto", "update");
    props.put("spring.datasource.dbCreate", "update");
    LocalContainerEntityManagerFactoryBean factory = new LocalContainerEntityManagerFactoryBean();
    factory.setJpaVendorAdapter(vendorAdapter);
    factory.setPackagesToScan("mypackage.demo");
    factory.setDataSource(dataSource);
    factory.setJpaProperties(props);
    factory.afterPropertiesSet();

    return factory.getObject();
}

Upvotes: 2

Views: 4894

Answers (2)

redeveloper
redeveloper

Reputation: 119

I handled this with such:

    JdbcUserDetailsManagerConfigurer<AuthenticationManagerBuilder> configurer =
            auth.jdbcAuthentication().dataSource(dataSource);

    // Check if the auth schema has been populated (from a persistent source); if not, set it to populate
    if (!dataSource.getConnection().getMetaData().getTables(null, "", "USERS", null).first()) {
        configurer = configurer.withDefaultSchema();
    }

Upvotes: 0

Anders Persson
Anders Persson

Reputation: 337

The USERS tables (and some others) was created in my case when the Spring Security was configured. This is part of that code:

public class SecurityConfiguration extends WebSecurityConfigurerAdapter {

@Autowired
private DataSource dataSource;

@Autowired
public void configureGlobal(AuthenticationManagerBuilder auth) throws Exception {
    auth.
        jdbcAuthentication()
        .dataSource(dataSource)
        .withDefaultSchema(); 
}

If I start with an already existing database that is configured and data deployed within it I remove the row:

      .withDefaultSchema();

and Spring Security will use the existing tables in the database.

Upvotes: 7

Related Questions