Hi Uxio, Sure I can share that. I had to do a few tweaks to the database. The first is if you are using SAML, you need to change the samlobject columns from varchar(5000) to text, because signed assertions will exceed those 5000 characters really fast.
Next you want to update the constraints for tables saml2_artifacts and
saml2_attribute_query_tickets to cascade on delete. I ran into a situation
where the TGT got removed but the SAML stuff stayed behind, causing
exceptions to be throw in my log a lot.
Finally the biggest thing you need to do is enable the lo module by running
the query "CREATE EXTENSION lo;" so you can have lo_manager function take
care of the oid fields, because jdbc and odbc both handle removing large
objects in postgresql wrong. In that they don't exist in the table, so when
they remove the row, the objects get orphaned and just take up space
For each oid field in the tables ticketgrantingticket and serviceticket
you'll want to create the following trigger:
CREATE TRIGGER t_trigger_name BEFORE UPDATE OR DELETE ON target_table
FOR EACH ROW EXECUTE PROCEDURE lo_manage(target_column);
--- cas.properties --
cas.ticket.registry.jpa.url=jdbc:postgresql:cas-ticket-registry
cas.ticket.registry.jpa.dialect=org.hibernate.dialect.PostgreSQL95Dialect
cas.ticket.registry.jpa.user=<redacted>
cas.ticket.registry.jpa.ddlAuto=none
cas.ticket.registry.jpa.password=<redacted>
cas.ticket.registry.jpa.driverClass=org.postgresql.Driver
cas.ticket.registry.jpa.crypto.signing.key=<redacted>
cas.ticket.registry.jpa.crypto.signing.keySize=512
cas.ticket.registry.jpa.crypto.encryption.key=<redacted>
cas.ticket.registry.jpa.crypto.encryption.keySize=16
cas.ticket.registry.jpa.crypto.alg=AES
cas.ticket.registry.jpa.crypto.enabled=true
-- end file--
Make sure ddlAuto is none or else all your changes will get overwritten on
the next reboot.
Attached is my SQL schema dump from my modified database.
Enjoy
--Mike K
On Wed, Feb 7, 2018 at 4:38 AM, Uxío Prego <[email protected]> wrote:
> I'm sorry I can't help you, but it would be very sweet if you could share
> your effective serviceticket or ticketgrantingticket table schema from the
> times when you were using PostgrelSQL as ticket registry for CAS 5...
>
> Regards,
>
> Uxío Prego
>
>
>
> Madiva Soluciones
> CL / SERRANO GALVACHE 56
> <https://maps.google.com/?q=CL+/+SERRANO+GALVACHE+56&entry=gmail&source=g>
> BLOQUE ABEDUL PLANTA 4
> 28033 MADRID
> +34 917 56 84 94 <+34%20917%2056%2084%2094>
> www.madiva.com
> www.bbva.com
>
> The activity of email inboxes can be systematically tracked by colleagues,
> business partners and third parties. Turn off automatic loading of images
> to hamper it.
>
> 2018-02-07 9:03 GMT+00:00 Mike Kromarek <[email protected]>:
>
>> I recently switched from the Postgresql JPA ticket registry to MongoDB
>> and am having a strange issue. The authentication succeeds, but then it
>> fails to add the ticket to the mongo database, causing the process to fail
>> and return to the login screen.
>>
>> -- cas.properties --
>> cas.ticket.registry.mongo.host=localhost
>> cas.ticket.registry.mongo.userId=<redacted>
>> cas.ticket.registry.mongo.password=<redacted>
>> cas.ticket.registry.mongo.databaseName=casdb
>> cas.ticket.registry.mongo.collectionName=cas-ticket-registry
>> cas.ticket.registry.mongo.dropCollection=false
>> cas.ticket.registry.mongo.timeout=5000
>> cas.ticket.registry.mongo.writeConcern=NORMAL
>> cas.ticket.mongo.conns.lifetime=60000
>> cas.ticket.mongo.conns.perHost=10
>> cas.ticket.registry.mongo.idleTimeout=30000
>>
>>
>> CAS connects to the database with the specified user, makes all the
>> tables and seems like everything should be good. Then it encodes the TGT,
>> but fails to add it.
>>
>>
>> 2018-02-07 00:46:30,024 DEBUG [org.apereo.cas.ticket.factory
>> .DefaultTicketGrantingTicketFactory] - <Encoded ticket-granting ticket
>> id [TGT-******************************************3wOfaglzGL-JN
>> pegctV--qfA0S5-xCE-aws-stage-cas.highline.edu]>
>> 2018-02-07 00:46:30,025 DEBUG
>> [org.apereo.cas.ticket.registry.MongoDbTicketRegistry]
>> - <Adding ticket [TGT-******************************************
>> 3wOfaglzGL-JNpegctV--qfA0S5-xCE-aws-stage-cas.highline.edu]>
>> 2018-02-07 00:46:30,118 ERROR
>> [org.apereo.cas.ticket.registry.MongoDbTicketRegistry]
>> - <Failed adding [TGT-******************************************
>> 3wOfaglzGL-JNpegctV--qfA0S5-xCE-aws-stage-cas.highline.edu]:
>> [java.lang.NullPointerException]>
>> 2018-02-07 00:46:30,118 DEBUG
>> [org.apereo.cas.AbstractCentralAuthenticationService]
>> - <Publishing [org.apereo.cas.support.events
>> .ticket.CasTicketGrantingTicketCreatedEvent@2c84b7f8[ticketG
>> rantingTicket=TGT-******************************************
>> 3wOfaglzGL-JNpegctV--qfA0S5-xCE-aws-stage-cas.highline.edu]]>
>>
>> Has anyone else ran into this?
>>
>> --Mike K
>>
>>
>> --
>> - Website: https://apereo.github.io/cas
>> - Gitter Chatroom: https://gitter.im/apereo/cas
>> - List Guidelines: https://goo.gl/1VRrw7
>> - Contributions: https://goo.gl/mh7qDG
>> ---
>> 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 [email protected].
>> To view this discussion on the web visit https://groups.google.com/a/ap
>> ereo.org/d/msgid/cas-user/051a23e8-bb02-48a3-ab26-86b9a2fa3c
>> 40%40apereo.org
>> <https://groups.google.com/a/apereo.org/d/msgid/cas-user/051a23e8-bb02-48a3-ab26-86b9a2fa3c40%40apereo.org?utm_medium=email&utm_source=footer>
>> .
>>
>
> --
> - Website: https://apereo.github.io/cas
> - Gitter Chatroom: https://gitter.im/apereo/cas
> - List Guidelines: https://goo.gl/1VRrw7
> - Contributions: https://goo.gl/mh7qDG
> ---
> 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 [email protected].
> To view this discussion on the web visit https://groups.google.com/a/
> apereo.org/d/msgid/cas-user/CANidDKYVAdV7t0SdMHA1gF3MF4AN_
> seyJMS_bN8CWAYr3RmTOw%40mail.gmail.com
> <https://groups.google.com/a/apereo.org/d/msgid/cas-user/CANidDKYVAdV7t0SdMHA1gF3MF4AN_seyJMS_bN8CWAYr3RmTOw%40mail.gmail.com?utm_medium=email&utm_source=footer>
> .
>
--
- Website: https://apereo.github.io/cas
- Gitter Chatroom: https://gitter.im/apereo/cas
- List Guidelines: https://goo.gl/1VRrw7
- Contributions: https://goo.gl/mh7qDG
---
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 [email protected].
To view this discussion on the web visit
https://groups.google.com/a/apereo.org/d/msgid/cas-user/CALfsmq6N_F1JxS04x4rR2HJOM4Jj%2BVYxMj6DRyLdPJbDU1YTtg%40mail.gmail.com.
20180207024922.sql
Description: Binary data
