Build an audit log for your Symfony app

What is an audit log?

An audit log is a record of all the actions that have been performed on a system, such that a full chronological trail of events can be reconstructed. This is useful for a number of reasons, including:

• Security: An audit log can be used to detect and prevent malicious activity.
• Compliance: An audit log can be used to demonstrate compliance with regulations.
• Troubleshooting: An audit log can be used to diagnose problems.
• Investigation: An audit log can be used to investigate incidents and determine where, when and why a data change occurred.

For example, if you run an online store, you might want to know when a customer's personal details were changed, or who changed a product's price and why, and what the price was before the change.

Why build our own application audit log?

You could build an audit log at the database level, through triggers and stored procedures. But this is a bit of a pain, and it's not very flexible. It's also not very scalable, because you need to write a trigger for every table you want to audit.

You also might want to include information about the user who made the change, and the reason for the change, or other data which belongs to the application and simply isn't available to the database layer just off a row change.

In a Symfony application, you could write audit log events manually in your controllers, but this is also cumbersome, not very flexible, and not very scalable. It's also very easy this way to miss important events that you should be auditing.

What we really want is to be able to automatically log all the changes that happen in our application, and to be able to easily add new events to the log. We also want to be able to easily filter the log by user, by entity, by date, and so on.

Fortunately, Symfony and Doctrine provide us with the tools we need to monitor our application for data changes and build an audit log with very little effort.

What we'll be building

In this tutorial, we're going to create a sample Symfony application which listens to events in the Doctrine ORM to create an audit log. Every time a record is inserted, updated or deleted in our Postgres database, we will add a row to to the audit log table which will tell us what entity type was altered, the date and time, what its ID was, what user made the change, their IP address and the route in the app which triggered the change.

For insertion and deletion events, we'll record the full data of the record added or removed and for update events, we'll record a neat diff showing only the fields which changed with the old and new values. In all cases, we'll store this in our audit log table as a native JSON type, allowing us to perform powerful search queries on the log data any time in the future.

As an example, a representation of one of our audit log entries as JSON might look like the following:

{
  "id": 1, 
  "entity_type": "Customer", 
  "entity_id": 15, 
  "action": "update", 
  "data": {
    "name": {
      "from": "John Smith",
      "to": "John Doe"
    },
    "email": {
      "from": "johnsmith@example.com",
      "to": "johndoe@example.com"
    }
  },
  "created_at": "2022-09-20 114528",
  "user_id": 12,
  "ip_address": "1.2.3.4",
  "route": "customer_edit"
}

Our sample app will use the following entities:

• User: A user of the application.
• Customer: A customer record, comprising title, first name, last name and email address.
• AuditLog: The audit log entity, which will record:
  • The user who made the change, if any.
  • The entity type that was changed.
  • The entity ID
  • The action that was performed; insert, update or delete.
  • The date and time of the change.
  • The IP address of the user who made the change.
  • The change data, which will either a JSON representation of the full entity data for inserts and deletes, or a JSON representation of the changed fields for updates.
  • The route name in the application which triggered the change.

Setting up the project

As usual for my tutorials, we'll be using the latest Symfony and PHP versions, and we'll be running our database via Docker.

Ensure you have the prerequisites installed:

• Symfony CLI
• Composer
• Docker
• PHP 8.1 or later.

Of course, if you don't want to use Docker for your database, you can use whatever you like. Just make sure you have Postgres 13 or later installed and update your application .env file accordingly.

Create a new Symfony project:

symfony new audit-log --webapp

Once your project is created, modify the docker-compose.override.yml file to include the host port the database should be exposed on and ensure this matches the DATABASE_URL in your .env file:

# docker-compose.override.yml
database:
  ports:
    - "5432:5432"

# .env
DATABASE_URL="postgresql://app:!ChangeMe!@127.0.0.1:5432/app?serverVersion=14&charset=utf8"

Start the database:

docker-compose up -d

This will launch a Postgres container and create your app database.

Creating the database schema

We'll need a table to store our audit log entries, and a table to store our customer records. We'll also create a minimal User entity to demonstrate how to log changes made by an authenticated user.

Create the User entity

symfony console make:user

