Feature request: permissions change history for auditing

--- On Mon, 30/11/09, Thom Brown <thombrown@gmail.com> wrote:

As far as I am aware, there is no way to tell when a
user/role was granted permissions or had permissions
revoked, or who made these changes.  I'm wondering if
it would be useful for security auditing to maintain a
history of permissions changes only accessible to
superusers?

I'd have thought you could keep track of this in the logs by setting log_statement >= ddl ?

I'm pretty sure this is a feature that's not wanted, but the ability to add triggers to these sorts of events would surely make more sense than a specific auditing capability.

2009/11/30 Glyn Astill <glynastill@yahoo.co.uk>

--- On Mon, 30/11/09, Thom Brown <thombrown@gmail.com> wrote:

As far as I am aware, there is no way to tell when a
user/role was granted permissions or had permissions
revoked, or who made these changes. I'm wondering if
it would be useful for security auditing to maintain a
history of permissions changes only accessible to
superusers?

I'd have thought you could keep track of this in the logs by setting
log_statement >= ddl ?

I'm pretty sure this is a feature that's not wanted, but the ability to add
triggers to these sorts of events would surely make more sense than a
specific auditing capability.

I concede your suggestion of the ddl log output. I guess that could then be
filtered to obtain the necessary information.

Thanks

Thom

Thom Brown wrote:

2009/11/30 Glyn Astill <glynastill@yahoo.co.uk
<mailto:glynastill@yahoo.co.uk>>

--- On Mon, 30/11/09, Thom Brown <thombrown@gmail.com
<mailto:thombrown@gmail.com>> wrote:

As far as I am aware, there is no way to tell when a
user/role was granted permissions or had permissions
revoked, or who made these changes. I'm wondering if
it would be useful for security auditing to maintain a
history of permissions changes only accessible to
superusers?

I'd have thought you could keep track of this in the logs by
setting log_statement >= ddl ?

I'm pretty sure this is a feature that's not wanted, but the
ability to add triggers to these sorts of events would surely make
more sense than a specific auditing capability.

I concede your suggestion of the ddl log output. I guess that could
then be filtered to obtain the necessary information.

This could probably be defeated by making the permissions changes in a
stored function. Or even a DO block, I suspect, unless you had
log_statement = all set.

I do agree with Glyn, though, that making provision for auditing one
particular event is not desirable.

cheers

andrew

Thom Brown escreveu:

As far as I am aware, there is no way to tell when a user/role was
granted permissions or had permissions revoked, or who made these
changes. I'm wondering if it would be useful for security auditing to
maintain a history of permissions changes only accessible to superusers?

If the utility command hook patch is approved, it will be possible to track
commands rather than DML ones. In that case, it would be trivial to do some
extension that covers your audit concerns.

[1]: https://commitfest.postgresql.org/action/patch_view?id=196

--
Euler Taveira de Oliveira
http://www.timbira.com/