postgresql数据库命令行授权实操

登陆数据库psql -h 127.0.0.1 -p 5432 -U postgres;

#访问库
grant connect on database zjy to zjy;
GRANT
#访问schmea
 grant usage on schema zjy to zjy;
GRANT
#访问表
grant select,insert,update,delete on all tables in schema zjy to zjy;
GRANT
#如果访问自增序列，需要授权
grant select,update on all sequences in schema zjy to zjy;
GRANT

注意：上面的授权只对历史的一些对象授权，后期增加的对象是没有权限的，需要给个默认权限

#默认表权限
 ALTER DEFAULT PRIVILEGES IN SCHEMA zjy GRANT select,insert,update,delete ON TABLES TO zjy;
ALTER DEFAULT PRIVILEGES

#默认自增序列权限
ALTER DEFAULT PRIVILEGES IN SCHEMA zjy GRANT select,update ON sequences TO zjy;
ALTER DEFAULT PRIVILEGES

--postgresql 授权给用户只读权限

CREATE USER aypc WITH ENCRYPTED PASSWORD 'aypc';

alter user aypc set default_transaction_read_only=on;

GRANT USAGE ON SCHEMA axpay to aypc;

ALTER DEFAULT PRIVILEGES IN SCHEMA axpay GRANT SELECT ON TABLES TO aypc;

GRANT CONNECT ON DATABASE pcd to aypc;

GRANT USAGE ON SCHEMA axpay to aypc;

GRANT SELECT ON ALL SEQUENCES IN SCHEMA axpay TO aypc;

GRANT SELECT ON ALL TABLES IN SCHEMA axpay TO aypc;

修改密码 alter user aypc with password 'aypcdada$';

--删除用户之前要先收回权限
revoke all on database ucd from aypc;

REVOKE SELECT
    ON TABLE "dcp"."user_stock_add_record" FROM "aypc";
REVOKE SELECT
    ON TABLE "dcp"."user_stock_cls" FROM "aypc";
REVOKE USAGE ON SCHEMA "dcp" FROM "aypc";

drop user aypc

 

GRANT ALL PRIVILEGES ON DATABASE 数据库名 TO 用户名;

 

GRANT ALL PRIVILEGES ON TABLE 表名 TO 用户名;