Thread: PostgreSQL

PostgreSQL

From
Joseph Kennedy
Date:
I would like restrict access to sensitive or restricted information for some users (eg. hide data of one or more
clientsfor 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





Re: PostgreSQL

From
Thomas Kellerer
Date:
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



Re: PostgreSQL

From
Giovanni Biscontini
Date:
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

 

 

 

____________________________________

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

Re: PostgreSQL

From
Giovanni Biscontini
Date:
Hi, 
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:
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 ?

Wiadomość napisana przez Giovanni Biscontini <biscontini.g@es2000.it> w dniu 07.02.2023, o godz. 12:16:


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








Re: PostgreSQL

From
Laurenz Albe
Date:
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



Re: PostgreSQL

From
Joseph Kennedy
Date:
Thank you Laurenz, I just wanted to make sure.

Do you know any alternative solution to RLS ?

> Wiadomość napisana przez Laurenz Albe <laurenz.albe@cybertec.at> w dniu 07.02.2023, o godz. 15:34:
>
> 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



Re: PostgreSQL

From
"Peter J. Holzer"
Date:
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!"

Attachment

Re: PostgreSQL

From
Brad White
Date:
I'm  setting my options for my DNS-less connection using 
and 

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.

Re: PostgreSQL

From
Brad White
Date:
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:
I'm  setting my options for my DNS-less connection using 
and 

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.

Re: PostgreSQL

From
Stephen Frost
Date:
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

Attachment

Re: PostgreSQL

From
Joseph Kennedy
Date:
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. 

Wiadomość napisana przez Stephen Frost <sfrost@snowman.net> w dniu 08.02.2023, o godz. 01:36:

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

Re: PostgreSQL

From
Laurenz Albe
Date:
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



Re: PostgreSQL

From
Joseph Kennedy
Date:
> 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
tabeletc. 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 ?








Re: PostgreSQL

From
Laurenz Albe
Date:
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