JPA-Ticket-Registry on postgres

[Hal Deadman]

I am trying to use the JPA ticket registry in 5.0.1 with Postgres. I can't find any DDL to create the schema so I am letting hibernate create the tables.
 
In this document https://apereo.github.io/cas/5.0.x/installation/JPA-Ticket-Registry.html they describe four options for ddlAuto:

validate - validate the schema, but make no changes to the database.
update - update the schema.
create - create the schema, destroying previous data.
create-drop - drop the schema at the end of the session.

If I want to have multiple CAS servers pointing at the same set of tables in the same DB, it doesn't seem like create or create-drop would make sense because they both appear to drop the tables on start-up. The OID of the table changes with the create option on startup so i assume it is re-creating the table.

I would like to use "validate" but after letting the tables be created using the "create" option the validate options fails with an error like:

Caused by: org.hibernate.tool.schema.spi.SchemaManagementException: Schema-valid
ation: wrong column type encountered in column [lockVer] in table [locks]; found
 [int4 (Types#INTEGER)], but expecting [integer default 0 (Types#BIGINT)]

The DDL for the table extracted by pgAdmin shows the column is: "lockVer integer NOT NULL DEFAULT 0" which seems to be what it is expecting.


I can start up CAS with ddlAuto set to "update" but when I login I get an an error:

<ERROR: cannot execute SELECT FOR UPDATE in a read-only transaction>
2016-12-15 09:52:16,249 ERROR [org.apereo.cas.ticket.registry.JpaTicketRegistry]
 - <Error getting ticket TGT-**********************************************3osVS
fZwtw-XYZXYZXYZV from registry.>

If I set jpaLockingTgtEnabled to false then I can login but it seems like locking should work in postgres. It appears that DefaultTicketRegistrySupport is @Transactional readonly=true and the getAuthenticatedPrincipalFrom method starts a read-only transaction which eventually fails when hibernate tries to lock row with "for update" clause. Is there another TicketRegistrySupport bean I should be using?

A possibly related issue, I am seeing the DefaultTicketRegistry bean being created from CasCoreTicketsConfiguration despite the bean being @ConditionalOnMissingBean(name = "ticketRegistry"). The jpaTicketRegistry bean which appears to be aliased to ticketRegistry is definitely being used so I am not sure why the DefaultTicketRegistry bean is being created.

To summarize:

 - JPA ticket registry seems to be working but the ddlAuto validate doesn't seem to work (and create/create-drop seem no better than in-memory if they are lost on restart)

 - jpa locking is not working for me

 - I don't know why the DefaultTicketRegistry bean is being created. 

[Ray Bon]

Hal,

Lots of questions. I will tackle JPA.

I have been going through some angst trying to get JPA to jive with Oracle (no success, I think it is a hibernate 5 issue not CAS 4.2.6). I extracted the create/alter statements and created the tables manually (sql below). Manual table creation means you will have to check the domain classes for changes when upgrading and apply the appropriate alter statements. The default ddlAuto is 'create-drop' so you _must_ provide a value when going to production. Any value will do. If 'validate' is not working, try 'update', if that does not work try 'donothing' or 'ignore' or 'blah' and hibernate will skip over ddlAuto behaviour.

Ray

Sql for Oracle including fields required by services management application:


create sequence hibernate_sequence start with 1 increment by 1;

create table RegisteredServiceImplProperty (
  id number(19, 0) not null,
  property_values blob,
  primary key (id)
);

create table RegisteredServiceImpl (
  id number(19, 0) NOT NULL ENABLE,
  access_strategy blob,
  attribute_release blob,
  bypassApprovalPrompt varchar(255),
  clientId varchar(255),
  clientSecret varchar(255),
  description varchar(255) not null,
  evaluation_order integer not null,
  expression_type VARCHAR(15) DEFAULT 'ant' not null,
  logo varchar(255),
  logout_type integer,
  logout_url varchar(255),
  name varchar(255) not null,
  proxy_policy blob,
  public_key blob,
  required_handlers blob,
  serviceId varchar(255) not null,
  theme varchar(255),
  username_attr blob,
  primary key (id)
);

create table RegisteredServiceImpl_Props (
  AbstractRegisteredService_id number(19, 0) not null,
  properties_id number(19, 0) not null,
  properties_KEY varchar(255) not null,
  primary key (AbstractRegisteredService_id, properties_KEY)
);

alter table RegisteredServiceImpl_Props
  add constraint FK_Property
  foreign key (properties_id)
  references RegisteredServiceImplProperty;

alter table RegisteredServiceImpl_Props
  add constraint FK_RegisteredServiceImpl
  foreign key (AbstractRegisteredService_id)
  references RegisteredServiceImpl;

--
- CAS gitter chatroom: https://gitter.im/apereo/cas
- CAS mailing list guidelines: https://apereo.github.io/cas/Mailing-Lists.html
- CAS documentation website: https://apereo.github.io/cas
- CAS project website: https://github.com/apereo/cas
---
You received this message because you are subscribed to the Google Groups "CAS Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to cas-user+u...@apereo.org.
To view this discussion on the web visit https://groups.google.com/a/apereo.org/d/msgid/cas-user/CADM%2BNBPsCn03U7nGyF1-YVhO3PKWyLpvXgNse-KrX%3D6fPS55fA%40mail.gmail.com.

-- 
Ray Bon
Programmer Analyst
Development Services, University Systems
2507218831 | CLE C023 | rb...@uvic.ca

[Adrian Chong]

Setting as "none". Then the Hibernate will do nothing, if you prefer the tables are manually created.