postgreSQL数据库默认用户postgres常用命令

1、修改用户postgres的密码

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

2、查看下当前schema的所有者：

// 查看当前schema的所有者，相当于\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; 

3、查询结果如图所示，模式“abc”的所有者为postgresql用户
针对模式“abc”， 使用超级管理员postgresql给普通用户test授权，命令如下：

// 最后一条命令就是授予初始权限
grant select on all tables in schema abc to test;
grant usage on schema abc to test;
alter default privileges in schema abc 

#将表mytable,授权给testUser;
#GRANT SELECT ON TABLE mytable TO testUser;

4、查看默认权限
授权完成，通过pg_default_acl表查看默认权限：

// 查看初始权限
select  * from pg_catalog.pg_default_acl;

5、把模式“abc”的拥有者(owner)修改为dbadmin用户（可以事先创建好），执行以下命令：

// 修改模式“abc”拥有者为：dbadmin
ALTER SCHEMA abc OWNER TO  "dbadmin";

// 查看模式的拥有者，相当于\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;

6、postgre查询所有用户，postgre中查询用户所拥有的权限

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';

7、把适用于该对象的所有权限都赋予目标角色。
用特殊的名字 PUBLIC 把对象的权限赋予系统中的所有角色。 在权限声明的位置上写 ALL，表示把适用于该对象的所有权限都赋予目标角色。

beigang=#  grantall on schema csm_ca to public;

GRANT

beigang=# revoke all on schema csm_ca frompublic;

REVOKE

8、先创建一个角色xxx，再创建一个超级用户csm、普通用户csm_ca，csm用户创建一个数据库testdb，在这个数据库里创建一个schema：csm_ca，然后赋予普通用户csm_ca操作数据库testdb里schema：csm_ca里的表的权限。

#create role：
#create role xxx with superuser;

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

9、超级用户csm给普通用户csm_ca授予操作schema 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

10、创建用户

#创建普通用户
postgres=# create user test encrypted password 'test';

#创建超级用户
postgres=# create user test2 superuser;

#创建一个普通用户，并且赋予相关权限
# create user test createdb createrole inherit password 'test';

#将超级用户修改为普通用户
# alter user test nosuperuser;

#修改用户为超级用户
postgres=# alter user test superuser;

#修改用户密码
postgres=# alter user test2 password 'test';

#修改用户名
postgres=# alter user test2 rename to test3;

#锁定/解锁用户,不允许/允许其登录
postgres=# alter user test nologin;
postgres=# alter user test login;

#设置用户的连接数,其中0表示不允许登录,-1表示无限制
postgres=# alter user test connection limit 10;

11、授予用户数据库权限

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

12、授予用户查看刚授权的数据库的里面的表的权限

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

13、附带一条：修改的表的类型

alter table 表名 alter 字段名 type 类型;

14、附带一条：增加表新的字段

alter table 表名 add column 字段名 text（字段类型）;

15、新增：设置主键自增

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');

16、新增：postgres创建B-Tree索引

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

添加各种约束

(1)、 添加主键

alter table goods add primary key(sid);

（2）、 添加外键

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

on update cascade: 被引用行更新时，引用行自动更新；

    on update restrict:    被引用的行禁止更新；

    on delete cascade:     被引用行删除时，引用行也一起删除；

    on dellete restrict:   被引用的行禁止删除；

（3）. 删除外键

alter table orders drop constraint orders_goods_id_fkey;

(4). 添加唯一约束

alter table goods add constraint unique_goods_sid unique(sid);

(5). 删除默认值

alter table goods  alter column sid drop default;

(6). 修改字段的数据类型

alter table goods alter column sid type character varying;

(7). 重命名字段

alter table goods rename column sid to ssid;

17、创建唯一键约束

constraint user_info_unique_userid unique(userid)

18、

19、

20、

21、

22、

23、

24、

25、

扩展

编辑配置文件

文件：postgresql.conf

位置：/var/lib/pgsql/data/postgresql.conf

添加/修改：在所有IP地址上监听，从而允许远程连接到数据库服务器：

listening_address: '*'

文件：pg_hba.conf

位置：/var/lib/pgsql/data/pg_hba.conf

添加/修改：允许任意用户从任意机器上以密码方式访问数据库，把下行添加为第一条规则：

host    all             all             0.0.0.0/0               md5

参考链接 ：
postgres 创建新用户并授权 : https://blog.csdn.net/XuHang666/article/details/81506297

postgresql用户权限管理 ： https://blog.csdn.net/hjh872505574/article/details/91411530

PostgreSQL的用户、角色和权限管理 ： https://blog.csdn.net/BeiiGang/article/details/8604578?depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-3&utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-3

postgresql-用户和权限 : https://blog.csdn.net/colorful_sun/article/details/81190521?depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1&utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-1

postgre查询所有用户，postgre中查询用户所拥有的权限 : https://blog.csdn.net/yonggie/article/details/80160915

PostgreSQL查看默认权限 : https://blog.csdn.net/qq_33158376/article/details/86571928?depth_1-utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-14&utm_source=distribute.pc_relevant.none-task-blog-BlogCommendFromBaidu-14