Postgres Pro
Downloads
Home   >   mailing lists

Thread: how to remove set_config from all user

how to remove set_config from all user

From
Garry Chen
Date:

Hi All,

                Is there a way to revoke select set_config(‘eeee’,bb’,false) from users?  I will not to allow any user be able to the “select set_config(….)” statement.

 

Thank you very much,

Garry

Re: how to remove set_config from all user

From
"David G. Johnston"
Date:
On Thu, Jan 18, 2018 at 12:08 PM, Garry Chen <gc92@cornell.edu> wrote:

Hi All,

                Is there a way to revoke select set_config(‘eeee’,bb’,false) from users?  I will not to allow any user be able to the “select set_config(….)” statement.



​As a practical matter, no.  They can just do: "SET ... TO ..." to get around the function privilege denial even if worked out a usable hack.

David J.

RE: how to remove set_config from all user

From
Garry Chen
Date:

In that case what is the best practice for it?  Any suggestion.

 

Garry

 

From: David G. Johnston [mailto:david.g.johnston@gmail.com]
Sent: Thursday, January 18, 2018 2:16 PM
To: Garry Chen <gc92@cornell.edu>
Cc: pgsql-novice@lists.postgresql.org
Subject: Re: how to remove set_config from all user

 

On Thu, Jan 18, 2018 at 12:08 PM, Garry Chen <gc92@cornell.edu> wrote:

Hi All,

                Is there a way to revoke select set_config(‘eeee’,bb’,false) from users?  I will not to allow any user be able to the “select set_config(….)” statement.

 

 

​As a practical matter, no.  They can just do: "SET ... TO ..." to get around the function privilege denial even if worked out a usable hack.

 

David J.

 

Re: how to remove set_config from all user

From
"David G. Johnston"
Date:
On Thu, Jan 18, 2018 at 12:19 PM, Garry Chen <gc92@cornell.edu> wrote:

In that case what is the best practice for it?  Any suggestion.


​Please don't top-post.​

​Maybe you should explain why you want to do such a thing first.  There isn't really any practice, let alone a best one, to do exactly what you say.  Most system variables are changeable by users.  There are some that can be changed that could be abused but the general thinking is that while doing so maliciously is possible there are lots of others ways a user with access to a database session can cause you grief too and that solutions to this attack vector are social, not technical, in nature.

David J.
​

RE: how to remove set_config from all user

From
Garry Chen
Date:

Maybe you should explain why you want to do such a thing first.  There isn't really any practice, let alone a best one, to do exactly what you say.  Most system variables are changeable by users.  There are some that can be changed that could be abused but the general thinking is that while doing so maliciously is possible there are lots of others ways a user with access to a database session can cause you grief too and that solutions to this attack vector are social, not technical, in nature.

 

David J.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

What I try to do is performing a row level security by setting a variable such that RLS function can only return the rows base on the variable.  I have two way to do this one is using SET SESSION AUTHORIZATION the other is set a variable by using set_config.  If I use SET  SESSION AUTHORIZATION it increase administration overhead.  If I use set_config the potential wrong access of RLS is possible.   Do you have any suggestion?

 

Garry

 

​

RE: how to remove set_config from all user

From
Stephen Froehlich
Date:

Would table partitioning perhaps be a better way to do this?

 

--Stephen

 

From: Garry Chen [mailto:gc92@cornell.edu]
Sent: Thursday, January 18, 2018 12:49 PM
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-novice@lists.postgresql.org
Subject: RE: how to remove set_config from all user

 

Maybe you should explain why you want to do such a thing first.  There isn't really any practice, let alone a best one, to do exactly what you say.  Most system variables are changeable by users.  There are some that can be changed that could be abused but the general thinking is that while doing so maliciously is possible there are lots of others ways a user with access to a database session can cause you grief too and that solutions to this attack vector are social, not technical, in nature.

 

David J.

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

What I try to do is performing a row level security by setting a variable such that RLS function can only return the rows base on the variable.  I have two way to do this one is using SET SESSION AUTHORIZATION the other is set a variable by using set_config.  If I use SET  SESSION AUTHORIZATION it increase administration overhead.  If I use set_config the potential wrong access of RLS is possible.   Do you have any suggestion?

 

Garry

 

​

RE: how to remove set_config from all user

From
Garry Chen
Date:

Thank you for your suggestion but I don’t think table partitioning is a practical solution for RLS.

 

Garry

 

 

From: Stephen Froehlich [mailto:s.froehlich@cablelabs.com]
Sent: Thursday, January 18, 2018 2:51 PM
To: Garry Chen <gc92@cornell.edu>; David G. Johnston <david.g.johnston@gmail.com>
Cc: pgsql-novice@lists.postgresql.org
Subject: RE: how to remove set_config from all user

 

Would table partitioning perhaps be a better way to do this?

 

--Stephen

 

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

What I try to do is performing a row level security by setting a variable such that RLS function can only return the rows base on the variable.  I have two way to do this one is using SET SESSION AUTHORIZATION the other is set a variable by using set_config.  If I use SET  SESSION AUTHORIZATION it increase administration overhead.  If I use set_config the potential wrong access of RLS is possible.   Do you have any suggestion?

 

Garry

 

​