# PostgreSQL

### Overview

The Veza PostgreSQL integration provides visibility into your PostgreSQL database security posture by discovering resources, permissions, and access controls. This integration enables security teams and database administrators to:

* Map and monitor access across databases, schemas, tables, and other database objects
* Analyze role-based access control (RBAC) including privilege inheritance
* Identify users and groups with elevated privileges
* Track permission delegation through grant options
* Monitor administrative capabilities and sensitive operations

For managed PostgreSQL instances, see [Azure PostgreSQL](/4yItIzMvkpAvMVFAamTf/integrations/integrations/azure/azure-postgresql.md) or [RDS PostgreSQL](/4yItIzMvkpAvMVFAamTf/integrations/integrations/aws/rds-postgresql.md) documentation.

### Prerequisites

Before configuring the integration, you must have:

* Network connectivity from Veza to your PostgreSQL server via:
  * A [deployed Insight Point](/4yItIzMvkpAvMVFAamTf/integrations/connectivity/insight-point.md) in your network (recommended for production)
  * Direct connection using Veza's internal Insight Point (suitable for testing)
* A PostgreSQL service account with appropriate permissions
* PostgreSQL server connection information (hostname, port, and database name)

### Creating the Service Account

The integration requires a PostgreSQL user with SELECT privileges on system catalog tables. To create a properly-scoped service account:

1. Log in to your PostgreSQL server with an administrator account:

   ```sql
   psql -U postgres
   ```
2. Create a dedicated service account with a strong password:

   ```sql
   CREATE ROLE veza_service WITH LOGIN PASSWORD 'your-strong-password-here';
   ```
3. Grant minimum required permissions:

   ```sql
   -- Grant read access to required system catalogs
   GRANT SELECT ON
       pg_catalog.pg_user,
       pg_catalog.pg_group,
       pg_catalog.pg_namespace,
       pg_catalog.pg_class,
       pg_catalog.pg_database,
       pg_catalog.pg_auth_members,
       pg_catalog.pg_attribute,
       pg_catalog.pg_roles,
       pg_catalog.pg_trigger,
       pg_catalog.pg_proc,
       pg_catalog.pg_collation,
       pg_catalog.pg_conversion,
       pg_catalog.pg_type,
       pg_catalog.pg_event_trigger,
       pg_catalog.pg_extension,
       pg_catalog.pg_foreign_data_wrapper,
       pg_catalog.pg_foreign_table,
       pg_catalog.pg_language,
       pg_catalog.pg_largeobject_metadata,
       pg_catalog.pg_operator,
       pg_catalog.pg_opclass,
       pg_catalog.pg_opfamily,
       pg_catalog.pg_policy,
       pg_catalog.pg_publication,
       pg_catalog.pg_sequence,
       pg_catalog.pg_foreign_server,
       pg_catalog.pg_statistic_ext,
       pg_catalog.pg_subscription,
       pg_catalog.pg_tablespace,
       pg_catalog.pg_ts_config,
       pg_catalog.pg_ts_dict,
       pg_catalog.pg_parameter_acl
   TO veza_service;
   ```
4. Grant additional permissions to collect authentication type information for PostgreSQL users:

   ```sql
   GRANT SELECT ON pg_hba_file_rules TO veza_service;
   GRANT EXECUTE ON FUNCTION pg_hba_file_rules() TO veza_service;
   ```

   <div data-gb-custom-block data-tag="hint" data-style="info" class="hint hint-info"><p>These grants are per-database and must be run while connected to the database Veza uses for extraction. By default, Veza connects to the <code>postgres</code> database.</p></div>
5. Verify the service account permissions:

   ```sql
   -- Connect as service account
   \connect - veza_service

   -- Test access to system catalogs
   SELECT * FROM pg_catalog.pg_roles LIMIT 1;
   ```

### Configuring PostgreSQL on the Veza Platform

1. In Veza, go to the **Integrations** page
2. Click *Add Integration* and search for PostgreSQL
3. Click **Next** to add an integration
4. Configure the following settings:
   * **Insight Point**: Choose whether to use the default data plane or a deployed Insight Point
   * **Name**: A friendly name to identify the unique integration
   * **Host**: The hostname or IP address of the PostgreSQL server.
   * **Port**: The port number on which the PostgreSQL server is listening (default is usually 5432).
   * **Username**: The username to authenticate with the PostgreSQL server.
   * **Password**: The password associated with the provided username.
   * **Database**: The name of the specific database to connect to.
5. Click *Create Integration* to save the configuration

### Enabling Lifecycle Management

To provide access privilege to the Postgres DB (target system) as a managed service, perform the following:

```sql

   ALTER ROLE veza_service WITH SUPERUSER CREATEROLE CREATEDB REPLICATION;

```

This privilege enables:

* User provisioning (CREATE ROLE with LOGIN)
* User deprovisioning (ALTER ROLE NOLOGIN)
* Group management (CREATE/DROP ROLE)
* Group membership management (GRANT/REVOKE role membership)

See [Lifecycle Management: PostgreSQL](/4yItIzMvkpAvMVFAamTf/integrations/integrations/postgresql/provisioning.md) for more information.

### Notes and Supported Entities

PostgreSQL (since version 8.1) implements all users and groups as roles, using the following model:

* The login attribute determines the role type: "true" for users, "false" for groups
* Roles can receive privileges either directly or through inheritance
* Users can join groups as members, inheriting all group privileges
* Roles can grant their privileges to other roles. The receiving role (grantee) inherits these privileges when their inherit attribute is "true"

Veza models these PostgreSQL resources and access controls using the following graph entities:

#### SQL Server Objects

**PostgreSQL Instance**

The server instance that hosts databases and manages authentication.

| Attribute          | Description                                                                |
| ------------------ | -------------------------------------------------------------------------- |
| server\_id         | The unique identifier for the PostgreSQL server                            |
| azure\_tenant\_id  | The Azure tenant ID for Azure-hosted PostgreSQL instances                  |
| aws\_account\_id   | The AWS account ID associated with the PostgreSQL instance, if applicable  |
| aws\_account\_name | The name of the AWS account associated with the PostgreSQL server instance |

**PostgreSQL User**

Represents a role with login privileges.

| Attribute                              | Description                                                                                                                                       |
| -------------------------------------- | ------------------------------------------------------------------------------------------------------------------------------------------------- |
| is\_super\_user                        | Indicates whether the user has superuser privileges                                                                                               |
| can\_create\_db                        | Specifies if the user can create new databases                                                                                                    |
| can\_create\_role                      | Indicates if the user has the ability to create roles                                                                                             |
| can\_initiate\_streaming\_replication  | Defines if the user can initiate streaming replication                                                                                            |
| can\_bypass\_all\_row\_level\_security | Specifies whether the user can bypass row-level security                                                                                          |
| external\_account\_type                | Describes the type of external account associated with the user, if any                                                                           |
| iam\_connect\_enabled                  | Indicates if AWS IAM authentication is enabled for the user                                                                                       |
| aws\_account\_id                       | Specifies the AWS account ID if applicable                                                                                                        |
| aws\_account\_name                     | Provides the name of the AWS account associated with the user                                                                                     |
| authentication\_type                   | The authentication method for this user, such as `iam`, `md5`, or `scram-sha-256`. Requires additional grants on `pg_hba_file_rules` to populate. |

**PostgreSQL Group**

Represents a role without login privileges, used for grouping permissions.

| Attribute                              | Description                                                           |
| -------------------------------------- | --------------------------------------------------------------------- |
| is\_super\_group                       | Indicates whether the group has superuser privileges                  |
| can\_create\_db                        | Specifies if the group can create new databases                       |
| can\_create\_role                      | Indicates if the group has the ability to create roles                |
| can\_initiate\_streaming\_replication  | Defines if the group can initiate streaming replication               |
| can\_bypass\_all\_row\_level\_security | Specifies whether the group can bypass row-level security             |
| aws\_account\_id                       | Specifies the AWS account ID associated with the group, if applicable |
| aws\_account\_name                     | Provides the name of the AWS account associated with the group        |

#### Database Objects

The integration supports the following database objects:

**PostgreSQL Database**

A collection of schemas and database-wide configurations.

| Attribute       | Description                                                  |
| --------------- | ------------------------------------------------------------ |
| id              | A unique identifier for the PostgreSQL database              |
| server\_id      | The ID of the server where the database resides              |
| name            | The name of the PostgreSQL database                          |
| owner           | The owner of the PostgreSQL database                         |
| raw\_privileges | The raw privilege information for the database, if available |

**PostgreSQL Schema**

A namespace that contains named database objects such as tables, views, and functions.

| Attribute       | Description                                                |
| --------------- | ---------------------------------------------------------- |
| id              | A unique identifier for the schema                         |
| server\_id      | The ID of the server where the schema is hosted            |
| database        | The name of the database containing this schema            |
| name            | The name of the schema                                     |
| owner           | The owner of the schema                                    |
| raw\_privileges | The raw privilege information for the schema, if available |

**PostgreSQL Table**

Represents tables, views, and materialized views, grouped together as they share similar security properties.

| Attribute       | Description                                               |
| --------------- | --------------------------------------------------------- |
| id              | A unique identifier for the table                         |
| server\_id      | The ID of the server where the table is hosted            |
| database        | The name of the database containing the table             |
| schema          | The schema name containing the table                      |
| name            | The name of the table                                     |
| owner           | The owner of the table                                    |
| raw\_privileges | The raw privilege information for the table, if available |

**PostgreSQL Trigger**

Represents functions and procedures, grouped due to similar security characteristics.

| Attribute       | Description                                                 |
| --------------- | ----------------------------------------------------------- |
| id              | A unique identifier for the trigger                         |
| server\_id      | The ID of the server where the trigger is hosted            |
| database        | The name of the database containing the trigger             |
| schema          | The schema name containing the trigger                      |
| table           | The name of the table associated with the trigger           |
| name            | The name of the trigger                                     |
| owner           | The owner of the trigger                                    |
| raw\_privileges | The raw privilege information for the trigger, if available |

