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

[Damian Carey <jamianb@xxxxxxxxx>]

Tom, Rob & Adrian,

I understand exactly what each of you are getting at, but instead of fumbling and further wasting your time I'm going to get a freelancer to smash out a suitable setup sans beginner mistakes. It's a pretty basic problem for a learned colleague.

I've clearly thrived in my safely walled PG garden for too long to sort this basic stuff. Our Linux consultant is top notch, but PG's not his core skill.

Appreciate you all taking the time. Wish I could have asked a more satisfying question with a neat and useful solution :-)

Huge thx.

-Damian

On Tue, 14 Feb 2023 at 10:58, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:

On 2/13/23 13:45, Damian Carey wrote:
> Hi,
>
> Amateur question here :-{  Despite using Postgres for 15 years it's
> always been locked safely inside a VPS with Hibernate on top powering a
> Java web app. Each customer is on a separate VPS which typically has
> ~500k rows over about 30 tables. Basic but very effective.
>
> 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.

Define in detail what "... databases are visible" means?

In psql does \l show anything?


>
> Ubuntu 22.04
> PG14
> Their Linux user (say): "user2" / "theuser2linuxpwd"
> Postgres user (say): "user2" / "myuser2pwd"
> Postgres db they access (say): "mytransferdb"
>
> In psql I did:
> create user user2 with encrypted password 'myuser2pwd';
> grant all privileges on database mytransferdb to user2;

The above GRANT is not doing what you probably think it is doing.

From

https://www.postgresql.org/docs/current/ddl-priv.html

"all privileges on database: means:

CREATE

     For databases, allows new schemas and publications to be created
within the database, and allows trusted extensions to be installed
within the database.

CONNECT

     Allows the grantee to connect to the database. This privilege is
checked at connection startup (in addition to checking any restrictions
imposed by pg_hba.conf).

>
> I didn't think it was a pg_hba.conf issue because via SSH tunnel
> they appear inside linux as if localhost (I think?).
>
> After you stop laughing/crying, can anyone guide me?
>
> Huge thx
> -Damian

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx