create role

CREATE ROLE meow_admin LOGIN PASSWORD 'admin_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE meow_user LOGIN PASSWORD 'user_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;
CREATE ROLE meow_readonly LOGIN PASSWORD 'readonly_password' NOSUPERUSER INHERIT NOCREATEDB NOCREATEROLE NOREPLICATION;

CREATE DATABASE meow_db;

ALTER DATABASE meow_db OWNER TO meow_admin;

psivilege

-- PUBLIC視為當前所有角色 --
REVOKE USAGE ON SCHEMA public FROM PUBLIC;
GRANT USAGE ON SCHEMA public to meow_admin;
GRANT CREATE ON SCHEMA public to meow_admin;
GRANT USAGE ON SCHEMA public to meow_user;
GRANT USAGE ON SCHEMA public to meow_readonly;

GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLE users to meow_user;
GRANT SELECT, INSERT, UPDATE, DELETE, REFERENCES ON TABLE cats  to meow_user;

GRANT SELECT ON TABLE users to meow_readonly;
GRANT SELECT ON TABLE cats  to meow_readonly;

users table

CREATE TABLE users (
  id bigserial primary key,
  email varchar not null,
  password_digest varchar not null,
  name varchar not null,
  gender varchar not null,
  created_at timestamp with time zone not null default current_timestamp,
	updated_at timestamp with time zone not null default current_timestamp,
  deleted_at timestamp with time zone 
)

cats table

CREATE TABLE cats (
  id uuid primary key,
	user_id bigint not null,
	name vchar not null,
	gender vchar not null,

	created_at timestamp with time zone not null default current_timestamp,
	updated_at timestamp with time zone not null default current_timestamp,
  deleted_at timestamp with time zone 
)

constrains

ALTER TABLE cats ADD CONSTRAINT cats_fk_user_id FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE;

Index

CREATE UNIQUE INDEX CONCURRENTLY users_fk_email ON users(email);
CREATE INDEX CONCURRENTLY users_index_created_at ON users(created_at);
CREATE INDEX CONCURRENTLY cats_index_created_at ON cats(created_at);