CODEWITHSUNDEEP
amazon-redshift
Manash Deb
Manash Deb

Reputation: 331

how to revoke pg_catalog.pg_database access from a user in Redshift

Our use case involves multiple databases in a redshift cluster, but users in each database should not be able to see other database names. To achieve this, I revoked access permissions below from public and also tried cascade, but users are still able to access pg_catalog.pg_database. Is there a way to revoke this access?

create database newdb1;
REVOKE USAGE ON SCHEMA information_schema FROM PUBLIC;
REVOKE USAGE ON SCHEMA pg_catalog FROM PUBLIC; 
REVOKE SELECT ON TABLE pg_catalog.pg_database FROM PUBLIC; 
REVOKE USAGE ON SCHEMA information_schema FROM PUBLIC cascade;
REVOKE USAGE ON SCHEMA pg_catalog FROM PUBLIC cascade;
REVOKE SELECT ON TABLE pg_catalog.pg_database FROM PUBLIC cascade; 
create user new_user password 'xxxxxx';
grant USAGE ON SCHEMA public to new_user;
revoke select on table pg_catalog.pg_database from new_user CASCADE;

Below is the Aginity Connection Screen for this new user, showing all database names, though he should not have any access on these:

enter image description here

I can also view pg_catalog.pg_database when i login as new_user:

enter image description here

Upvotes: 2

Views: 1370

Answers (1)

Manash Deb
Manash Deb

Reputation: 331

Found the answer :)

I needed to run below revoke statement in each databases in the cluster to solve this. Byt default, redshift creates a new version of pg_database in each of its databases and hence we need to revoke this permission from all of them.

REVOKE SELECT ON TABLE pg_catalog.pg_database FROM PUBLIC;

Upvotes: 2

Related Questions