Granting select to a role

I'm having an issue, where I want to create a role and grant select on all the tables in my database to that role.

So far as an example:

I've created the role, "create role tmp_role;"

granted usage on that role to all the schemas, "grant usage on schema tmp_schema to tmp_role;"

granted select on all tables to that role, "used a sql generated sql"

granted that role to the a user, "grant tmp_role to tmp_user;"

granted usage on schema to the user, "grant usage on schema tmp_schema to tmp_user;"

alter that user to have that as a default role, "alter user tmp_user default role tmp_role"

Seems I'm still not seeing any of the tables in the database.. is there something I'm missing when it comes to using roles?

Thanks for the help in advance

Steps looks correct.

It all statements were successful it should work.

Here are some checks you can do

LOGIN as tmp_user

1. Verify that role is granted to user. Run : show available_roles;

2. Verify that role is enabled in session. Run : show enabled_roles;

You should see tmp_role in both cases.

LOGIN as DBADMIN

1. Verify that role granted to user. Run: select * from grants where grantee = 'tmp_user';

You should see line for ROLE grant to USER

2. Verify that needed permissions granted to role. Run: select * from grants where grantee = 'tmp_role';

You should see a lot of lines for each SELECT/USAGE grant to ROLE

Sign up