Postgres sql file seems to not finish

Question

I am using the postgres docker image to create a local database for development. A feature of the image is that any shell scripts in a directory will be run on startup, I use this to setup my schema and users. Everything is working great for creating my schema and user with full access to it, however when I create a read only user things don't seem to work. The script appears to suddenly stop without granting the read only access I request. Its not a syntax issue because I can use psql to connect to the database and copy paste the commands which works perfectly.

The shell script that runs any sql files in the directory, only one for now.

for sql in /docker-entrypoint-initdb.d/*.sql
do
    gosu postgres postgres --single < $sql
done

The actual sql file that gets run (connection details redacted)

-- Main user with full access to its schema
CREATE SCHEMA s;
CREATE USER u PASSWORD 'p';
GRANT ALL ON SCHEMA s TO u;
GRANT ALL ON ALL TABLES IN SCHEMA s TO u;


-- Readonly user
CREATE USER readonly PASSWORD 'r';

-- THE FOLLOWING DOESN'T SEEM TO RUN
-- Allows read on everything in schema s
GRANT USAGE ON SCHEMA s TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA s TO readonly;

Its those last two commands that don't seem to run. I can connect with the user 'readonly' but will get permission errors on schema 's' until I use psql to re-run the last two commands on the database.

The logs contain no errors or warnings

that is really weird. can you exec into that image and run gosu postgres postgres --single < /docker-entrypoint-initdb.d/*.sql ? Do the logs of the postgres appear in the docker logs image output? — Greg
– Greg, Commented Mar 25, 2015 at 13:38
Yes the logs from postgres appear with docker logs. I can see the database start up and I can see the database being altered, but there are no errors or signs of issues. I tried running exec on the container to re-run the file, but it gives me an error saying "lock file "postmaster.pid" already exists" — Colin Murphy
– Colin Murphy, Commented Mar 25, 2015 at 14:18
maybe you could exec in with bash, shutdown the postmaster, then try to run the script? maybe the problem is with single user mode? i went and looked at my db startup scripts, i use multi user mode to 'init' database in docker. this is interesting... stackoverflow.com/questions/28244869/… — Greg
– Greg, Commented Mar 25, 2015 at 15:07
Thanks for that link to the other question. It seems like the real problem is that while they suggest using single user mode it doesn't actually work in practice. I have a psql wrapper I use for running scripts on remote servers, I've modified it to fit my needs for a local server — Colin Murphy
– Colin Murphy, Commented Mar 25, 2015 at 16:42

Greg · Accepted Answer · 2015-03-25 15:46:04Z

1

it just occurred to me while typing this answer is your postgres --single command line does not reference a database. Is it possible that the schema setting is happening in the 'postgres' database, when you really want it in your target database!?! Anyway, from the referenced SO answer it might be a good idea to do this setup in multiuser mode. I'd do that something like this:

echo "Starting PostgreSQL server..."
/etc/init.d/postgresql start

for sql in /docker-entrypoint-initdb.d/*.sql
do
    gosu postgres psql -Upostgres -h $(hostname -i) -f ${sql} ${DB_NAME}
done

echo "Done with initialization ... database "$DB_NAME" is ready to use"

/etc/init.d/postgresql stop

of course you have to worry about the pg_hba access methods here. In my environment, I am trusting the connection from the private ip address, YMMV. -g

answered Mar 25, 2015 at 15:46

Greg

6,7992 gold badges29 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Colin Murphy Over a year ago

You're right, while they suggest using single user mode in the docker container its not a viable solution. I have a script that I use to bulk run sql scripts against a server and I've modified it to handle single scripts against my development database better.

Greg Over a year ago

Did you consider that the grant on the readonly user was in the wrong database?

Collectives™ on Stack Overflow

Postgres sql file seems to not finish

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related