Skip to main content

Access control: DCL

DCL (Data Control Language) is the SQL sublanguage for security - deciding who may read, change, or manage each part of the database. It has two verbs, GRANT and REVOKE, applied to roles. This is the authorization layer: separate from authentication (proving who you are) and from encryption, auditing, and backups, which round out a database's security.

PostgreSQL, not the sandbox

Roles and privileges are a server feature, and SQLite has none of it - a SQLite database is a file, so access is just filesystem permissions. These examples are PostgreSQL and do not run in this site's sandbox.

Roles: users and groups​

In PostgreSQL there are no separate "users" and "groups" - everything is a role. The only difference is one attribute:

  • A login role can connect to the database (it has the LOGIN attribute) - this is what you think of as a user.
  • A group role has no LOGIN; it exists to bundle privileges and hand them to other roles.
CREATE ROLE marketing;                      -- group role (no login)
CREATE ROLE app_user LOGIN PASSWORD 'secret'; -- login role (a user)

CREATE USER is just shorthand for CREATE ROLE ... LOGIN.

Privileges: GRANT and REVOKE​

A privilege is the right to perform one action on one object - SELECT, INSERT, UPDATE, DELETE on a table, EXECUTE on a function, and so on. GRANT gives a privilege; REVOKE takes it back.

GRANT SELECT ON customers TO marketing;   -- marketing can read customers
REVOKE DELETE ON orders FROM marketing;   -- but cannot delete orders

Privileges apply per object, or at the schema and database level. The governing principle is default-deny, least privilege: a new role starts with almost nothing, and you grant only what its job requires. When designing access, ask two questions every time - to whom, and which privilege. A customer role should never get INSERT on internal tables; a read-only reporting role gets SELECT and nothing else.

Role membership: inheriting privileges​

Granting one role to another makes the second a member of the first and lets it inherit those privileges. This is how you avoid granting the same rights one by one:

GRANT marketing TO app_user;     -- app_user now inherits marketing's rights
REVOKE marketing FROM app_user;  -- and loses them again

Set up the privileges once on a group role, then add or remove members as people and services come and go.

Modern PostgreSQL security (2026)​

The defaults and tools have tightened in recent versions - know these:

  • Locked-down public schema. Since PostgreSQL 15, ordinary roles can no longer create objects in the public schema by default. Earlier versions let everyone write there - a long-standing footgun now closed.
  • Predefined roles. PostgreSQL ships built-in roles like pg_read_all_data and pg_write_all_data (since v14) so you can grant broad read or write access without hand-listing every table.
  • Row-Level Security (RLS). For per-row control, ALTER TABLE ... ENABLE ROW LEVEL SECURITY plus CREATE POLICY restricts which rows a role sees - e.g. each tenant sees only their own data, enforced by the database itself.
  • WITH GRANT OPTION lets a role pass a privilege on to others; ALTER DEFAULT PRIVILEGES sets what newly created objects grant automatically.

Together these let you enforce access in the database, not just in application code - a second line of defence if the app is ever bypassed.

Quick quiz​

Access control with DCL

5 questions

1What is the purpose of DCL statements?

2How do you create a role in PostgreSQL?

3What does the DELETE privilege grant?

4Which best describes good privilege design?

5A reporting service should read every table but change nothing. Best approach in modern PostgreSQL?

Next up

DCL secures the database from the inside. SQL injection and safe queries covers the most common way that defence gets bypassed from the application side - and the one-line habit that stops it.