Insufficientprivilege Permission Denied For Schema Public

The error insufficient privilege permission denied for schema public occurs when a database user attempts to access objects within the public schema without the required rights, and it is a common obstacle in PostgreSQL and other relational database management systems. This message indicates that the user’s role lacks the necessary permissions to perform the requested operation, such as creating tables, selecting data, or altering schema definitions. Understanding the root cause and applying the correct fixes can restore access and prevent future disruptions in your database environment Still holds up..

Introduction

The insufficient privilege permission denied for schema public error typically appears when a role tries to execute a command that requires elevated rights on the default public schema. In PostgreSQL, the public schema is automatically created for each new database and serves as the default location for tables, views, and other objects. When a user does not possess the appropriate privileges, the server rejects the request and returns the permission‑denied message. This article explains why the error happens, how to diagnose it, and step‑by‑step solutions to grant the needed rights while maintaining security best practices.

Understanding the Error Message

• insufficient privilege – the role lacks one or more required permissions (e.g., CREATE, SELECT, USAGE).
• permission denied – the database server explicitly refuses the operation because security checks fail.
• schema public – the target object is the public schema, which is the default schema in most databases.

Key Components

1. Role – a database user or group that is mapped to a login name.
2. Privileges – specific rights such as USAGE, CREATE, SELECT, INSERT, UPDATE, DELETE, and ALTER.
3. Schema – a namespace that organizes database objects; public is the built‑in schema.

When any of these elements is missing or mismatched, the error is raised.

Common Causes

• Newly created user that has not been granted any privileges on the public schema.
• Role inheritance issues – a role may be missing a direct grant but still inherits rights through a group role.
• Default privileges not set – objects created after a user is granted rights may inherit default permissions that exclude the user.
• Misconfigured search_path – the user may be looking for objects in a different schema, causing the error to appear even if privileges exist.
• Temporary session settings – revoked privileges during a session can trigger the message temporarily.

How to Diagnose the Issue

1. Check the user’s role membership

   SELECT rolname, rolcanlogin, rolsuper, rolcreatelogin
   FROM pg_roles
   WHERE rolname = 'your_user';
   

2. Inspect current privileges on the public schema

   SELECT grantee, privilege_type, is_grantable
   FROM information_schema.role_table_grants
   WHERE table_schema = 'public';
   

3. Review the search_path

   SHOW search_path;
   

4. Examine recent log entries for detailed error codes that may point to missing roles or revoked permissions.

Resolving the Permission Issue

Grant Basic USAGE on the public schema

GRANT USAGE ON SCHEMA public TO your_user;

This allows the user to see objects within the schema.

Grant CREATE rights for new objects

GRANT CREATE ON SCHEMA public TO your_user;

Required when the user needs to create tables, functions, or other objects.

Grant specific object privileges (example: SELECT on all tables)

GRANT SELECT ON ALL TABLES IN SCHEMA public TO your_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO your_user;

The first command fixes existing objects; the second ensures future tables inherit the SELECT privilege.

Use role‑based grants for groups

If multiple users need the same rights, create a role (e.g., app_reader) and grant it the necessary privileges, then add users to that role:

CREATE ROLE app_reader NOINHERIT;
GRANT USAGE, CREATE, SELECT ON SCHEMA public TO app_reader;
GRANT app_reader TO alice, bob, charlie;

Adjust default privileges for future objects

ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO your_user;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT EXECUTE ON FUNCTIONS TO your_user;

These commands set the default rights for objects created later by the user.

Preventive Measures

• Regularly audit role grants with queries against information_schema.role_table_grants and pg_roles.
• Apply the principle of least privilege: give users only the rights they truly need.
• Document schema usage and maintain a roster of roles that require access to the public schema.
• Automate grant processes using scripts that run after user creation or role changes.
• Monitor search_path settings to avoid accidental cross‑schema access that can mask permission problems.

FAQ

Q1: Why does the error mention “schema public” even if I’m working in another schema?

FAQ

Q1: Why does the error mention “schema public” even if I’m working in another schema?

