PostgreSQL: Complete Guide to Managing Users, Permissions & Databases

Introduction

PostgreSQL is a powerful, reliable, and open-source relational database management system (RDBMS) widely used in applications of all sizes. One of its strengths lies in its flexibility and robustness, but to make the most of it, it's crucial to understand how to manage users, permissions, and databases. Proper security management is critical to protect your data from unauthorized access and to maintain system integrity.

In this in-depth guide, we will walk you through the essential procedures for effective access and data management in PostgreSQL, providing practical examples and useful tips for a secure and organized approach. This article is optimized for those with a basic understanding of IT who want to improve their PostgreSQL skills.

What is a User (or Role) in PostgreSQL?

In PostgreSQL, the concept of a "user" is actually managed through "roles" (ROLE). A role is an entity that can have access privileges to database objects and can be used to represent a user, a group of users, or an application. When we talk about PostgreSQL user management, we are actually referring to role management.

• Roles can be "login roles" (real users with the ability to connect to the database) or "group roles" (used to group privileges).
• Every user who connects to PostgreSQL must be associated with a role.
• Permissions are assigned to roles, and users belonging to that role inherit those permissions.

User Management

Creating and managing users is the first step in controlling who can access your PostgreSQL database.

Creating a New User

To create a user in PostgreSQL, you use the CREATE ROLE command or its alias CREATE USER (which is practically the same thing, with the only difference being that CREATE USER implies LOGIN by default unless specified otherwise).

Basic syntax:

CREATE ROLE role_name [WITH OPTIONS];

Example for creating a new PostgreSQL user with a password and login permissions:

CREATE USER my_user WITH PASSWORD 'my_secure_password';

Common options:

• LOGIN: Allows the user to connect to the database. (Implied with CREATE USER).
• NOLOGIN: The user cannot connect (useful for group roles).
• PASSWORD 'string': Assigns a password to the user.
• ENCRYPTED PASSWORD 'string': Similar to PASSWORD, but forces encryption.
• VALID UNTIL 'timestamp': Sets an expiration date for the password or user.
• SUPERUSER: Grants all privileges (use with extreme caution).
• CREATEROLE: Allows the user to create new roles.
• CREATEDB: Allows the user to create databases in PostgreSQL.

Example of a user who can create databases but not other roles:

CREATE USER dev_user WITH PASSWORD 'secret123' CREATEDB NOCREATEROLE;

Modifying an Existing User

To change a user password in PostgreSQL or other properties, use the ALTER ROLE (or ALTER USER) command.

ALTER USER user_name WITH PASSWORD 'new_password';

To rename a user:

ALTER ROLE old_name RENAME TO new_name;

To change database/role creation privileges:

ALTER USER another_user WITH CREATEDB;
ALTER USER another_user WITH NOCREATEDB;

Deleting a User

To remove a user in PostgreSQL, use the DROP ROLE (or DROP USER) command. Make sure the user does not own any objects in the database; otherwise, you will need to transfer ownership or delete them first.

DROP USER user_name;

If the user owns objects, you may need to change ownership first:

REASSIGN OWNED BY user_name TO new_owner;
DROP OWNED BY user_name; -- Deletes objects owned by the user that were not reassigned.

Then, proceed with DROP USER user_name;

Viewing Existing Users

To view users in PostgreSQL and their attributes, you can use the following methods:

• From the psql console, use the \du command (or \du+ for more details):

  \du

• Through an SQL query on the pg_roles system view:

  SELECT rolname FROM pg_roles;

  SELECT rolname, rolsuper, rolcreaterole, rolcreatedb, rolcanlogin FROM pg_roles;

Permission (Privilege) Management

PostgreSQL permission management is the core of database security. Permissions control what operations a user (or role) can perform on specific database objects like tables, views, sequences, functions, and the databases themselves.

GRANT: Assigning Permissions

The GRANT command is used to assign permissions to a user in PostgreSQL. The general syntax is:

GRANT privilege [, ...] ON object_type object [, ...] TO role [, ...] [WITH GRANT OPTION];

Examples:

• Grant all privileges on a table:

  GRANT ALL PRIVILEGES ON TABLE my_table TO my_user;

• Grant only read (SELECT) permissions on a view:

  GRANT SELECT ON VIEW my_view TO read_only_user;

• Grant connection permissions to a database:

  GRANT CONNECT ON DATABASE my_database TO app_user;

• Grant create permissions on a schema (to allow the user to create tables within it):

  GRANT CREATE ON SCHEMA public TO developer_user;

• Permissions on all future tables in a schema (useful to avoid granting permissions every time):

  ALTER DEFAULT PRIVILEGES FOR ROLE schema_owner IN SCHEMA my_schema GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO app_user;

REVOKE: Revoking Permissions

The REVOKE command is used to revoke permissions in PostgreSQL that were previously granted. The syntax is similar to GRANT:

REVOKE privilege [, ...] ON object_type object [, ...] FROM role [, ...];

Example:

REVOKE DELETE ON TABLE my_table FROM my_user;

To revoke a permission with the GRANT OPTION:

REVOKE GRANT OPTION FOR SELECT ON TABLE my_table FROM admin_user;

Common Permission Types

