I want to create a postgres user that can access only one database on the postgres server at all.

Currently my flow is:

create database database1; create user user1 with password 'pass'; grant all privileges on database database1 to user1;

but user1 can still see a list of dbs, users, tables etc. Is there a way to prevent that user from seeing that info? The user needs to be able to write to and read from that db.

Thanks a lot.

 

 

Each user can see other databases and roles listed, but should not be able to see tables in other databases, ever.

If you revoke CONNECT privilege on all databases except the allotted one, the user will not be able to access the contents of other databases.

Roles and database names are global, and not readily blockable. You can try Frank Heikens suggestion of selective revocations on the system tables, but you take risks to do that. PostgreSQL developers on the usenet mailing lists have discouraged tampering with access to the system catalogs.

Psql, among other tools, assumes they will be available and functions poorly without them.

Why is knowing the names of other databases and roles so bad?

 

By default any objects you create are created in the public schema. Also, any users that you create have CREATE and USAGE privileges on the public schema. You should revoke CREATE and USAGE to the public schema for this user, or you should change the default access level. You'll also need to move the database to which this user has access into the user's schema, or a schema accessible to the user. See DDL Schemas in the Postgres manual.

 

 

REVOKE the SELECT permissions on the information_schema and some sections in the system catalog.    最佳答案