PostgreSQL User Management

PostgreSQL User management:

Default User:

Postgres when the installation is complete, a user named Postgres is automatically created in the operating system and the Postgres database, together with a database named Postgres.

1. Group roles:

A group role can be thought of as a set of database users. Group roles can have database objects (such as tables) and can assign permissions on those objects to other roles to control who has access to which objects.

--Create a role:

Create role Role_emp;

--View the roles in the system, such as:

Select Rolname from Pg_roles;

--Modify the syntax of the group role name:

Alter role group role name Rename to new group role name;

--Delete Group roles

The name of the drop role group character;

2, the role of various permissions

①: Login Permissions: Login

--Create a group role with logon permissions, such as:

Create role Zhang Login;

②:superuser (Super User)

The Superuser Superuser has the highest permissions on the database operation and can complete all permissions checks on the database.

--Create a database super user, such as:

Create role Zhang Superuser;

Note: Only superuser can have privileges to create super users.

③:createdb Creating a Database

--Create a group role with the CREATE database permissions, such as

Create role Zhang Createdb;

④:createrole Creating a role

--Create a role with permissions to create roles, such as:

Create role Zhang Createrole;

⑤: Password

Password permissions are required when the client authentication method requires a connection to the database. Common authentication methods include: Password,md5,crypt.

--Create a role with password permissions, such as:

Create role Zhang Password ' 123456 ';

3. Account Management

Method 1: Use the CreateUser command in the system command line to create

CreateUser username, such as [[email protected] ~]$ CreateUser Zhangfeng

Method 2: Use the Create role directive in the Postgressql command line

CREATE ROLE rolename;

Method 3: Use the Create User directive on the Postgressql command line

CREATE USER username;

Note: The difference between create and create role is that the user created by the Create username has logon rights by default and the Create role does not.

Example: Create a user Zhangfeng password 123456, and have CREATE DATABASE permissions and create role permissions, such as:

Create user zhangfeng password ' 123456 ' Createdb createrole;

--Delete User

Drop user Zhangfeng;

--Modify User password

Alter user Zhangfeng password ' 123456 ';

3. Group roles and user Rights management

--Authorization to group roles:

Alter role name permission 1, permission 2,,,;

For example, add permissions to create a data table and create a role for the ro_emp role:

Alter role ro_emp Createdb Createrole;

--Authorization to the user

Alter user username permission 1, permission 2,,;

Such as: Add the Create data table and create role permissions to the user,

Alter user Zhangfeng Createdb Createrole;

--Retract Group role permissions

Take back the Create data table and create role permissions for the role_emp role, such as:

Alter role role_emp Nocreatedb Nocreaterole;

--Recover user rights

Alter user Zhangfeng Nocreatedb Nocreaterole;

4. Database Rights Management

--Modify the owner of the database

ALTER DATABASE name owner to owner name;

Such as:

ALTER DATABASE TestDB owner to Zhangfeng;

--Increase the user's data table permissions

Grant permissions on the data table to the user name;

Such as:

Grant update on EMP to Zhangfeng;

PostgreSQL User Management