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
Last activity: Jul 28, 2025, 04:09 PM