On Wed, May 6, 2020 at 9:23 AM Justis Lincoln Mackaoui <jmackaou@calpoly.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:
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”
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.
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'