在PopSQL中访问public schema提示权限拒绝，如何执行指定CREATE TABLE语句？

Got it, let's tackle this permission issue head-on. The error permission denied for schema public means your current database user doesn't have the right to create objects in the public schema. Here's how to fix it step by step:

Step 1: Identify your current user (and confirm the issue) #

First, run this query in PopSQL to check which user you're logged in as:

SELECT current_user;

Note down the username returned (let's call it your_user for later). You can also verify the existing permissions on the public schema with:

SELECT nspname, usename, privilege_type 
FROM pg_namespace 
JOIN pg_user ON pg_namespace.nspowner = pg_user.usesysid 
WHERE nspname = 'public';

This will show you which users have what permissions on public—you'll likely see your user is missing the CREATE privilege.

Step 2: Grant the required permissions #

To fix this, you need to run a GRANT command as a user with superuser privileges (like the default postgres user, or an admin in your team).

1. In PopSQL, switch your connection to use a superuser/admin account (if you have access).
2. Run this query, replacing your_user with the username you noted earlier:

GRANT CREATE ON SCHEMA public TO your_user;

If you still run into issues, you can grant both USAGE and CREATE permissions to cover all bases:

GRANT USAGE, CREATE ON SCHEMA public TO your_user;

Step 3: Test your create table statement #

Switch back to your original user in PopSQL, then run your table creation statement again:

CREATE TABLE student ( 
  fname VARCHAR(20), 
  lname VARCHAR(20), 
  Adresse VARCHAR(14) 
);

This should now execute without the permission error.

Quick note for team environments #

If you don't have access to a superuser account, reach out to your database administrator—they'll need to run the GRANT command for you. Also, keep in mind that in some organizations, using the public schema might not be standard practice, but if that's what you need for this task, the above steps will get you sorted.

内容的提问来源于stack exchange，提问作者Mahdi Elhajuojy