27

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.

Jordan Arsenault
  • 7,100
  • 8
  • 53
  • 96
Dan
  • 6,008
  • 7
  • 40
  • 41

3 Answers3

10

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?

Rdbhost
  • 929
  • 2
  • 8
  • 20
  • 5
    users will be creating dbs on our side. We dont' want user to be able to see each others databases, tables names etc as a measure of data privacy/security. – Dan Jul 29 '10 at 19:14
  • 3
    Its a bad thing when users can list the tables in databases, that are not theirs. – harmv Jul 15 '13 at 14:58
  • 19
    It gives out data what is not surely wanted. For example, a database named *"cicallc"* may indicate that "Cica Llc" is your customer. A following database named "cicaalfresco" may indicate, what are they doing by you. The number of the databases may show from your system, how many users (=customers) are there on it. And so on. It is a conceptual mistake from the postgresql developers. An ordinary user should find himself in an environment, where he has access to *only* anything for which he have to, and no more. – peterh Apr 19 '17 at 14:02
  • 1
    @peterh-ReinstateMonica " It is a conceptual mistake from the postgresql developers" exactly; I can't believe they made it so. That would be like making login names of all users (in any system) available to each logged in user. – P Marecki May 01 '20 at 08:23
  • Unless I find a way around that, I guess the lesson is to have totally meaningless database names, not in any way related to user/role names who can access them. – P Marecki May 01 '20 at 08:34
  • (Correction; damn -- any user can still run `\l` and get a list of db's and owners... hopeless...) – P Marecki May 01 '20 at 08:40
  • 1
    @PMarecki Note, m$ sql has the same problem. Mysql has not. – peterh May 01 '20 at 14:12
  • Wondering is there any updates on this with the recent versions? – viggy28 May 30 '21 at 08:03
  • @viggy28 see my comment below Frank's answer.. yes a step in the right direction but not yet complete – LMSingh Sep 29 '22 at 16:40
  • @peterh similarly the Oracle database (the big one, not Oracle mysql) also does not have this problem.. and yes we can put three $$$ next to Oracle compared to one for M$ SQL. For example oracle view "ALL_TABLES describes the relational tables accessible to the current user" If the user does not have privileges they can not access DBA_TABLES view which lists all the tables in the database. – LMSingh Sep 29 '22 at 17:21
3

REVOKE the SELECT permissions on the information_schema and some sections in the system catalog.

Frank Heikens
  • 117,544
  • 24
  • 142
  • 135
  • 1
    Wondering is there any updates on this with the recent versions? – viggy28 May 30 '21 at 08:03
  • 1
    @viggy28: No, nothing has changed. When you allow the user to connect to your database, this user can get (some) information from your database. The choice is yours. – Frank Heikens May 30 '21 at 18:25
  • On a cursory check of latest docs, the information_schema views have been updated and seem to have corrected the problem but system catalog remains a big gaping hole. For example the docs say "Only those tables and views are shown that the current user has access to (by way of being the owner or having some privilege)." https://www.postgresql.org/docs/current/infoschema-tables.html I've not checked full details but it's a step in the right direction. Revoking system catalog permissions will probably affect tools like pgAdmin, assuming they're still relying on system views. – LMSingh Sep 29 '22 at 16:39
2

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.

Sam Coles
  • 4,003
  • 24
  • 19