How to Add and Delete Users in PostgreSQL

Introduction

Managing user accounts is a crucial aspect of database administration. This tutorial covers the steps to add and delete users in PostgreSQL, one of the most popular relational database management systems.

Prerequisites

Before we dive into the management of users, ensure you have the following:

• PostgreSQL installed
• Access to the PostgreSQL command line tool (psql)
• Administrative privileges to your database

Understanding PostgreSQL Roles

In PostgreSQL, a user is also referred to as a role. Roles can have login privileges and can own database objects. Before adding or removing users, understanding roles and privileges is fundamental.

Creating a User

Use the following SQL command to create a new user (role) with login privileges:

CREATE ROLE username WITH LOGIN PASSWORD 'password';

You can also add extra options, such as setting role expiration:

CREATE ROLE username WITH LOGIN PASSWORD 'password' VALID UNTIL 'YYYY-MM-DD';

Granting Privileges

After creating a user, you might want to grant them access to a database:

GRANT ALL PRIVILEGES ON DATABASE dbname TO username;

Advanced User Configuration

For finer control, assign specific privileges like creating databases or managing roles:

CREATE ROLE username WITH LOGIN CREATEDB CREATEROLE;

Alter and Set Role Attributes

You might need to alter user settings after creation. Here’s how you do it:

ALTER ROLE username WITH PASSWORD 'newpassword';

Creating a Role with Specific Privileges

To only provide access to specific tables, use the GRANT command meticulously:

GRANT SELECT ON table_name TO username;

Deleting a User

When a user’s access is no longer required, it’s simple to remove them:

DROP ROLE IF EXISTS username;

Revoke Privileges Before Deletion

Sometimes it’s advisable to revoke privileges before deletion, especially if credentials have been compromised:

REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;
DROP ROLE username;

Handling Orphaned Objects

Deleting users who own database objects can cause issues. Ensure objects are reassigned or dropped:

REASSIGN OWNED BY old_username TO new_username;
DROP OWNED BY old_username;

Managing Access for a User

Limit a user’s session duration:

ALTER ROLE username SET idle_in_transaction_session_timeout TO '5min';

What’s Next?

You can do more research on your own to improve the workflow of managing users in PostgreSQL. Below are some suggestions.

Scripting Multiple Users

For repetitive tasks, scripting can streamline user creation and removal. Consider using shell scripts or SQL script files for this purpose.

Using External Authentication Methods

To further secure your PostgreSQL server, external authentication methods like LDAP or Kerberos can be used – consult the PostgreSQL documentation for detailed steps on setting these up.

Automated User Management

On a larger scale or in a cloud environment, automating the process with tools like Ansible or Puppet for infrastructure as code (IaC) may be invoked.

Conclusion

Mastering how to add and delete users plays a significant role in PostgreSQL database management. By understanding how to manage roles effectively, administrators ensure optimal security and functionality of database operations.