3

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)
Improve this question
2
  • Can't reproduce this in a quick test. Can you add the result of select version(); and \dn+ in psql please? Commented Jun 13, 2020 at 11:29
  • @DanielVérité thank you for taking a look at this! I've updated my post with the requested output Commented Jun 15, 2020 at 18:30

2 Answers 2

Reset to default
8

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
1
  • 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! Commented Jun 16, 2020 at 19:34
2

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;
Improve this answer
1
  • relatedly, this was also a good solution for the case where a masteruser creates a database and then alters owner but ... changing the ownership of the database didn't change the ownership of the public schema Commented Nov 10, 2021 at 19:15

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.