PostgreSQL Configuration

3 min

Purpose

In PostgreSQL, the goal is to allow the user [username] to read data from specific schemas (e.g., public, information_schema, etc.) and from catalog tables if necessary.

Grant Permission

-- Grant SELECT on all tables under " public "
GRANT SELECT ON ALL TABLES IN SCHEMA public TO [ username ];

-- Grant SELECT on all tables under " information_schema "
GRANT SELECT ON ALL TABLES IN SCHEMA information_schema TO [
username ];

 -- Allow SELECT on pg_catalog tables
GRANT SELECT ON pg_catalog . pg_class TO [ username ];
GRANT SELECT ON pg_catalog . pg_attribute TO [ username ];
GRANT SELECT ON pg_catalog . pg_database TO [ username ];
GRANT SELECT ON pg_catalog . pg_collation TO [ username ];
GRANT SELECT ON pg_catalog . pg_tables TO [ username ];
GRANT SELECT ON pg_catalog . pg_views TO [ username ];

-- Allow SELECT on information_schema tables
GRANT SELECT ON information_schema . columns TO [ username ];
GRANT SELECT ON information_schema . views TO [ username ];
GRANT SELECT ON ALL TABLES IN SCHEMA < schema_name > TO [ username ];

Testing Permissions

To verify that [username] can read from the PostgreSQL catalogs or schema tables, execute:

-- Example test for pg_catalog
SELECT * FROM pg_catalog . pg_tables ;

If the user can run this query without an error, the privileges have been successfully applied.