You can use the default values for all the questions on this one; we don't really care about the specifics of the user entity for this tutorial, just something with an ID we can log as a foreign key.

Create the Customer entity

symfony console make:entity Customer

# Just create the entity and hit enter when asked if you want to add any mroe fields,
# we'll add the fields manually later.

Open the src/Entity/Customer.php file and add the following fields, along with getters and setters.

<?php

namespace App\Entity;

use App\Repository\CustomerRepository;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: CustomerRepository::class)]
class Customer
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 255, nullable: true)]
    private ?string $firstName = null;

    #[ORM\Column(length: 255, nullable: true)]
    private ?string $lastName = null;

    #[ORM\Column(length: 255)]
    private ?string $email = null;

    #[ORM\Column(length: 255, nullable: true)]
    private ?string $title = null;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getFirstName(): ?string
    {
        return $this->firstName;
    }

    public function setFirstName(?string $firstName): self
    {
        $this->firstName = $firstName;

        return $this;
    }

    public function getLastName(): ?string
    {
        return $this->lastName;
    }

    public function setLastName(?string $lastName): self
    {
        $this->lastName = $lastName;

        return $this;
    }

    public function getEmail(): ?string
    {
        return $this->email;
    }

    public function setEmail(string $email): self
    {
        $this->email = $email;

        return $this;
    }

    public function getTitle(): ?string
    {
        return $this->title;
    }

    public function setTitle(?string $title): self
    {
        $this->title = $title;

        return $this;
    }
}

Create the AuditLog entity

symfony console make:entity AuditLog

# Just create the entity and hit enter when asked if you want to add any mroe fields,
# we'll add the fields manually later.

Open the src/Entity/AuditLog.php file and add the following:

<?php

namespace App\Entity;

use App\Repository\AuditLogRepository;
use Doctrine\ORM\Mapping as ORM;

#[ORM\Entity(repositoryClass: AuditLogRepository::class)]
class AuditLog
{
    #[ORM\Id]
    #[ORM\GeneratedValue]
    #[ORM\Column]
    private ?int $id = null;

    #[ORM\Column(length: 255)]
    private ?string $entityType = null;

    #[ORM\Column]
    private ?int $entityId = null;

    #[ORM\Column]
    private ?\DateTimeImmutable $createdAt = null;

    #[ORM\ManyToOne]
    private ?User $user = null;

    #[ORM\Column(length: 255)]
    private ?string $action = null;

    #[ORM\Column(length: 255, nullable: true)]
    private ?string $requestRoute = null;

    #[ORM\Column]
    private array $eventData = [];

    #[ORM\Column(length: 255)]
    private ?string $ipAddress = null;

    public function getId(): ?int
    {
        return $this->id;
    }

    public function getEntityType(): ?string
    {
        return $this->entityType;
    }

    public function setEntityType(string $entityType): self
    {
        $this->entityType = $entityType;

        return $this;
    }

    public function getEntityId(): ?int
    {
        return $this->entityId;
    }

    public function setEntityId(int $entityId): self
    {
        $this->entityId = $entityId;

        return $this;
    }

    public function getCreatedAt(): ?\DateTimeImmutable
    {
        return $this->createdAt;
    }

    public function setCreatedAt(\DateTimeImmutable $createdAt): self
    {
        $this->createdAt = $createdAt;

        return $this;
    }

    public function getUser(): ?User
    {
        return $this->user;
    }

    public function setUser(?User $user): self
    {
        $this->user = $user;

        return $this;
    }

    public function getAction(): ?string
    {
        return $this->action;
    }

    public function setAction(string $action): self
    {
        $this->action = $action;

        return $this;
    }

    public function getRequestRoute(): ?string
    {
        return $this->requestRoute;
    }

    public function setRequestRoute(?string $requestRoute): self
    {
        $this->requestRoute = $requestRoute;

        return $this;
    }

    public function getEventData(): array
    {
        return $this->eventData;
    }

    public function setEventData(array $eventData): self
    {
        $this->eventData = $eventData;

        return $this;
    }

    public function getIpAddress(): ?string
    {
        return $this->ipAddress;
    }

