CONNECT Privileges¶

Introduction¶

By default, PostgreSQL allows all users to connect to all databases through the PUBLIC pseudo-role. This page explains how Muppy manages CONNECT privileges to restrict database access to specific users.

Use cases:

Isolate Dev Server databases so only the owner can connect
Grant specific users access to selected databases
Prevent accidental connections to production databases

Key Concepts¶

Who Can Connect to a Database?¶

User Type	Can Connect?
Owner	Always (implicit PostgreSQL privilege)
Superuser	Always (bypasses all restrictions)
PUBLIC	By default yes, unless explicitly revoked
User with GRANT	Only if explicitly granted

Owner Implicit Privilege

The database owner can always connect to their database. This is a PostgreSQL implicit privilege that cannot be revoked. When you REVOKE CONNECT FROM PUBLIC, the owner is not affected.

Superuser Bypass

PostgreSQL superusers bypass all permission checks. If db_user_is_superuser=True, CONNECT restrictions have no effect on that user.

PostgreSQL ACL Format¶

Database privileges are stored in the datacl column (Access Control List). Muppy parses this to determine the current state:

ACL Value	Meaning
`NULL`	Default: PUBLIC has CONNECT
`=c` or `=Tc`	PUBLIC has explicit CONNECT
No `=c` pattern	PUBLIC was revoked

The PUBLIC has CONNECT column in the database list view reflects this computed value.

Managing CONNECT Privileges¶

From Database List View¶

Navigate to PostgreSQL > Databases
Select one or more databases (must be from same cluster)
Click Revoke CONNECT... or Grant CONNECT... button
Complete the wizard

Grant CONNECT Wizard¶

The Grant CONNECT wizard allows you to give a user access to selected databases.

Fields:

Select User: Choose from the cluster's existing users (dropdown)
PostgreSQL User: Or type a username manually
Also Revoke from PUBLIC: When checked (default), first revokes PUBLIC then grants to the specified user

Restore Default Behavior

To restore PostgreSQL's default behavior (everyone can connect), use the Grant wizard with username PUBLIC. This grants CONNECT back to PUBLIC.

Revoke CONNECT Wizard¶

The Revoke CONNECT wizard removes a user's ability to connect to selected databases.

Fields:

Select User: Choose from the cluster's existing users (dropdown)
PostgreSQL User: Or type a username manually

Revoking from PUBLIC

When you revoke CONNECT from PUBLIC, only the database owner and superusers will be able to connect. All other users will be denied access.

App Definition Configuration¶

Three PostgreSQL privilege fields control database user creation and isolation:

Field	Description
PG User is SUPERUSER	Creates the PostgreSQL user with SUPERUSER privilege
PG User can Create Databases	Creates the PostgreSQL user with CREATEDB privilege
Revoke PUBLIC CONNECT	Applies REVOKE CONNECT FROM PUBLIC on new databases

Configuration Flow¶

Configure fields on the App Definition (template)
When a Dev Server is instantiated, fields are copied via sync_all_from_template()
Fields are used during:
setup_dev_server() provisioning task
"Create Database" wizard (passed via context)

UI Visibility Rules¶

PG User can Create Databases is hidden when PG User is SUPERUSER=True (superusers can always create databases)

Dev Server Usage¶

Automatic Application¶

When Revoke PUBLIC CONNECT is enabled on a Dev Server:

At provisioning: The setup_dev_server() task automatically applies REVOKE CONNECT FROM PUBLIC after database creation or restore
Via wizard: The "Create Database" button passes this setting to the wizard context

Owner Access

The db_user (owner) can always connect to their databases. This is an implicit PostgreSQL privilege.

Retrofitting Existing Databases¶

To apply isolation to databases that already exist:

Open the Dev Server form
Click Update Databases List to refresh the filtered database list
Click Revoke PUBLIC button (visible when Revoke PUBLIC CONNECT is enabled)
Confirm the action

This applies REVOKE CONNECT FROM PUBLIC to all databases in the filtered list.

Checking Privileges¶

In Database List View¶

The PUBLIC has CONNECT column shows the current state for each database:

True: PUBLIC can connect (default PostgreSQL behavior)
False: PUBLIC was revoked, only owner/superusers/granted users can connect

This value is computed from the PostgreSQL ACL (datacl column) and updated when the database list is refreshed.

Accessible Databases for a User¶

The PG Cluster model provides get_user_accessible_database_ids() to query which databases a specific user can access. This is used by Dev Server to populate the db_filtered_list_ids field.