Roles in postgres not inheritet by user

Question

I have create 2 roles . Read only and full_access like this.

CREATE ROLE read_only;
CREATE ROLE full_access;

the i add with grant privileges

GRANT SELECT ON ALL TABLES  ON DATABASE db_test TO read_only;
GRANT ALL PRIVILEGES ON DATABASE db_test TO full_access;

after that i create a user

CREATE USER ex;
ALTER USER "ex" WITH  PASSWORD '00000' ;
grant full_access TO "ex" ;

The user ex has the role but it cannot perform select read update on all tables. what is wrong? I want the user ex to be in the role full_access and be able to read write update tables. BUT only from a role because i have to add also other users to this role. I have to add other users to the role read_only and only read.

I do not want to add to user select read write update but to get(inherit form full_access role) it from the role . Other user will get the read only access from the read_only role .

Here some photos

the role role permissions user user properties permissions Is it possible ? thanks

    CREATE ROLE test_schema_read_only;
    GRANT USAGE ON SCHEMA test TO test_schema_read_only;
    GRANT SELECT ON ALL TABLES IN SCHEMA test TO test_schema_read_only;
    GRANT USAGE, CREATE ON SCHEMA test TO test_schema_read_only;
    REVOKE ALL ON ALL TABLES IN SCHEMA public FROM test_schema_read_only;
     

    CREATE ROLE test_schema_full_access;
    GRANT USAGE ON SCHEMA test TO test_schema_full_access;
    GRANT USAGE, CREATE ON SCHEMA test TO test_schema_full_access;
    GRANT SELECT, INSERT, UPDATE,DELETE ON ALL TABLES IN SCHEMA  test TO test_schema_full_access;
    GRANT USAGE ON ALL SEQUENCES IN SCHEMA test  TO test_schema_full_access;
    REVOKE ALL ON ALL TABLES IN SCHEMA public FROM test_schema_full_access;
    ALTER DEFAULT PRIVILEGES IN SCHEMA test GRANT USAGE ON SEQUENCES TO test_schema_full_access;
    
    
    
    DROP USER IF EXISTS "ex";
    CREATE USER "ex";
    ALTER USER "ex" WITH  PASSWORD '00000' ;
    GRANT test_schema_full_access TO 'ex' ;
    
    
    DROP USER IF EXISTS "sec";
    CREATE USER "sec";
    ALTER USER "sec" WITH  PASSWORD '00000' ;
    GRANT test_schema_read_only TO 'sec' ;

Also hier the result of \ztable and \du user result of \z and \du

Laurenz Albe · Answer

Your first GRANT is a syntax error. But your whole approach is flawed: granting permissions on the database won't give you any permissions on tables in the database.

Use a combination of

GRANT USAGE ON SCHEMA x TO read_only;

and

GRANT SELECT ON ALL TABLES IN SCHEMA x TO read_only;

to achieve your goal. You'll have to run that for each schema.

Roles in postgres not inheritet by user

1 Answers