Search code examples
postgresqlrolesprivilegessql-grant

Postgresql CREATE GRANT - Limit Access to other Databases on Server?

I have 2 Postgresql databases, called abc and xyz. I want to create a user for each to be used in a website, so I've created a user with a command like:

CREATE ROLE abc LOGIN PASSWORD 'abc';

I then did this to give them permission to the database:

GRANT ALL PRIVILEGES ON DATABASE abc TO abc;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO abc;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO abc;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL on TABLES TO abc;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT ALL on SEQUENCES TO abc;

At that point the abc user can access the tables and I believe any future created tables.

I noticed that the abc user can see that the xyz database exists and actually go into it and see the schema, tables, and even structure of the tables in pgAdmin.

I'd like to prevent this and lock down the abc user to only be aware of the abc database as much as possible.

Where did I go wrong in this configuration and what's the best way to lock this down?

Thanks!

Solution

  • You'd have to forbid connections to the other database to the user.

    There are two possibilities:

    1. Via database permissions:

      -- by default, everybody can connect
REVOKE CONNECT ON DATABASE other FROM PUBLIC;
-- now you have to grant access to the legitimate users specifically
GRANT CONNECT ON DATABASE other TO other_users;

    2. Via pg_hba.conf.

      Don't add an entry to the file that allows abc to connect to other.

      So rather than having

      host all all 0.0.0.0/0 scram-sha-256

      have entries like

      host other +other_users 0.0.0.0/0 scram-sha-256
host abc   abc          0.0.0.0/0 scram-sha-256