3

I'm trying to give a new user all permissions for an existing database. I had run the following commands:

CREATE USER new_user WITH PASSWORD 'pass';
GRANT CONNECT ON DATABASE my_base TO new_user;
GRANT USAGE ON SCHEMA public TO new_user;
GRANT ALL PRIVILEGES ON DATABASE my_base TO new_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO new_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO new_user;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO new_user;

After connecting as the user new_user I can see all tables, but trying to SELECT on any table yields: [42501] ERROR: permission denied for relation [name of table]

Command \l list new_user in an Access privileges.
Command \z list 0 rows.

Version: PostgreSQL 10.10 (Ubuntu 10.10-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04.1) 7.4.0, 64-bit

Maxim Andreev
  • 182
  • 2
  • 10
  • 1
    Perhaps you ran the `GRANT` statements in the wrong database. – Laurenz Albe Nov 21 '19 at 12:47
  • @LaurenzAlbe I tried to run commands as `postgres` user from `psql`, and as a superuser from the database `my_base` – Maxim Andreev Nov 21 '19 at 16:13
  • @LaurenzAlbe I tried to run commands directly from a server and you were right. All commands must be run from within the database in question. Before I tried to run commands from external tool and it were not closing/opening connection when I changed database (so my commands weren't technically run from within the database I tried to give permission for). – Maxim Andreev Nov 21 '19 at 16:25

0 Answers0