pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedsecurity issues
5+ messages / 5 participants
[nested] [flat]
* security issues
@ 2021-12-10 14:33 Frank Eckes <frank.eckes@online.de>
0 siblings, 3 replies; 5+ messages in thread
From: Frank Eckes @ 2021-12-10 14:33 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hi everybody,
To access data in a PostgreSQL database I write queries which contains
the business rules
how t access data. This is working fine and also the permission are
working fine.
But I found out that a user can see the complete business rules in a
query or a procedure which is
a big security issue.
Is there e possibility that I can hide the definition and th user can
only see the data or can execute
the procedure/function.
And even worse, if i define a foreign server (e.g ORACLE) everybody can
see the credentials in a user mapping
which should not be allowed. This might be a show stopper of using
PostgreSQL in security environments.
Regards
Frank Eckes
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: security issues
@ 2021-12-10 16:20 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Frank Eckes <frank.eckes@online.de>
2 siblings, 1 reply; 5+ messages in thread
From: Laurenz Albe @ 2021-12-10 16:20 UTC (permalink / raw)
To: Frank Eckes <frank.eckes@online.de>; pgsql-admin@lists.postgresql.org
On Fri, 2021-12-10 at 15:33 +0100, Frank Eckes wrote:
> To access data in a PostgreSQL database I write queries which contains
> the business rules
>
> how t access data. This is working fine and also the permission are
> working fine.
>
> But I found out that a user can see the complete business rules in a
> query or a procedure which is
>
> a big security issue.
>
> Is there e possibility that I can hide the definition and th user can
> only see the data or can execute
>
> the procedure/function.
No, there is no good way to do that.
It is very unusual to put secret information into a view definition...
> And even worse, if i define a foreign server (e.g ORACLE) everybody can
> see the credentials in a user mapping
>
> which should not be allowed. This might be a show stopper of using
> PostgreSQL in security environments.
Then you use Oracle external authentication, for example with a
secure key store on the PostgreSQL server. Then you don't need a
password.
Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: security issues
@ 2021-12-10 16:21 David G. Johnston <david.g.johnston@gmail.com>
parent: Frank Eckes <frank.eckes@online.de>
2 siblings, 0 replies; 5+ messages in thread
From: David G. Johnston @ 2021-12-10 16:21 UTC (permalink / raw)
To: Frank Eckes <frank.eckes@online.de>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
On Friday, December 10, 2021, Frank Eckes <frank.eckes@online.de> wrote:
>
> Is there e possibility that I can hide the definition and th user can only
> see the data or can execute
>
> the procedure/function.
No. This comes up every so often so the archives are a good place to find
a more lengthy discussion.
> And even worse, if i define a foreign server (e.g ORACLE) everybody can
> see the credentials in a user mapping
>
> which should not be allowed. This might be a show stopper of using
> PostgreSQL in security environments.
>
>
This one is a bit more nuanced and I’m not as familiar with the specifics.
For a PostgreSQL server I would setting up pg_hba.conf to allow a
non-password connection from the specific machine and user so that password
credentials are simply not required.
David J.
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: security issues
@ 2021-12-10 16:41 Tom Lane <tgl@sss.pgh.pa.us>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2021-12-10 16:41 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Frank Eckes <frank.eckes@online.de>; pgsql-admin@lists.postgresql.org
Laurenz Albe <laurenz.albe@cybertec.at> writes:
> On Fri, 2021-12-10 at 15:33 +0100, Frank Eckes wrote:
>> And even worse, if i define a foreign server (e.g ORACLE) everybody can
>> see the credentials in a user mapping
> Then you use Oracle external authentication, for example with a
> secure key store on the PostgreSQL server. Then you don't need a
> password.
That's one way, but AFAIK the above claim is nonsense. You can
only see the connection options for user mappings that belong
to you (unless you're a superuser).
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: security issues
@ 2021-12-10 16:52 Haroldo Stenger <haroldo.stenger@gmail.com>
parent: Frank Eckes <frank.eckes@online.de>
2 siblings, 0 replies; 5+ messages in thread
From: Haroldo Stenger @ 2021-12-10 16:52 UTC (permalink / raw)
To: ; +Cc: pgsql-admin@lists.postgresql.org
Set up two postgresql databases.
In one of them you have the tables and the secret views, and restrict
access to the secret views to a 'postgres_restricted' user.
In the other one database define:
create extension postgres_fdw;
create server compras_y_costos foreign data wrapper postgres_fdw options
(host 'localhost', port '5432', dbname 'database_which_holds_the
tables_and_the_secret_view');
create user mapping for postgres server ..... options (user
'postgres_restricted', password 'postgres_restricted_password');
create foreign table remote_reference__to_secret_view ()...
Then grant public access to the views via the wrapper.
this should work fine.
El vie, 10 dic 2021 a la(s) 11:33, Frank Eckes (frank.eckes@online.de)
escribió:
> Hi everybody,
>
> To access data in a PostgreSQL database I write queries which contains
> the business rules
>
> how t access data. This is working fine and also the permission are
> working fine.
>
> But I found out that a user can see the complete business rules in a
> query or a procedure which is
>
> a big security issue.
>
> Is there e possibility that I can hide the definition and th user can
> only see the data or can execute
>
> the procedure/function.
>
> And even worse, if i define a foreign server (e.g ORACLE) everybody can
> see the credentials in a user mapping
>
> which should not be allowed. This might be a show stopper of using
> PostgreSQL in security environments.
>
>
> Regards
>
> Frank Eckes
>
>
>
>
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2021-12-10 16:52 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-12-10 14:33 security issues Frank Eckes <frank.eckes@online.de>
2021-12-10 16:20 ` Laurenz Albe <laurenz.albe@cybertec.at>
2021-12-10 16:41 ` Tom Lane <tgl@sss.pgh.pa.us>
2021-12-10 16:21 ` David G. Johnston <david.g.johnston@gmail.com>
2021-12-10 16:52 ` Haroldo Stenger <haroldo.stenger@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