postgresqldatabase-permissions

I granted all privileges to a table to a user, but still get "permission denied"

I created a role read_role via pgAdmin:

CREATE ROLE read_role WITH
NOLOGIN
NOSUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION;

and gave it permissions on my schema

alter default privileges in schema intgr grant SELECT on tables to read_role ;
GRANT SELECT ON ALL TABLES IN SCHEMA intgr TO read_role ;

Then I create a user via pgAdmin and made it member of read_role:

CREATE ROLE user WITH
LOGIN
NOSUPERUSER
INHERIT
NOCREATEDB
NOCREATEROLE
NOREPLICATION
ENCRYPTED PASSWORD '';

GRANT read_role TO user WITH ADMIN OPTION;

And when I login with this user and try to select from that table, it returns me "permission denied".

What did I do wrong?

Solution

  • You forgot the permissions on the schema itself; a frequent omission:

    GRANT USAGE ON SCHEMA intgr TO read_role;