PostgreSQL 13 :: ERROR: permission denied for table 'mydb'

Question

Below I'm creating database mydb and populating it. Notice that the last step I perform is setting the password for postgres. This is simply to avoid password prompts during previous steps.

I followed steps in other StackOverflow posts, namely issuing the GRANT ALLs on TABLES, SEQUENCES and FUNCTIONS, but am still facing the below issue.

mydb.sh:

su - postgres <<xEOFx
set +H

psql -c "CREATE DATABASE mydb"
psql -c "CREATE USER user01 WITH ENCRYPTED PASSWORD 'SomePassword'"

psql -c "GRANT ALL PRIVILEGES ON ALL TABLES    IN SCHEMA public to user01"
psql -c "GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public to user01"
psql -c "GRANT ALL PRIVILEGES ON ALL FUNCTIONS IN SCHEMA public to user01"

psql --dbname=mydb --username=postgres -f /tmp/mydb.sql
psql -c "GRANT ALL PRIVILEGES ON DATABASE mydb TO user01"

psql -c "ALTER USER postgres WITH PASSWORD 'AnotherPassword'"
exit
xEOFx

The script does not fail, but I also cannot access the mydb as user01:

jdoe$ psql --username=user01 --dbname=mydb
Password for user user01: 
psql (13.2)
Type "help" for help.

mydb=> \c
You are now connected to database "mydb" as user "user01".
mydb=> \dt
             List of relations
 Schema |     Name      | Type  |  Owner   
--------+---------------+-------+----------
 public | some_table    | table | postgres
(1 rows)

mydb=> select * from some_table;
ERROR:  permission denied for table some_table
mydb=>

SIDEBAR: I do notice that the owner of some_table is postgres, and would prefer that it be user01. Perhaps this could be part of the issue.

What am I missing?

Thank you in advance.

EDIT: Note that I tried running psql --dbname=mydb --username=postgres -f /tmp/mydb.sql before the GRANT ALL statements, too.

Here's also a great insight into least privilege setup in [psql](https://stackoverflow.com/a/41547993/1736679) — Efren, Aug 24 '22 at 07:20

score 6 · Accepted Answer · answered Apr 09 '21 at 00:47

6

You are granting privileges to tables, sequences, and functions in the public schema in the database postgres, not mydb. By default, psql will connect to the database named the same as the current user, which is postgres in this case. Make sure you run the commands in mydb by adding -d mydb to your psql commands.

answered Apr 09 '21 at 00:47

Jeremy

6,313
17
20

I see. `UpVoted`. Let me rearrange things using your suggestion and retry. I'll select this as the answer if it works. Be back soon. `LoL`. Thank you. – NYCeyes Apr 09 '21 at 01:04
It was a combination of what you indicated along with a `./pg_hba.conf` problem, but both were necessary. Thank you for the information. – NYCeyes Apr 09 '21 at 01:32

PostgreSQL 13 :: ERROR: permission denied for table 'mydb'

1 Answers1