GRANT USAGE ON SCHEMA doesn't do anything

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)

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;

I had the same issue and contacted AWS support;

Template0 owner is rdsadmin. You should not use it to create a new database. Template1 owner is root (Master user). You should usee it to create a new database. [...]we are not able to alter the schema owner back to rdsadmin, as it is a super user reserved for RDS. So, you should never use the template0 to create a database.

In my case it could be fixed by running:

alter schema public owner to root;

GRANT USAGE ON SCHEMA doesn't do anything

Related

Recent Posts