Horton
Menu

Thread

  1. PostgreSQL

    Joseph Kennedy <joseph.kennedy.486@gmail.com> — 2023-02-07T11:02:21Z

    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

  2. Re: PostgreSQL

    shammat@gmx.net — 2023-02-07T11:10:07Z

    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

  3. Re: PostgreSQL

    Giovanni Biscontini <biscontini.g@es2000.it> — 2023-02-07T11:15:57Z

    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/>







____________________________________

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*

  4. Re: PostgreSQL

    Joseph Kennedy <joseph.kennedy.486@gmail.com> — 2023-02-07T11:36:57Z

    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

        

        

        

        

      
    

    
      

    --

    
      

         
      
      

         
      
      

        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

  5. Re: PostgreSQL

    Giovanni Biscontini <biscontini.g@es2000.it> — 2023-02-07T13:19:49Z

    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:

> 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
>>
>>
>>
>>
>>
>
>

  6. Re: PostgreSQL

    Laurenz Albe <laurenz.albe@cybertec.at> — 2023-02-07T14:34:23Z

    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

  7. Re: PostgreSQL

    Joseph Kennedy <joseph.kennedy.486@gmail.com> — 2023-02-07T14:46:39Z

    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

  8. Re: PostgreSQL

    Peter J. Holzer <hjp-pgsql@hjp.at> — 2023-02-07T21:42:30Z

    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!"

  9. Re: PostgreSQL

    Brad White <b55white@gmail.com> — 2023-02-07T23:42:11Z

    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.

  10. Re: PostgreSQL

    Brad White <b55white@gmail.com> — 2023-02-08T00:11:49Z

    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
> 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.
>

  11. Re: PostgreSQL

    Stephen Frost <sfrost@snowman.net> — 2023-02-08T00:35:58Z

    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

  12. Re: PostgreSQL

    Joseph Kennedy <joseph.kennedy.486@gmail.com> — 2023-02-09T08:54:00Z

    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

  13. Re: PostgreSQL

    Laurenz Albe <laurenz.albe@cybertec.at> — 2023-02-09T15:42:05Z

    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

  14. Re: PostgreSQL

    Joseph Kennedy <joseph.kennedy.486@gmail.com> — 2023-04-02T21:21:26Z

    > 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 ?

  15. Re: PostgreSQL

    Laurenz Albe <laurenz.albe@cybertec.at> — 2023-04-03T09:45:00Z

    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