Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Since PostgreSQL 9.0, each large object has its own access rights that are granted with:

GRANT { { SELECT | UPDATE } [, ...] | ALL [ PRIVILEGES ] }
  ON LARGE OBJECT loid [, ...]
  TO { [ GROUP ] role_name | PUBLIC } [, ...] [ WITH GRANT OPTION ]

The SELECT permission allows for opening the object in read-only mode, and for UPDATE, the doc says:

 For large objects, this privilege allows writing or truncating the object.

But if a large object belongs to user1, I can't find how to grant the permission to user2 to delete (lo_unlink) this object. If user1 says GRANT UPDATE on LARGE OBJECT xyz TO user2; and user2 runs SELECT lo_unlink(xyz);, it's denied with:

 ERROR:  must be owner of large object xyz

Is it really the case that only the owner a large object is ever able to delete it, or I'm missing something?

My use case is a db that stores mailboxes that are shared between different db users. All the tables with mail contents and large objects with mail attachments are owned by a "master" user, and other users may or may not delete messages depending on their database role.

Deleting a message means deleting everything related to it, including attachments stored in large objects. So it would make sense that a non-owner should be able to delete large objects, just like he can delete from other tables if he has been granted the DELETE privilege on these tables.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

That's consistent with other objects, which cannot be DROPped except by the owner. It seems kind of painful for large objects, though; maybe bug -hackers?

I'd work around this by using oid references instead of lo, and then using vaccumlo to remove them once the references to them are removed.

share|improve this answer
    
So lo_unlink() would have been perceived by the implementers as a DDL DROP-like operation, while from my point of view, it's a DML DELETE-like operation. That might indeed explain why it's too strict permission-wise. –  Daniel Vérité Sep 19 '12 at 10:18
1  
@DanielVérité That's my best guess, but it's well worth asking on pgsql-general or -hackers, as this seems like an oddity that should be addressed. –  Craig Ringer Sep 19 '12 at 10:58

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.