Skip site navigation (1) Skip section navigation (2)

Re: pre-proposal: permissions made easier

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Jeff Davis <pgsql(at)j-davis(dot)com>
Cc: David Fetter <david(at)fetter(dot)org>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pre-proposal: permissions made easier
Date: 2009-06-29 18:41:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers

> I don't see why either of these things should be properties of the
> schema. It seems to make much more sense for these defaults to be a
> property of the user who creates the objects.

The main reason is existing practice.  Currently, most applications I 
see in the field which bother with having several ROLES have all 
database objects belonging to one ROLE ("db_owner").  So for most people 
setting permissions for all objects belonging to a specific user would 
amount to setting permissions for all objects of that type in a given 

There's also the fact that SCHEMAs currently have their own visibility 
rules and permissions, which seems to me to dovetail nicely with the ACLs.

This is, of course, assuming that we are talking about setting 
permissions in saved objects, that is, all the object belonging to a 
particular user.

The approach I could see as valuable in vastly simplyfying things would 
be to set the permission on the user regardless of object properties; 
that is, the user is defined as WITH SELECT, INSERT, UPDATE ON ALL 
TABLES.  These user permissions would supercede any object permissions 
for that role.

This would make DBA's lives vastly simpler and make them more likely to 
use permissions.  But would it actually benefit security?

The problem I see with this approach is that in 95% of the applications 
I run across there are a few tables which really need to be "locked 
down" and restricted from most user access (maybe accessed only by an 
SRF).  In large development shops where more than one person has their 
hands on the DB, I can easily see one developer accidentally bypassing 
object-level security set up by another DBA through this mechanism.

The second, and bigger problem I can see is that this opens a whole new 
set of security holes by allowing an end-run around the existing access 
control structure with attackers can try to exploit.

Josh Berkus
PostgreSQL Experts Inc.

In response to


pgsql-hackers by date

Next:From: Bernd HelmleDate: 2009-06-29 18:52:52
Subject: Re: [PATCH] [v8.5] Security checks on largeobjects
Previous:From: Tom LaneDate: 2009-06-29 18:40:31
Subject: Re: Query progress indication - an implementation

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group