Announcement Announcement Module
Collapse
No announcement yet.
New ACL Module Page Title Module
Move Remove Collapse
This topic is closed
X
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • New ACL Module

    Hi, I'm having difficulty getting the contacts sample that ships with Spring Security 2 M1 to use a PostgreSQL DB rather than HSQLDB.

    I've been hacking away at a PostgreSQL table creation script based on the setup script found in DataSourcePopulator of the contacts sample but have run into trouble due to the way IDs are generated / set:

    Code:
    SEVERE: Exception sending context initialized event to listener instance of class org.springframework.web.context.ContextLoaderListener
    org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'dataSourcePopulator' defined in class path resource [applicationContext-common-business.xml]: Invocation of init method failed; nested exception is org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO acl_sid (id, principal, sid) VALUES (null, ?, ?)]; ERROR: null value in column "id" violates not-null constraint; nested exception is org.postgresql.util.PSQLException: ERROR: null value in column "id" violates not-null constraint
    Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [INSERT INTO acl_sid (id, principal, sid) VALUES (null, ?, ?)]; ERROR: null value in column "id" violates not-null constraint; nested exception is org.postgresql.util.PSQLException: ERROR: null value in column "id" violates not-null constraint
    Caused by: org.postgresql.util.PSQLException: ERROR: null value in column "id" violates not-null constraint
    	at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1548)
    	at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1316)
    	at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:191)
    	at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:452)
    	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:351)
    	at org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2Statement.java:305)
    ...
    ...
    In this thread Diatonicman, refers to obeying the contract for the new ACL service... where can I find details of this contract? The only documentation I've been able to find relating to the new ACL module is the source code provided for the sample apps :s

    Originally posted by diatonicman View Post
    ... Thus the long way to a short answer is no you don't need to use the schema. Obey the contract for acl service and it should be fine. Thats what I am doing. ...
    .

    Thanks in advance.
    Craig

  • #2
    The contract for the service is the javadocs and what you can glean by reading the source code...;-)

    The acl code expects that those id fields will get populated via a trigger, auto numbers, identity, or whaatever postgres provides to support this. You will likely need to examine most of the db queries in the aclservice as well as the BasicLookupStrategy to make sure they are postgres compatible. I know nothing about postgres but i think you need the serial datatype. You may also need to remove the null insert value from your insert statements....

    Good luck

    Comment


    • #3
      Hi diatonicman,

      Thanks for your response. I've been busy porting the schema from HSQL to PostgreSQL. I've almost finished rewriting the classes from the org.springframework.security.acls.jdbc package to use PostgreSQL... It's just booted under Tomcat for the first time without error

      The PostgreSQL setup script I've used is:

      Code:
      DROP TABLE IF EXISTS acl_sid CASCADE;
      CREATE TABLE acl_sid
      (
        id serial PRIMARY KEY,
        principal boolean NOT NULL,
        sid character varying NOT NULL,
        CONSTRAINT unique_uk_1 UNIQUE (sid, principal)
      ) 
      WITHOUT OIDS;
      ALTER TABLE acl_sid OWNER TO securecontacts;
      
      DROP TABLE IF EXISTS acl_class CASCADE;
      CREATE TABLE acl_class
      (
        id serial PRIMARY KEY,
        class character varying NOT NULL,
        CONSTRAINT unique_uk_2 UNIQUE (class)
      ) 
      WITHOUT OIDS;
      ALTER TABLE acl_class OWNER TO securecontacts;
      
      DROP TABLE IF EXISTS acl_object_identity CASCADE;
      CREATE TABLE acl_object_identity
      (
        id serial PRIMARY KEY,
        object_id_class bigint NOT NULL,
        object_id_identity bigint NOT NULL,
        parent_object bigint,
        owner_sid bigint,
        entries_inheriting boolean NOT NULL,
        CONSTRAINT unique_uk_3 UNIQUE (object_id_class, object_id_identity),
        CONSTRAINT foreign_fk_1 FOREIGN KEY (parent_object) REFERENCES acl_object_identity (id),
        CONSTRAINT foreign_fk_2 FOREIGN KEY (object_id_class) REFERENCES acl_class (id),
        CONSTRAINT foreign_fk_3 FOREIGN KEY (owner_sid) REFERENCES acl_sid (id)
      ) 
      WITHOUT OIDS;
      ALTER TABLE acl_object_identity OWNER TO securecontacts;
      
      DROP TABLE IF EXISTS acl_entry CASCADE;
      CREATE TABLE acl_entry
      (
        id serial PRIMARY KEY,
        acl_object_identity bigint NOT NULL,
        ace_order int NOT NULL,
        sid bigint NOT NULL,
        mask integer NOT NULL,
        granting boolean NOT NULL,
        audit_success boolean NOT NULL,
        audit_failure boolean NOT NULL,
        CONSTRAINT unique_uk_4 UNIQUE (acl_object_identity, ace_order),
        CONSTRAINT foreign_fk_4 FOREIGN KEY (acl_object_identity) REFERENCES acl_object_identity (id),
        CONSTRAINT foreign_fk_5 FOREIGN KEY (sid) REFERENCES acl_sid (id)
      ) 
      WITHOUT OIDS;
      ALTER TABLE acl_entry OWNER TO securecontacts;
      
      DROP TABLE IF EXISTS users CASCADE;
      CREATE TABLE users
      (
        username character varying NOT NULL PRIMARY KEY,
        password character varying NOT NULL,
        enabled boolean NOT NULL
      ) 
      WITHOUT OIDS;
      ALTER TABLE users OWNER TO securecontacts;
      
      DROP TABLE IF EXISTS authorities CASCADE;
      CREATE TABLE authorities
      (
        username character varying NOT NULL,
        authority character varying NOT NULL,
        CONSTRAINT fk_authorities_users FOREIGN KEY(username) REFERENCES users (username)
      ) 
      WITHOUT OIDS;
      ALTER TABLE authorities OWNER TO securecontacts;
      
      DROP INDEX IF EXISTS ix_auth_username CASCADE;
      CREATE UNIQUE INDEX ix_auth_username ON authorities
      (
      	username,
      	authority
      );
      
      DROP TABLE IF EXISTS contacts CASCADE;
      CREATE TABLE contacts
      (
        id serial PRIMARY KEY,
        contact_name character varying NOT NULL,
        email character varying NOT NULL
      ) 
      WITHOUT OIDS;
      ALTER TABLE contacts OWNER TO securecontacts;
      I needed to change the SQL in JdbcMutableAclService and BasicLookupStrategy. I'll post the code for JdbcMutableAclService and BasicLookupStrategy once I've debugged a few remaining runtime issues

      Thanks again.
      Craig

      Comment


      • #4
        securecontacts is the PostgreSQL user I've setup for JDBC access... Lines setting permissions for securecontacts probably aren't needed tbh

        Comment


        • #5
          I've got the contacts sample working using PostgreSQL and have attached the updates to this thread.
          The packages for various classes in the contacts sample have been moved - This just helped me to get to grips with the responsibilty of the classes within the sample.
          I'd be glad to hear feedback
          Craig

          Comment

          Working...
          X