    public function setIpAddress(string $ipAddress): self
    {
        $this->ipAddress = $ipAddress;

        return $this;
    }
}

Now you can generate and run the migrations:

symfony console make:migration
symfony console doctrine:migrations :migrate

Create and configure the login form

I won't cover this in detail, since it's an elementary part of any Symfony project using the default security configurations.

We've created a User security entity already and the make tool has configured this for you as a user provider.

All that's left for you to do here is follow the instructions in the Symfony Security: Form Login docs to generate a controller for the login route, configure the security.yaml file form_login section and create the login form template.

Don't forget to insert a test user in to your database! You can do this by running the following SQL against your Postgres database. If you are using the default Docker Compose configuration, you can run this command from the root of your project:

docker compose exec database psql -Uapp

# Then run the following SQL:
# This user will have the password "password"
INSERT INTO "user" ("id", "email", "roles", "password") VALUES (nextval('user_id_seq'), 'me+ex1@davegebler.com', '["ROLE_ADMIN"]', '$2y$10$sc32Zer27M/98I0glMpsqO54/oWhDApb2u2rGqr134PEpJlcNvZX6');

Once you're done, ensure you can log in to the application via your /login page. In your local development environment, you should have the Symfony Web Profiler at the bottom of the page, which will show you the security section with the current user and the roles they have.

Create the AuditLogger service

Create a new service class in src/Service/AuditLogger.php. This is the service class which will receive data from our Doctrine event listeners and persist it to the database.

<?php

namespace App\Service;

use App\Entity\AuditLog;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Component\HttpFoundation\RequestStack;
use Symfony\Component\Security\Core\Security;

class AuditLogger
{
    private EntityManagerInterface $em;
    private Security $security;
    private RequestStack $requestStack;

    public function __construct(EntityManagerInterface $entityManager, Security $security, RequestStack $requestStack)
    {
        $this->em = $entityManager;
        $this->security = $security;
        $this->requestStack = $requestStack;
    }

    public function log(string $entityType, string $entityId, string $action, array $eventData): void
    {
        $user = $this->security->getUser();
        $request = $this->requestStack->getCurrentRequest();
        $log = new AuditLog;
        $log->setEntityType($entityType);
        $log->setEntityId($entityId);
        $log->setAction($action);
        $log->setEventData($eventData);
        $log->setUser($user);
        $log->setRequestRoute($request->get('_route'));
        $log->setIpAddress($request->getClientIp());
        $log->setCreatedAt(new \DateTimeImmutable);
        $this->em->persist($log);
        $this->em->flush();
    }
}

Create the AuditSubscriber event subscriber

Create a new event subscriber class in src/EventSubscriber/AuditSubscriber.php.

This is the class which will hook in to the Doctrine lifecycle events. Doctrine provides a Doctrine\Bundle\DoctrineBundle\EventSubscriber\EventSubscriberInterface interface which we can implement to create our subscriber class. Then, thanks to Symfony's autoconfiguration and auto-wiring, our application will automatically call the functions in our class when the corresponding Doctrine events are triggered.

The events we're interested in are preRemove, postRemove, postUpdate and postPersist.

<?php

namespace App\EventSubscriber;

use App\Service\AuditLogger;
use Doctrine\Bundle\DoctrineBundle\EventSubscriber\EventSubscriberInterface;
use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Event\LifecycleEventArgs;
use Symfony\Component\Serializer\SerializerInterface;

/**
 * Class which listens on Doctrine events and writes an audit log of any entity changes made via Doctrine.
 */
class AuditSubscriber implements EventSubscriberInterface
{
    // Thanks to PHP 8's constructor property promotion and 8.1's readonly properties, we can
    // simply declare our class properties here in the constructor parameter list! 
    public function __construct(
        private readonly AuditLogger $auditLogger,
        private readonly SerializerInterface $serializer,
        private $removals = []
    ) {
    }

    // This function tells Symfony which Doctrine events we want to listen to.
    // The corresponding functions in this class will be called when these events are triggered.
    public function getSubscribedEvents(): array
    {
        return [
            'postPersist',
            'postUpdate',
            'preRemove',
            'postRemove',
        ];
    }

