PostgreSQL

Joseph Kennedy schrieb am 07.02.2023 um 12:02:

I would like restrict access to sensitive or restricted information
for some users (eg. hide data of one or more clients for some
database users).

PostgreSQL allows to create security policy as Row-Level Security,
policies based on the querying user.

Are there any other alternatives to RLS, are there any better
solutions??

You can create a view layer and only grant access to the data through views
(and obviously revoke access to the tables directly)

Or allow access only through (set returning) functions

Hi Joseph,
one way to filter without RLS is using functions to make queries:
this way you can cross check input parameters with login user (= user
issuing query). Using functions slow an execution but incapsulate query
letting you be able even to parse input parameters and filter results.
Sorry but I have no time ref for RLS, and remember that before you can use
RLS you must enable it on the table you want to use in.
best regards, Giovanni

Il giorno mar 7 feb 2023 alle ore 12:02 Joseph Kennedy <
joseph.kennedy.486@gmail.com> ha scritto:

I would like restrict access to sensitive or restricted information for
some users (eg. hide data of one or more clients for some database users).

PostgreSQL allows to create security policy as Row-Level Security,
policies based on the querying user.

Are there any other alternatives to RLS, are there any better solutions??

What are the advantages and disadvantages of RLS?

How much RLS will cause a decrease in database performance?

JK

--

*Cordiali Saluti*

*Dott. Giovanni Biscontini*

* [Divisone Software]*

*Str. Ponte Alto Sud, 74 41123 Modena (MO)*

Phone: 059_452094
Fax: 059_8672171
E-mail: biscontini.g@es2000.it

Skype: g.biscontini.eurosystem2000

WEB: https://www.es2000.it <http://www.es2000.it/&gt;

____________________________________

Privacy e riservatezza: il presente messaggio, così come i relativi
allegati, contengono dati ed informazioni da considerarsi strettamente
riservate ed è indirizzato esclusivamente al destinatario sopra indicato,
il quale è l'unico autorizzato a trattarlo in osservanza delle norme del
Regolamento UE 2016/679 (RGPD) . Preghiamo chiunque ricevesse questo
messaggio per errore di evitare di copiarlo, divulgarlo, distribuirlo a
terzi e di dare notizia al mittente dell’errato invio, distruggendone poi
l'eventuale copia cartacea e la copia in formato elettronico.

Il titolare dei dati potrà esercitare tutti i diritti di cui all'art.7 del
suddetto decreto tra cui quelli di accesso, rettifica, aggiornamento,
opposizione al trattamento e cancellazione

*Please, print this e-mail only if necessary*

Hi,
RLS rely on Create policy command:
https://www.postgresql.org/docs/15/sql-createpolicy.html
it need a table_name, only

Il giorno mar 7 feb 2023 alle ore 12:37 Joseph Kennedy <
joseph.kennedy.486@gmail.com> ha scritto:

On Tue, 2023-02-07 at 12:36 +0100, Joseph Kennedy wrote:

RLS policy it only affects to command as SELECT, INSERT, UPDATE, DELETE or
can affects to operations on whole database, table like vacuum or reindex etc ?

No, that wouldn't make any sense. Row Level Security determines who
is allowed to see or create which data. Jobs like removing dead tuples
or rebuilding an index have nothing to do with that.

Besides, anything that requires the table owner or a superuser cannot
be reasonably controlled with row level security, because both the
table owner and a superuser are exempt from RLS anyway.

Yours,
Laurenz Albe

Thank you Laurenz, I just wanted to make sure.

Do you know any alternative solution to RLS ?

On 2023-02-07 12:36:57 +0100, Joseph Kennedy wrote:

RLS policy it only affects to command as SELECT, INSERT, UPDATE, DELETE or can
affects to operations on whole database, table like vacuum or reindex etc ?

"Row level security" works, as the name implies at the row level.
Tables, schemas and databases and covered by the "usual" SQL privilege
system.

hp

--
_ | Peter J. Holzer | Story must make more sense than reality.
|_|_) | |
| | | hjp@hjp.at | -- Charles Stross, "Creative writing
__/ | http://www.hjp.at/ | challenge!"

