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

Question

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!

Laurenz Albe · Accepted Answer · 2020-10-23 06:57:37Z

2

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

There are two possibilities:

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;

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

answered Oct 23, 2020 at 6:57

Laurenz Albe

256k22 gold badges310 silver badges386 bronze badges

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

1 Comment

Andrew T Over a year ago

Thanks, the REVOKE/GRANT makes perfect sense and works well. I hadn't thought about pg_hba.conf as a solution but like that idea too.

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related