Why can't I see user privileges in postgresql?

user_5421539 0
Viewed 515

I am using PostgreSQL 11.8 in AWS RDS and I created a user as below:

CREATE USER test WITH LOGIN;
GRANT rds_iam TO test;

the code runs success but I can't find the user test from:

SELECT * FROM information_schema.table_privileges where grantee='test';

it returns an empty result to me.

I am able to see that user by running

SELECT *FROM pg_catalog.pg_user where usename='test';.

Why can't I grant access to the user?

postgresql
3 Answers

Your GRANT statement didn't grant a privilege on a table, it added the user to the role (“group”) rds_iam.

User test itself doesn't have any privileges on tables, it only inherits them.

information_schema.table_privileges will only show the privileges that were granted to a user, not the privileges inherited via role membership.

user_64643080

Your SQL command is correct. The problem is on the given role_name. You can get all users by executing the below command

SELECT *FROM pg_catalog.pg_user

Make sure the given role_name is in the listed role name in the PostgreSQL server. Below the command, you can get users with role

SELECT usename AS role_name,
CASE 
  WHEN usesuper AND usecreatedb THEN 
    CAST('superuser, create database' AS pg_catalog.text)
  WHEN usesuper THEN 
     CAST('superuser' AS pg_catalog.text)
  WHEN usecreatedb THEN 
     CAST('create database' AS pg_catalog.text)
  ELSE 
     CAST('' AS pg_catalog.text)
END role_attributes
FROM pg_catalog.pg_user
ORDER BY role_name desc;
user_123877660

In PostgreSQL RDS I seen this behavior, where "information_schema.table_privileges" is not getting updated with privileges which granted to any user or role If the table owner is not "dbuser".

So I changed the table owner to "dbuser" immediately it's started showing all the grants which I granted to different users and roles. Not sure how the "Table owner" impacting "table_privileges" view.

user_155256230
Related