joe/blog: blog posts @ 44a99959991281f30bfa56e201fbc05e104e8c23

title: "Postgresql 操作" date: 2022-10-12T15:06:02+07:00

draft: true

基本环境

OS: Manjaro(Arch Serial)
Postgresql: v14.5.1

手册

安装

arch linux 安裝

pacman -Syu
pacman -S postgresql

運行 ```shell sudo su - postgres initdb --locale en_US.UTF-8 -D /var/lib/postgres/data exit # quit postgres user shell

sudo su - postgres createdb createuser dropdb dropuser

以上命令或者 `sudo -u postgres createdb`

```

sudo -u postgres psql # enter psql
alter user <username> with encrypted password 'password';
grant all privileges on database <database> to <username>;

sudo su - postgres
create user <username> with password 'password';
create database <database> owner <username>;
grant all privileges on database <database> to <username>;
\q

命令 ```shell
\h SQL help
\? psql help
\l show all databases;
\c connect to
\d list all tables;
\du list all users
\e open editor
\conninfo: list database and connection info
\password: modify password
\z

\dp [tablename]: 查看对象的访问权限列表


## 常用 SQL

```sql
-- 查看当前用户
select user; 
-- 当前数据库
select current_database();
-- 建表
create table <schema>.<table>();
-- 角色
create role <role>;
alter role <role> with CREATEDB;
drop role <role>
-- 能登录的用户
select * from pg_user;
-- 所有角色
select * from pg_roles;
-- 查看 guest 表权限
select * from information_schema.role_table_grants where grantee='guest' group by table_name,table_schema;
-- 查看表所属权限
select * from information_schema.role_table_grants where table_name='tablename' group by grantee,table_schema;
-- 查看用户表权限
select * from information_schema.table_privileges where grantee='guest';
-- 查看用户的 USAGE 权限
select * from information_schema.usage_privileges where grantee='guest';
-- 检查权限
select has_database_privilege(<user>, <database>, <privilege>);
select has_schema_privilege(<user>, <schema>, <privilege>);
select has_table_privilege(<user>, <table>, <privilege>);
-- 查看 schema 所有表
\dt <schema>.*
select * from information_schema.tables where table_schema='public';