DEV Community

Michael
Michael

Posted on • Originally published at gbase.cn

Permission Governance in GBase 8c: Separate Role Boundaries First, Then Assign Privileges

#gbase #database #数据库 #security

Chaos in permission management almost always starts with granting privileges directly to users. The foundation of a maintainable gbase database security model is strict separation of Users, Roles, and Privileges — users log in, roles carry permissions, and object privileges are granted only to roles.

1. Core Principle: Users Bind to Roles, Roles Carry Permissions

A typical three‑tier role structure:

  • Read‑only role: for reports, audits, and read‑only access.
  • Read‑write role: for routine application reads and writes.
  • Management role: for object creation and maintenance, never bound directly to application programs. 
-- Create roles
CREATE ROLE app_read_role;
CREATE ROLE app_rw_role;
CREATE ROLE app_ddl_role;

-- Create users
CREATE USER app_reader IDENTIFIED BY 'Example#2026';
CREATE USER app_writer IDENTIFIED BY 'Example#2026';
CREATE USER app_owner  IDENTIFIED BY 'Example#2026';

-- Bind users to roles
GRANT app_read_role TO app_reader;
GRANT app_rw_role   TO app_writer;
GRANT app_ddl_role  TO app_owner;
Enter fullscreen mode Exit fullscreen mode

Grant database, schema, and object privileges to the roles, never to individual users:

GRANT CONNECT ON DATABASE bizdb TO app_read_role, app_rw_role, app_ddl_role;
GRANT USAGE ON SCHEMA billing TO app_read_role, app_rw_role, app_ddl_role;

GRANT SELECT ON TABLE billing.settle_result TO app_read_role;
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE billing.settle_result TO app_rw_role;
GRANT CREATE, USAGE ON SCHEMA billing TO app_ddl_role;
Enter fullscreen mode Exit fullscreen mode

When someone changes roles, you only adjust the user‑role binding — no per‑table re‑grant needed.

2. When Troubleshooting, Check the Upper Permission Layers First

Many "missing table permission" errors are actually missing CONNECT or USAGE higher up. Follow this order:

Symptom Most Likely Missing Privilege
Cannot connect to database CONNECT ON DATABASE
Schema visible but object access fails USAGE ON SCHEMA
Query on a table fails SELECT ON TABLE/VIEW
Write operations fail INSERT/UPDATE/DELETE, sometimes SELECT also required
Calling a function fails EXECUTE ON FUNCTION

3. Use Default Privileges to Set Boundaries for Future Objects

Manual GRANT only affects existing objects. New tables, sequences, and functions won't inherit those grants. ALTER DEFAULT PRIVILEGES defines preset access rules for future objects, preventing midnight alerts caused by forgotten grants.

ALTER DEFAULT PRIVILEGES IN SCHEMA billing
GRANT SELECT ON TABLES TO app_read_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA billing
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_rw_role;

ALTER DEFAULT PRIVILEGES IN SCHEMA billing
GRANT USAGE, SELECT ON SEQUENCES TO app_rw_role;
Enter fullscreen mode Exit fullscreen mode

Apply default privileges early in any schema where objects are continuously created.

4. Separation of Duties for High‑Security Environments

GBase 8c's separation of duties splits traditional superuser power into a System Administrator (SYSADMIN) and a Security Administrator (CREATEROLE + POLADMIN). This prevents a single account from both maintaining the system and having unlimited access to data. It's strongly recommended in finance, government, and telecom environments. Note: when separation of duties is not enabled, the system administrator's effective privileges are broader.

5. Least Privilege by Business Action Chain

Least privilege means "exactly what's needed to perform the task," not "as little as possible."

  • Report querying: CONNECT + USAGE + SELECT
  • Business writes: CONNECT + USAGE + SELECT + INSERT + UPDATE + DELETE
  • Calling functions: add EXECUTE to the above
  • Creating objects: CREATE ON SCHEMA/DATABASE
  • Table maintenance: add INDEX, VACUUM, ALTER as needed

6. Connection Entry Is Also a Permission Boundary

Security governance must cover not only object‑level privileges but also who can connect from which IP using which authentication method. Regularly review listen_addresses and pg_hba.conf. Manually editing pg_hba.conf is a high‑risk operation and must follow documented procedures.

7. Recommended Governance Sequence

  1. Separate administrator responsibilities — evaluate separation of duties; at minimum distinguish ops, security, and audit roles.
  2. Design roles by job function, not by individual.
  3. Grant database and schema privileges first, then table/view/function privileges.
  4. Set default privileges so new objects automatically inherit the right rules.
  5. Users only bind to roles — never grant object privileges directly to users.
  6. Unify connection‑level and object‑level governance.

A solid permission design in a gbase database isn't about writing clever GRANT statements — it's about building a role hierarchy that stays clean as teams and objects grow. When the foundation is right, audits are painless, incident boundaries are clear, and new objects land with the correct permissions from day one.

Top comments (0)