The error message often points to public because PostgreSQL defaults to searching that schema first. When you reference a table, function, or type without an explicit schema name, the planner checks public before falling back to any other schemas listed in search_path. If the object you intend to use does not exist in public, but a similarly named object does, the planner may resolve the reference to the wrong object—or, if the object truly doesn’t exist anywhere, the error will still cite public as the originating schema.

Additionally, ownership and default privileges are tied to the schema where objects are created. If a role lacks USAGE on a schema, PostgreSQL returns a permission error that includes the schema name, regardless of which schema you think you are operating in.

Key take‑aways

Q2: How can I pinpoint the exact object that is causing the permission denial?

1. Enable detailed logging

   SET log_min_messages = 'DETAIL';
   SET log_line_prefix = '%t [%p]: ';
   

   Then reproduce the failure. The log will contain a DETAIL line that lists the specific object (e.g., relation "mytable" of schema "public").

2. Use pg_stat_activity
   While a session is blocked, query:

   SELECT pid, query_start, state, wait_event_type, wait_event, pid, usename
   FROM pg_stat_activity
   WHERE state = 'waiting';
   

   The wait_event column often reveals the underlying relation No workaround needed..

3. Check pg_locks

   SELECT * FROM pg_locks WHERE NOT granted;
   

   Joining on pg_class and pg_namespace can expose the exact table or function.

4. take advantage of pg_trace (for procedural code)

   SELECT * FROM pg_trace(start_time := clock_timestamp());
   

   Run the problematic routine, then stop tracing and examine the output.

Q3: What if the issue persists after granting the basics?

When the fundamentals (USAGE, CREATE, object‑level grants) are in place, consider these less‑obvious culprits:

Q4: How do I automate the grant‑setup for new users?

A repeatable script can be stored as a SQL function and executed after each CREATE ROLE or CREATE USER. Below is a compact, PostgreSQL‑agnostic example you can drop into a maintenance schema (e.g., mgmt).

-- 1. Create a helper function that applies a “public‑schema reader” policy
CREATE OR REPLACE FUNCTION mgmt.apply_public_reader_privileges(p_role name)
RETURNS void AS $
BEGIN
    -- USAGE

 -- USAGE on the schema itself
    EXECUTE format('GRANT USAGE ON SCHEMA public TO %I', p_role);

    -- SELECT on all existing tables
    EXECUTE format('GRANT SELECT ON ALL TABLES IN SCHEMA public TO %I', p_role);

    -- USAGE on sequences (needed to read serial/identity values)
    EXECUTE format('GRANT USAGE ON ALL SEQUENCES IN SCHEMA public TO %I', p_role);

    -- EXECUTE on existing functions
    EXECUTE format('GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO %I', p_role);

    -- Default privileges so future objects inherit the same policy
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO %I', p_role);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE ON SEQUENCES TO %I', p_role);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO %I', p_role);

END;
$ LANGUAGE plpgsql;

To use it, run the function as a superuser or the schema owner immediately after creating a role:

SELECT mgmt.apply_public_reader_privileges('analytics_reader');

