5. Users and Permissions

User- and Permission Management in Rational Database Management Systems

Relational database systems (RDBMS) like PostgreSQL and MySQL are not just “data storage”. They are multi-user systems where many people and applications access the same data at the same time.

Real databases often contain critical information:

• customer data
• school data (students/grades)
• system logs
• orders and invoices

Without permissions, any user could:

• read confidential data
• modify or delete important records
• destroy database structure (DROP TABLE)
• cause damage accidentally (or intentionally)

Therefore they need:

• User management
• Permission management (Privileges)

1. General Concepts

1.1 Authentication vs Authorization

Authentication = “Who are you?”

This is the login step. The database checks if you are allowed to connect using:

• username + password
• certificates
• SSO / Kerberos / AD login (depending on setup)

After authentication the DB knows: “You are app_user.”

Authorization = “What are you allowed to do?”

After login, the database decides what actions are allowed:

• Can you read table students?
• Can you insert new grades?
• Can you delete rows?
• Can you drop tables?

Authorization is the permission check.

1.2 Core principle: Least Privilege

Give each user only the minimum permissions needed.

Why?

• If an account is hacked, damage stays limited.
• If an application has a bug (e.g., SQL injection), attackers cannot “own the entire database”.
• Accidents are less destructive.

1.3 Typical privilege types

DML – Data Manipulation Language (data operations)

These permissions control work on table data:

• SELECT → read data
• INSERT → add new rows
• UPDATE → modify existing rows
• DELETE → remove rows

DDL – Data Definition Language (structure operations)

These permissions control schema/structure:

• CREATE → create tables/views/etc.
• ALTER → modify tables (add columns, change types)
• DROP → remove objects (dangerous!)

DDL rights usually belong to admins or migration users.

EXECUTE – Run stored logic

• EXECUTE → run stored procedures or functions

1.4 Common user types in projects

Admin

• full access
• manages users, permissions, backups, schema changes

Application user (“service account”)

• used by a backend service (FastAPI, Django, etc.)
• should have only the required CRUD permissions
• should not have admin or schema-changing rights

Read-only / reporting user

• only SELECT
• used for dashboards and statistics

Developer user (DEV/TEST only)

• more permissions sometimes allowed in development
• not recommended in production

2. PostgreSQL Permission System

PostgreSQL permissions are layered. Think of it like a building with rooms and doors:

1. Database door (~ entry of the building)
2. Schema door (~ entry of a floor of the building)
3. Object door (table/function/etc.)

If any door along the path to the room is locked → you get permission denied.

Structure of a PostgreSQL database

flowchart TB
  subgraph S["PostgreSQL Server/Cluster"]
    direction TB

    subgraph D["Database: schooldb"]
      direction TB

      subgraph PRIV["Schema: private"]
        direction TB
        PRbox["Private Tables<br/>(e.g. audit_log, students_private)"]
      end

      subgraph API["Schema: api"]
        direction TB
        APIbox["API Functions<br/>(e.g. get_students(), add_grade())"]
      end

      subgraph REP["Schema: reporting"]
        direction TB
        REPbox["Views<br/>Materialized Views"]
      end

      subgraph PUB["Schema: public"]
        direction TB
        PUBbox["Tables<br/>Views<br/>Sequences<br/>Functions / Procedures<br/>Triggers"]
      end
    end

    subgraph R["Roles / Users"]
      direction TB
      Rbox["app_user<br/>report_user<br/>db_admin"]
    end

    subgraph OWN["Ownership"]
      direction TB
      OWNbox["Each object has 1 owner<br/>Owner can ALTER/DROP<br/>ALTER ... OWNER TO ..."]
    end

    subgraph G["Privileges (Permissions)"]
      direction TB
      Gbox["CONNECT (Database)<br/>USAGE (Schema)<br/>SELECT/INSERT/UPDATE/DELETE (Tables/Views)<br/>EXECUTE (Functions/Procedures)<br/>USAGE/SELECT (Sequences)"]
    end

    D -.-> R
    D -.-> G
    D -.-> OWN
  end

  linkStyle 0 stroke:transparent,stroke-width:0px;
  linkStyle 1 stroke:transparent,stroke-width:0px;
  linkStyle 2 stroke:transparent,stroke-width:0px;

2.1 Roles / Users

In PostgreSQL, almost everything is a ROLE.

• A ROLE with LOGIN is a real user.
• A ROLE without LOGIN is a group role (used for permission management).

Example roles:

• app_user → backend/API access
• report_user → read-only reporting
• db_admin → full control

Why use roles? Because permissions are easier to manage:

• change one role → all users using that role are updated

Create users (roles with LOGIN)

CREATE ROLE app_user WITH LOGIN PASSWORD 'pw';
CREATE ROLE report_user WITH LOGIN PASSWORD 'pw';

Create a group role (no LOGIN)

CREATE ROLE app_rw;

GRANT

You attach a group role to a user:

GRANT app_rw TO app_user;

Meaning:

• app_user “inherits” permissions from app_rw

So, you can change permission sets of a group (or a complete hierarchy of groups) without changing individual users.

GRANT SELECT ON public.students TO app_rw;

REVOKE

If you want to remove a group role from a user:

REVOKE app_rw FROM app_user;

If a user was allowed to forward privileges to others:

GRANT SELECT ON public.students TO app_user WITH GRANT OPTION;

You can remove only the right to grant it further:

REVOKE GRANT OPTION FOR SELECT ON public.students FROM app_user;

Result:

• app_user can still SELECT
• but cannot grant SELECT to others anymore

Powerful role attributes

Some role attributes give admin-like powers:

• CREATEDB → can create databases
• CREATEROLE → can create roles and manage role membership
• SUPERUSER → full control

Example:

CREATE ROLE db_admin WITH LOGIN PASSWORD 'pw' SUPERUSER;

Important: In real projects, avoid SUPERUSER except for real admins.

There are more details in the CREATE ROLE reference: https://www.postgresql.org/docs/current/user-manag.html

2.2 Ownership (Owner)

In PostgreSQL, every database object has exactly one owner.

Examples of objects with an owner:

• database (schooldb)
• schema (public, api, reporting, …)
• tables (students)
• views / materialized views
• sequences
• functions / procedures

Usually, the creator of an object automatically becomes the owner.

Privileges vs Ownership

Privileges (GRANT/REVOKE) control actions like:

• SELECT, INSERT, UPDATE, DELETE
• EXECUTE
• USAGE
• CONNECT

Ownership controls the structural power over an object:

• ALTER ... (change structure / definition)
• DROP ... (delete object)

Important: ALTER and DROP are not normal GRANT privileges.

They are owner powers.

That means:

The right to modify or destroy an object is inherent in being the object’s owner.

So you cannot “fix” ownership power using REVOKE.

Example scenario

• app_user owns the table public.students
• you revoke all privileges

REVOKE ALL PRIVILEGES ON public.students FROM app_user;

Even after this, app_user can still do:

DROP TABLE public.students;
ALTER TABLE public.students ADD COLUMN test int;

The ability to ALTER and DROP comes from ownership, not from the GRANT privileges.

So this is the key idea:

• GRANT/REVOKE controls “working with data”
• OWNER controls “controlling the object itself”

Best practice - separation of duties

It’s a good practise to separate:

Owner / Migration role (strong permissions, DDL)

• db_admin or migration_user
• owns schemas and tables
• runs migrations (CREATE/ALTER/DROP)

Application role (minimal rights, DML only)

• app_user
• can only do CRUD (SELECT/INSERT/UPDATE/DELETE)
• should not own tables

Reporting role

• report_user
• mostly SELECT

This prevents catastrophic damage if an application user is compromised (SQL injection, leaked password, etc.).

Changing ownership

Ownership can be transferred using:

ALTER TABLE public.students OWNER TO db_admin;
ALTER SCHEMA public OWNER TO db_admin;
ALTER DATABASE schooldb OWNER TO db_admin;

This is common after an initial setup where tables were created using the wrong role.

Ownership and teams

In a team, if multiple developers create tables with their own accounts, the database ends up with mixed ownership:

• table A owned by dev1
• table B owned by dev2
• view C owned by dev3

This causes problems later:

• migrations may fail due to missing ownership
• permissions become inconsistent
• ALTER DEFAULT PRIVILEGES can behave unexpectedly because it is role-specific

