Re: Clarity Bug for Schema Permissions, Potential Vulnerability?

On Wed, May 6, 2020 at 9:23 AM Justis Lincoln Mackaoui <jmackaou(at)calpoly(dot)edu>
wrote:

> Hi there,
>
>
>
> There’s an issue with the consistency of “ERROR: permission denied” which
> is a small gotcha for new Postgres users like myself. It may be desirable
> behavior but please bear with me.
>

Hi Justis,

I believe your question does not constitute a bug report and could be
better answered in pgsql-general or pgsql-novice mailing list.

Here’s a scenario displaying the current behavior in PostgreSQL 11.5:
>
> 1. The database has two schemas, “public” and “test”.
> 2. The database has two users, “admin” and “test_user”.
> 3. Admin user runs a variation of “CREATE SCHEMA test_schema”
> 4. Admin user runs “CREATE TABLE test_schema.test_table”
>
>
>
> 1. Test_user runs “SELECT * FROM test_schema.test_table”, receives
> “ERROR: permission denied” (as expected).
> 2. Admin runs “GRANT ALL PRIVILEGES ON test_schema.test_table TO
> test_user”, receives “Query returned successfully”
> 3. Test user runs “SELECT * FROM test_schema.test_table”, receives
> “ERROR: permission denied”
>
> ---
>
> If you now check the permissions on “test_table”, you’ll see that it
> correctly granted all privileges to “test_user”, despite throwing errors
> when “test_user” attempts to access it; this is the source of confusion.
> Experienced users will know that you must FIRST run “GRANT USAGE ON SCHEMA
> test_schema TO test_user”, so that the user has access to the schema.
>

I'm wondering if we could provide a more helpful error message in this
case, something along the lines of:

DETAIL: 'usage' permission required on schema 'test_schema'

Cheers,
--
Alex