BUG #18068: Insufficient permission unless SUPERUSER - Mailing list pgsql-bugs

The following bug has been logged on the website:

Bug reference:      18068
Logged by:          Thaddeus Fuller
Email address:      ghw@ghwfluffy.com
PostgreSQL version: 15.4
Operating system:   Ubuntu 22.04 / Docker
Description:

Hello, thank you for looking at my issue. I can't seem to get Postgres 15+
dockers to work with my test project. I have tried to ensure this is not
something wrong with my test or understanding, and I have provided a script
to replicate the issue.

The only way I am able to run a CREATE TABLE command on the database is if I
grant SUPERUSER permission to my role.

I have included a bash script below that replicates the issue. There are
some variables you can tweak, such as the docker image and the user
permissions. The only way the script works is to use postgres previous to 15
or to grant SUPERUSER permission.

Here is the notable output when you run the bash script:

---------------------


The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.

The database cluster will be initialized with locale "en_US.utf8".
The default database encoding has accordingly been set to "UTF8".
The default text search configuration will be set to "english".

Data page checksums are disabled.

fixing permissions on existing directory /var/lib/postgresql/data ... ok
creating subdirectories ... ok
selecting dynamic shared memory implementation ... posix
selecting default max_connections ... 100
selecting default shared_buffers ... 128MB
selecting default time zone ... Etc/UTC
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok


Success. You can now start the database server using:

    pg_ctl -D /var/lib/postgresql/data -l logfile start

initdb: warning: enabling "trust" authentication for local connections
initdb: hint: You can change this by editing pg_hba.conf or using the option
-A, or --auth-local and --auth-host, the next time you run initdb.
waiting for server to start....2023-08-24 02:17:33.279 UTC [48] LOG:
starting PostgreSQL 15.4 (Debian 15.4-1.pgdg120+1) on x86_64-pc-linux-gnu,
compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
2023-08-24 02:17:33.280 UTC [48] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2023-08-24 02:17:33.284 UTC [51] LOG:  database system was shut down at
2023-08-24 02:17:33 UTC
2023-08-24 02:17:33.287 UTC [48] LOG:  database system is ready to accept
connections
 done
server started

/usr/local/bin/docker-entrypoint.sh: running
/docker-entrypoint-initdb.d/init-db.sql
CREATE DATABASE
CREATE ROLE
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT


waiting for server to shut down....2023-08-24 02:17:33.458 UTC [48] LOG:
received fast shutdown request
2023-08-24 02:17:33.459 UTC [48] LOG:  aborting any active transactions
2023-08-24 02:17:33.460 UTC [48] LOG:  background worker "logical
replication launcher" (PID 54) exited with exit code 1
2023-08-24 02:17:33.460 UTC [49] LOG:  shutting down
2023-08-24 02:17:33.461 UTC [49] LOG:  checkpoint starting: shutdown
immediate
2023-08-24 02:17:33.493 UTC [49] LOG:  checkpoint complete: wrote 929
buffers (5.7%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.012 s,
sync=0.015 s, total=0.033 s; sync files=310, longest=0.002 s, average=0.001
s; distance=4228 kB, estimate=4228 kB
2023-08-24 02:17:33.500 UTC [48] LOG:  database system is shut down
 done
server stopped

PostgreSQL init process complete; ready for start up.

2023-08-24 02:17:33.589 UTC [1] LOG:  starting PostgreSQL 15.4 (Debian
15.4-1.pgdg120+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14)
12.2.0, 64-bit
2023-08-24 02:17:33.589 UTC [1] LOG:  listening on IPv4 address "0.0.0.0",
port 5432
2023-08-24 02:17:33.589 UTC [1] LOG:  listening on IPv6 address "::", port
5432
2023-08-24 02:17:33.592 UTC [1] LOG:  listening on Unix socket
"/var/run/postgresql/.s.PGSQL.5432"
2023-08-24 02:17:33.595 UTC [64] LOG:  database system was shut down at
2023-08-24 02:17:33 UTC
2023-08-24 02:17:33.599 UTC [1] LOG:  database system is ready to accept
connections
+ HOST=localhost
+ psql -d ghw -U ghw -h localhost -c 'CREATE TABLE test (id INT NOT NULL,
PRIMARY KEY(id))'
ERROR:  permission denied for schema public
LINE 1: CREATE TABLE test (id INT NOT NULL, PRIMARY KEY(id))
                     ^