Best practice:

Run all schema changes using one dedicated role (migration_user / db_admin).

For more information about privileges and ownership see: https://www.postgresql.org/docs/18/ddl-priv.html

2.3 Database

Database level: CONNECT

To use the database schooldb, a role must be allowed to connect:

CONNECT on the database

Meaning:

“You may enter this database.”

Without CONNECT, the user cannot really use the DB (even if login works).

2.4 Schema

Schema level: USAGE

PostgreSQL databases contain multiple schemas (namespaces), for example:

• public → default schema for standard tables
• reporting → views / materialized views for dashboards
• api → stored functions acting like an API layer
• private → internal tables not meant for normal users

To access objects inside a schema, you need:

USAGE on that schema

Meaning:

“You may enter this area/namespace.”

This is a common PostgreSQL beginner issue: A user might have table rights but still fails because schema USAGE is missing.

2.5 Objects

Object permissions: Tables and Views

Once CONNECT and USAGE are okay, you still need permissions on the actual objects:

Tables

Typical application permissions:

• SELECT, INSERT, UPDATE
• maybe DELETE

Views and materialized views

Most reporting users need only:

• SELECT

Example reasoning from the diagram:

• app_user → CRUD in schema public
• report_user → SELECT in schema reporting

Functions / Procedures

Object permissions: Functions / Procedures (EXECUTE)

In schema api, you may store functions like:

• get_students()
• add_grade(student_id, grade)

Then the application can be designed like this:

• app user may call functions (EXECUTE)
• but app user may not access private tables directly

Execution Rights and Security Context

Functions and procedures can run in two different security contexts:

1. Security Invoker (default) By default, functions and procedures run with the privileges of the user who calls them.

2. Security Definer

   When a function or procedure is defined with the keyword SECURITY DEFINER, it runs with the privileges of the owner of the function/procedure, not the caller. This allows a low-privileged user to execute an operation that normally requires higher privileges — in a controlled way.

2.6 Default privileges (PostgreSQL “gotcha”)

In PostgreSQL:

GRANT ... ON ALL TABLES IN SCHEMA public

only affects existing tables.

If you later create a new table, permissions are not automatically granted.

Solution: ALTER DEFAULT PRIVILEGES This is important for e.g. teamwork and long-running projects.

2.7 Verify/check privileges and ownership

Check privileges

e.g. table privileges:

\dp public.students

Example result:

                                     Access privileges
 Schema |   Name   | Type  |     Access privileges      | Column privileges | Policies
--------+----------+-------+----------------------------+-------------------+----------
 public | students | table | db_admin=arwdDxt/db_admin +|                   |
        |          |       | app_user=arwd/db_admin    +|                   |
        |          |       | report_user=r/db_admin     |                   |
(1 row)

Meaning of letters (common ones)

• r = SELECT
• a = INSERT
• w = UPDATE
• d = DELETE
• D = TRUNCATE
• x = REFERENCES
• t = TRIGGER

So:

• app_user=arwd → CRUD
• report_user=r → SELECT only
• db_admin=arwdDxt → basically everything

(/db_admin at the end of a line means: the grantor was the role/user db_admin)

Check owners

Tables / views details

\dt+ public.*

Schemas details

\dn+

Databases details

\l+

Query information_schema for privileges

(= who has which privileges on a specific database object?)

e.g. on table mytable:

SELECT
  grantee,
  privilege_type
FROM information_schema.role_table_grants
WHERE table_schema = 'public'
  AND table_name   = 'mytable'
ORDER BY grantee, privilege_type;

Example result:

grantee	privilege_type
db_admin	DELETE
db_admin	INSERT
db_admin	REFERENCES
db_admin	SELECT
db_admin	TRIGGER
db_admin	TRUNCATE
db_admin	UPDATE
app_user	DELETE
app_user	INSERT
app_user	SELECT
app_user	UPDATE
report_user	SELECT

Query pg_class/pg_namespace for owners

(= who is owner of a specific database object?)

e.g. on table mytable:

SELECT
  n.nspname AS table_schema,
  c.relname AS table_name,
  pg_get_userbyid(c.relowner) AS owner
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
  AND c.relname = 'mytable';

