Examples: further postgres setup?

[vdice]

I needed a few more postgres setup commands (edit: on Mac via homebrew-installed postgres) before I could successfully run the postgres examples and wanted to check in to see if I have them right.

I followed the README in each postgres example but hit the following errors in short order (here is the postgres server output):

2024-11-26 14:24:06.819 MST [66187] FATAL:  role "postgres" does not exist
2024-11-26 14:24:06.821 MST [66188] FATAL:  role "postgres" does not exist
2024-11-26 14:33:41.690 MST [66324] FATAL:  role "postgres" is not permitted to log in
2024-11-26 14:33:41.691 MST [66325] FATAL:  role "postgres" is not permitted to log in
2024-11-26 14:36:25.222 MST [66435] ERROR:  permission denied for table articletest

I ran the following commands to get past these errors. Are these correct and should we add them (or similar) to the READMEs?

$ psql -d spin_dev -c 'CREATE ROLE postgres;'
CREATE ROLE

$ psql -d spin_dev -c 'ALTER ROLE "postgres" WITH LOGIN;'
ALTER ROLE

$ psql -d spin_dev -c 'GRANT ALL ON articletest TO postgres;'
GRANT

[itowlson]

Comparing those instructions with my usual self-instructions it looks like passing -U postgres to the createdb command might do the trick? That's a Docker Postgres image, though, so it's possible it has something baked in that bare metal doesn't - but even with my rare ventures into bare metal I don't recall having to faff with roles. Could you try re-running the createdb with the -U postgres and see if it helps?

[vdice]

Oh, thanks for mentioning the difference between bare metal and Docker. I now recall that I used to also run via the Docker image but unthinkingly ran directly on my Mac today. So I think your hunch about the Docker image having the proper roles already set up is a good one.

Alas, if I haven't already created the role, the createdb cmd will fail:

$ createdb -U postgres spin_dev
createdb: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL:  role "postgres" does not exist

However thanks to https://stackoverflow.com/a/35308200 it turns out that Mac users can get the postgres superuser/role/etc set up in one fell swoop via e.g. /opt/homebrew/bin/createuser -s postgres after initializing the db. Afterwards, no other special setup was needed.

So, I'm not so sure we have anything to update. I suppose we could add a note specific to Mac users running homebrew-installed postgres locally... or just have this record of my experience here in the form of a closed issue for those particular users when/if needed.

[itowlson]

We could change the instructions to refer to the Docker image? Folks who want to use bare metal Postgres are likely to know their dev system well enough to translate the instructions across, and folks who are not deep into Postgres may be more comfortable with Docker magic to get started rather than having to figure out bare metal (and then the instructions would in theory be portable across all OSes).

[vdice]

We could change the instructions to refer to the Docker image?

👍 I like that. I can work on adding this to the example READMEs