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:
- The database has two schemas, “public” and “test”.
- The database has two users, “admin” and “test_user”.
- Admin user runs a variation of “CREATE SCHEMA test_schema”
- Admin user runs “CREATE TABLE test_schema.test_table”
- Test_user runs “SELECT * FROM test_schema.test_table”, receives “ERROR: permission denied” (as expected).
- Admin runs “GRANT ALL PRIVILEGES ON test_schema.test_table TO test_user”, receives “Query returned successfully”
- 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.
My argument is that step #6, the granting of privileges on the table, should return an “ERROR: permission denied” because the “test_user” should not know at all about the objects contained within “test_schema”. This would prevent the confusing state of contradictory permissions and access-errors.
---
This problem with contradictory permissions and access-errors is relatively minor and can be fixed by the user with a little playing around, however I’m curious if it might expose any real vulnerabilities. It seems as though the driver only validates the permissions of the logged-in user “admin”, who does have access to the schema “test_schema” as well as “test_table”, and not the permissions of “test_user”.
My suggestion is that the separation of permissions between Database/Schema/Table/User be respected equally by the driver, in the sense that both the logged-in user and any user-object pair in the expression have the same permission validation ran against them.
I’m no security specialist or expert on PostgreSQL’s source code, but I worry that someone who IS might come up with a strategy to exploit this behavior.
Thank you for your work,
Justis Mackaoui
New PostgreSQL user
