In this tutorial, I will explain how to manage users and permissions in PostgreSQL.
In PostgreSQL, everything is built around the following concepts:Character.
When installing PostgreSQL on macOS for the first time, the scriptCreated a role with your macOS username, And list the permissions granted.
There are no users in PostgreSQL, only roles.
psql postgresIn your terminal, you will automatically log in to PostgreSQL with your macOS username to access the created role.
In my case
flaviocopesThe role has been created, I can use
Look? I have the followingRole attributesby default:
And I’m not a member of any other role (more on this later)
Create a new role
CREATE ROLE <role>;
CREATE ROLE testing;
We have a new role,
Cannot loginRole attributes. Our newly created user will not be able to log in.
You can do this by typing
psql postgres -U testing, But you will see this error:
To solve this problem, we must add
LOGINRole attributes at creation:
CREATE ROLE <role> WITH LOGIN;
If we delete the role using the following method:
DROP ROLE <role>;
WITH LOGINthis time:
DROP ROLE testing; CREATE ROLE testing WITH LOGIN;
We can see that
testingCharacters can log in because we don’t have
Cannot loginCharacter attributes this time:
Try to add command by
psql postgres -U testing:
=>Because we don't have
SuperuserCurrent character attributes.
Add a password to the role
On the previous
CREATE ROLECommand us to create a role without a password. Of course, having a (secure) password is very important. you can use it
CREATE ROLE <role> WITH LOGIN PASSWORD '<password>';
Another way to define roles is
LOGINAutomatically added attributes (effectively create users who can log in) for use
CREATE USER <role> PASSWORD '<password>';
Add role attributes to roles
Can be used later
Suppose we create a role without LOGIN attribute:
CREATE ROLE <username> PASSWORD '<password>';
We can add it using the following methods:
ALTER ROLE <role> WITH LOGIN;
Built-in character attributes
LOGINThe role attribute already exists to allow the role to log in.
But, what other built-in role attributes can we use?
NOLOGIN: Allow (or disallow) login to PostgreSQL
NOSUPERUSER: Allow (or disallow) super user authority. The database super user will bypass other permission checks, except in the following cases
LOGIN(Must be granted separately).
NOCREATEDB: Allow (or not allow) the ability to create a new database
NOCREATEROLE: Allow (or not allow) the ability to create new roles
NOCREATEUSER: Allow (or not allow) the ability to create new users
NOINHERIT: Allow (or not allow) to make privileges inheritable
NOREPLICATION: Grant (or not grant) copy permissions (advanced topics that we will not cover)
In PostgreSQL, there are no user groups.
Instead, you can create roles with specific permissions, and then grant those roles to other roles.
If roles have the INHERIT attribute, these roles will inherit the permissions granted to them.
Create a group role
To create a group role, type
CREATE ROLE <groupname>;
The syntax is the same as creating a role.
After creating a group role, you can use the following command to add the role to the group role
GRANT <groupname> TO <role>
For example, we can create a
flavioUser role, that is, the "employee" group role, and then assign the user to the group role:
CREATE USER flavio PASSWORD 'superSecret123