Example result:

table_schema	table_name	owner
public	mytable	db_admin

2.8 PostgreSQL common mistakes

• missing CONNECT
• missing schema USAGE
• new tables missing rights → no default privileges set
• ownership confusion: user is owner → can still ALTER/DROP even after REVOKE
• rights granted to role, but role not assigned (or membership revoked) → “why does the user still have / not have access?”
• search_path problems: user queries students but table is in another schema → use public.students or fix search_path

3. MySQL Permission System

MySQL has a simpler “shape” than PostgreSQL.

Server → Database → Objects

It does not have the extra schema layer inside a database like PostgreSQL.

• MySQL ~ simple Bungalow

• PostgreSQL ~ Building with multiple floors

Structure of a MySQL database

flowchart TB
  subgraph S["MySQL Server"]
    direction TB

    subgraph D["Database (Schema): schooldb"]
      direction TB
      Dbox["Tables<br/>Views<br/>Functions<br/>Procedures<br/>Triggers<br/>Events (Scheduler)"]
    end

    subgraph U["Users (user@host)"]
      direction TB
      Ubox["'app_user'@'localhost'<br/>'report_user'@'%'<br/>'root'@'localhost'"]
    end

    subgraph G["Privileges (Permissions)"]
      direction TB
      Gbox["SELECT/INSERT/UPDATE/DELETE<br/>CREATE/ALTER/DROP<br/>EXECUTE"]
    end
  end

3.1 Users and Roles

Users

MySQL users always include a host: user@host

An account is defined as:

• 'app_user'@'localhost'
• 'app_user'@'192.168.1.%'
• 'app_user'@'%'

Meaning:

• host is part of the identity
• the same username can exist multiple times with different hosts

In PostgreSQL:

• the role is just app_user
• allowed hosts are controlled elsewhere (pg_hba.conf, firewall, cloud settings)

Roles

MySQL also supports roles:

CREATE ROLE 'app_rw';
GRANT SELECT, INSERT, UPDATE ON schooldb.* TO 'app_rw';
GRANT 'app_rw' TO 'app_user'@'localhost';

But compared to PostgreSQL:

• roles are less central in everyday setups
• many MySQL tutorials still teach “grant directly to user”
• still useful and recommended in larger setups

Enabling Roles

In MySQL, a role may be granted but not automatically active in sessions unless you set it.

Make a role active for the current session

SET ROLE 'app_rw';

Make roles active automatically on login (recommended)

SET DEFAULT ROLE 'app_rw' TO 'app_user'@'localhost';
SET DEFAULT ROLE 'reporting_ro' TO 'report_user'@'localhost';

3.2 “Schema” vs “Database”

In MySQL: SCHEMA is basically the same as DATABASE.

So MySQL does not have multiple schemas like public, reporting, api inside one database like PostgreSQL does.

3.3 Objects

Inside a MySQL database you commonly have:

• tables
• views
• stored procedures / functions
• triggers

3.4 Permission assignment

Permission assignment is simpler and follows this common pattern:

CREATE ROLE 'role_reporting';

GRANT SELECT ON schooldb.* TO 'role_reporting';

GRANT 'role_reporting' TO 'report_user'@'localhost';

Don’t forget to enable the role automatically on login (important!)

SET DEFAULT ROLE 'role_reporting' TO 'report_user'@'localhost';

Or assign permissions directly to the user without a role (not recommended):

GRANT SELECT ON schooldb.* TO 'app_user'@'localhost';

In PostgreSQL you usually need multiple grants:

• CONNECT
• USAGE on schema
• table privileges
• default privileges for future objects

3.5 Verify/check privileges

Show grants for a specific user

SHOW GRANTS FOR 'app_user'@'localhost';

Example result:

+-------------------------------------------------------------------------------------------+
| Grants for app_user@localhost                                                              |
+-------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `app_user`@`localhost`                                               |
| GRANT `role_app`@`%` TO `app_user`@`localhost`                                            |
| SET DEFAULT ROLE `role_app`@`%` TO `app_user`@`localhost`                                  |
+-------------------------------------------------------------------------------------------+

• app_user itself has basically no direct privileges (USAGE)

• but it gets permissions via the role role_app

