PostgreSQL 11 and Spring Data - Part 1 The Database

In the past I used PostgreSQL as an alternative to Oracle for my experiments. Using a cloud-based development environment is now more practical and efficient than installing a server.

But I regularly find myself in a place where connections are difficult and I cannot access the database or some services from my development environment. In these cases a complete environment installed locally on the laptop is an advantage.

Installation

I use Ubuntu 18.04 as my operating system and Ubuntu describes very well how to do it in the official documentation, so I just list the commands, where I added instructions to update the database to release 11:

1. $sudo apt-get install curl ca-certificates (only if curl not already installed)
2. $curl https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
3. $sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
4. $sudo apt update
5. $sudo apt install postgresql-11
6. $sudo -u postgres psql template1
7. postgres=# ALTER ROLE postgres with encrypted password 'YOUR_PASSWORD';
8. postgres=# \q
9. $sudo nano /etc/postgresql/11/main/pg_hba.conf
10. local   all        postgres                md5
11. <ctrl>X and answer yes
12. $sudo systemctl restart postgresql.service

Database

The management of the database requires a graphical tool to avoid a lot of typing work. I use the Ultimate version of Intellj that I already have for business reasons.

The development database is simply called laboratory. The PostgreSQL documentation is complete and easy to read so I'll just list the SQL statements I used to create the users and the database.

At the prompt login as postgres $ psql postgres postgres.

create role labadmin login createrole password 'labadmin';
comment on role labadmin is 'login for laboratory database administration';
create database laboratory owner labadmin;
comment on database laboratory is 'database for experiments';

postgres=# \i labadmin.sql

create role labuser login password 'labuser';
comment on role labuser is 'login for applications';
create schema business;
comment on schema business is 'schema containing all business related entities';

postgres=# \i laboratory.sql

drop table if exists business.B_LOGINS cascade;
drop sequence if exists business.B_LOGINS_id_seq cascade;

drop table if exists business.B_PERSONS cascade;
drop sequence if exists business.B_PERSONS_id_seq;

-- PERSONS --------------------------------------------------------------------

create sequence business.B_PERSONS_id_seq minvalue 1000;

create table business.B_PERSONS
(
  id numeric(16) default nextval('business.B_PERSONS_id_seq') not null,
  firstname varchar(64) not null,
  lastname varchar(64) not null,
  qualifier varchar(16),
  deleted timestamp
);

comment on table business.B_PERSONS is 'all people';

comment on column business.B_PERSONS.id is 'autoincremented primary key';

comment on column business.B_PERSONS.firstname is 'the name of the person (mandatory)';

comment on column business.B_PERSONS.lastname is 'the surname of the person';

comment on column business.B_PERSONS.qualifier is 'the role of the person: ADMIN, OPERATOR, CUSTOMER or undefined (NULL)';

comment on column business.B_PERSONS.deleted is 'the timestamp of the deletion';

alter table business.B_PERSONS
  add constraint B_PERSONS_pk
    primary key (id);

-- LOGINS ---------------------------------------------------------------------

create sequence business.B_LOGINS_id_seq minvalue 1000;

create table business.B_LOGINS
(
  id numeric(16) default nextval('business.B_LOGINS_id_seq')
    constraint B_LOGINS_pk
      primary key,
  b_persons_id numeric(16) not null
    constraint B_LOGINS_b_persons_id_fk
      references business.b_persons,
  name varchar(32) not null,
  password varchar(32) not null,
  locked numeric(1) not null,
  opt_structure varchar(4000),
  deleted timestamp
);

create unique index b_logins_name_uindex
 on business.b_logins (name);

comment on table business.B_LOGINS is 'all people logins';

comment on column business.B_LOGINS.id is 'autoincremented primary key';

comment on column business.B_LOGINS.b_persons_id is 'the ID of the related person';

comment on column business.B_LOGINS.name is 'UserLogin name or alias';

comment on column business.B_LOGINS.password is 'UserLogin password';

comment on column business.B_LOGINS.locked is '0 - false (UserLogin not locked), 1 - true (UserLogin locked)';

comment on column business.B_LOGINS.opt_structure is 'the structured string to handle One Time passwords';

comment on column business.B_LOGINS.deleted is 'the timestamp of the deletion';

postgres=# \i laboratory_tables.sql

-- LOGINS ---------------------------------------------------------------------
create or replace view business.LOGINS as
  select id, b_persons_id as persons_id, name, password, locked, opt_structure
    from business.b_logins
    where deleted is null;

comment on view business.logins is 'returns all logins that aren''t deleted';

-- PERSONS --------------------------------------------------------------------