2023-08-24 02:17:35.713 UTC [68] ERROR:  permission denied for schema public
at character 14
2023-08-24 02:17:35.713 UTC [68] STATEMENT:  CREATE TABLE test (id INT NOT
NULL, PRIMARY KEY(id))
+ cleanup
+ rm -rf /tmp/init.sql.1289076
+ docker stop pgtest
2023-08-24 02:17:35.772 UTC [1] LOG:  received fast shutdown request
2023-08-24 02:17:35.773 UTC [1] LOG:  aborting any active transactions
2023-08-24 02:17:35.777 UTC [1] LOG:  background worker "logical replication
launcher" (PID 67) exited with exit code 1
2023-08-24 02:17:35.777 UTC [62] LOG:  shutting down
2023-08-24 02:17:35.778 UTC [62] LOG:  checkpoint starting: shutdown
immediate
2023-08-24 02:17:35.788 UTC [62] LOG:  checkpoint complete: wrote 4 buffers
(0.0%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.003 s,
sync=0.002 s, total=0.012 s; sync files=3, longest=0.001 s, average=0.001 s;
distance=0 kB, estimate=0 kB
2023-08-24 02:17:35.795 UTC [1] LOG:  database system is shut down




---------------------

And here is the script to replicate the issue:

---------------------





#!/bin/bash

set -eux -o pipefail

PGDATABASE=ghw
export PGUSER=ghw
export PGPASSWORD=ghw

PGDOCKER=pgtest
#PGVERSION="postgres:14" # Works
PGVERSION="postgres:15" # Does not work

# Test cases for user configuratiopn
VERY_PERMISSIBLE="
CREATE DATABASE ${PGDATABASE};
CREATE USER ${PGUSER} WITH ENCRYPTED PASSWORD '${PGPASSWORD}';
GRANT ALL PRIVILEGES ON DATABASE ${PGDATABASE} TO ${PGUSER};
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO ${PGUSER};
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO ${PGUSER};
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO ${PGUSER};
GRANT USAGE ON SCHEMA public TO ${PGUSER};
GRANT CREATE ON SCHEMA public TO ${PGUSER};

GRANT ALL PRIVILEGES ON DATABASE ${PGDATABASE} TO PUBLIC;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO PUBLIC;
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO PUBLIC;
GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public TO PUBLIC;
GRANT USAGE ON SCHEMA public TO PUBLIC;
GRANT CREATE ON SCHEMA public TO PUBLIC;"

CREATE_PERMISSIBLE="
CREATE DATABASE ${PGDATABASE};
CREATE USER ${PGUSER} WITH PASSWORD '${PGPASSWORD}';
GRANT CONNECT ON DATABASE ${PGDATABASE} TO ${PGUSER};
GRANT USAGE ON SCHEMA public TO ${PGUSER};
GRANT CREATE ON SCHEMA public TO ${PGUSER};"

SUPERUSER="
CREATE DATABASE ${PGDATABASE};
CREATE USER ${PGUSER} WITH SUPERUSER ENCRYPTED PASSWORD '${PGPASSWORD}';"

# Choose config
CONFIG="${VERY_PERMISSIBLE}"
#CONFIG="${CREATE_PERMISSIBLE}"
#CONFIG="${SUPERUSER}"

# Put config in temp file
TMPFILE="/tmp/init.sql.$$"
cleanup() {
    rm -rf "${TMPFILE}"
    docker stop pgtest
}
trap cleanup EXIT
echo "${CONFIG}" > "${TMPFILE}"

# Start docker detached but showing output
docker pull "${PGVERSION}"
docker run \
    --rm \
    --name "${PGDOCKER}" \
    --network host \
    -e POSTGRES_PASSWORD="${PGPASSWORD}" \
    -v "${TMPFILE}":/docker-entrypoint-initdb.d/init-db.sql \
    "${PGVERSION}" &

# Wait for docker to start
sleep 3

# Test query
HOST="localhost"
psql -d "${PGDATABASE}" -U "${PGUSER}" -h "${HOST}" -c "CREATE TABLE test
(id INT NOT NULL, PRIMARY KEY(id))"
echo "!! SUCCESS !!"