Steps to Create Users and Assign Databases in PostgreSQL

Log in to PostgreSQL:

psql -U postgres

Create two new users (user1 and user2):

CREATE USER user1 WITH PASSWORD 'yourPassword';
CREATE USER user2 WITH PASSWORD 'yourPassword';

Create databases for each user:

CREATE DATABASE user1dbshare OWNER user1;
CREATE DATABASE user1dbprivate OWNER user1;
CREATE DATABASE user2dbshare OWNER user2;
CREATE DATABASE user2dbprivate OWNER user2;

Create a common_readonly role and configure it to access shared data for all users:

CREATE ROLE common_readonly NOLOGIN;
GRANT CONNECT ON DATABASE user1dbshare TO common_readonly;
GRANT CONNECT ON DATABASE user2dbshare TO common_readonly;

GRANT USAGE ON SCHEMA public TO common_readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO common_readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO common_readonly;

GRANT common_readonly TO user1;
GRANT common_readonly TO user2;

Log in to each database and create a table:

For user1dbshare:

\c user1dbshare user1
CREATE TABLE memo (
    id SERIAL PRIMARY KEY,
    key_words VARCHAR(255),
    content TEXT,
    english_content TEXT
);

For user1dbprivate:

\c user1dbprivate user1
CREATE TABLE memo (
    id SERIAL PRIMARY KEY,
    key_words VARCHAR(255),
    content TEXT,
    english_content TEXT
);

For user2dbshare:

\c user2dbshare user2
CREATE TABLE memo (
    id SERIAL PRIMARY KEY,
    key_words VARCHAR(255),
    content TEXT,
    english_content TEXT
);

For user2dbprivate:

\c user2dbprivate user2
CREATE TABLE memo (
    id SERIAL PRIMARY KEY,
    key_words VARCHAR(255),
    content TEXT,
    english_content TEXT
);

Exit:

\q