I'm setting my options for my DNS-less connection using
https://odbc.postgresql.org/docs/config.html
and
https://odbc.postgresql.org/docs/config-opt.html

I found it interesting that the sample at the bottom of the first page had
options that don't exist on the second page.
Namely, B4, B5, D4. I hope those are not important.

The second page has options that are nowhere mentioned on the first page,
and vice-versa.

"Recognize unique indexes" is there as "UniqueIndex", but no indication of
the proper keyword to include it in the connection string.

CX - Abbreviate doesn't seem to have a corresponding entry in the
descriptions on page 1, and from some of the samples I found, seems like a
bit field, but no indication of possible options to use.

And lastly, one of the ODBC dialog boxes I saw had a checkbox for Keyset
query optimization, but no discussion on either page.
Same with Distributed Transaction settings.

Seems like, in an ideal world, there would be one page with the
description and that description would include the keyword and possible
options for each setting.

Thanks,
Brad.

Sorry about the reply failure. I was trying to start a new thread and
failed.

On Tue, Feb 7, 2023 at 5:42 PM Brad White <b55white@gmail.com> wrote:

Greetings,

* Joseph Kennedy (joseph.kennedy.486@gmail.com) wrote:

Thank you Laurenz, I just wanted to make sure.

Do you know any alternative solution to RLS ?

... to do what? If you want to limit the set of rows that a given user
can see, RLS is how to do that. If you want to limit the schemas or
tables that a user can see, that isn't possible in PG today (though
there was some interest a while back in making that work via RLS on the
catalogs which you might be able to find).

Thanks,

Stephen

As I wtote, I would like restrict access to sensitive or restricted information for some users (eg. hide data of one or more clients for some database users).

My question is: do you know some other solution to do that ?

Some 3rd party solution eg. similar to pgpool or something else ?

Maybe RLS is the beat solution for me.

Thank you for your suggestions.

On Thu, 2023-02-09 at 09:54 +0100, Joseph Kennedy wrote:

As I wtote, I would like restrict access to sensitive or restricted information
for some users (eg. hide data of one or more clients for some database users).

My question is: do you know some other solution to do that ? 

It is easy to hide data from users: use permissions or row level security.

It is impossible to hide metadata from users, but we don't consider that a problem.

If you want that, use a database or a database cluster per user.

Yours,
Laurenz Albe

Wiadomość napisana przez Laurenz Albe <laurenz.albe@cybertec.at> w dniu 09.02.2023, o godz. 16:42:

On Thu, 2023-02-09 at 09:54 +0100, Joseph Kennedy wrote:

As I wtote, I would like restrict access to sensitive or restricted information
for some users (eg. hide data of one or more clients for some database users).

My question is: do you know some other solution to do that ?

It is easy to hide data from users: use permissions or row level security.

It is impossible to hide metadata from users, but we don't consider that a problem.

If you want that, use a database or a database cluster per user.

Yours,
Laurenz Albe

I have one more question on this topic regarding Row Level Security.

I would like to hide some rows for users who should have full rights to database eg. create Index, drop table, create tabel etc. but e.g. create index can be done by owner or superusers but they are bypassed by Row Level Security.

Is possible to use RLS for users who should have full rights to database ?

On Sun, 2023-04-02 at 23:21 +0200, Joseph Kennedy wrote:

I have one more question on this topic regarding Row Level Security.

I would like to hide some rows for users who should have full rights to database
eg. create Index, drop table, create tabel etc. but e.g.  create index can be
done by owner or superusers but they are bypassed by Row Level Security.

Is possible to use RLS for users who should have full rights to database ?

If "full rights" implies dropping tables, the user must either be the owner
of these tables or a superuser. You cannot use Row Level Security with a
superuser. You can use ALTER TABLE ... FORCE ROW LEVEL SECURITY, so that
Row Level Security applies to the table owner as well, but you cannot keep
the owner from disabling that setting.

Yours,
Laurenz Albe