Managing PostgreSQL Users and Permissions on VPS

What you will read?

1 Setting Up PostgreSQL on VPS
2 Creating and Managing PostgreSQL Users
3 Using GRANT and REVOKE Commands

PostgreSQL is a powerful, open-source relational database management system that allows for the creation of multiple users with varying levels of access and control. Managing users and their permissions is crucial for maintaining the security, organization, and efficiency of the database system. PostgreSQL offers a flexible approach to user management, enabling administrators to define who can access the database, what operations they can perform, and the specific databases and tables they can interact with.

In PostgreSQL, user management revolves around roles, which can represent individual users or groups of users. Roles can be granted various permissions, such as the ability to connect to the database, create objects like tables or views, or perform data manipulation tasks like SELECT, INSERT, UPDATE, and DELETE. Understanding how to create, manage, and control these roles is fundamental for maintaining a secure and well-organized PostgreSQL environment.

This section provides an overview of how PostgreSQL handles user management, including role creation, permission assignment, and best practices for securing your database from unauthorized access.

Setting Up PostgreSQL on VPS

Setting up PostgreSQL on a Virtual Private Server (VPS) is a common practice for hosting databases for web applications, development environments, or data analytics. The setup process typically involves installing PostgreSQL, configuring the database, and securing the server.

The installation process depends on the operating system running on your VPS. Here’s a general overview:

On Ubuntu/Debian-based systems:

Update the package list

sudo apt update

Install PostgreSQL:

sudo apt install postgresql postgresql-contrib

On CentOS/RHEL-based systems:

Install the PostgreSQL repository:

sudo yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-centos11-12.noarch.rpm

2.Install PostgreSQL:

sudo yum install postgresql-server postgresql-contrib

Starting and Enabling PostgreSQL Service

Once the installation is complete, start the PostgreSQL service and enable it to start on boot:

On Ubuntu/Debian-based systems

sudo systemctl start postgresql
sudo systemctl enable postgresql

On CentOS/RHEL-based systems:

On CentOS/RHEL-based systems:

Verifying the Installation

To check if PostgreSQL is running correctly, use the following command:

sudo systemctl status postgresql

You should see an active status indicating PostgreSQL is running.

Configuring PostgreSQL for Remote Access

By default, PostgreSQL is set to only allow local connections. If you need to access the database remotely:

Edit the PostgreSQL configuration file:

sudo nano /etc/postgresql/{version}/main/postgresql.conf

Change the listen_addresses parameter to allow connections from other machines:

listen_addresses = '*'

Adjust the client authentication file:

sudo nano /etc/postgresql/{version}/main/pg_hba.conf

Add a line to allow access from your remote IP:

host    all             all             {remote_ip}/32            md5

Securing PostgreSQL

Change the default postgres password:
```
sudo -u postgres psql
\password postgres
```
Consider setting up a firewall to restrict access to your VPS and database.

Creating and Managing PostgreSQL Users

Managing PostgreSQL users is crucial for ensuring the right access control to your databases. This process involves creating users, assigning roles, and managing permissions for secure access.

Creating a New User

To create a new PostgreSQL user, follow these steps:

Create a new user with a password:

CREATE USER username WITH PASSWORD 'password';

Exit the PostgreSQL shell:
```
\q
```

Assigning Roles and Permissions

Roles are used to group users and manage permissions:

Granting superuser privileges (for admin access):

ALTER USER username WITH SUPERUSER;

Granting database creation privileges:

ALTER USER username LOGIN;

Granting Database Access
To give a user access to a specific database:

Log into the PostgreSQL shell.
Grant access to the user for a specific database:
```
GRANT CONNECT ON DATABASE dbname TO username;
```

Revoking Permissions
If you need to revoke permissions or roles from a user:

Log into the PostgreSQL shell.

Revoke permissions:

REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;

Managing User Passwords
To change a user’s password:

ALTER USER username WITH PASSWORD 'newpassword';

Deleting a User
To delete a user completely:

Log into the PostgreSQL shell.
Drop the user:
```
DROP USER username;
```

Using GRANT and REVOKE Commands

The GRANT and REVOKE commands in PostgreSQL are essential for managing permissions and access control. They allow database administrators to assign and remove privileges from users or roles, ensuring the right level of access to databases, tables, and other database objects.

Using the GRANT Command
The GRANT command is used to assign specific permissions to a user or role. Here’s how it works:

Granting Privileges on a Database
To grant a user access to a database, use the following syntax:
```
GRANT CONNECT ON DATABASE dbname TO username;
```
Granting Privileges on a Table
To grant a user privileges on a specific table:
```
GRANT SELECT, INSERT, UPDATE ON TABLE tablename TO username;
```
Granting All Privileges on a Database
To give a user all privileges on a database:
```
GRANT ALL PRIVILEGES ON DATABASE dbname TO username;
```
Granting Privileges on Schema Objects
If you want to grant access to schema objects (tables, views, etc.):
```
GRANT USAGE ON SCHEMA schemaname TO username;
```
Granting Privileges to a Role
To grant privileges to a role (group of users), simply replace the user with the role:
```
GRANT SELECT ON TABLE tablename TO role_name;
```
Using the REVOKE Command
The REVOKE command is used to remove previously granted privileges from users or roles. Here’s how to revoke permissions:
1. Revoking Privileges on a Database
  To revoke a user’s access to a database:
```
REVOKE CONNECT ON DATABASE dbname FROM username;
```
2. Revoking Privileges on a Table
  To revoke privileges on a table:
```
REVOKE SELECT, INSERT, UPDATE ON TABLE tablename FROM username;
```
3. Revoking All Privileges on a Database
  To remove all privileges on a database:
```
REVOKE ALL PRIVILEGES ON DATABASE dbname FROM username;
```
4. Revoking Privileges on Schema Objects
  To revoke access to schema objects:
```
REVOKE USAGE ON SCHEMA schemaname FROM username;
```
5. Revoking Privileges from a Role
  To revoke privileges from a role:
```
REVOKE SELECT ON TABLE tablename FROM role_name;
```

Managing PostgreSQL Users and Permissions on VPS

Table of Contents

Setting Up PostgreSQL on VPS

On Ubuntu/Debian-based systems:

On CentOS/RHEL-based systems:

Starting and Enabling PostgreSQL Service

On Ubuntu/Debian-based systems

On CentOS/RHEL-based systems:

Verifying the Installation

Configuring PostgreSQL for Remote Access

Securing PostgreSQL

Creating and Managing PostgreSQL Users

Creating a New User

Assigning Roles and Permissions

Granting superuser privileges (for admin access):

Granting database creation privileges:

Using GRANT and REVOKE Commands

Related Posts