• SELECT: Allows reading data.
• INSERT: Allows inserting new rows.
• UPDATE: Allows modifying existing rows.
• DELETE: Allows deleting rows.
• TRUNCATE: Allows emptying a table.
• REFERENCES: Allows creating foreign keys.
• USAGE: Permission on sequences (for nextval()), schemas (to access objects within), and data types/domains.
• CONNECT: Permission to connect to a database.
• CREATE: Permission to create objects within a database or schema.
• TEMPORARY (or TEMP): Permission to create temporary tables.
• EXECUTE: Permission to execute functions.
• ALL PRIVILEGES: Grants all available permissions for the specified object.

Viewing Assigned Permissions

To view permissions in PostgreSQL, you can use the following psql commands:

• To view permissions on tables and sequences:

  \dp

• To view details of a table, including permissions:

  \d my_table

You can also query the system catalog views or information_schema for more detailed information, although they are more complex:

SELECT
    grantee,
    table_schema,
    table_name,
    privilege_type
FROM
    information_schema.table_privileges
WHERE
    table_name = 'your_table_name';

For a deep understanding of PostgreSQL GRANT and REVOKE, consult the official PostgreSQL documentation: GRANT and REVOKE.

Database Management

In addition to users and permissions, it's essential to know how to manage PostgreSQL databases, including their creation, deletion, and ownership.

Creating a New Database

To create a database in PostgreSQL, use the CREATE DATABASE command. Only a superuser or a user with the CREATEDB privilege can perform this operation.

CREATE DATABASE new_database_name;

You can specify the owner and other options:

CREATE DATABASE my_database OWNER my_db_user ENCODING 'UTF8' LC_COLLATE 'en_US.UTF-8' LC_CTYPE 'en_US.UTF-8' TEMPLATE template0;

For a PostgreSQL database creation with a specific user, it is good practice to assign the owner immediately.

Deleting a Database

To remove a database in PostgreSQL, use the DROP DATABASE command. Make sure no one is connected to the database you want to delete.

DROP DATABASE database_to_delete_name;

It is advisable to connect to another database (e.g., postgres) before deleting a database. Otherwise, you may encounter errors.

Changing a Database Owner

To change the PostgreSQL database owner, use ALTER DATABASE:

ALTER DATABASE database_name OWNER TO new_owner;

The new owner must be an existing role.

Security Best Practices

PostgreSQL database security is a fundamental aspect. Here are some recommendations:

• Principle of Least Privilege: Assign users only the permissions strictly necessary to perform their functions. Do not grant SUPERUSER privileges unless absolutely essential. This significantly reduces the risk in case an account is compromised.
• Strong Passwords and Rotation: Use complex and hard-to-guess passwords. Consider a policy of regular password rotation.
• Use of Roles (Groups): Organize users into roles. Assign permissions to roles rather than individual users. This simplifies the management and revocation of permissions when a user changes roles or leaves the organization. This is the heart of PostgreSQL roles and users.
• Auditing and Monitoring: Regularly check PostgreSQL logs to detect suspicious activity or unauthorized access attempts. Perform a periodic PostgreSQL permissions audit to ensure they are still appropriate.
• Limited Remote Access: If you need to configure remote user access in PostgreSQL, make sure to correctly configure the pg_hba.conf file to limit access only from authorized IP addresses and, if possible, use SSL/TLS connections.
• Do not use the postgres user for applications: The postgres user is the default superuser and should not be used for application connections. Create dedicated users with minimal privileges.

Frequently Asked Questions (FAQ)

Q: What is the difference between CREATE USER and CREATE ROLE?

A: In PostgreSQL, CREATE USER is essentially an alias for CREATE ROLE WITH LOGIN. So, CREATE USER creates a role that can connect to the database, while CREATE ROLE by default creates a role that cannot, unless you specify WITH LOGIN.

Q: How do I grant a user permissions on all existing and future tables in a schema?

A: For existing tables, use GRANT on each table (or on all tables in a schema with a loop or script). For future tables, use ALTER DEFAULT PRIVILEGES, specifying the desired permissions.

Q: What happens if I delete a user who owns objects?

A: PostgreSQL will not allow you to delete a user if they own databases or other objects. You must first change the ownership of the objects with REASSIGN OWNED BY or delete them with DROP OWNED BY, and only then can you delete the PostgreSQL user.

Q: How can I connect to PostgreSQL as a specific user?

A: You can use the psql client with the -U (user) option: psql -U user_name -d database_name. You will be prompted for a password if authentication is not configured via .pgpass or environment variables.

Conclusion and Call to Action

Managing users, permissions, and databases in PostgreSQL is a fundamental skill for anyone working with this powerful data management system. Understanding the concepts of roles, the correct use of GRANT and REVOKE commands, and security best practices will allow you to build robust and reliable applications while protecting your most valuable information.

If you wish to delve deeper into these topics, optimize your database performance, or need advanced support in configuring and securing your PostgreSQL systems in cloud or complex environments, our team of experts is at your disposal.

Don't leave your data security to chance. Contact us today for a personalized consultation on your Cloud, Linux, and DevOps infrastructure! Simplify management, strengthen security, and maximize the efficiency of your systems.

Visit our website or email us the information about our Cloud, Linux, and DevOps Consulting services. We are here to help you navigate technical challenges and achieve your goals successfully!