Thread: unprivileged user

The other day I returned idly to thinking about some work I did a few 
years ago on creating a totally unprivileged user, i.e. one with not 
even public permissions. The work I did then involved hacking the 
pg_catalog, information_schema and public schemas and their contents. 
Unfortunately, it doesn't survive  a dump and restore, so it's really 
quite fragile. But it occurred to me that this could be more easily and 
robustly accomplished if we create a role attribute of NOPUBLIC or some 
such.

Thoughts?

cheers

andrew

Andrew Dunstan <andrew@dunslane.net> writes:
> The other day I returned idly to thinking about some work I did a few 
> years ago on creating a totally unprivileged user, i.e. one with not 
> even public permissions.

And the point would be what exactly?
        regards, tom lane

Tom Lane wrote:
> Andrew Dunstan <andrew@dunslane.net> writes:
>   
>> The other day I returned idly to thinking about some work I did a few 
>> years ago on creating a totally unprivileged user, i.e. one with not 
>> even public permissions.
>>     
>
> And the point would be what exactly?
>
>             
>   

Well, when I was looking at it originally it was in the context of a 
layered security setup, where we wanted to minimise the danger from a 
client machine (say a web server) being subverted. The reasoning was 
that if the subverted user had no access to the database layout, but had 
only access to a very tightly defined set of stored functions, it would 
be harder to devise attacks against the database. It might be argued 
that this is security by obscurity, but obscurity does have some uses, 
albeit never as a complete security mechanism.

Some time later it came up again, this time when someone wanted to use a 
readonly database (hence no pg_dump required) with an application and 
wanted to keep the database layout and the source code of stored 
functions hidden as they regarded it as proprietary information.

cheers

andrew

On tor, 2009-12-10 at 17:20 -0500, Andrew Dunstan wrote:
> Some time later it came up again, this time when someone wanted to use a 
> readonly database (hence no pg_dump required) with an application and 
> wanted to keep the database layout and the source code of stored 
> functions hidden as they regarded it as proprietary information.

Well, the information schema already implements a policy of this sort,
because it only shows information about things you have some kind of
access to. (I assume you are allowed to know about the things you have
access to.)

The problem in this sort of scheme is always that the system catalogs
are world readable, and changing that would break about every tool and
driver in existence.  It's not clear how to fix that, at least not
without row-level security.  Or how did your old patch address this?

Peter Eisentraut wrote:
> On tor, 2009-12-10 at 17:20 -0500, Andrew Dunstan wrote:
>   
>> Some time later it came up again, this time when someone wanted to use a 
>> readonly database (hence no pg_dump required) with an application and 
>> wanted to keep the database layout and the source code of stored 
>> functions hidden as they regarded it as proprietary information.
>>     
>
> Well, the information schema already implements a policy of this sort,
> because it only shows information about things you have some kind of
> access to. (I assume you are allowed to know about the things you have
> access to.)
>
> The problem in this sort of scheme is always that the system catalogs
> are world readable, and changing that would break about every tool and
> driver in existence.  It's not clear how to fix that, at least not
> without row-level security.  Or how did your old patch address this?
>
>
>   

Well, it will certainly break plenty of things. But it didn't prevent 
the client from doing the things we wanted to allow it to do, i.e. make 
a few function calls.

Basically what I did was to revoke public privileges from just about 
everything I could lay my hands on and regrant the same privileges to a 
group called pseudopublic, which contained every user but the one I 
wanted to restrict. The client I tested with was psql, and I recall 
being surprised that I was still able to do what I wanted - I had 
thought I would break things completely. I haven't tried to repeat the 
experiment recently, though.

cheers

andrew