Introduction to PostgreSQL user roles and their attributes

Source: Internet
Author: User

Introduction to PostgreSQL user roles and their attributes

1. Users created by create role do not carry the LOGIN attribute by default, while users created by create user have the LOGIN attribute by default, as shown below:

  1. Postgres= # Create role pg_test_user_1;/* the LOGIN attribute is not included by default */
  2. CREATE ROLE
  3. Postgres= # Create user pg_test_user_2;/* Default LOGIN attribute */
  4. CREATE ROLE
  5. Postgres= # \ Du
  6. List of roles
  7. Role name | Attributes | Member
  8. ---------------- + -------------- + -----------
  9. Pg_test_user_1 | Cannot login | {}
  10. Pg_test_user_2 || {}
  11. Postgres | Superuser | {}
  12. : Create role
  13. : Create DB
  14. Postgres= #

2. Assign role attributes when creating a user

  1. Postgres= # Create role pg_test_user_3 CREATEDB;/* attributes for creating a database */
  2. CREATE ROLE
  3. Postgres= # \ Du
  4. List of roles
  5. Role name | Attributes | Member
  6. ---------------- + -------------- + -----------
  7. Pg_test_user_1 | Cannot login | {}
  8. Pg_test_user_2 || {}
  9. Pg_test_user_3 | Create DB | {}
  10. : Cannot login
  11. Postgres | Superuser | {}
  12. : Create role
  13. : Create DB
  14. Postgres= # Create role pg_test_user_4 createdb password '000000';/* attributes for creating a database and logging on with a PASSWORD */
  15. CREATE ROLE
  16. Postgres= # \ Du
  17. List of roles
  18. Role name | Attributes | Member
  19. ---------------- + -------------- + -----------
  20. Pg_test_user_1 | Cannot login | {}
  21. Pg_test_user_2 || {}
  22. Pg_test_user_3 | Create DB | {}
  23. : Cannot login
  24. Pg_test_user_4 | Create DB | {}
  25. : Cannot login
  26. Postgres | Superuser | {}
  27. : Create role
  28. : Create DB
  29. Postgres= #
Postgres = # create role pg_test_user_3 CREATEDB; /* Attributes for creating a database */CREATE ROLEpostgres = # \ du List of roles Role name | Attributes | Member of ---------------- + ------------ + ----------- pg_test_user_1 | Cannot login | {} items | {} pg_test_user_3 | Create DB | {}: cannot login postgres | Superuser | {}: Create role: Create DBpostgres = # create role pg_test_user_4 createdb password '123 '; /* Attributes for creating a database and logging in with a password */CREATE ROLEpostgres = # \ du List of roles Role name | Attributes | Member of ---------------- + ------------ + ----------- pg_test_user_1 | Cannot login | {} pg_test_user_2 | |{} pg_test_user_3 | Create DB | {}: cannot login pg_test_user_4 | Create DB |{}: Cannot login S | Superuser |{}: Create role: Create DBpostgres = #

3. Grant various permissions to existing users

Use alter role.

  1. Postgres= # \ Du
  2. List of roles
  3. Role name | Attributes | Member
  4. ---------------- + -------------- + -----------
  5. Pg_test_user_3 | Create DB | {}
  6. : Cannot login
  7. Pg_test_user_4 | Create DB | {}
  8. : Cannot login
  9. Postgres | Superuser | {}
  10. : Create role
  11. : Create DB
  12. Postgres= # Alter role pg_test_user_3 with login;/* grant logon permission */
  13. ALTER ROLE
  14. Postgres= # \ Du
  15. List of roles
  16. Role name | Attributes | Member
  17. ---------------- + -------------- + -----------
  18. Pg_test_user_3 | Create DB | {}
  19. Pg_test_user_4 | Create DB | {}
  20. : Cannot login
  21. Postgres | Superuser | {}
  22. : Create role
  23. : Create DB
  24. Postgres= # Alter role pg_test_user_4 with createrole;/* grant the ROLE creation permission */
  25. ALTER ROLE
  26. Postgres= # \ Du
  27. List of roles
  28. Role name | Attributes | Member
  29. ---------------- + -------------- + -----------
  30. Pg_test_user_3 | Create DB | {}
  31. Pg_test_user_4 | Create role | {}
  32. : Create DB
  33. : Cannot login
  34. Postgres | Superuser | {}
  35. : Create role
  36. : Create DB
  37. Postgres= # Alter role pg_test_user_4 with password '000000';/* change the PASSWORD */
  38. ALTER ROLE
  39. Postgres= # Alter role pg_test_user_4 valid until 'Jul 7 14:00:00 2012 + 8';/* set the ROLE validity period *
  40. ALTER ROLE

4. view the information in the role table:

  1. Postgres= # SELECT * FROM pg_roles;
  2. Rolname | rolsuper | rolinherit | rolcreaterole | rolcreatedb | rolcatupdate | rolcanlogin | rolconnlimit | rolpassword | rolvaliduntil | rol
  3. Config | oid
  4. ---------------- + ---------- + ------------ + --------------- + ------------- + -------------- + --------------- + -------------- + ------------- + -------------------- + ----
  5. ------- + -------
  6. Postgres | t |-1 | ********* |
  7. | 10
  8. Pg_test_user_3 | f | t |-1 | ******** |
  9. | 16390
  10. Pg_test_user_4 | f | t | f |-1 | ********* | 14:00:00 + 08 |
  11. | 16391
  12. (3 rows)
  13. Postgres= #

5. About the alter role statement:

  1. ALTER ROLE
  2. Name
  3. Alter role -- modify a database ROLE
  4. Syntax
  5. Alter role name [[WITH] option [...]
  6. The option here can be:
  7. SUPERUSER | NOSUPERUSER
  8. | CREATEDB | NOCREATEDB
  9. | CREATEROLE | NOCREATEROLE
  10. | CREATEUSER | NOCREATEUSER
  11. | INHERIT | NOINHERIT
  12. | LOGIN | NOLOGIN
  13. | Connection limit connlimit
  14. | [ENCRYPTED | UNENCRYPTED] PASSWORD 'Password'
  15. | Valid until 'timestamp'
  16. Alter role name rename to newname
  17. Alter role name SET configuration_parameter {TO |=}{ value | DEFAULT}
  18. Alter role name RESET configuration_parameter description
  19. Alter role modifies the attributes of a database ROLE.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.