登陆数据库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 用户名;