PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – ERROR: permission denied for schema pg_catalog

I have postgresql database locally. I want to create a postgresql user that does not have access to pg_catalog and information_schema. I want this user to only read data in the database and not fetch information related to database or should not create,delete update or insert anything into database.

I tried doing this with below commands

`

    CREATE USER username WITH PASSWORD 'password' CREATEDB;
    REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM your_username;
    REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM your_username;
    REVOKE ALL PRIVILEGES ON DATABASE your_database_name FROM your_username; 
    GRANT SELECT ON TABLE my_db.public.area_name,my_db.public.building_name, 
    my_db.public.floor_number TO new_user;
    REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM newrole;
    REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM public;
    REVOKE ALL PRIVILEGES ON SCHEMA information_schema FROM newrole;
    REVOKE ALL PRIVILEGES ON SCHEMA information_schema FROM public;

`
After doing all of this, when i query below :

SELECT tablename FROM pg_catalog.pg_tables WHERE schemaname != 'pg_catalog' AND schemaname != 'information_schema';

It say’s permission denied which is what i wanted in the first place. Now the issue come’s up when i try to run a query that has interval keyword in it as for interval data type it needs access to pg_catalog for which i have revoked permissions.

for eg.
if I run a query like

SELECT current_date AS "Today",
       current_date + INTERVAL '1 week' AS "Next Week";

It gives me error

ERROR: permission denied for schema pg_catalog

How can i restrict this user to query on the data but not on the metadata or structure of database ?

I want it to have access to interval data type but not pg_catalog.
Please feel free ask any questions if needed. Any kind of help will be super appreciated. thank you

ps – The examples/Table names are dummy here as i can’t write the actual table names. kindly ignore that part.

Tags:

2

Answers


  1. 0

    You cannot remove all access to pg_catalog. How is it supposed to look up details on the interval data type if it can’t access the catalogue? How can you query a table without referring to its name or any of its columns?

    If you really don’t want users to be able to see the structure of your database, don’t let them run queries directly.

    Restrict their access to only calling functions and use those functions to provide the desired API.

    Login or Signup to reply.
  2. 0

    The errors you see indicate that you are breaking the database. Don’t change permissions or anything on the system schema or the system objects. In PostgreSQL, most metadata are public, and you cannot change that without breaking some functionality in the database.

    If you are concerned about security, this is the wrong way. "Security by obscurity" has a bad name for a good reason. In such a case, you wouldn’t allow the user to execute arbitrary SQL statements anyway, and your application can easily avoid querying the pg_catalog tables directly.

    If your concern is that you don’t want to clutter the user’s metadata queries with objects that don’t concern the user, run your metadata queries against the views in information_schema. These views only show objects on which the user has permissions. If that is not feasible (for example, if you want to view metadata not available in information_schema) add the appropriate WHERE conditions to your metadata queries to filter out the entries that don’t concern the user.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search