PostgreSQL常用操作之创建用户

查看帮助

创建用户

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/17/sql-createuser.html

创建用户

创建一个超级用户

下面我们创建了一个超级管理员，用户名是root密码是root。

postgres=# CREATE USER root WITH SUPERUSER PASSWORD 'root';  
CREATE ROLE

创建一个数据库root_user_db, 所有者为root用户

postgres=# CREATE DATABASE root_user_db OWNER root;  
CREATE DATABASE

连接数据库

• -h 指定主机
• -p 指定端口
• -d 指定数据库
• -U 指定用户
• -W 指定密码

➜  ~ psql -h 127.0.0.1 -p 5432 -d root_user_db  -U root -W    
Password:    
psql (17.6 (Debian 17.6-1))  
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off, ALPN: postgresql)  
Type "help" for help.  
  
root_user_db=#

查看所有用户

postgres=# \du  
                            List of roles  
Role name |                         Attributes                            
-----------+------------------------------------------------------------  
msf       |    
postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS  
root      | Superuser