In Postgres 10, is it possible to automatically insert the name of the login role when creating a new row?

i.e. I'm already using a timestamp to track the date that a row was created. It would also be useful to see which user created the row.



Yes. Just make the column's default session_user. (It's a pseudo-variable, not a function call).

If you want to prevent a user from overriding it, you should also make sure to GRANT write permissions to the table on a per-column basis, not to the table as a whole, and make sure the regular operating user is not the table owner.

Or alternately, have a BEFORE INSERT OR UPDATE trigger that sets NEW.the_column := session_user.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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