0

When I create a USER with "CREATE USER user1 WITH PASSWORD 'aaaaaaaa' It is also creating a ROLE in PG_ROLES table with role name as same as 'user1'

Is it a trigger peforrming the this? Or is it default behavior?

Krishna
  • 501
  • 1
  • 8
  • 17

1 Answers1

2

Assuming you are not running a very old (<8.1) version of Postgresql this is explained by the documentation for CREATE USER:

CREATE USER is now an alias for CREATE ROLE. The only difference is that when the command is spelled CREATE USER, LOGIN is assumed by default, whereas NOLOGIN is assumed when the command is spelled CREATE ROLE.

Further details are available in the Database Roles section of the documentation:

The concept of roles subsumes the concepts of “users” and “groups”. In PostgreSQL versions before 8.1, users and groups were distinct kinds of entities, but now there are only roles. Any role can act as a user, a group, or both.

The system catalogs docs explain that:

The view pg_roles provides access to information about database roles.

So based upon this it is to be expected that running CREATE USER will have an impact on pg_roles.

Brits
  • 14,829
  • 2
  • 18
  • 31
  • OK, Thanks. Why they are making systems which literally confuses user. Keeping User and Roles as distinct and associate is good, and used universally even on AWS too. If they want to utilize old code, they can achieve user friendly too. Not a big deal, Thanks. – Krishna Jan 21 '20 at 22:50
  • It can be confusing but is well documented. The rationale for the change is discussed in the responses to [this question](https://stackoverflow.com/questions/8485387/why-did-postgresql-merge-users-and-groups-into-roles). – Brits Jan 21 '20 at 23:03
  • Can I safely assume that, If I create 100 user, apart from creating 100 new entries on PG_USER table , it also adds 100 new ROLES on PG_ROLES table (Ofcourse I need to GRANT proper ROLES to created USER later). Am I right? – Krishna Jan 22 '20 at 19:46
  • As per my answer "there are only roles"; commands related to users are there for compatibility but when you run "CREATE USER" this actually addes a role. The information on roles is stored in the system catalogs (tables holding config info) ```pg_authid``` and ```pg_db_role_setting```. Both ```PG_USER``` and ```PG_ROLES``` are views (not tables) that access data from those catalogs (```PG_USER``` does this indirectly). So adding 100 users will 'add' 100 roles (and adding a role with login athority will 'add' a user). – Brits Jan 22 '20 at 21:32
  • Thanks Brits, Appreciate your answers. – Krishna Jan 23 '20 at 19:09