Re: HOWTO? Permissions for user to access a single db - Mailing list 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.
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 ....
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.
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".
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@sss.pgh.pa.us> wrote:
Damian Carey <jamianb@gmail.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
pgsql-general by date: