Permission issues accessing tables in PostgreSQL database

Question

I have a PostgreSQL database. I have a library that accesses said database. I have test code to test that library.

Previously everything worked fine, but this morning my test logs have errors like this:

ERROR:  permission denied for table configuration

In an attempt to narrow down the problem I created a script to recreate the database and all permissions from scratch. It is based upon countless similar scripts I have used previously. It starts like this:

DROP DATABASE boards_db;
DROP OWNED BY test_user;
DROP USER test_user;
CREATE USER test_user WITH PASSWORD 'testing';
CREATE DATABASE boards_db WITH ENCODING='UTF8' OWNER = test_user;

\c boards_db

GRANT ALL PRIVILEGES ON DATABASE boards_db to test_user;
ALTER DEFAULT PRIVILEGES GRANT ALL ON TABLES TO test_user;
ALTER DEFAULT PRIVILEGES GRANT ALL ON SEQUENCES TO test_user;

CREATE TABLE IF NOT EXISTS sessions (
    user_id INTEGER PRIMARY KEY,
    admin BOOLEAN NOT NULL,
    session_id TEXT
);

In trying to debug this issue I foolishly meddled with the pg_hba.conf file. It now looks as below, and I can now log on as the test user (or other users) without specifying a hostname, thanks to the line "local all all md5" that I added

# Database administrative login by Unix domain socket
local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     md5
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            md5
host    replication     all             ::1/128                 md5

However, I STILL cannot actually do anything with any of the tables in the database.

$ psql -W -U test_user -d boards_db
Password: 
psql (12.9 (Ubuntu 12.9-0ubuntu0.20.04.1))
Type "help" for help.

boards_db=> select * from sessions;
ERROR:  permission denied for table sessions

The trick of setting default privileges before creating all the tables etc. has worked before in a multitude of database. Why doesn't it work here? Why can test_user not access the tables?

Permission issues accessing tables in PostgreSQL database

0 Answers