After Migration creates a table, execute a SQL GRANT on that table

Need Help
1

Hi there,

The DBA has requested that the PostgreSQL database has 2 users: db_admin and db_user

db_admin will be the owner of the database and will be able to create tables.
db_user will only be able to access the data on the tables.

In terms of security and separation of concerns, this makes a lot of sense.

I can solve the issue of the users with 2 connections: admin and default.

The Migrations(Phynx) will use the admin connection and the app will use the default connection.

All good up to here if we didn’t need to issue a GRANT for each table we create, but alas, we do.

Reading the docs I think we can use something like this:

use Cake\Datasource\ConnectionManager;

$connection = ConnectionManager::get('default');
$connection->execute('GRANT SELECT,... ON TABLE xyz TO db_user');

I now have 3 questions:

  1. Is it possible to get the connection object from AbstractMigration?
  2. Is it possible to get the connection name from AbstractMigration?
  3. Will the above hack work as expected?

Cheers,
Gus

2

Hey there,

I was able to get to this point:

use Cake\Datasource\ConnectionManager;
use Migrations\AbstractMigration;

class CreateUsers extends AbstractMigration
{
    private $table = 'users';

    public function up()
    {
        $table = $this->table($this->table);
        $table
            ->addColumn('name', 'string', [
                'default' => null,
                'limit' => 255,
                'null' => false
            ])
            // Many more columns exist, removed for brevity of example
            ->create();

        $conn = ConnectionManager::get('default');
        $username = $conn->config()['username'];

        $this->execute('GRANT SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON TABLE ' . $this->table . ' TO ' . $username . ';');
    }

    public function down(){
        $this
            ->table($this->table)
            ->drop()
            ->save();
    }
}

And it runs without giving an error. No error UP, no error DOWN.

Problem, now, is that the GRANT is performed but has no effect on the visibility of the table users to the db_user user.

I’m now baffled by the fact that if I perform the GRANT from HeidiSQL or via psql it sticks, but via the migrations, it doesn’t.

Is there some kind of flush I need to add, or is there a transaction that I need to commit?

Cheers,
Gus