Thread: Roles versus users

I am creating an application that will need to have access control. There
will basically be the groups (roles ?):

* normal user (can do insert on a limited sate of tables, and select on a
slightly larger set

* project manager will have some increased insert and select capabilities

* sysadmin will be able to do select and insert on all tables in the schema

There will be more than one person in each of these groups. My original
intent was to create roles, and assign users to appropriate roles, using
inheritance to add increasingly greater capabilities. That is the inheritance
would look like this

normal user <- project manager <- sysadmin

But, I have run up ion a note in the documentation that says that create user
is actually a synonym for create role.

So, should I just create roles for each user?


-- 
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
                        -- Benjamin Franklin

On 8/17/19 4:56 PM, stan wrote:
> I am creating an application that will need to have access control. There
> will basically be the groups (roles ?):
> 
> * normal user (can do insert on a limited sate of tables, and select on a
> slightly larger set
> 
> * project manager will have some increased insert and select capabilities
> 
> * sysadmin will be able to do select and insert on all tables in the schema
> 
> There will be more than one person in each of these groups. My original
> intent was to create roles, and assign users to appropriate roles, using
> inheritance to add increasingly greater capabilities. That is the inheritance
> would look like this
> 
> normal user <- project manager <- sysadmin
> 
> But, I have run up ion a note in the documentation that says that create user
> is actually a synonym for create role.

You need to read the rest of the paragraph:

"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."

https://www.postgresql.org/docs/11/sql-createrole.html

"CREATE ROLE adds a new role to a PostgreSQL database cluster. A role is 
an entity that can own database objects and have database privileges; a 
role can be considered a “user”, a “group”, or both depending on how it 
is used. ..."


> 
> So, should I just create roles for each user?
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com