Unable to grant all privileges to new user on Postgres

I’m creating a new user in a Postgres database (AWS RDS) and trying to grant all privileges on all tables in public schema of an existing database to that user. It is failing with below error. I even tried after logging in as the rds_superuser (postgres) and got the same error.

psql (13.3, server 10.15)

test_db=> dt
              List of relations
 Schema |     Name     | Type  |    Owner     
--------+--------------+-------+--------------
 public | table1       | table | test_user
 public | table2       | table | test_user
 public | table3       | table | test_user
(3 rows)

test_db=> CREATE ROLE new_user WITH LOGIN PASSWORD 'by1ne8Cs0Z2' VALID UNTIL '2021-05-29';
CREATE ROLE

test_db=> GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;

ERROR: permission denied for relation table1

Can someone help me out here?

EDIT: Found a workaround but not sure if this is the best approach. Logged in as the postgres user, ran the below query and then logged back in as the new_user and was able to run select queries on the tables.

test_db=> du new_user
                                 List of roles
    Role name    |                 Attributes                  |   Member of    
-----------------+---------------------------------------------+----------------
 new_user        | Password valid until 2021-05-29 00:00:00+00 | {}

test_db=> GRANT test_user to new_user;

test_db=> du new_user
                                 List of roles
    Role name    |                 Attributes                  |   Member of    
-----------------+---------------------------------------------+----------------
 new_user        | Password valid until 2021-05-29 00:00:00+00 | {test_user}

EDIT2: Please find the output below.

test_db=> dp table1 
                               Access privileges
 Schema |    Name    | Type  | Access privileges | Column privileges | Policies 
--------+------------+-------+-------------------+-------------------+----------
 public | table1     | table |                   |                   | 
(1 row)

Answer

You have to issue the GRANT statements as user test_user.

Only the owner can grant privileges on an object (or a user who has been granted the privilege WITH GRANT OPTION).

Advertisement

Answer