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)
Advertisement
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
).