1.创建用户
\help create users; --查看命令帮助
\h create users
\du --查看用户信息
\l --列出数据库
2.创建数据库
create database db_name
\c db_nam --连接到数据库
\dt --查看表
\d tablename --可以查看这个表的表结构
\x
select * from pg_tables;
用来登录数据库实例,管理数据库对象
create user和create role的作用差不多,唯一的区别就是create role 创建的用户不带连接权限
postgres=# \help create user;
postgres=# \help create user;
Command: CREATE USER
Description: define a new database role
Syntax:
CREATE USER name [ [ WITH ] option [ ... ] ]
where option can be:
SUPERUSER | NOSUPERUSER
| CREATEDB | NOCREATEDB
| CREATEROLE | NOCREATEROLE
| INHERIT | NOINHERIT
| LOGIN | NOLOGIN
| REPLICATION | NOREPLICATION
| BYPASSRLS | NOBYPASSRLS
| CONNECTION LIMIT connlimit
| [ ENCRYPTED ] PASSWORD 'password' | PASSWORD NULL
| VALID UNTIL 'timestamp'
| IN ROLE role_name [, ...]
| IN GROUP role_name [, ...]
| ROLE role_name [, ...]
| ADMIN role_name [, ...]
| USER role_name [, ...]
| SYSID uid
URL: https://www.postgresql.org/docs/14/sql-createuser.html
postgres=#
例如: create user admin with SUPERUSER password '123' --创建了一个超级管理员的用户admin
[postgres@v-vlxsz-devdb01 ~]$ psql -U admin -d postgres -h 127.0.0.1 -p 5432 --连接数据库
(-d指定数据库名字)
postgres=# alter user admin with password '1234' --修改用户密码
postgres=# \help alter user --查看帮助
postgres=# alter user name rename to new_name --修改用户名
postgres@v-vlxsz-devdb01 ~]$ psql
psql (14.1)
Type "help" for help.
postgres=# alter user admin with nologin ; --改用户权限
ALTER ROLE
postgres=# \du --查看有哪些用户,用户信息
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-----------
admin | Superuser, Cannot login | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
replica | Replication | {}
postgres=#