Yii2: RBAC + Postgresql is broken

Created on 6 Feb 2017 · 13Comments · Source: yiisoft/yii2

What steps will reproduce the problem?

Use DbManager with Postgresql, run migration to create RBAC tables.
Create rule class, put it into namespace
Add rule to authManager

What is the expected result?

Rule to be written to DB

What do you get instead?

Database error, because '\' in namespace is not escaped as it is required by bytea database type.

Additional info

| Q | A
| ---------------- | ---
| Yii version | 2.0.11
| PHP version | 7
| Database | Postgresql 9.5 (I suppose version is insignificant)
| Operating system | Any

The bug is caused by changing database column type from "text" to "bytea" (see 23790272dc561aacf2070cbbda396f49e44cbb7d). In case of bytea we couldn't write data directlty, we should escape special symbols first.

PostgreSQL ready for adoption bug

Source

StarGuardian

All 13 comments

can you show the exact error your get?

cebe on 6 Feb 2017

Yii2 generated following insert:
INSERT INTO "auth_rule" ("name", "data", "created_at", "updated_at") VALUES ('isRole-root', 'O:22:"aurora\rbac\IsRoleRule":4:{s:4:"role";s:4:"root";s:4:"name";s:11:"isRole-root";s:9:"createdAt";i:1486115818;s:9:"updatedAt";i:1486115818;}', 1486115818, 1486115818)'

And I got error: ERROR: invalid input syntax for type bytea
LINE 1: ...created_at", "updated_at") VALUES ('isRole-root', 'O:22:"aur...

It is error from pgAdmin, but Yii console command has the same result. I couldn't copy command output because I downgraded Yii to 2.0.10

StarGuardian on 6 Feb 2017

Failed to reproduce. Here are my steps:

Configure DB connection and AuthManager components for both web and console applications:

'db' => [
    'class' => \yii\db\Connection::class,
    'dsn' => 'pgsql:host=localhost;dbname=yiitest;port=5432',
    'username' => 'yiitest',
    'password' => 'yiitest',
    'charset' => 'utf8',
],
'authManager' => [
    'class' => \yii\rbac\DbManager::class
],

Migrate the auth component:

./yii migrate --migrationPath=@yii/rbac/migrations

Create TestRule:

TestRule.php

<?php

namespace app\rbac;

use yii\rbac\Rule;

class TestRule extends Rule
{
    public $name = 'testRule';

    public function execute($user, $item, $params)
    {
        return true;
    }
}

Create console action to register rule and permissions:

public function actionAddRule()
{
        $am = Yii::$app->getAuthManager();

        $rule = new TestRule();
        $am->add($rule);

        $permission = $am->createPermission('test');
        $permission->ruleName = $rule->name;
        $am->add($permission);
}

Run action, go to DB and check result:

SELECT * FROM auth_rule;

name: testRule
data: O:17:"app\rbac\TestRule":3:{s:4:"name";s:8:"testRule";s:9:"createdAt";i:1486417693;s:9:"updatedAt";i:1486417693;}
created_at: 1486417693
updated_at: 1486417693

Also I see we have PgSQLManagerTest that should cover this case.

SilverFire on 6 Feb 2017

👍1

Thanks for posting in our issue tracker.
In order to properly assist you, we need additional information:

When does the issue occur?
What do you see?
What was the expected result?
Can you supply us with a stacktrace? (optional)
Do you have exact code to reproduce it? Maybe a PHPUnit tests that fails? (optional)

Thanks!

_This is an automated comment, triggered by adding the label status:need more info._

yii-bot on 6 Feb 2017

@StarGuardian could you help up to reproduce this issue?

SilverFire on 11 Feb 2017

It has been 2 or more weeks with no response on our request for more information.
In order for our issue tracker to be effective, we are closing this issue.

If you want it to be reopened again, feel free to supply us with the requested information.

Thanks!

_This is an automated comment, triggered by adding the label expired._

yii-bot on 27 Apr 2017

I've reproduced this issue on Yii 2.0.15.1 and Postgresql 10.4.

Exception: SQLSTATE[22P02]: Invalid text representation: 7 ERROR:  invalid input syntax for type bytea
The SQL being executed was: INSERT INTO "auth_rule" ("name", "data", "created_at", "updated_at") VALUES ('isDraftNews', 'O:65:"consultantplus\www\apps\admin\components\rbac\rules\NewsDraftRule":3:{s:4:"name";s:11:"isDraftNews";s:9:"createdAt";i:1540220528;s:9:"updatedAt";i:1540220528;}', 1540220528, 1540220528) (.../vendor/yiisoft/yii2/db/Schema.php:664)

DmLapin on 22 Oct 2018

How exactly?

samdark on 22 Oct 2018

@samdark, nothing special. Scenario is the same as was described early. I've created migration for my RBAC initialization and try to add namespaced rule:

$authManager = \Yii::$app->getAuthManager();
$newsDraftRule = new NewsDraftRule(); // namespaced object
$authManager->add($newsDraftRule);

PostgreSql has some restrictions for bytea input format. It has to be either Hex or Escape format. Fast solution (not sure that it's good one) can look like this:

'data' => bin2hex(serialize($rule)), // in rbac\DbManager

It produces ASCII-string, that compatible with Escape Format. While restoring from DB we should use unserialise(hex2bin($value)) in this case.

Or, maybe, more correct way is to generate Hex-format string like \x<HEX-DATA> in \yii\db\pgsql\QueryBuilder::normalizeTableRowData().

DmLapin on 23 Oct 2018

👍1

I figured out the problem. It "begins" in \yii\db\pgsql\Schema::resolveTableNames.

} else {
    $table->schemaName = $this->defaultSchema;
    $table->name = $parts[0];
}

We don't use default public schema. We configured search_path of postgres with another value. And all our code, that makes sql queries without schema name correctly finds tables in database.

Methods of \yii\db\pgsql\Schema class always use schema name, which is public by default. Thats why loading columns info failes. See steps:

\yii\rbac\DbManager::addRule($rule)
\yii\db\Command::insert('{{%auth_rule}}', ['data' => serialize(...)])
\yii\db\pgsql\QueryBuilder::insert('{{%auth_rule}}', ['data' => serialize(...)], &$params)
\yii\db\pgsql\QueryBuilder::normalizeTableRowData('{{%auth_rule}}', ['data' => serialize(...)])
$this->db->getSchema()->getTableSchema('{{%auth_rule}}') fails, because it tries internally to get schema of public.auth_rule
Value of data column considered as string, not as \PDO::PARAM_LOB.
Insert error occured

DmLapin on 23 Oct 2018

@DmLapin does that mean that we should introduce an additional configuration parameter for the pgsql connection, to allow the user to globally pick a different search_path, or does it have to be done on a table basis?