    public function postPersist(LifecycleEventArgs $args): void
    {
        $entity = $args->getObject();
        $entityManager = $args->getObjectManager();
        $this->log($entity, 'insert', $entityManager);
    }

    public function postUpdate(LifecycleEventArgs $args): void
    {
        $entity = $args->getObject();
        $entityManager = $args->getObjectManager();
        $this->log($entity, 'update', $entityManager);
    }

    // We need to store the entity in a temporary array here, because the entity's ID is no longer
    // available in the postRemove event. We convert it to an array here, so we can retain the ID for 
    // our audit log.
    public function preRemove(LifecycleEventArgs $args): void
    {
        $entity = $args->getObject();
        $this->removals[] = $this->serializer->normalize($entity);
    }

    public function postRemove(LifecycleEventArgs $args): void
    {
        $entity = $args->getObject();
        $entityManager = $args->getObjectManager();
        $this->log($entity, 'delete', $entityManager);
    }

    // This is the function which calls the AuditLogger service, constructing
    // the call to `AuditLogger::log()` with the appropriate parameters.
    private function log($entity, string $action, EntityManagerInterface $em): void
    {
        $entityClass = get_class($entity);
        // If the class is AuditLog entity, ignore. We don't want to audit our own audit logs!
        if ($entityClass === 'App\Entity\AuditLog') {
            return;
        }
        $entityId = $entity->getId();
        $entityType = str_replace('App\Entity\\', '', $entityClass);
        // The Doctrine unit of work keeps track of all changes made to entities.
        $uow = $em->getUnitOfWork();
        if ($action === 'delete') {
            // For deletions, we get our entity from the temporary array.
            $entityData = array_pop($this->removals);
            $entityId = $entityData['id'];
        } elseif ($action === 'insert') {
            // For insertions, we convert the entity to an array.
            $entityData = $this->serializer->normalize($entity);
        } else {
            // For updates, we get the change set from Doctrine's Unit of Work manager.
            // This gives an array which contains only the fields which have
            // changed. We then just convert the numerical indexes to something
            // a bit more readable; "from" and "to" keys for the old and new values.
            $entityData = $uow->getEntityChangeSet($entity);
            foreach ($entityData as $field => $change) {
                $entityData[$field] = [
                    'from' => $change[0],
                    'to' => $change[1],
                ];
            }
        }
        $this->auditLogger->log($entityType, $entityId, $action, $entityData);
    }
}

Record some audit log entries

Let's create a few endpoints to try out our audit logger. We'll create a Customer, then change their details, then delete them - and we should get three corresponding audit log entries for these actions.

Create a new controller class for testing in src/Controller/IndexController.php.

<?php

namespace App\Controller;

use App\Entity\Customer;
use App\Repository\CustomerRepository;
use Doctrine\ORM\EntityManagerInterface;
use Symfony\Bundle\FrameworkBundle\Controller\AbstractController;
use Symfony\Component\HttpFoundation\JsonResponse;
use Symfony\Component\HttpFoundation\Response;
use Symfony\Component\Routing\Annotation\Route;

class IndexController extends AbstractController
{
    #[Route('/add_customer', name: 'app_add_customer')]
    public function addCustomer(EntityManagerInterface $entityManager): Response
    {
        $customer = new Customer();
        $customer->setFirstName('John');
        $customer->setLastName('Smith');
        $customer->setEmail('customer1@davegebler.com');
        $customer->setTitle('Mr');
        $entityManager->persist($customer);
        $entityManager->flush();
        return new JsonResponse(['success' => true]);
    }

    #[Route('/change_customer', name: 'app_change_customer')]
    public function changeCustomer(EntityManagerInterface $entityManager, CustomerRepository $customerRepository): Response
    {
        $customer = $customerRepository->findOneBy(['email' => 'customer1@davegebler.com']);
        $customer->setFirstName('Jane');
        $customer->setLastName('Smith');
        $customer->setEmail('customer2@davegebler.com');
        $customer->setTitle('Mrs');
        $entityManager->flush();
        return new JsonResponse(['success' => true]);
    }

    #[Route('/delete_customer', name: 'app_delete_customer')]
    public function deleteCustomer(EntityManagerInterface $entityManager, CustomerRepository $customerRepository): Response
    {
        $customer = $customerRepository->findOneBy(['email' => 'customer2@davegebler.com']);
        $entityManager->remove($customer);
        $entityManager->flush();
        return new JsonResponse(['success' => true]);
    }
}

Now fire up the app with the Symfony local web server:

symfony server:start

And visit the following URLs in your browser:

• http://localhost:8000/add_customer
• http://localhost:8000/login
  • Login with the user you created earlier.
• http://localhost:8000/change_customer
• http://localhost:8000/delete_customer

View the audit log entries

Head back to your psql terminal (docker compose exec database psql -Uapp) and run the following query:

SELECT * FROM audit_log;

You should see three rows, one for each action we performed.

Because we only logged in after the first action, the user ID is NULL for the first row.

 id | user_id | entity_type | entity_id |     created_at      | action |    request_route    |                                                                       event_data                                                                       | ip_address
----+---------+-------------+-----------+---------------------+--------+---------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+------------
  1 |         | Customer    |         1 | 2022-09-19 195841 | insert | app_add_customer    | {"id":1,"firstName":"John","lastName":"Smith","email":"customer1@davegebler.com","title":"Mr"}                                                        | ::1
  2 |       2 | Customer    |         1 | 2022-09-19 195959 | update | app_change_customer | {"firstName":{"from":"John","to":"Jane"},"email":{"from":"customer1@davegebler.com","to":"customer2@davegebler.com"},"title":{"from":"Mr","to":"Mrs"}} | ::1
  3 |       2 | Customer    |         1 | 2022-09-19 200159 | delete | app_delete_customer | {"id":1,"firstName":"Jane","lastName":"Smith","email":"customer2@davegebler.com","title":"Mrs"}                                                       | ::1

Amazing! We have a clear record of each action performed on our entities, and the update record shows an efficient diff of only the fields which changed.

Even better, because we are using the native JSON type in PostgreSQL, we can query the audit log entries using the JSON operators and functions. For example, let's find all the audit log entries where the email address changed:

SELECT event_data->'email'->>'from' AS old_email, event_data->'email'->>'to' AS new_email FROM audit_log_entries WHERE event_data->'email'->>'from' IS NOT NULL;

Secure our audit log

There's one last thing we should sort out before we finish. The whole purpose of an audit log is to provide a reliable trail of events which can be used as evidence about activity within the system.

But what happens if someone gains access to the credentials our web application uses to connect to the database? They could potentially modify or erase entries in the audit log, and we'd have no way of knowing.

Our web application only needs to add new entries to the audit log, it doesn't need to modify or delete these entries after the fact.

Therefore, in order to mitigate risk, we can employ the principle of least privilege and create a separate database user which only has permission to insert new rows into the audit log table.

This user will retain read/write access as normal to all the other tables used by our application.

Create a new database user with the following command:

docker compose exec database psql -Uapp -c "CREATE USER audit_logger WITH PASSWORD 'audit_logger';"

Grant the new user full privileges on all tables in the app schema, then revoke the delete and update privileges on the audit_log table:

docker compose exec database psql -Uapp -c "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO audit_logger;"
docker compose exec database psql -Uapp -c "GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO audit_logger;"
docker compose exec database psql -Uapp -c "REVOKE DELETE, UPDATE, TRUNCATE, TRIGGER ON audit_log FROM audit_logger;"

Finally, check the privileges granted to the new user to ensure they are correct:

docker compose exec database psql -Uapp -c "SELECT grantee, privilege_type, table_name FROM information_schema.table_privileges WHERE table_name='audit_log';"

Now we need only update our application config to use the new user and password we've created:

# .env
DATABASE_URL=postgresql://audit_logger:audit_logger@127.0.0.1:5432/app

💡️ If you're using the Symfony local web server, you'll need to restart it for the changes to take effect. You may also need to update the user and password in docker-compose.yaml if you're using Docker Compose, since the Symfony server will read database credentials from there and create environment variables which override the .env configuration.

Check the new user can't modify the audit log

Let's test that the new user can't modify the audit log. First, we'll create a new endpoint in our sample controller:

#[Route('/change_audit_log', name: 'app_change_audit_log')]
public function changeAuditLog(EntityManagerInterface $entityManager, AuditLogRepository $auditLogRepository): Response
{
    $auditLog = $auditLogRepository->find(1);
    $auditLog->setRequestRoute('app_foobar');
    $entityManager->flush();
    return new JsonResponse(['success' => true]);
}

Now visit http://localhost:8000/change_audit_log in your browser. You should see an error message from Symfony which will be something like:

An exception occurred while executing a query: SQLSTATE[42501]: Insufficient privilege: 7 ERROR: permission denied for table audit_log

Great! We've successfully prevented the new user from modifying the audit log.

Conclusion

In this article, we've seen how to create an audit log for our entities using Doctrine lifecycle events and PostgreSQL's native JSON type.

We've also seen how to secure our audit log by creating a separate database user with limited privileges.

I hope you've enjoyed the tutorial and as always, any questions or comments please feel free to leave them below.

Addendum - auditing entities with relations

💡️ It occurred to me after initially publishing the blog post that you might also like to know how to properly handle changes to entities with relations, i.e. fields which are references to other entities. A quick proof-of-concept can be achieved by modifying the AuditSubscriber slightly.

Say we add a one-to-one User relation to our Customer entity (just for example sake) and then we look up a customer record, look up a user and change the customer's user accordingly. So if in our database, the customer's user_id field was 1 and we change it to 2:

$customer = $customerRepository->find(1);
$customer->setUser($userRepository->find(2));
$entityManager->flush();

What actually happens here is the change set will include two objects for the user field. The second (the "to" part of the change) will be the full User object with ID 2 we just looked up, but if we've never looked up the original User with ID one, the first part of the change set (the "from" part) will be a Doctrine proxy object, because we haven't done anything which requires Doctrine to go to the database and retrieve the real thing.

But what we can do is normalize these objects to arrays and in doing so, we will cause the proxy to be loaded and swapped for the real, relevant record.

At this point your audit log would contain both full User objects under the "from" and "to" keys. And that makes sense - we have a full JSON representation in our audit log of what the User inside the Customer looked like at the time the change happened. But we probably don't want to record the whole thing - there are fields such as hashed passwords and others which will just be bloat in the audit logs and aren't needed. There'll also be a few fields left over from the original proxy object (side note: this may be a bug that Symfony's serializer component picks up these special fields, I haven't looked in to it too much since it's an easy problem to solve, but I'm not convinced it's intentional or correct behaviour that they're included in the first place).

So how do we store only the properties from User we're interested in? Simple: we provide a context to our normalizer telling it what fields we want to exclude.

Open up your AuditSubscriber and make the following changes:

// Add a constant to the class
    const IGNORED_ATTRIBUTES_CONTEXT = [
        'ignored_attributes' =>
            [
                'password',
                'userIdentifier',
                '__initializer__',
                '__cloner__',
                '__isInitialized__',
            ]
    ];

// Switch lines which call $this->serializer->normalize($entity) to...
$this->serializer->normalize($entity, null, self::IGNORED_ATTRIBUTES_CONTEXT)

// And add the following extra logic in our log() function
// just under the line $entityData = $uow->getEntityChangeSet($entity);
foreach ($entityData as $key => $value) {
    if (is_object($value[0])) {
        $entityData[$key][0] = $this->serializer->normalize($value[0], null, self::IGNORED_ATTRIBUTES_CONTEXT);
    }
    if (is_object($value[1])) {
        $entityData[$key][1] = $this->serializer->normalize($value[1], null, self::IGNORED_ATTRIBUTES_CONTEXT);
    }
}

That's it, you'll now find in our example, only the User id and email address is included in the audit log when you change a User on a Customer.

Comments

$dbal = $this->entityManager->getConnection();
try {
        $dbal->beginTransaction();
        $dbal->insert('audit_log', [
                'id' => $id,
                'entity_type' => $auditLog->getEntityType(),
                'entity_id' => $auditLog->getEntityId(),
                // ... etcetera
        ]);
        $dbal->commit();
}
catch (Exception $e) { ... // rollback and log system error }