• and the role is enabled automatically due to SET DEFAULT ROLE ...

So, we have to look up grants for role_app:

SHOW GRANTS FOR 'role_app';

A possible output could be:

+-------------------------------------------------------------------------------------------+
| Grants for role_app@%                                                                      |
+-------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `role_app`@`%`                                                       |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `schooldb`.* TO `role_app`@`%`                     |
| GRANT EXECUTE ON `schooldb`.* TO `role_app`@`%`                                            |
+-------------------------------------------------------------------------------------------+

Check table-level privileges via information_schema (= who has which privileges on a specific table?)

e.g. on table mytable:

SELECT
  GRANTEE,
  PRIVILEGE_TYPE
FROM information_schema.TABLE_PRIVILEGES
WHERE TABLE_SCHEMA = 'schooldb'
  AND TABLE_NAME   = 'mytable'
ORDER BY GRANTEE, PRIVILEGE_TYPE;

Example result:

GRANTEE	PRIVILEGE_TYPE
'app_user'@'localhost'	DELETE
'app_user'@'localhost'	INSERT
'app_user'@'localhost'	SELECT
'app_user'@'localhost'	UPDATE
'report_user'@'localhost'	SELECT
'role_app'@'%'	DELETE
'role_app'@'%'	INSERT
'role_app'@'%'	SELECT
'role_app'@'%'	UPDATE

Check schema-level privileges (schooldb.*)

SELECT
  GRANTEE,
  PRIVILEGE_TYPE
FROM information_schema.SCHEMA_PRIVILEGES
WHERE TABLE_SCHEMA = 'schooldb'
ORDER BY GRANTEE, PRIVILEGE_TYPE;

Example result:

GRANTEE	PRIVILEGE_TYPE
'app_user'@'localhost'	CREATE
'app_user'@'localhost'	DROP
'app_user'@'localhost'	REFERENCES
'report_user'@'localhost'	SELECT
'role_app'@'%'	SELECT
'role_app'@'%'	INSERT
'role_app'@'%'	UPDATE
'role_app'@'%'	DELETE

3.6 REVOKE in MySQL

In MySQL you can remove privileges again using REVOKE.

Revoke privileges from a user

Remove one privilege:

REVOKE SELECT ON schooldb.mytable FROM 'app_user'@'localhost';

Remove multiple privileges:

REVOKE INSERT, UPDATE, DELETE ON schooldb.mytable FROM 'app_user'@'localhost';

Remove privileges on an entire database:

REVOKE SELECT ON schooldb.* FROM 'report_user'@'localhost';

Revoke role membership

If a role was granted to a user:

GRANT 'app_rw' TO 'app_user'@'localhost';

You can remove the role again:

REVOKE 'app_rw' FROM 'app_user'@'localhost';

Important: default roles vs active roles

If a role was set as default:

SET DEFAULT ROLE 'app_rw' TO 'app_user'@'localhost';

This affects new sessions of the user (after the next login).

If you revoke the role membership, existing sessions may still keep their currently active roles until reconnect or SET ROLE is executed again.

To verify current permissions:

SHOW GRANTS FOR 'app_user'@'localhost';

3.7 “Owner” in MySQL?

MySQL does not have a central object ownership concept like PostgreSQL where every table/view/function has exactly one owner with implicit ALTER/DROP power.

In MySQL, access control is mainly based on:

• Privileges via GRANT (to users and/or roles)
• object types (tables, views, routines, triggers, events)
• the identity user@host

So, for tables you typically manage everything with GRANT / REVOKE (and roles).

Some objects (views, procedures, functions, triggers, events) store a DEFINER (user@host).

The DEFINER defines the security context (SQL SECURITY DEFINER vs INVOKER) → whose privileges are used when the object runs.

DEFINER is mainly about execution permissions, not about “who may drop/alter the object”.

3.8 MySQL common mistakes

• application runs as root
• too open host 'user'@'%'
• too powerful grants like GRANT ALL ON *.*
• role granted but not active (session has no permissions until SET ROLE / SET DEFAULT ROLE)
• using WITH GRANT OPTION in production (users can silently escalate by granting to others)
• REVOKE done, but access still exists because privileges come via a role (check SHOW GRANTS for roles too)