#### PostgreSQL Privileges and Effective Permissions

PostgreSQL uses a granular privilege system that controls access to different types of database objects. Privileges can be:

* Granted directly to roles
* Inherited through role membership
* Granted with GRANT OPTION, allowing the recipient to grant the privilege to others

Veza models PostgreSQL's native privilege system to generate *Effective Permissions*, showing the cumulative access granted by a role's directly-assigned and inherited access. These effective permission types include:

* **Data Read**: Abilities to read or view data
* **Data Write**: Abilities to create or modify data
* **Data Delete**: Abilities to remove data
* **Metadata Write**: Abilities to modify database structures and permissions
* **Non Data**: Special privileges like USAGE or CONNECT that are not related to data.

**Example Privilege to Effective Permission Mapping**

**Database privileges** control basic access and administrative capabilities. Example mappings include:

| PostgreSQL Privilege | Symbol | Veza Effective Permission | Capability              |
| -------------------- | ------ | ------------------------- | ----------------------- |
| CREATE               | C      | Metadata Create           | Create schemas          |
| TEMPORARY            | T      | Data Read                 | Create temporary tables |
| CONNECT              | c      | Non Data                  | Basic connection access |

**Schema privileges** determine access to contained objects:

| PostgreSQL Privilege | Symbol | Veza Effective Permission | Capability               |
| -------------------- | ------ | ------------------------- | ------------------------ |
| USAGE                | U      | Non Data                  | Access objects in schema |
| CREATE               | C      | Metadata Create           | Create new objects       |
| ALTER                | C      | Metadata Write            | Modify schema            |

**Table privileges** provide granular control, including column-level access.

| PostgreSQL Privilege | Symbol | Veza Abstract Permission | Column-Aware | Capability               |
| -------------------- | ------ | ------------------------ | ------------ | ------------------------ |
| SELECT               | r      | Data Read                | Yes          | Read data                |
| INSERT               | a      | Data Write               | Yes          | Add data                 |
| UPDATE               | w      | Data Write               | Yes          | Modify data              |
| DELETE               | d      | Data Delete              | No           | Remove rows              |
| TRUNCATE             | D      | Data Delete              | No           | Remove all data          |
| REFERENCES           | x      | Non Data                 | No           | Reference in constraints |
| TRIGGER              | t      | Non Data                 | No           | Create triggers          |

**Permission Inheritance**

Veza analyzes the following factors when determining effective permissions:

1. **Default Privileges for Object Owners**: PostgreSQL automatically grants some privileges to object owners:
   * Database owners: `C*T*c*` (Create, Temporary, Connect)
   * Schema owners: `U*C*` (Usage, Create)
   * Table owners: `a*r*w*d*D*x*t*` (All table privileges)
2. **Public Privileges**: PostgreSQL uses the PUBLIC role as a way to apply privileges to all other roles in the system. For some database objects, PostgreSQL assigns default public privileges, which allows all users to perform the specified actions. Object owners can revoke these default privileges. Administrators can adjust these defaults with the `ALTER DEFAULT PRIVILEGES` command.

   | Object                  | Default PUBLIC Privileges |
   | ----------------------- | ------------------------- |
   | `DATABASE`              | Tc                        |
   | `DOMAIN`                | U                         |
   | `FUNCTION`, `PROCEDURE` | X                         |
   | `LANGUAGE`              | U                         |
   | `TYPE`                  | U                         |
3. **Role Membership**: When a role is granted membership in another role, it inherits all privileges of that role. Veza tracks these inheritance chains to show the resulting effective permissions.
4. **Grantable Privileges**: For privileges granted `WITH GRANT OPTION`, Veza represents a "Grantable" version of the privilege node (e.g., "GrantableSelect"), indicate that a user can grant or revoke a specific privilege on a specific resource. As Effective Permissions, these are represented as `Metadata Write`, indicating the ability to modify the permission metadata of a resource.

**Administrative Privileges**

Some privileges require superuser status or special grants:

* Event trigger management
* Foreign data wrapper configuration
* Tablespace creation
* Language installation

Veza maps these administrative privileges to `Metadata Write` to help identify roles with elevated access.

**Alter Roles**

Super users can change the properties of other users via the Alter Role command. Also, a role granted admin privileges to another role can call Alter Role on that role. In Graph, you can identify roles with permission to alter other roles:

* System Mode: Veza shows an \*Alter Role\` privilege node in the path connecting *Postgre SQL User* > *Alter Role* > *PostgreSQL Instance*. This indicates that a user can change the properties of another user in the PostgreSQL server.
* Effective Mode: Grantable system privilege nodes map to `Metadata Update`, indicating a user can change the metadata properties - privileges in this case - of another role.


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.veza.com/4yItIzMvkpAvMVFAamTf/integrations/integrations/postgresql.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.