postgreSQL數(shù)據(jù)庫(kù)默認(rèn)用戶(hù)postgres常用命令分享

#alter user postgres with password ‘xxxx';(其中xxxx是修改的密碼)。

// 查看當(dāng)前schema的所有者，相當(dāng)于\du元命令
SELECT n.nspname AS "Name",
 pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname > 'information_schema'
ORDER BY 1; 

// 最后一條命令就是授予初始權(quán)限
grant select on all tables in schema abc to test;
grant usage on schema abc to test;
alter default privileges in schema abc 
#將表mytable,授權(quán)給testUser;
#GRANT SELECT ON TABLE mytable TO testUser;

// 查看初始權(quán)限
select * from pg_catalog.pg_default_acl;

// 修改模式“abc”擁有者為：dbadmin
ALTER SCHEMA abc OWNER TO "dbadmin";
// 查看模式的擁有者，相當(dāng)于\du元命令
SELECT n.nspname AS "Name",
 pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname > 'information_schema'
ORDER BY 1;

select * from pg_roles;
select * from pg_user;

select * from information_schema.table_privileges where grantee='cc';

select * from information_schema.table_privileges where grantee='user_name';

beigang=# grantall on schema csm_ca to public;
GRANT
beigang=# revoke all on schema csm_ca frompublic;
REVOKE

#create role：
#create role xxx with superuser;
#Create user：
# create user csm with superuserpassword 'csm';
# create user csm_ca with password 'csm_ca';

beigang=# grant all on schema csm_ca to csm_ca;
GRANT
beigang=# grant all on all tables in schema csm_ca to csm_ca;
GRANT

#創(chuàng)建普通用戶(hù)
postgres=# create user test encrypted password 'test';
#創(chuàng)建超級(jí)用戶(hù)
postgres=# create user test2 superuser;
#創(chuàng)建一個(gè)普通用戶(hù)，并且賦予相關(guān)權(quán)限
# create user test createdb createrole inherit password 'test';
#將超級(jí)用戶(hù)修改為普通用戶(hù)
# alter user test nosuperuser;
#修改用戶(hù)為超級(jí)用戶(hù)
postgres=# alter user test superuser;
#修改用戶(hù)密碼
postgres=# alter user test2 password 'test';
#修改用戶(hù)名
postgres=# alter user test2 rename to test3;
#鎖定/解鎖用戶(hù),不允許/允許其登錄
postgres=# alter user test nologin;
postgres=# alter user test login;
#設(shè)置用戶(hù)的連接數(shù),其中0表示不允許登錄,-1表示無(wú)限制
postgres=# alter user test connection limit 10;

GRANT ALL PRIVILEGES ON DATABASE 數(shù)據(jù)庫(kù)名 TO 用戶(hù)名;

GRANT ALL PRIVILEGES ON TABLE 表名 TO 用戶(hù)名;

alter table 表名 alter 字段名 type 類(lèi)型;

alter table 表名 add column 字段名 text（字段類(lèi)型）;

CREATE SEQUENCE user_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE 
NO MAXVALUE
CACHE 1;
 
alter table sys_user alter COLUMN id set DEFAULT nextval('user_id_seq');

-- create index '索引名' on '表名' ('需要索引的字段')
CREATE INDEX ip_store_inde on ip_store (ip_network);

alter table goods add primary key(sid);

alter table orders add foreign key(goods_id) references goods(sid) on update cascade on delete cascade;

alter table orders drop constraint orders_goods_id_fkey;

alter table goods add constraint unique_goods_sid unique(sid);

alter table goods alter column sid drop default;

alter table goods alter column sid type character varying;

alter table goods rename column sid to ssid;

constraint user_info_unique_userid unique(userid)

listening_address: '*'

host  all       all       0.0.0.0/0        md5