PostgreSQL user, role, and permission management

PostgreSQL permissions are divided into two parts: ldquo; system permission rdquo; or database user attributes, which can be granted to role or user (the difference between the two lies in login permissions)

PostgreSQL permissions are divided into two parts: ldquo; system permission rdquo; or database user attributes, which can be granted to role or user (the difference between the two lies in login permissions)

); Some are the operation permissions on database objects. The super user is not checked for permissions, and the others are acl-based. For database objects, only the owner and the Super User can perform any operations, and other operations are performed using the acl ..

You can use the following command to create and delete roles,

Create role name;

Drop role name;

For convenience, you can also directly call the createuser and dropuser programs on shell commands. These tools provide encapsulation for the corresponding commands:

Createuser name

Dropuser name

Database objects have the following permissions: SELECT, INSERT, UPDATE, DELETE, RULE, REFERENCES, TRIGGER, CREATE, TEMPORARY, EXECUTE, and USAGE. For details, see the following definitions.

Of privilege bits */

# Define ACL_INSERT (1 <0)/* forrelations */

# DefineACL_SELECT (1 <1)

# DefineACL_UPDATE (1 <2)

# DefineACL_DELETE (1 <3)

# DefineACL_TRUNCATE (1 <4)

# DefineACL_REFERENCES (1 <5)

# DefineACL_TRIGGER (1 <6)

# DefineACL_EXECUTE (1 <7)/* for functions */

# DefineACL_USAGE (1 <8)/* for ages, namespaces, FDWs, and

* Servers */

# DefineACL_CREATE (1 <9)/* for namespaces and databases */

# DefineACL_CREATE_TEMP (1 <10)/* for databases */

# DefineACL_CONNECT (1 <11)/* for databases */

# DefineN_ACL_RIGHTS 12/* 1 plus the last 1 <

# DefineACL_NO_RIGHTS 0

/* Currently, SELECT... for update/for share requires UPDATE privileges */

# DefineACL_SELECT_FOR_UPDATE ACL_UPDATE

We can assign the object permission to all roles in the system with a special name "PUBLIC. Write "ALL" at the position where the permission is declared to grant ALL permissions for the object to the target role.

Beigang = # grantall on schema csm_ca to public;

GRANT

Beigang = # revoke all on schema csm_ca frompublic;

REVOKE

Beigang = #

The all permission of each object is defined as follows:

/*

* Bitmasks defining "allrights" for each supported object type

*/

# DefineACL_ALL_RIGHTS_COLUMN (ACL_INSERT | ACL_SELECT | ACL_UPDATE | ACL_REFERENCES)

# DefineACL_ALL_RIGHTS_RELATION (ACL_INSERT | ACL_SELECT | ACL_UPDATE | ACL_DELETE | ACL_TRUNCATE | ACL_REFERENCES | ACL_TRIGGER)

# DefineACL_ALL_RIGHTS_SEQUENCE (ACL_USAGE | ACL_SELECT | ACL_UPDATE)

# DefineACL_ALL_RIGHTS_DATABASE (ACL_CREATE | ACL_CREATE_TEMP | ACL_CONNECT)

# Define ACL_ALL_RIGHTS_FDW (ACL_USAGE)

# DefineACL_ALL_RIGHTS_FOREIGN_SERVER (ACL_USAGE)

# DefineACL_ALL_RIGHTS_FUNCTION (ACL_EXECUTE)

# DefineACL_ALL_RIGHTS_LANGUAGE (ACL_USAGE)

# DefineACL_ALL_RIGHTS_LARGEOBJECT (ACL_SELECT | ACL_UPDATE)

# DefineACL_ALL_RIGHTS_NAMESPACE (ACL_USAGE | ACL_CREATE)

# DefineACL_ALL_RIGHTS_TABLESPACE (ACL_CREATE)

For user attributes, see:

Interface for accessing information about database roles. It is only a public readable view of the pg_authid table and fills the password field with a blank space.

Table 42-39.pg_roles Field

Name

Type

Reference

Description

Rolname

Name

Role name

Rolsuper

Bool

Roles with Super User Permissions

Rolcreaterole

Bool

You can create more roles.

Rolcreatedb

Bool

Roles that can create databases

Rolcatupdate

Bool

You can directly update the role of a system table. (Unless this field is true, the Super User cannot do this .)

Rolcanlogin

Bool

A role that can be logged on, that is, the role can be assigned an identifier for initiating session authentication.

Rolpassword

Text

Not a password (always ********)

Rolvaliduntil

Timestamptz

Password Expiration date (for password authentication only); if there is no expiration time, It is NULL

Rolconfig

Text []

Set the default session for variables during runtime