According to restrict_and_check_grant() responsible for this in the source code postgres/src/backend/catalog/aclchk.c:235, that behaviour is dictated by the SQL standard:
/*
* If we found no grant options, consider whether to issue a hard error.
* Per spec, having any privilege at all on the object will get you by
* here.
*/
If the user has any privileges on the target object, they only get a warning in this scenario, not an error. Your testuser already has USAGE, so the attempt to also get CREATE privilege is rejected with only a warning, and the command is considered succesfully completed despite being ineffective. It's been like this since at least Postgres 9.3.
This part, further explains the behaviour and shows where that warning is being emitted:
/*
* Restrict the operation to what we can actually grant or revoke, and
* issue a warning if appropriate. (For REVOKE this isn't quite what the
* spec says to do: the spec seems to want a warning only if no privilege
* bits actually change in the ACL. In practice that behavior seems much
* too noisy, as well as inconsistent with the GRANT case.)
*/
this_privileges = privileges & ACL_OPTION_TO_PRIVS(avail_goptions);
if (is_grant)
{
if (this_privileges == 0)
{
if (objtype == OBJECT_COLUMN && colname)
ereport(WARNING,
(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED),
errmsg("no privileges were granted for column \"%s\" of relation \"%s\"",
colname, objname)));
else
ereport(WARNING,
(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED),
errmsg("no privileges were granted for \"%s\"",
objname)));
}
else if (!all_privs && this_privileges != privileges)
{
if (objtype == OBJECT_COLUMN && colname)
ereport(WARNING,
(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED),
errmsg("not all privileges were granted for column \"%s\" of relation \"%s\"",
colname, objname)));
else
ereport(WARNING,
(errcode(ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED),
errmsg("not all privileges were granted for \"%s\"",
objname)));
}
}
Note that it's ereport(WARNING, and the errcode(ERRCODE_WARNING_PRIVILEGE_NOT_GRANTED) and errmsg() don't mean it's really a true error, as in exception. This continues uninterrupted, just emitting an accompanying message.
Since that behaviour has not and will not change on Postgres' side, my guess is that there are other differences between your tests, e.g.:
- previously, you ran that as different users, in different order, on different databases, etc. This would result in
testuser not having the usage privilege (yet) at the time of attempting grant create
- by the time you attempted
grant create, the grant usage got revoked or it was in another session and transaction that got invalidated or issued a rollback
- you weren't getting errors, only warnings, and the tests started to ignore/suppress the
WARNING-level message that this command should give you:
WARNING: no privileges were granted for "testschema"
For the last one, you might want to check your client_min_messages setting:
client_min_messages (enum)
Controls which message levels are sent to the client. Valid values are DEBUG5, DEBUG4, DEBUG3, DEBUG2, DEBUG1, LOG, NOTICE, WARNING, and ERROR. Each level includes all the levels that follow it. The later the level, the fewer messages are sent. The default is NOTICE. Note that LOG has a different rank here than in log_min_messages.
INFO level messages are always sent to the client.
usersandtestuserroles have.