0

For whatever reason, our database is not respecting GRANT commands. Below is an example with a newly created user.

pddbtest=> create user test_user login;
CREATE ROLE

then verify user does not have schema permission yet

pddbtest=> SELECT rolname, has_schema_privilege(rolname, 'public', 'usage') from pg_roles where rolname='test_user';
  rolname  | has_schema_privilege
-----------+----------------------
 test_user | f
(1 row)

then grant permission

pddbtest=> grant usage on schema public to test_user;
GRANT

then read if permission exists now (it does not)

pddbtest=> SELECT rolname, has_schema_privilege(rolname, 'public', 'usage') from pg_roles where rolname='test_user';
  rolname  | has_schema_privilege
-----------+----------------------
 test_user | f
(1 row)

Even after granting permission on the schema, which doesn't return any errors, the database still sees the permission as not granted. (if I add a select grant on all tables in schema after, I also still get a schema permission denied error when trying to query any of the tables)

(Search path is "$user",public should that information be relevant)

EDIT: Here is the output of SELECT version(); and \dn+ as requested:

PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit


                             List of schemas
   Name    |     Owner      |         Access privileges        | Description 
-----------+----------------+----------------------------------+------------------------
 fnrbaks   | appuser_root   |                                  |
 mask      | maint_user_2   | maint_user_2=UC/maint_user_2+    |
           |                | readonly_viewer=U/maint_user_2   | 
 pganalyze | appuser_root   | appuser_root=UC/appuser_root    +| 
           |                | pganalyze=U/appuser_root        +| 
           |                | pganalyze2=U/appuser_root        | 
 public    | rdsadmin       | appuser_root=UC/rdsadmin        +| standard public schema
           |                | proddb=U/rdsadmin                | 
(4 rows)
  • Can't reproduce this in a quick test. Can you add the result of select version(); and \dn+ in psql please? – Daniel Vérité Jun 13 at 11:29
  • @DanielVérité thank you for taking a look at this! I've updated my post with the requested output – Danny C Jun 15 at 18:30
0

The public schema belongs here to rdsadmin. A possible explanation to the GRANT not working is that it's run by an under-priviledged user (not rdsadmin and not superuser) that doesn't have the right to grant rights on public:

grant usage on schema public to test_user;

By default if that grant is ineffective, a warning is displayed:

WARNING:  no privileges were granted for "public"

but it depends on the client_min_messages setting. If that setting is below the warning threshold (for instance it's set to error), then the warning is not displayed and you'd have the behavior shown in the question. This can be verified with:

 SHOW client_min_messages;

and changed to a more verbose level with:

SET client_min_messages TO notice;
| improve this answer | |
  • ah, yes this was it! the message level was suppressing the WARNING, and apparently rdsadmin didn't give the root user the GRANT OPTION to assign USAGE permissions to other users. thank you thank you! – Danny C Jun 16 at 19:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.