PostgreSQL privileges reference

This reference lists the minimum PostgreSQL role attributes and privileges that Kong Gateway needs for migrations, runtime operations, and admin CLI commands. Use these privileges instead of full database ownership. This follows the principle of least privilege.

This reference covers Kong Gateway core and its bundled plugins only. A custom plugin can define its own DAOs, migrations, tables, and triggers. If you use a custom plugin, grant the write user the extra privileges its code needs.

The following kong.conf parameters control which PostgreSQL role Kong Gateway connects as. See the configuration reference for the full list of datastore parameters.

Kong Gateway opens three kinds of database sessions. Each one needs a different set of privileges:

• Migration session: The kong migrations CLI connects to pg_database as pg_user. If the schema doesn’t exist, it creates pg_schema, switches to it, and runs DDL. At this point, the Gateway process isn’t running yet.
• Runtime session: The Gateway process opens pooled connections to pg_database as pg_user. Each connection switches to pg_schema.
• Admin CLI session: A CLI command other than kong migrations can also open a database session (for example, kong workspace rename, kong config db_import, and kong config db_export). This session is a one-off, non-pooled connection. The command connects to pg_database as pg_user and switches to pg_schema, the same as a runtime session. The command can run whether the Gateway process is active or stopped. It can also run statements that the runtime session never runs, including TRUNCATE on specific tables and privileges on sequences.

Kong Gateway connects with a write user (pg_user) and, optionally, a read-only user (pg_ro_user). Kong Gateway uses the read-only user only in runtime sessions.

LOGIN is the only attribute that either user needs. The following table lists this minimal set. You can add other attributes, for example CONNECTION LIMIT, for your own operational reasons. Kong Gateway doesn’t require them.

Kong Gateway needs different PostgreSQL privileges depending on the operation being performed, following the principle of least privilege.

Connection privileges

Both the write user and the read-only user need CONNECT privilege on pg_database. PostgreSQL grants CONNECT to PUBLIC by default. As a result, this privilege is already open for all roles, unless you revoke it.

Migration-time privileges

Applies to:

• Traditional mode with database = postgres.
• Hybrid mode control plane nodes (role = control_plane).

Both run kong migrations against a real schema.

Does not apply to:

• Traditional DB-less mode (database = off), because it has no schema to migrate.
• Hybrid mode data plane nodes (role = data_plane). Kong Gateway config validation forces database = off for a data plane node. As a result, the node never connects to PostgreSQL.

Before migration, the write user needs CREATE privilege on pg_database to create the Kong Gateway schema.

kong migrations reset/bootstrap/up/finish runs the following Data Definition Language (DDL) statements automatically. The following script makes the write user the schema owner (replace $PG_SCHEMA with the actual name of pg_schema):

-- kong migrations reset
DROP SCHEMA IF EXISTS $PG_SCHEMA CASCADE;

-- kong migrations bootstrap/up/finish
CREATE SCHEMA IF NOT EXISTS $PG_SCHEMA AUTHORIZATION CURRENT_USER;
GRANT ALL ON SCHEMA $PG_SCHEMA TO CURRENT_USER;
SET SCHEMA $PG_SCHEMA;

After migration, the write user owns the schema and every object the migration session creates in it. Ownership already grants the write user every privilege it needs. As a result, no further GRANT is necessary.

Runtime privileges

Applies to:

• Traditional mode with database = postgres.
• Hybrid mode control plane nodes (role = control_plane).

Does not apply to: Modes that don’t open a database connection.

• Traditional DB-less mode loads configuration from a declarative file instead of a database.
• A hybrid mode data plane node keeps all state in memory.

If the same role runs migrations and runtime, skip this section. Ownership already covers it. If you configure a different write user for runtime, grant it the following privileges:

Don’t reduce the runtime user to SELECT only.

Kong Gateway also writes to the database directly, even when no client calls the Admin API:

The runtime session never runs TRUNCATE. Grant TRUNCATE only to the role that runs admin CLI commands.

Admin CLI privileges

Applies to:

• Traditional mode with database = postgres.
• Hybrid mode control plane nodes (role = control_plane).

Does not apply to the following commands:

• kong config db_import and kong config db_export fail immediately when database = off (traditional DB-less mode and hybrid mode data planes).
• kong workspace manages workspace rows that exist only in a database-backed deployment.

If the same role runs the admin CLI and migrations, skip this section. Ownership already covers it. If you configure a different write user for admin CLI commands, grant it the runtime privileges, plus the following privileges:

Read-only user privileges

The read-only user is optional. If you don’t point pg_ro_host at a read replica, skip this section. Without it, Kong Gateway never activates the read-only connector. The read-only role doesn’t need to exist.

Only the migration-time write user owns the schema and its tables. As a result, only that user can grant privileges on them. Connect as the migration-time write user or a superuser. Run the following SQL commands to configure the read-only user:

-- [DCL] Allow connection to the database (default on)
GRANT CONNECT ON DATABASE $PG_DATABASE TO $PG_RO_USER;

-- [DCL] Allow access to the schema
GRANT USAGE ON SCHEMA $PG_SCHEMA TO $PG_RO_USER;

-- [DCL] Allow SELECT on existing tables created by the migration-time write user
GRANT SELECT ON ALL TABLES IN SCHEMA $PG_SCHEMA TO $PG_RO_USER;

-- [DCL] Auto-grant SELECT on future tables created by the migration-time write user
ALTER DEFAULT PRIVILEGES FOR ROLE $PG_USER IN SCHEMA $PG_SCHEMA GRANT SELECT ON TABLES TO $PG_RO_USER;

Replace the following variables with the actual values:

• $PG_SCHEMA with the name of the schema (for example, public).
• $PG_RO_USER with the value of pg_ro_user.
• $PG_USER with name of your user (for example, kong).

Connect as a PostgreSQL superuser. Create the write user and database before you run migrations:

-- Create the write user
CREATE ROLE kong WITH LOGIN PASSWORD '$PASSWORD';

-- Create the database, owned by the write user
-- The kong role owns the kong database, so no further GRANT is necessary
CREATE DATABASE kong OWNER kong;

Optionally, create a read-only user before you start the gateway:

-- Create the read-only user
CREATE ROLE kong_ro WITH LOGIN PASSWORD '$PASSWORD';

-- Grant minimal privileges to the read-only user
GRANT CONNECT ON DATABASE kong TO kong_ro;
GRANT USAGE ON SCHEMA public TO kong_ro;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO kong_ro;
ALTER DEFAULT PRIVILEGES FOR ROLE kong IN SCHEMA public GRANT SELECT ON TABLES TO kong_ro;