create or replace view business.PERSONS as
  select id, firstname, lastname, qualifier
    from business.b_persons
    where deleted is null;

comment on view business.persons is 'returns all persons that aren''t deleted';

postgres=# \i laboratory_views.sql

drop function if exists business.check_if_person_deleted_trigger cascade;

drop function if exists business.check_if_person_deleted_trigger cascade;

create function business.check_if_person_deleted_trigger()
  returns trigger as $b_login$
declare
  delvar timestamp;
  relid numeric(16);
  delcur cursor for select deleted from business.b_persons where business.b_persons.id = relid;

begin

  if (TG_OP = 'UPDATE') then
    relid := NEW.b_persons_id;
    open delcur;
    fetch delcur into delvar;
    close delcur;
    raise notice 'new.deleted = %', NEW.deleted;
    raise notice 'delvar = %', delvar;
    if (NEW.deleted isnull AND delvar is not null) then
      raise exception 'Cannot undelete the UserLogin with ID % if the user is deleted!', relid;
    end if;
  end if;

  return null;

end; $b_login$
language PLPGSQL;

drop function if exists business.delete_person_trigger cascade;

create function business.delete_person_trigger()
  returns trigger as $b_persons$
begin

   if (TG_OP = 'UPDATE') then
      update business.b_logins
        set deleted = current_timestamp
      where b_persons_id = NEW.id;
   end if;

   return null;

end; $b_persons$
language PLPGSQL;

alter function business.delete_person_trigger() owner to labadmin;

postgres=# \i laboratory_functions.sql

-- B_LOGINS -------------------------------------------------------------------
drop trigger if exists b_logins_check_if_person_deleted on business.b_logins;
create trigger b_logins_check_if_person_deleted
  after update of deleted on business.b_logins
  for each row
  when (NEW.deleted is null)
  execute procedure business.check_if_person_deleted_trigger();

-- B_PERSONS ------------------------------------------------------------------
drop trigger if exists b_persons_delete_login on  business.b_persons;
create trigger b_persons_delete_login
  after update of deleted on business.b_persons
  for each row
  when (NEW.deleted is not null)
  execute procedure business.delete_person_trigger();

postgres=# \i laboratory_triggers.sql

-- DATABASE -------------------------------------------------------------------
-- DATABASE -------------------------------------------------------------------
revoke connect on database laboratory from labuser;
grant connect on database laboratory to labuser;

-- SCHEMA BUSINESS ------------------------------------------------------------
revoke usage on schema business from labuser;
grant usage on schema business to labuser;

-- FUNCTIONS ..................................................................
revoke all privileges on function business.check_if_person_deleted_trigger() from labuser;
grant execute on function business.check_if_person_deleted_trigger() to labuser;
revoke all privileges on function business.delete_person_trigger() from labuser;
grant execute on function business.delete_person_trigger() to labuser;

-- LOGINS ---------------------------------------------------------------------
revoke all privileges on business.b_logins_id_seq from labuser;
revoke all privileges on business.logins from labuser;
grant usage on sequence business.b_logins_id_seq to labuser;
grant select, insert, update, delete on table business.logins to labuser;

-- PERSONS --------------------------------------------------------------------
revoke all privileges on business.b_persons_id_seq from labuser;
revoke all privileges on business.persons from labuser;
grant usage on sequence business.b_persons_id_seq to labuser;
grant select, insert, update, delete on table business.persons to labuser;

postgres=# \i laboratory_grants.sql
postgres=# \q

At the prompt login as labuser $ psql laboratory labuser.

insert into business.persons (firstname, lastname, qualifier) values ('Lovely', 'Girl', 'CUSTOMER');
insert into business.persons (firstname, lastname, qualifier) values ('Pretty', 'Woman', 'CUSTOMER');
insert into business.persons (firstname, lastname, qualifier) values ('Nice', 'Guy', 'CUSTOMER');
insert into business.persons (firstname, lastname, qualifier) values ('Handsome', 'Man', 'CUSTOMER');
insert into business.persons (firstname, lastname, qualifier) values ('Cocco', 'Drilli', null);

postgres=# \i persons.sql

insert into business.logins (persons_id, name, password, locked) values (1000, 'lovely', 'girl', 0);
insert into business.logins (persons_id, name, password, locked) values (1001, 'pretty', 'woman', 0);
insert into business.logins (persons_id, name, password, locked) values (1002, 'nice', 'guy', 0);
insert into business.logins (persons_id, name, password, locked) values (1003, 'handsome', 'man', 0);
insert into business.logins (persons_id, name, password, locked) values (1004, 'cocco', 'drilli', 0);

postgres=# \i login.sql