All commands must be executed while connected to the right database in the right database cluster. Make sure of it.
The user needs access to the database, obviously:
GRANT CONNECT ON DATABASE my_db TO my_user;
And (at least) the USAGE privilege on the schema:
GRANT USAGE ON SCHEMA public TO my_user;
Or grant USAGE on all custom schemas:
DO
$$
BEGIN
-- RAISE NOTICE '%', ( -- use instead of EXECUTE to see generated commands
EXECUTE (
SELECT string_agg(format('GRANT USAGE ON SCHEMA %I TO my_user', nspname), '; ')
FROM pg_namespace
WHERE nspname <> 'information_schema' -- exclude information schema and ...
AND nspname NOT LIKE 'pg\_%' -- ... system schemas
);
END
$$;
Then, all permissions for all tables (requires Postgres 9.0 or later).
And don't forget sequences (if any):
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO my_user;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO my_user;
For older versions you could use the "Grant Wizard" of pgAdmin III (the default GUI).
There are some other objects, the manual for GRANT has the complete list as of Postgres 12:
privileges on a database object (table, column, view, foreign table, sequence, database, foreign-data wrapper, foreign server, function, procedure, procedural language, schema, or tablespace)
But the rest is rarely needed. More details:
Consider upgrading to a current version.