The `format(... %

    -- Default privileges so future objects inherit the same policy
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT    ON TABLES   TO %I', p_role);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE    ON SEQUENCES TO %I', p_role);
    EXECUTE format('ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE  ON FUNCTIONS TO %I', p_role);

    -- Finally, give the role the ability to set its own session‑time GUCs if needed
    EXECUTE format('ALTER ROLE %I SET search_path TO public', p_role);
END;
$ LANGUAGE plpgsql;

5. Tying the function to role creation

A common pattern is to automate the whole “create‑role + grant‑setup” flow in a single script that a DBA can run from a shell or a CI pipeline. On the flip side, the following mgmt. create_user_with_defaults function does exactly that: it creates the login (or role), calls the helper from section 4, and logs the action in an audit table And that's really what it comes down to. And it works..

-- ------------------------------------------------------------------
-- mgmt.create_user_with_defaults
--   Creates a new role and immediately applies a standard set of
--   privileges to the *public* schema.  The function is safe to run
--   multiple times – it will skip creation if the role already exists.
-- ------------------------------------------------------------------
CREATE OR REPLACE FUNCTION mgmt.create_user_with_defaults(
    p_role_name      name,
    p_login          boolean := true,      -- true → LOGIN, false → role without login
    p_password       text    := NULL,      -- plain‑text password (will be hashed)
    p_superuser     boolean := false,
    p_inherit        boolean := true,
    p_bypass_rls     boolean := false,
    p_valid_until    text    := NULL)      -- interval‑style timestamp
RETURNS void
AS $
DECLARE
    v_quoted_name text := quote_ident(p_role_name);
BEGIN
    -- 1️⃣  Create or alter the role
    IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_roles WHERE rolname = p_role_name) THEN
        EXECUTE format(
            'CREATE ROLE %I %s %s %s %s %s %s',
            p_role_name,
            CASE WHEN p_login          THEN 'LOGIN'          ELSE 'NOLOGIN'       END,
            CASE WHEN p_superuser     THEN 'SUPERUSER'      ELSE 'NOSUPERUSER'   END,
            CASE WHEN p_inherit       THEN 'INHERIT'        ELSE 'NOINHERIT'     END,
            CASE WHEN p_bypass_rls    THEN 'BYPASSRLS'      ELSE 'NOBYPASSRLS'   END,
            CASE WHEN p_password IS NOT NULL
                 THEN format('PASSWORD %L', p_password)
                 ELSE '' END,
            CASE WHEN p_valid_until IS NOT NULL
                 THEN format('VALID UNTIL %L', p_valid_until)
                 ELSE '' END
        );
    ELSE
        -- Update existing role to match the desired options
        EXECUTE format('ALTER ROLE %I %s %s %s %s',
            p_role_name,
            CASE WHEN p_login          THEN 'LOGIN'          ELSE 'NOLOGIN'       END,
            CASE WHEN p_superuser     THEN 'SUPERUSER'      ELSE 'NOSUPERUSER'   END,
            CASE WHEN p_inherit       THEN 'INHERIT'        ELSE 'NOINHERIT'     END,
            CASE WHEN p_bypass_rls    THEN 'BYPASSRLS'      ELSE 'NOBYPASSRLS'   END
        );
        IF p_password IS NOT NULL THEN
            EXECUTE format('ALTER ROLE %I PASSWORD %L', p_role_name, p_password);
        END IF;
        IF p_valid_until IS NOT NULL THEN
            EXECUTE format('ALTER ROLE %I VALID UNTIL %L', p_role_name, p_valid_until);
        END IF;

        END IF;
    END IF;
END;
$ LANGUAGE plpgsql;

## How to use it

Because the function is idempotent, you can safely include it in deployment scripts or run

### Practical Examples

#### 1️⃣ One‑liner for a typical application user  
```sql
SELECT mgmt.create_user_with_defaults(
    p_role_name    => 'app_web_user',
    p_login        => true,
    p_password     => 'SecurePass123!',
    p_superuser    => false,
    p_inherit      => true,
    p_bypass_rls   => false,
    p_valid_until  => NULL
);

Running this statement multiple times will never create duplicate roles; if app_web_user already exists, the function will simply adjust its login/password attributes to match the supplied defaults.

2️⃣ A read‑only reporting role without a password

SELECT mgmt.create_user_with_defaults(
    p_role_name    => 'report_reader',
    p_login        => false,      -- role can log in but will not have a password
    p_password     => NULL,
    p_superuser    => false,
    p_inherit      => true,
    p_bypass_rls   => false,
    p_valid_until  => NULL
);

Because p_login is false, the role is created with NOLOGIN. This is handy for roles that are granted to other objects (e.g., GRANT USAGE ON SCHEMA reports TO report_reader) without ever needing a database connection.

3️⃣ Enforcing an expiration date for temporary users

SELECT mgmt.create_user_with_defaults(
    p_role_name    => 'temp_analyst',
    p_login        => true,
    p_password     => 'TempPass456!',
    p_superuser    => false,
    p_inherit      => true,
    p_bypass_rls   => false,
    p_valid_until  => '2025-06-30 23:59:59'
);

