Re: Clarity Bug for Schema Permissions, Potential Vulnerability?

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.

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”

 

5. Test_user runs “SELECT * FROM test_schema.test_table”, receives “ERROR: permission denied” (as expected).
6. Admin runs “GRANT ALL PRIVILEGES ON test_schema.test_table TO test_user”, receives “Query returned successfully”
7. 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.