1

I have a regression test for a PostgreSQL database that checks to make sure a particular user cannot self-assign additional privileges to a particular schema. I do this by logging in as the user "testuser", then executing this SQL: fiddle

GRANT CREATE ON SCHEMA testschema TO testuser;

In past PostgreSQL versions, this would raise an error (again, this user should not be able to grant permissions on the schema.) With version 17, the SQL does not raise an error (but no additional privilege is granted.)

Using @Michal Charemza's excellent query from this question shows that testuser's privileges for testschema to be USAGE, for the database to be CONNECT, and to be inheriting the role users.

Running the same privilege query on the users role shows it has no privileges.

So: under v17, why doesn't testuser's attempt to GRANT additional privileges on a schema raise an error if testuser only has USAGE privilege on the schema?

2
  • I would not be surprised if there was a change in behavior from 16+ due to changes as detailed here 16.0 Release Notes, section E.12.3.1.4. Privileges. You would need to add more information to the question text detailing what privileges are on the schema, and/or the schema creation command, as well as the privileges the users and testuser roles have. Commented 14 hours ago
  • @Zegarek This is passing strange. I just ran the regression test on v15 and it too failed to produce an error. Commented 13 hours ago

1 Answer 1

2

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.

Sign up to request clarification or add additional context in comments.

2 Comments

Makes sense (and thank you for the relevant source code). So even setting the client_min_messages down wouldn't cause an error to be returned. (Which I tested.) It's so odd that the test was originally receiving an error code (45201, which is correct).
It also could mean that in the previous test this ran on a different session/db/user, or the previous grant got undone prior to this one. The logging level wasn't meant to make it an error, only to expose what I suspected was a warning being emitted.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.