If the role already exists, the VALID UNTIL clause is refreshed, guaranteeing that the temporary privilege window is always up‑to‑date even after re‑running a migration script That's the part that actually makes a difference..

4️⃣ Using environment variables for secrets

When deploying the function from a CI/CD pipeline, it is best practice to inject passwords from the environment rather than hard‑coding them:

DO $
BEGIN
    PERFORM mgmt.create_user_with_defaults(
        p_role_name    => current_setting('MGMT_ROLE_NAME'),
        p_login        => true,
        p_password     => current_setting('MGMT_ROLE_PASSWORD'),
        p_superuser    => false,
        p_inherit      => true,
        p_bypass_rls   => false,
        p_valid_until  => NULL
    );
END $;

Set the settings before execution, e.g.:

export MGMT_ROLE_NAME='ci_builder'
export MGMT_ROLE_PASSWORD='$(openssl rand -base64 24)'
psql -f deploy_role.sql

5️⃣ Detecting drift with a “dry‑run” wrapper

If you want to see what changes the function would apply without actually modifying the database, you can wrap the call in a transaction and rollback:

BEGIN;
-- Capture the planned ALTER statements (requires a custom logger or a trigger)
SELECT mgmt.create_user_with_defaults(
    p_role_name    => 'audit_user',
    p_login        => true,
    p_password     => 'AuditPass789!',
    p_superuser    => false,
    p_inherit      => true,
    p_bypass_rls   => false,
    p_valid_until  => NULL
);
-- ROLLBACK;   -- uncomment to keep the dry‑run silent
COMMIT;

Why This Function Matters in Modern Deployments

• Idempotency – Deployment scripts can be run repeatedly across environments (dev, test, prod

...production environments. This declarative approach eliminates the need for ad-hoc ALTER ROLE commands and ensures that every principal created through the pipeline adheres to the organization's security baseline, even as teams scale across dozens of projects.

Centralized Security Policy Enforcement
Because the function encapsulates password complexity, validity windows, and permission inheritance in one place, security teams can enforce compliance without requiring every developer to remember the correct CREATE ROLE syntax. Policy changes–such as updating password rotation cycles or default expiration dates–propagate instantly to all future role creations, and can be back-applied to existing roles via a simple migration script And that's really what it comes down to. That alone is useful..

Secrets Management Integration
The pattern of pulling credentials from environment variables or secret stores not only prevents hard-coded passwords in version control but also aligns with industry standards for secret lifecycle management. When combined with CI/CD tools that mask sensitive outputs, this ensures that database credentials remain ephemeral and auditable, reducing the attack surface across the infrastructure That's the whole idea..

Operational Visibility and Drift Detection
The dry-run wrapper pattern provides a safety net for change management processes. By executing the function within a transaction that can be rolled back, DBAs and DevOps engineers can preview the exact set of roles

. The function's output can be logged to an audit table, providing a complete history of role creation and modification events. This transparency simplifies compliance with regulations such as GDPR, HIPAA, or SOX, which require proof of access control and change management.

Reduced Operational Overhead
By automating the creation and configuration of database roles, the function minimizes manual intervention. This not only speeds up deployment cycles but also reduces the risk of human error. Here's a good example: if a new policy requires all roles to have bypass_rls set to false by default, the function can be updated once, and all future roles will automatically comply Most people skip this — try not to..

Conclusion

To keep it short, the create_user_with_defaults function is a cornerstone of a dependable, scalable, and secure database deployment strategy. In practice, it embodies the principles of infrastructure as code by treating database roles as declarative resources that can be versioned, reviewed, and applied consistently. Which means by embedding security policies, integrating with secrets management, and providing operational safeguards, this approach empowers teams to deliver features with confidence, knowing that the underlying access control is both flexible and fortified. As organizations continue to embrace DevOps and cloud-native architectures, such patterns will become indispensable for maintaining agility without compromising security.