Skip to content

Create Database Objects

1. Create Database

  • 1.1 Create Database Folder（Change SEQAS to your actual SID)
su - postgres
mkdir -p /pgdata/SEQAS_tbs/ZABBIX
chmod 700 /pgdata/SEQAS_tbs/
ll -d /pgdata/SEQAS_tbs/

image-20260202124133059

  • 1.2 Create tablespace（Change SID and DB)
psql -p 5432

create tablespace "ZABBIX"
owner postgres
location '/pgdata/SEQAS_tbs/ZABBIX';
\db

Notiche: You must change SID and DB what you need

image-20260202124601783

  • 1.3 Create database and revoke public schema permission（Change SEQAS to your actual DB name)
create database "ZABBIX"
with
owner = postgres
encoding = 'UTF8'
lc_collate = 'en_US.UTF-8'
lc_ctype = 'en_US.UTF-8'
tablespace = "SEQAS_tbs"
connection limit = -1;

revoke all on schema public from public;

\l

image-20260202125238202

2. Create User and schema

  • 2.1 Create User
create user "ZABBIX" with password 'zabbix';
\du

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

image-20260202125555052

  • 2.2 Create Schema
\c ZABBIX
create schema "ZABBIX-IT" authorization "ZABBIX";
grant all on schema "ZABBIX-IT" to "ZABBIX";
\dn

image-20260202130211239

  • 2.3 Create readonly User
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";