PostgreSQL user permissions

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.

By runningpsql postgresIn your terminal, you will automatically log in to PostgreSQL with your macOS username to access the created role.

In my caseflaviocopesThe role has been created, I can use\ducommand:

Look? I have the followingRole attributesby default:

  • Superuser
  • Create role
  • Create DB
  • Replication
  • Bypass RLS

And I’m not a member of any other role (more on this later)

Create a new role

useCREATE ROLEcommand:

CREATE ROLE <role>;

E.g:

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\qCommand, thenpsql postgres -U testing, But you will see this error:

To solve this problem, we must addLOGINRole attributes at creation:

CREATE ROLE <role> WITH LOGIN;

If we delete the role using the following method:

DROP ROLE <role>;

And addWITH LOGINthis time:

DROP ROLE testing;
CREATE ROLE testing WITH LOGIN;

We can see thattestingCharacters can log in because we don’t haveCannot loginCharacter attributes this time:

Try to add command by\qExit, thenpsql postgres -U testing:

Please notequicklyFrom=#To=>Because we don't haveSuperuserCurrent character attributes.

Add a password to the role

On the previousCREATE ROLECommand us to create a role without a password. Of course, having a (secure) password is very important. you can use itPASSWORDKey words:

CREATE ROLE <role> WITH LOGIN PASSWORD '<password>';

Create user

Another way to define roles isLOGINAutomatically added attributes (effectively create users who can log in) for useCREATE USER:

CREATE USER <role> PASSWORD '<password>';

Add role attributes to roles

Can be used laterALTER ROLEcommand.

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

We sawLOGINThe role attribute already exists to allow the role to log in.

But, what other built-in role attributes can we use?

  • LOGIN/NOLOGIN: Allow (or disallow) login to PostgreSQL
  • SUPERUSER/NOSUPERUSER: Allow (or disallow) super user authority. The database super user will bypass other permission checks, except in the following casesLOGIN(Must be granted separately).
  • CREATEDB/NOCREATEDB: Allow (or not allow) the ability to create a new database
  • CREATEROLE/NOCREATEROLE: Allow (or not allow) the ability to create new roles
  • CREATEUSER/NOCREATEUSER: Allow (or not allow) the ability to create new users
  • INHERIT/NOINHERIT: Allow (or not allow) to make privileges inheritable
  • REPLICATION/NOREPLICATION: Grant (or not grant) copy permissions (advanced topics that we will not cover)

Group role

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 roleGRANT:

GRANT <groupname> TO <role>

For example, we can create aflavioUser role, that is, the "employee" group role, and then assign the user to the group role:

CREATE USER flavio PASSWORD 'superSecret123
;create CharacterStaff;GrantStaffToflavio;

You can use the following methods to remove a role from a group role:

REVOKE <groupname> FROM <username>

example:

REVOKE employee FROM flavio;

Group role attributes

By default, adding a role to a group role willIs notMake the role inherit the attributes (permissions) of the group role.

You need to useINHERITAttributes.

Suppose you created an employee group role and assigned itCREATEDBAttributes:

CREATE ROLE employee WITH CREATEDB INHERIT;

Now use to create a new roleINHERIT:

CREATE ROLE flavio;
GRANT employee TO flavio;


More database tutorials: