Re: HOWTO? Permissions for user to access a single db

From: Damian Carey <jamianb(at)gmail(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Postgresql <pgsql-general(at)postgresql(dot)org>
Subject: Re: HOWTO? Permissions for user to access a single db
Date: 2023-02-13 22:44:09
Message-ID: CA+QCafcdMdBtuiyDF_o2h_wac2MDqUxka82MtwAc-Tq8eRLXWA@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi Tom,

Sorry for the kinda-complicated response.

We have worked for years with this other product (let's call it PP), and
maybe 20% of our customers are in common, traditionally both products
sitting on the same windows PC in some office accessing localhost PG. No
problem. All data belongs to the customer and security is a customer issue.

PP only has a windows desktop product. We also have a web solution served
from Linux VPS (one VPS per customer), and any single customer is on one
VPS/IP which only has a single PG installation/instance on it. 100% default
PG setup. No tweaking at all. 100% isolation from everything.

The PP product is still running on the windows PC in the customer office,
so we give them an SSH tunnel to get into our VPS at 5432.

The PP product is on MSSQL, so they use some connector (sorry, no idea
what) from the customer PC to access my PG14 on Ubuntu.

In our first trial/proof-of-concept we gave them PG superuser access. They
could see everything in PG, including the bits they need. Working, but too
open for my liking.

This is their screenshot supplied to me of a working connection ....

[image: image.png]

Below is our second trial/proof-of-concept where I tried to limit them to
ONLY need-to-know on the one shared database they read from.

It seems they are accessing (the one and only) PG cluster on the VPS, but
no database is visible, only "default".

[image: image.png]

Apologies for the vagaries.

I assumed this is just a pg user permissions issue. Maybe not.
-Damian

On Tue, 14 Feb 2023 at 08:59, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Damian Carey <jamianb(at)gmail(dot)com> writes:
> > We now need to provide access to an associate company to a single
> database
> > (3 tables, ~10k rows) that our java app writes to (not JDBC, via
> > Hibernate). We have a nice SSH tunnel coming in, but they cannot view the
> > shared database (yes, I'm an amateur).
>
> > I'm just looking for beginners suggestions to get this db visible to this
> > user so we can continue our trials. They have their own Linux user login,
> > and their SSH access gives them access to port 5432 and nothing else.
> They
> > can see postgres, but no databases are visible.
>
> What do you mean by "visible" ... that "select * from pg_database"
> shows only "postgres"? If so, the most likely theory is that they
> are not connecting to the same Postgres instance you are.
> There's not any permission-based filtering on what you can see in
> that catalog.
>
> regards, tom lane
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2023-02-13 22:54:32 Re: HOWTO? Permissions for user to access a single db
Previous Message Tom Lane 2023-02-13 21:59:48 Re: HOWTO? Permissions for user to access a single db