[SOLVED] Isolating User Access to a Single Schema in PostgreSQL

Isolating User Access to a Single Schema in PostgreSQL

I am by no means a DBA. My goal is to have a user (username) to access a single schema (user_schema) table and view and nothing else from other schemas on PostgreSQL 15.3 [psql (15.3 (Debian 15.3-0+deb12u1), server 15.4)].

what I do is:

CREATE ROLE username LOGIN PASSWORD 'user_passwd';
CREATE SCHEMA user_schema;
ALTER USER username SET search_path = user_schema;  -- Set the search path to 'user's schema'
GRANT USAGE ON SCHEMA olga TO username;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA user_schema TO username;
REVOKE SELECT ON ALL TABLES IN SCHEMA other_schema FROM username;

So when I connect to the database via:

$ psql -h <PUBLIC_IP> -U username -d postgres
Password for user username: 
psql (15.3 (Debian 15.3-0+deb12u1), server 15.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

postgres=> show search_path;
 search_path 
-------------
 user_schema
(1 row)

postgres=>

I verify that I am already directed to the correct schema and can access everything within it!

However, typing other_schema. and tab, I get some tab completion effect:

postgres=> SELECT * FROM other_schema.;
Display all 190 possibilities? (y or n)

which is undesired.

Although I get the correct behavior when targeting a specific table

postgres=> SELECT * FROM other_schema.table_name;
ERROR:  permission denied for schema other_schema
LINE 1: SELECT * FROM other_schema.table_name;
                      ^
postgres=>

How can I avoid this behavior, so as to completely isolate username's access to only the user_schema's objects?

Is this an autocompletion-related situation? If so, how can I deal with it?

Additionally, I would like this isolation to be propagated to all meta and backslash-commands (\dt, \l, \dn ... etc).

Right now I can see everything from everywhere:

postgres=> \dn
         List of schemas
   Name       |        Owner        
--------------+---------------------
 other_schema | postgres
 public       | pg_database_owner
 user_schema  | postgres
(3 rows)

With the search_path set to user_schema, I would like the outcome to be:

postgres=> \dn
         List of schemas
   Name       |        Owner        
--------------+---------------------
 user_schema  | postgres
(1 rows)

much like for \l...

postgres=> \l
                                                                List of databases
     Name      |       Owner       | Encoding |  Collate   |   Ctype    | ICU Locale | Locale Provider |            Access privileges            
---------------+-------------------+----------+------------+------------+------------+-----------------+-----------------------------------------
 postgres      | cloudsqlsuperuser | UTF8     | en_US.UTF8 | en_US.UTF8 |            | libc            | 
               |                   |          |            |            |            |                 | cloudsqlsuperuser=CTc/cloudsqlsuperuser
(1 rows)

postgres=>

Solution

You should first revoke all the privileges and then grant specific privileges. For example:

CREATE ROLE username LOGIN PASSWORD 'user_passwd';
CREATE SCHEMA user_schema;
ALTER USER username SET search_path = user_schema;

REVOKE ALL ON DATABASE db_1 from public;
REVOKE ALL ON SCHEMA public FROM PUBLIC;
REVOKE ALL ON ALL TABLES IN SCHEMA public FROM PUBLIC;
REVOKE ALL PRIVILEGES ON SCHEMA pg_catalog FROM PUBLIC;
REVOKE ALL PRIVILEGES ON SCHEMA information_schema FROM PUBLIC;

GRANT CONNECT, TEMPORARY ON DATABASE db_1 TO username;
GRANT USAGE, CREATE ON SCHEMA  user_schema TO username;
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA user_schema TO username;
GRANT USAGE, SELECT, UPDATE ON ALL SEQUENCES IN SCHEMA user_schema TO username;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA user_schema TO username;