Postgres Pro
Downloads
Home   >   mailing lists

Thread: column level, uid based authorization to update columns

column level, uid based authorization to update columns

From
"Gauthier, Dave"
Date:

In linux, given the linux based uid of the user, how might someone implement column level update restrictions on a uid basis?  For example...

 

create table foo (strcol varchar(256), intcol integer);

 

Now, I want linux processes runing under uid “joesmith” to be able to update strcol but not intcol.  Some other user could update intcol but not strcol.  Others could update both, others neither.  I’m also willing to give you a table that maps all uids to the columns they can update, something you could ref in a constraint or update trigger or something.   So that might be something like...

 

            create table foo_auth (uid varchar(256), cols text[]);

            insert into foo_auth (uid,cols) values (‘joesmith’,’{‘strcol’}’);

insert into foo_auth (uid,cols) values (‘jillbrown’,’{‘intcol’}’);

            insert into foo_auth (uid,cols) values (‘thedba’,’{‘strcol’,’intcol’}’);

 

Thanks in Advance !

 

Re: column level, uid based authorization to update columns

From
Ries van Twisk
Date:

On Sep 3, 2009, at 12:17 PM, Gauthier, Dave wrote:

In linux, given the linux based uid of the user, how might someone implement column level update restrictions on a uid basis?  For example...
 
create table foo (strcol varchar(256), intcol integer);
 
Now, I want linux processes runing under uid “joesmith” to be able to update strcol but not intcol.  Some other user could update intcol but not strcol.  Others could update both, others neither.  I’m also willing to give you a table that maps all uids to the columns they can update, something you could ref in a constraint or update trigger or something.   So that might be something like...
 
            create table foo_auth (uid varchar(256), cols text[]);
            insert into foo_auth (uid,cols) values (‘joesmith’,’{‘strcol’}’);
insert into foo_auth (uid,cols) values (‘jillbrown’,’{‘intcol’}’);
            insert into foo_auth (uid,cols) values (‘thedba’,’{‘strcol’,’intcol’}’);
 
Thanks in Advance !
 







Re: column level, uid based authorization to update columns

From
David Fetter
Date:
On Thu, Sep 03, 2009 at 10:17:15AM -0700, Gauthier, Dave wrote:
> In linux, given the linux based uid of the user, how might someone
> implement column level update restrictions on a uid basis?  For
> example...

You can use ident authentication in pg_hba.conf and per-column
GRANT/REVOKE in 8.4.

http://www.postgresql.org/docs/current/static/sql-grant.html

Cheers,
David.
--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778  AIM: dfetter666  Yahoo!: dfetter
Skype: davidfetter      XMPP: david.fetter@gmail.com

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

Re: column level, uid based authorization to update columns

From
Stephen Frost
Date:
* Gauthier, Dave (dave.gauthier@intel.com) wrote:
> In linux, given the linux based uid of the user, how might someone implement column level update restrictions on a
uidbasis?  For example... 

The first issue is getting the linux uid to equate to a PG role.  That
can be done using 'ident' authentication, though I would recommend only
allowing that on socket-based connections (not with tcp/ip connections).
You could also give 'joeuser' a username and password to connect to PG
with.

In PG, with 8.4, you can use column-level privileges to limit what a
given PG role can do.  See the GRANT syntax for 8.4.

    Thanks,

        Stephen
Attachment