Postgres user sees all schemas/tables

Question

When I create a new user for the database without any rights, this new user can still see all the schemas/tables etc. He can't access them, but he still sees them. I would like to revoke these privileges, but do not know how.

This is how I created the user:

CREATE USER wouter_test WITH PASSWORD 'wouter_test'
   NOSUPERUSER NOCREATEDB NOCREATEROLE NOCREATEUSER INHERIT;

based on this post I thought it may have to do with rights that users have to the public schema and the information the public schema contains: https://dba.stackexchange.com/questions/98892/minimal-grants-for-readonly-single-table-access-on-postgresql

Based on the wikisite: https://wiki.postgresql.org/wiki/Shared_Database_Hosting I used this command

REVOKE ALL ON SCHEMA public FROM wouter_test;

It did not work. The following did not seem to work either (to prevent the user from seeing and accessing a database called klm)

REVOKE connect ON DATABASE klm FROM wouter_test;

But still the user, in PGAdmin, can see all the databases, schemas and tables (including klm).

What am I doing wrong?

The DBA answer you linked talks about the PUBLIC role, not about the public schema. (Technically it mentions the schema too, but only says that it's usually not used, so it can be dropped altogether, instead of revoking/granting rights on it.) — pozs
– pozs, Commented Mar 28, 2017 at 9:56
Do you suggest I should revoke the public role, because the public role allows users to see the database structure? — Wouter Hoenderdaal
– Wouter Hoenderdaal, Commented Mar 28, 2017 at 10:04
If you want to manage rights for users/roles who are not created yet: yes, you typically want to grant/revoke on PUBLIC (but note that PUBLIC also covers already created users too). — pozs
– pozs, Commented Mar 28, 2017 at 10:06
Thank you. I am a little bit worried though what the consequences will be when I revoke on public. How does it affect already created users? — Wouter Hoenderdaal
– Wouter Hoenderdaal, Commented Mar 28, 2017 at 10:19
It will affect them too. As I said, every role is member of PUBLIC: newly created ones as well as old ones. GRANT for those you want to keep permissions before REVOKE from PUBLIC. — pozs
– pozs, Commented Mar 28, 2017 at 10:21

Laurenz Albe · Accepted Answer · 2017-03-28 10:25:16Z

2

You can REVOKE ... FROM PUBLIC to forbid all users to use an object, but you cannot keep the user from seeing the objects that way.

You can experiment with revoking privileges on system catalog tables like this:

REVOKE SELECT ON pg_catalog.pg_class FROM PUBLIC;

which will keep people from seeing the tables in the database.

This should keep the database functional, but it will cause errors in client programs like psql that expect to be able to read from these tables.

answered Mar 28, 2017 at 10:25

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Postgres user sees all schemas/tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related