Postgresql和MySQL还是有很多不同的。就比如授权来说。当下有个业务场景,我们的报表数据库需要根据业务划分不同的db,然后创建对应的user。
如果是MySQL, 可以这样做
mysql> create database mydb; mysql> create user a_user identified by 'ThePassword' -> ; Query OK, 0 rows affected (0.04 sec) mysql> grant all privileges on mydb.* to a_user@'%'; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)对于Postgres9.0以上
创建一个用户etl_user,允许登陆,永不过期
create role etl_user login password 'ThePassowrd' valid until 'infinity';将db权限授予user
grant all privileges on database mydb to etl_user ;或者,分别授权
GRANT CONNECT ON DATABASE mydb TO etl_user; GRANT USAGE ON SCHEMA public TO etl_user; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO etl_user; GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO etl_user;这时候,如果你要删除这个role,则必须先要删除关联的privileges
REVOKE CONNECT ON DATABASE mydb FROM etl_user; REVOKE ALL PRIVILEGES ON ALL TABLES IN SCHEMA public FROM etl_user; REVOKE ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public FROM etl_user; REVOKE ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public FROM etl_user; DROP USER etl_user;