Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.
CREATE TABLE AuthorizationSourceType
(
  AuthSourceTypeCd  CHAR(1)      NOT NULL
 ,Name              VARCHAR(50)  NOT NULL
 ,CONSTRAINT PK_AuthorizationSourceType PRIMARY KEY (AuthSourceTypeCd)
 ,CONSTRAINT AK_AuthorizationSourceType UNIQUE (Name)
)

CREATE TABLE


INSERT INTO AuthorizationSourceType
VALUES
  ('I','Internal')
 ,('E','External')

INSERT 0 2


CREATE TABLE AuthorizationSource
(
  AuthSourceCd      CHAR(4)      NOT NULL
 ,AuthSourceTypeCd  CHAR(1)      NOT NULL
 ,Name              VARCHAR(50)  NOT NULL
 ,CONSTRAINT FK_AuthorizationSource_Categorized_As_AuthorizationSourceType FOREIGN KEY (AuthSourceTypeCd) REFERENCES AuthorizationSourceType (AuthSourceTypeCd)
 ,CONSTRAINT PK_AuthorizationSource PRIMARY KEY (AuthSourceCd)
 ,CONSTRAINT AK_AuthorizationSource UNIQUE (Name)
)

CREATE TABLE


INSERT INTO AuthorizationSource
VALUES
  ('EML','I','Email')
 ,('BIRD','E','BirdApp')
 ,('FACE','E','Facepage')
 ,('HOOL','E','Hooli')

INSERT 0 4


CREATE TABLE AppUser
(
  UserId  INT  NOT NULL
 ,CONSTRAINT PK_User PRIMARY KEY (UserId)
)

CREATE TABLE


CREATE TABLE UserAuthorization
(
  UserId        INT      NOT NULL
 ,AuthSourceCd  CHAR(4)  NOT NULL
 ,CONSTRAINT FK_UserAuthorization_For_User FOREIGN KEY (UserId) REFERENCES AppUser (UserId)
 ,CONSTRAINT FK_UserAuthorization_Uses_AuthorizationSource FOREIGN KEY (AuthSourceCd) REFERENCES AuthorizationSource (AuthSourceCd)
 ,CONSTRAINT PK_UserAuthorization PRIMARY KEY (UserId, AuthSourceCd)
)

CREATE TABLE


CREATE TABLE UserEmailAuthorization
(
  UserId        INT           NOT NULL
 ,AuthSourceCd  CHAR(4)       NOT NULL
 ,Email         VARCHAR(250)  NOT NULL
 /* encrypted password could go here, possibly better in other table w/salt */
 ,CONSTRAINT FK_UserEmail_Used_For_UserAuthorization FOREIGN KEY (UserId, AuthSourceCd) REFERENCES UserAuthorization (UserId, AuthSourceCd)
 ,CONSTRAINT PK_UserEmailAuthorization PRIMARY KEY (UserId)
 ,CONSTRAINT AK_UserEmailAuthorization UNIQUE (Email)
 ,CONSTRAINT CK_AuthSourceCd_Is_Email CHECK (AuthSourceCd = 'EML')
)

CREATE TABLE


CREATE TABLE UserExternalAuthorization
(
  UserId           INT          NOT NULL
 ,AuthSourceCd     CHAR(4)      NOT NULL
 ,ExternalLoginId  VARCHAR(50)  NOT NULL /* Not sure of datatype here */
 ,CONSTRAINT FK_UserExternalAuthorization_Is_UserAuthorization FOREIGN KEY (UserId, AuthSourceCd) REFERENCES UserAuthorization (UserId, AuthSourceCd)
 ,CONSTRAINT PK_UserExternalAuthorization PRIMARY KEY (UserId, AuthSourceCd)
 ,CONSTRAINT AK_UserExternalAuthorization UNIQUE (AuthSourceCd, ExternalLoginId)
)
/* Needs trigger to reject insert if the AuthorizationSourceType is not external */

CREATE TABLE


CREATE TABLE Login
(
  UserId        INT           NOT NULL
 ,LoginTs       TIMESTAMP(6)  NOT NULL
 ,AuthSourceCd  CHAR(4)       NOT NULL
 ,Email         VARCHAR(250)  NOT NULL /* This is an audit value - email entered or returned by external auth*/
 ,CONSTRAINT FK_Login_With_UserAuthorization FOREIGN KEY (UserId, AuthSourceCd) REFERENCES UserAuthorization (UserId, AuthSourceCd)
 ,CONSTRAINT PK_Login PRIMARY KEY (UserId, LoginTs)
)

CREATE TABLE


/* Create user with email */
INSERT INTO AppUser VALUES (1);

INSERT INTO UserAuthorization VALUES(1,'EML');

INSERT INTO UserEmailAuthorization VALUES (1,'EML','jkl@mnopqrs.com');

INSERT 0 1



INSERT 0 1



INSERT 0 1


/* Create user with Hooli login */
INSERT INTO AppUser VALUES (2);

INSERT INTO UserAuthorization VALUES (2,'HOOL');

INSERT INTO UserExternalAuthorization VALUES (2,'HOOL','2trlsk83@%RTSddfjsjkdkjs');

INSERT 0 1



INSERT 0 1



INSERT 0 1


/* Add Facepage login to user 1 */
INSERT INTO UserAuthorization VALUES(1,'FACE');
INSERT INTO UserExternalAuthorization VALUES (1,'FACE','982346nkdsrWWW^YTYghdd');

INSERT 0 1



INSERT 0 1


/* User 1 logs in with email */
INSERT INTO Login VALUES (1,NOW(),'EML','jkl@mnopqrs.com')

INSERT 0 1


/* User 2 logs in with Hooli */
INSERT INTO Login VALUES (2,NOW(),'HOOL','abc@defghijk.net')

INSERT 0 1


/* User 1 logs in with Facepage (returns different email) */
INSERT INTO Login VALUES (1,NOW(),'FACE','jkl_work@bidness.com')

INSERT 0 1


SELECT
  *
FROM
  Login


userid
logints
authsourcecd
email


1
2023-09-29 22:08:44.776248
EML
jkl@mnopqrs.com

2
2023-09-29 22:08:44.778062
HOOL
abc@defghijk.net

1
2023-09-29 22:08:44.778978
FACE
jkl_work@bidness.com



SELECT 3
userid	logints	authsourcecd	email
1	2023-09-29 22:08:44.776248	EML	jkl@mnopqrs.com
2	2023-09-29 22:08:44.778062	HOOL	abc@defghijk.net
1	2023-09-29 22:08:44.778978	FACE	jkl_work@bidness.com