How to grant PostgreSQL user connect privileges to a database but not be able to query any tables in its public schema?

Question

I have a database named somedb owned by user someuser. I have revoked all privileges from public to this database.

I want to grant connect privileges to anotheruser, but I don't want anotheruser to be able to query any of the non-system tables (i.e. any table created by someuser) in its public schema. Is that possible? Or do I need to move all the tables in the public schema to a different schema?

When I GRANT CONNECT ON DATABASE somedb TO anotheruser; and connect to database somedb as user anotheruser, I can query all the tables in somedb.

I tried the following, but it didn't work:

REVOKE ALL ON somedb.public.sometable FROM anotheruser;
ALTER DEFAULT PRIVILEGES FOR USER anotheruser IN SCHEMA public
 REVOKE ALL ON TABLES FROM anotheruser;
ALTER DEFAULT PRIVILEGES FOR USER anotheruser IN SCHEMA public
 REVOKE ALL ON SEQUENCES FROM anotheruser;

It might be useful to know that anotheruser has SELECT and other privileges in a different database on the same server.

Answer 1

OK, I think I figured it out. I had to change the public schema's owner to be the owner of the database and then revoke all privileges on the public schema from public, like this:

ALTER SCHEMA public OWNER TO someuser;
REVOKE ALL ON SCHEMA public FROM PUBLIC;

I found this web page helpful.

• Ed Sabol: I found this web page helpful: severalnines.com/database-blog/…