一、连接
查看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';