MySQL ‘user’@’host’ Grants vs PostgreSQL Roles and pg_hba.conf After a Port

The first access incident was on the MySQL side, during a routine subnet expansion. The application had connected for years as ‘app’@’10.0.1.%’ with exactly the grants it needed. When the cluster grew into 10.0.14.0/24, the new pods failed at startup with ERROR 1045 (28000): Access denied for user ‘app’@’10.0.14.7’ — and the DBA team’s first response, “the grant is right there in mysql.user”, was both true and useless. MySQL accounts are not users; they are user-at-host pairs, matched most-specific-first, and no account existed for the new range. The fix was one CREATE USER, but the postmortem question — how many host-pattern variants of the app account existed, and did they all have the same privileges — turned up three variants with three different privilege sets, one of them with SUPER.

The second incident was after the PostgreSQL 16 migration, and it was the mirror image. A reporting service that authenticated fine from staging failed in production with FATAL: no pg_hba.conf entry for host, because production’s pg_hba.conf had been assembled by hand, the line someone added for the reporting subnet was below a catch-all reject-adjacent rule, and nobody had reloaded. MySQL folds connection policy and privilege into the account table; PostgreSQL splits them into two layers — pg_hba.conf deciding whether you may connect at all, roles deciding what you may do after — and each layer has ordering semantics that bite. These are the field notes from operating both.

How does MySQL identity actually work?

An account is the pair ‘user’@’host’, and at connection time MySQL sorts the account table by host specificity and picks the first match — ‘app’@’10.0.1.%’ beats ‘app’@’%’ for a client in that range, and the two accounts can hold completely different passwords and privileges. This design has three production consequences. First, adding a subnet means adding accounts, and the account count multiplies with environments; we audited one legacy server and found eleven variants of the same application user. Second, SHOW GRANTS FOR ‘app’@’%’ tells you nothing about what ‘app’@’10.0.1.%’ can do, so privilege audits must enumerate accounts, not users. Third, the legacy anonymous user (”@’localhost’) still exists on old installs and shadows real accounts for local connections — the classic symptom is a user who can connect but sees no databases, because they authenticated as the anonymous account that matched first.

-- MySQL 8.0: enumerate what actually exists before debugging a 1045
SELECT user, host FROM mysql.user WHERE user = 'app';
SHOW GRANTS FOR 'app'@'10.0.1.%';

-- PostgreSQL 16: the two layers, inspected separately
SELECT rolname, rolcanlogin, rolconnlimit FROM pg_roles WHERE rolname = 'app';
SELECT pg_read_file('pg_hba.conf');  -- superuser; or read the file directly

What are PostgreSQL’s two layers, and how does pg_hba.conf ordering bite?

Layer one is pg_hba.conf: a list of rules matched top to bottom, first match wins, each rule saying which users from which addresses to which databases may connect with which authentication method. The incident shape is always the same — someone appends a hostssl scram-sha-256 line for a new client at the bottom, but an existing host all all 0.0.0.0/0 md5 line above it matches first, and the new line is dead. Worse, edits do nothing until SELECT pg_reload_conf() or a SIGHUP, so “I changed the file and it still fails” is a rite of passage. Our standard checks: read the file in order, not grep it; confirm the matched rule by testing with the exact user, database, and source address; and always reload before concluding anything. Layer two is roles: PostgreSQL unified users and groups into roles with attributes, so CREATE USER is sugar for CREATE ROLE … LOGIN, a role without LOGIN cannot connect at all, and role membership with INHERIT is how group privileges flow. The mental model transfer from MySQL is: mysql.user becomes pg_hba.conf plus pg_roles, and the two must agree or nothing connects.

Why do GRANTs keep breaking after new tables appear?

Because the two engines make opposite default choices about future objects. MySQL’s GRANT SELECT ON appdb.* TO … covers every table created later in that database, which is usually what operators expect. PostgreSQL’s GRANT SELECT ON ALL TABLES IN SCHEMA public TO app covers only tables that exist at that moment; a table created next week by the migration role is invisible to the application role until someone grants on it — the famous production symptom is permission denied for table appearing right after a deploy that added a table, on an application that had “all the grants”. The durable fix is ALTER DEFAULT PRIVILEGES, declared per creating role, and the subtlety that burns people: default privileges belong to the role that creates the objects, so if migrations run as role migrate but someone created tables as postgres by hand, the defaults attached to migrate never apply. We run both statements, in the same migration, every time:

-- PostgreSQL: current tables AND future tables, for the role that runs migrations
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app;
ALTER DEFAULT PRIVILEGES FOR ROLE migrate IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app;

-- and since PostgreSQL 15: the public schema no longer grants CREATE to everyone,
-- so new roles need explicit schema privileges too:
GRANT USAGE ON SCHEMA public TO app;

That last line is its own migration-era trap: clusters upgraded from PostgreSQL 14 carry the old permissive public schema, while fresh 15-plus installs do not, so the same provisioning script works on the old cluster and fails on the new one. Relatedly, connection-level limits live in different places — MySQL’s max_user_connections per account versus PostgreSQL’s rolconnlimit — and the connection-budget comparison in MySQL versus PostgreSQL connections covers how those ceilings interact with pooling.

Where MonPG fits when access control breaks

Authentication failures are invisible inside the database until someone reads the log, and privilege failures masquerade as application bugs — the deploy worked, the table exists, the error only appears on the code path that touches it. MonPG’s PostgreSQL monitoring watches connection and error patterns at the instance level, so a sudden wall of failed logins after a subnet change or a spike in permission errors after a deploy shows up as an event with a timestamp, correlated with the change that caused it, instead of a morning of log archaeology.

MonPG monitors PostgreSQL today; MySQL support is on the roadmap. When it lands, the MySQL side of this article is what it will surface: authentication failures by account and host, so the first 1045 from a new range is a signal, not a mystery. Until then, the MySQL monitoring page tracks that work. The portable lesson: audit access as the pair (connection policy, privileges) on every environment change, because the two engines split that pair differently and both will let you believe a grant exists that does not.

Related documentation

• MySQL: connection verification
• PostgreSQL: database roles