pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedHOWTO? Permissions for user to access a single db
10+ messages / 4 participants
[nested] [flat]
* HOWTO? Permissions for user to access a single db
@ 2023-02-13 21:45 Damian Carey <jamianb@gmail.com>
2023-02-13 21:59 ` Re: HOWTO? Permissions for user to access a single db Tom Lane <tgl@sss.pgh.pa.us>
2023-02-13 23:58 ` Re: HOWTO? Permissions for user to access a single db Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 10+ messages in thread
From: Damian Carey @ 2023-02-13 21:45 UTC (permalink / raw)
To: pgsql-general
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.
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;
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: HOWTO? Permissions for user to access a single db
2023-02-13 21:45 HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
@ 2023-02-13 21:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-13 22:44 ` Re: HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Tom Lane @ 2023-02-13 21:59 UTC (permalink / raw)
To: Damian Carey <jamianb@gmail.com>; +Cc: pgsql-general
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
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: HOWTO? Permissions for user to access a single db
2023-02-13 21:45 HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
2023-02-13 21:59 ` Re: HOWTO? Permissions for user to access a single db Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-02-13 22:44 ` Damian Carey <jamianb@gmail.com>
2023-02-13 22:54 ` Re: HOWTO? Permissions for user to access a single db Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 10+ messages in thread
From: Damian Carey @ 2023-02-13 22:44 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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@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
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: HOWTO? Permissions for user to access a single db
2023-02-13 21:45 HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
2023-02-13 21:59 ` Re: HOWTO? Permissions for user to access a single db Tom Lane <tgl@sss.pgh.pa.us>
2023-02-13 22:44 ` Re: HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
@ 2023-02-13 22:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-13 23:14 ` Re: HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Tom Lane @ 2023-02-13 22:54 UTC (permalink / raw)
To: Damian Carey <jamianb@gmail.com>; +Cc: pgsql-general
Damian Carey <jamianb@gmail.com> writes:
> 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.
Black boxes are fun aren't they.
> 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]
These images didn't come through, but they probably wouldn't have
added anything anyway.
It seems that either their connector is doing something strange or
you misconfigured things on your side, but there's no evidence here
to say which. I'd counsel enabling log_connections, and maybe
log_statements too, and then looking into the postmaster log to see
what happens when they try to connect.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: HOWTO? Permissions for user to access a single db
2023-02-13 21:45 HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
2023-02-13 21:59 ` Re: HOWTO? Permissions for user to access a single db Tom Lane <tgl@sss.pgh.pa.us>
2023-02-13 22:44 ` Re: HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
2023-02-13 22:54 ` Re: HOWTO? Permissions for user to access a single db Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-02-13 23:14 ` Damian Carey <jamianb@gmail.com>
2023-02-13 23:41 ` Re: HOWTO? Permissions for user to access a single db Rob Sargent <robjsargent@gmail.com>
2023-02-13 23:46 ` Re: HOWTO? Permissions for user to access a single db Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 10+ messages in thread
From: Damian Carey @ 2023-02-13 23:14 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-general
Thx Tom
Fine advice that I will follow up.
One tiny thing without wasting (too much) more of your time.
In the working "promiscuous" version they get access the VPS as the same
linux user that my product is running on, and superuser PG access.
In the failed version their SSH login is as a different and very limited
linux user, as well as their own postgres user name.
Still on a "permissions" theme ... is their any glaring issues that are
required to provide a random linux user with permissions to access a DB?
(FYI every few years you graciously help me like this and I'm well aware of
our skill difference and the vague questions I regurgitate. Kudos.)
Thx
-Damian
On Tue, 14 Feb 2023 at 09:54, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Damian Carey <jamianb@gmail.com> writes:
> > 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.
>
> Black boxes are fun aren't they.
>
> > 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]
>
> These images didn't come through, but they probably wouldn't have
> added anything anyway.
>
> It seems that either their connector is doing something strange or
> you misconfigured things on your side, but there's no evidence here
> to say which. I'd counsel enabling log_connections, and maybe
> log_statements too, and then looking into the postmaster log to see
> what happens when they try to connect.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: HOWTO? Permissions for user to access a single db
2023-02-13 21:45 HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
2023-02-13 21:59 ` Re: HOWTO? Permissions for user to access a single db Tom Lane <tgl@sss.pgh.pa.us>
2023-02-13 22:44 ` Re: HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
2023-02-13 22:54 ` Re: HOWTO? Permissions for user to access a single db Tom Lane <tgl@sss.pgh.pa.us>
2023-02-13 23:14 ` Re: HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
@ 2023-02-13 23:41 ` Rob Sargent <robjsargent@gmail.com>
1 sibling, 0 replies; 10+ messages in thread
From: Rob Sargent @ 2023-02-13 23:41 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2/13/23 16:14, Damian Carey wrote:
> Thx Tom
>
> Fine advice that I will follow up.
>
> One tiny thing without wasting (too much) more of your time.
>
> In the working "promiscuous" version they get access the VPS as the
> same linux user that my product is running on, and superuser PG access.
>
> In the failed version their SSH login is as a different and very
> limited linux user, as well as their own postgres user name.
>
> Still on a "permissions" theme ... is their any glaring issues that
> are required to provide a random linux user with permissions to access
> a DB?
>
> (FYI every few years you graciously help me like this and I'm well
> aware of our skill difference and the vague questions I regurgitate.
> Kudos.)
>
I should wait for Tom to respond...
What was the create user command?
Or are you talking about the following?
GRANT { { CREATE | CONNECT | TEMPORARY | TEMP } [, ...] | ALL [ PRIVILEGES ] }
ON DATABASE/|database_name|/ [, ...]
TO/|role_specification|/ [, ...] [ WITH GRANT OPTION ]
[ GRANTED BY/|role_specification|/ ]
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: HOWTO? Permissions for user to access a single db
2023-02-13 21:45 HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
2023-02-13 21:59 ` Re: HOWTO? Permissions for user to access a single db Tom Lane <tgl@sss.pgh.pa.us>
2023-02-13 22:44 ` Re: HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
2023-02-13 22:54 ` Re: HOWTO? Permissions for user to access a single db Tom Lane <tgl@sss.pgh.pa.us>
2023-02-13 23:14 ` Re: HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
@ 2023-02-13 23:46 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 10+ messages in thread
From: Tom Lane @ 2023-02-13 23:46 UTC (permalink / raw)
To: Damian Carey <jamianb@gmail.com>; +Cc: pgsql-general
Damian Carey <jamianb@gmail.com> writes:
> Still on a "permissions" theme ... is their any glaring issues that are
> required to provide a random linux user with permissions to access a DB?
Well ... if they can "see" the postgres DB then there should be no such
issues, as they evidently managed to establish a database connection.
However, if we assume that that unnamed connector module is lying through
its teeth and presenting this dialog when it can't connect at all, then
there's a lot more possibilities to consider.
Just stopping to think a minute --- it's unlikely that a lashup such
as you describe would be trying to use a Unix socket file, as local
connections with psql probably do. Instead, I imagine that the connector
is trying to connect over a TCP connection being tunneled through the
SSH connection. Obvious things to check then include:
* Is SSH actually being told to provide this tunnel?
* Is the kernel firewall on the Linux machine allowing tunneled packets
to reach the database?
* Is Postgres listening on whichever address/port the tunneled packets
are addressed to? (This is trickier than it looks, as you have at least
localhost vs. external IP address to consider, not to mention IPv4 vs
IPv6)
* Is pg_hba.conf set up to allow the connection?
Only if the problem is at that last step will log_connections help much;
otherwise, no data is reaching Postgres at all.
regards, tom lane
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: HOWTO? Permissions for user to access a single db
2023-02-13 21:45 HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
@ 2023-02-13 23:58 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-02-14 04:35 ` Re: HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
1 sibling, 1 reply; 10+ messages in thread
From: Adrian Klaver @ 2023-02-13 23:58 UTC (permalink / raw)
To: Damian Carey <jamianb@gmail.com>; pgsql-general
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@aklaver.com
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: HOWTO? Permissions for user to access a single db
2023-02-13 21:45 HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
2023-02-13 23:58 ` Re: HOWTO? Permissions for user to access a single db Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-02-14 04:35 ` Damian Carey <jamianb@gmail.com>
2023-02-14 04:40 ` Re: HOWTO? Permissions for user to access a single db Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 10+ messages in thread
From: Damian Carey @ 2023-02-14 04:35 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general
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@aklaver.com>
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@aklaver.com
>
>
^ permalink raw reply [nested|flat] 10+ messages in thread
* Re: HOWTO? Permissions for user to access a single db
2023-02-13 21:45 HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
2023-02-13 23:58 ` Re: HOWTO? Permissions for user to access a single db Adrian Klaver <adrian.klaver@aklaver.com>
2023-02-14 04:35 ` Re: HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
@ 2023-02-14 04:40 ` Rob Sargent <robjsargent@gmail.com>
0 siblings, 0 replies; 10+ messages in thread
From: Rob Sargent @ 2023-02-14 04:40 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
On 2/13/23 21:35, Damian Carey wrote:
> 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
OK, gone there too! Hope you find someone whose company also helps out
here. There are consultants nearby
<https://www.postgresql.org/support/professional_support/northamerica/;
^ permalink raw reply [nested|flat] 10+ messages in thread
end of thread, other threads:[~2023-02-14 04:40 UTC | newest]
Thread overview: 10+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-02-13 21:45 HOWTO? Permissions for user to access a single db Damian Carey <jamianb@gmail.com>
2023-02-13 21:59 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-13 22:44 ` Damian Carey <jamianb@gmail.com>
2023-02-13 22:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-13 23:14 ` Damian Carey <jamianb@gmail.com>
2023-02-13 23:41 ` Rob Sargent <robjsargent@gmail.com>
2023-02-13 23:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-02-13 23:58 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-02-14 04:35 ` Damian Carey <jamianb@gmail.com>
2023-02-14 04:40 ` Rob Sargent <robjsargent@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox