Symfony 3.4, FOSUserBundle: SQL Errors with postgresql

Question

I have a normal User class with only id attribute was working really fine when i was using MySQL. Since i changed to Postgresql, i started having much issues.

Let me first give you an overview about my app, its a GIS on web, i put a TSV file and with a php script that i made, it reads the TSV file and save its data in the database. Then Geoserver reads the data and serve it to my leaflet map. So Postgresql is really important since Geoserver doesnt support MySQL.

At first i created the database, and its schema using the command line tool. Everything is fine till here, but when i tried to use the command line to create a user, it throws an SQLSTATE[42601] Syntax error:

In AbstractPostgreSQLDriver.php line 70:

An exception occurred while executing 'INSERT INTO user (username, username_canonical, emai
  l, email_canonical, enabled, salt, password, last_login, confirmation_token, password_reque
  sted_at, roles, id) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)' with params ["admin", "adm
  in", "[email protected]", "[email protected]", 1, null, "$2y$13$rS4j9aDRRpKMA2ge3QoDNuhmCWD
  9cO9Cmnzv\/PxqUmBxSximXcFZu", null, null, null, "a:0:{}", 1]:



 SQLSTATE[42601]: Syntax error: 7 ERREUR:  erreur de syntaxe sur ou près de « user »
  LINE 1: INSERT INTO user (username, username_canonical, email, email...
                      ^

In PDOStatement.php line 107:

  SQLSTATE[42601]: Syntax error: 7 ERREUR:  erreur de syntaxe sur ou près de « user »
  LINE 1: INSERT INTO user (username, username_canonical, email, email...
                  ^


In PDOStatement.php line 105:

  SQLSTATE[42601]: Syntax error: 7 ERREUR:  erreur de syntaxe sur ou près de « user »
  LINE 1: INSERT INTO user (username, username_canonical, email, email...
                  ^

Sorry about the french sentence it says basicly error of syntax on or near << user >>

Its not the only one issue i got, after this way didnt work i tried to use the register form:

An exception occurred while executing 'SELECT t0.username AS username_1, t0.username_canonical AS username_canonical_2, t0.email AS email_3, t0.email_canonical AS email_canonical_4, t0.enabled AS enabled_5, t0.salt AS salt_6, t0.password AS password_7, t0.last_login AS last_login_8, t0.confirmation_token AS confirmation_token_9, t0.password_requested_at AS password_requested_at_10, t0.roles AS roles_11, t0.id AS id_12 FROM user t0 WHERE t0.username_canonical = ?' with params ["user"]:

SQLSTATE[42703]: Undefined column: 7 ERREUR: la colonne t0.username n'existe pas
LINE 1: SELECT t0.username AS username_1, t0.username_canonical AS u...
^

Even i tried to enter the user manually to the database, but when i try to login:

Authentication request could not be processed due to a system problem.

I dont know if the other Entities that i made are working or not cuz i cant get into the admin panel to use them.

<?php
namespace AdminBundle\Entity;

use FOS\UserBundle\Model\User as BaseUser;
use SbS\AdminLTEBundle\Model\UserInterface as ThemeUser;
use Doctrine\ORM\Mapping as ORM;

/**
 * Class User
 * @package AdminBundle\Entity
 * @ORM\Entity
 * @ORM\Table(name="user")
 */
class User extends BaseUser implements ThemeUser
{
    /**
     * @var int
     *
     * @ORM\Id
     * @ORM\Column(type="integer")
     * @ORM\GeneratedValue(strategy="AUTO")
     */
    protected $id;

    public function __construct()
    {
        parent::__construct();
    }

    /**
     * Get Id
     *
     * @return int
     */
    public function getId()
    {
        return $this->id;
    }

    /**
     * Set Id
     *
     * @param int $id
     *
     * @return User
     */
    public function setId($id)
    {
        $this->id = $id;

        return $this;
    }

    /**
     * Should return FirstName and LastName
     * @return string
     */
    public function getName()
    {
    }

    /**
     * Should return path to avatar. See Twig User Extension.
     * @return string
     */
    public function getAvatar()
    {
    }

    /**
     * Should return Date of Registration
     * @return mixed
     */
    public function getMemberSince()
    {
    }

    /**
     * Should return Description (can be role, group or etc.)
     * @return string
     */
    public function getTitle()
    {
    }

    /**
     * Can return Additional Info
     * @return mixed
     */
    public function getInfo()
    {
    }
}

ThemeUser is an interface of an admin bundle that i am using, i just implemented it to have the bundle fonctionality. It didnt have any issue when i was using MySQL. I even didnt fill the methods cuz i dont need them.

Column                  Type                                  Comment
id                      integer  
username                varchar(180)     
username_canonical      varchar(180)     
email                   varchar(180)     
email_canonical         varchar(180)     
enabled                 boolean  
salt                    varchar(255) NULL    
password                varchar(255)     
last_login              timestamp(0) NULL    
confirmation_token      varchar(180) NULL  
password_requested_at   timestamp(0) NULL    
roles                   text                               (DC2Type:array)

Answer 1

"user" is a reserved keyword in postgreSQL, you should change your table name to something else, or add "`" (backtick) symbol

`user`

https://www.postgresql.org/docs/current/static/sql-keywords-appendix.html