📚 Create Database Objects¶

🔍 Prepare：¶

## OS and Software environment：

Environment name Version

OS Rocky Linux 9.x

DB PostgreSQL 16.X

1. Create Database（postgres）¶

1.1 Create Database Folder（Change SID)

su - postgres
mkdir -p /pgdata/SID_tbs/DB
chmod 700 /pgdata/SID_tbs/
ll -d /pgdata/SID_tbs/DB

1.2 Create tablespace（Change SID and DB)

su - postgres
psql -p 5436

CREATE TABLESPACE DB_tbs
OWNER postgres
LOCATION '/pgdata/SID_tbs/DB';
\db

Notiche: You must change SID and DB what you need

1.3 Create database and revoke public schema permission（Change DB)

su - postgres
psql -p 5436

CREATE DATABASE "DB"
WITH
OWNER = postgres
ENCODING = 'UTF8'
LC_COLLATE = 'en_US.UTF-8'
LC_CTYPE = 'en_US.UTF-8'
TABLESPACE = DB_tbs
CONNECTION LIMIT = -1;
revoke all on schema public from public;
\l

Notiche: DB name

2. Create User and schema（postgres）¶

2.1 Create User（postgres）

su - postgres
psql -p 5436

CREATE USER "username" WITH PASSWORD 'passwd';
\du

Users are global, and each instance can only have one account with the same name. Can Creating on all DB

2.2 Create Schema（postgres）

su - postgres
psql -p 5436

\c DB
CREATE SCHEMA "schemaname" AUTHORIZATION "username";
GRANT ALL ON SCHEMA "schemaname" TO "username";
\dn

2.3 Create readonly User（postgres）

su - postgres
psql -p 5436

CREATE USER "usernamereadonly" WITH PASSWORD 'passwd';
GRANT USAGE ON SCHEMA "schemaname" TO "usernamereadonly";  ###Must exist
GRANT SELECT ON SCHEMA "schemaname" TO "usernamereadonly";
GRANT SELECT ON "schemaname".TABLENAME TO "usernamereadonly";
\du

The schema is local, and different DBs can have multiple schemas with the same name. Creating them requires switching to the corresponding DB

Notiche: Create readonly user must be chose one If the Role&Schema name are not consistent, you may need to set search_path Example: Database Level： DB=# ALTER ROLE Username IN DATABASE "Databasename" SET search_path TO "schemaname, public";

Cluster Level： DB=# ALTER ROLE Username SET search_path TO "schemaname, public";