Sample Header Ad - 728x90

How do I create a restricted postgres role for a text-2-sql app?

0 votes
1 answer
38 views
I'm using AGI to generate Postgres SQL (AWS RDS Postgres) but I want to prevent users asking questions about underlying tables such as "Show me all databases", "Show me all users" that result in queries like SELECT * FROM pg_database and SELECT * FROM pg_roles. The working script I have for this is: Connect to postgres as postgres (super user):
CREATE ROLE client_creator WITH LOGIN PASSWORD '' CREATEDB CREATEROLE;
Connect to postgres as client_creator:
CREATE DATABASE "client_db";
CREATE ROLE "client_reader" WITH LOGIN PASSWORD '';
Connect to client_db as client_creator
GRANT CONNECT, TEMPORARY ON DATABASE "client_db" TO "client_reader";
GRANT SELECT ON ALL TABLES IN SCHEMA public TO "client_reader";
GRANT USAGE ON SCHEMA public TO "client_reader";

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO "client_reader"; 

REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM client_reader;
REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM public;
The issue is the final two REVOKE commands seem to have no effect since I can still run SELECT * FROM pg_roles and receive all rows. I've also tried revoking SELECT on tables in the pg_catalog too. Question is: * Can I do this? * Should I do this? Thoughts... * I have seen comments in other posts that suggest this is a bad idea but since I understand each database has a copy of pg_catalog I thought it would not be an issue * I believe the generate queries should not rely on pg_catalog objects but I may be wrong
Asked by Stokedout (103 rep)
Dec 29, 2024, 12:05 AM
Last activity: Jul 28, 2025, 04:09 PM