trying to create new connections after each login using PDO

[nlomb_hydrogeo]

I'm a newbie to PHP, so bear with me. I am trying to create a user registration/login system.  index.php is a login page, with a notice that users must be registered, and this notice contains a link to a registration page. User clicks on the link to get to the page and fill in the info - 8 fields in total. All the information gets into the users database table using a PDP prepared statement without a problem while the user is redirected back to the login page. After a successful login (checking username and password in the users table) we have a redirect to a test landing page. Everything works, except throughout this whole process the application is connected as postgres superuser. This initial PDO connection is established in an include file, init.php as shown below:

<?php
    ob_start();
    session_start();
    $host = '127.0.0.1';
$dbname = 'IGAD_GWIS';
$charset = 'utf8mb4';
$dsn = "pgsql:host=$host; dbname=$dbname; port =5432;";
$options = [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES=> false
];
        
        $pdo = new PDO($dsn, 'postgres', 'f1234u5678', $options);

In my login page I collect my POST variables from the login form (username and password)

if (!empty($_SERVER['REQUEST_METHOD'])) {

            if ($_SERVER['REQUEST_METHOD']=='POST') {

        $username=$_POST['username'];

        $password=$_POST['password'];

$_SESSION['username']=$username;

I then call a function in the php_functions include page:

$user_pdo = set_connection($pdo, $_SESSION['username']);

function set_connection($pdo,$uname) {
        try {
            $sqlconn="SELECT password FROM public.users WHERE username={$uname}";
              //$stmnt=$pdo->prepare($sql);
             $stmnt= $pdo->query($sqlconn);
             $conn_row =$stmnt->fetch();
             $conn_user = $conn_row['username'];
             $conn_pass = $conn_row['password'];
             $dbname = 'IGAD_GWIS';
            $charset = 'utf8mb4';
            $host = '127.0.0.1';
             $dsn = "pgsql:host=$host; dbname=$dbname; port =5432;";
             $options = [
                PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
                PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
                PDO::ATTR_EMULATE_PREPARES=> false];
            return
                $user_pdo = new PDO($dsn, $conn_user, $conn_pass, $options);

 

This doesn't do anything. No errors are thrown, but no new connection is established. I checked  in postgres -  select * from pg_stat_activity - it only shows the postgres superuser.

I need to have distinct connections with each user, as I intend to establish write privleges on tables.

Any suggestions?
            

Edited January 18 by nlomb_hydrogeo
left out a line

[mac_gyver]

the user registration/login system on your web site is for the purpose of determining what a user can see and do on the web pages. this has nothing to do with database connections. it is your application code that is performing the database operations and the database connection credentials are making a connection to allow your application code to do its work. it is up to your application code to determine what database operations can be performed for any user to your site.

3 hours ago, nlomb_hydrogeo said:

I need to have distinct connections with each user, as I intend to establish write privleges on tables.

this is not how web applications are implemented. post or describe an example of something you think requires this, and a forum member will provide information on how to correctly implement it.

[Strider64]

First get the PDO connection out of the function as that will cause you nothing but headaches.

Here's an example of a generic PDO connection ->

<?php
$host = '127.0.0.1'; // or your database host
$db   = 'your_database_name';
$user = 'your_database_username';
$pass = 'your_database_password';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    // Use $pdo to perform database operations
} catch (\PDOException $e) {
    throw new \PDOException($e->getMessage(), (int)$e->getCode());
}
?>

I would put this in a configuration file maybe name it config.php file?

My example would entail explaining OOP, so maybe someone else will do a better example for you. But here is how I do it ->

        $sql = "SELECT id, password FROM admins WHERE username =:username LIMIT 1";
        $user = $this->retrieve_credentials($sql, $username);
        if ($user && password_verify($password, $user['password'])) {
            session_regenerate_id(); // prevent session fixation attacks
            $_SESSION['user_id'] = $user['id'];
            return true;
        }

        return false;

and little more code

    protected function retrieve_credentials(string $sql, string $username): ?array
    {
        $stmt = $this->pdo->prepare($sql);
        $stmt->execute(['username' => $username]);
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        return $result !== false ? $result : null;
    }

 

Edited January 18 by Strider64

[nlomb_hydrogeo]

Hello Strider -  your code is very concise and clear. I think my main problem was not using the session_renerate_id function.

If you could provide the Class definition for your object instance, it would be much appreciated.