Sql admin
For many people taking the first step of moving from mysql to postgres is a large stride because the psql client takes some getting used to. To help bridge this gap we will look at some common mysql admin tasks carried out with the command line client and look at their counterparts in postgres.
|
In both systems the output produced from the commands
above are similar (though their presentation may differ).
However certain other tasks such as user and privilege
management display marked differences.
In mysql new users have to be created with the GRANT command while in postgres you need to make use of the CREATE USER command intead and follow up with a GRANT command. On the other hand recent versions of mysql does provide support for the DROP USER command, however in order to use it you have to first REVOKE PRIVILEGES. In other words only a user without privileges can be removed in this manner.
| Action | mysql command | pgsql command |
|---|---|---|
| Create a new user and grant privileges | GRANT ALL PRIVILGES ON dbname.* TO [email protected] IDENTIFIED by 'password'; |
CREATE USER username WITH PASSWORD
'password'; GRANT ALL PRIVILEGES ON DATABASE dbname to username' |
| Retrieve user privileges | SHOW GRANTS FOR user |
\dp An exact match is not available in postgres. |
| Delete user |
REVOKE ALL PRIVILEGES ON dbname.* FROM
username; followed by DELETE FROM mysql.user WHERE user='username'; FLUSH PRIVILEGES; |
DROP USER username; provided the user does not own any databases. |
An extension to the sql standard in mysql allows you to add a suffix of '@hostname' to the username to grant permissions to remote users, with postgresql you need to edit your pg_hba.conf file to allow/deny remote connections. Despite that the pg_hba.conf method is superior because it gives you plenty of flexibility and options not supported by the competition.
