Обсуждение: security issues
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
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
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.
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
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 ()...
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