一、连接
查看pgsql端口号名
netstat -a | grep PGSQL
切换数据库用户
sudo su postgres
登陆数据库
postgres用户下：
psql -p 6432
其他用户登陆
psql -U username -d dbname -h hostip -p port
查看数据库
\l
二、创建
创建用户
用户：user123，密码123456
CREATE USER user123 WITH PASSWORD '123456';
创建只读用户
只读用户：reader123，密码：123456
CREATE USER reader123 WITH PASSWORD '123456';
*创建数据库 *
数据库名：db123 ，owner：user123
create database db123 with owner=user123;
回收public权限
回收public的connect on database db123 权限，此时只有owner和postgres用户可以连接db123
revoke CONNECT ON DATABASE db123 from public;
赋权
grant all privileges on database db123 to user123;
创建只读权限
\c testdb1;
CREATE ROLE readaccess;
GRANT CONNECT ON DATABASE testdb1 TO readaccess;
GRANT USAGE ON SCHEMA public TO readaccess;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readaccess;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readaccess;三、修改
修改表的字段类型：
将t_user表的id字段由int改为bigint
ALTER TABLE t_user alter COLUMN id type bigint using id::bigint ;
四、删除
删除User
DROP OWNED BY user123;
DROP USER user123;
ALTER ROLE user123 WITH PASSWORD '123456';