Guardians of the Postgres, The Search for Postgres, The Never Ending Postgres Part 3
Day 3
I knew the mountain I had to climb today so I reviewed what I knew about postgres to prepare me: create and dropping databases, listing databases to prove it created or dropped, and opening psql for a named database, I was really hoping the error would just go away today. Sometimes that happens with JavaScript, apparently not with postgres.
Error: role "postgres" does not exist
At first I read it wrong and thought postgres does not exist but upon deeper head scratching I saw that all important but dwarfed by Error
word role
. Time the mother out! So we have postgres the tech, postgres the database and now postgres the role… Seriously?!!?!?! I know naming is the second hardest thing in development but I think we could have been just a little bit more descriptie. Maybe postgresUser
, jimmy
, or even admin
?
I found out that I can rudimentary map of the role landscape hidden psql
:
jordanpapaleo=# \du
List of roles
Role name | Attributes | Member of
---------------+------------------------------------------------------------+-----------
jordanpapaleo | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
jp-tester | | {}
newuser | Create DB | {}
(END)
Seems like postgres, the role formerly known as database, formerly known as database technology, is important and missing. There is a 13 in 4 chance that I may have done this on a previous attempt to colonize Postgres. You want a postgres super user, you got one.
$ createuser --help
# output
Usage:
createuser [OPTION]... [ROLENAME]
Options:
-c, --connection-limit=N connection limit for role (default: no limit)
-d, --createdb role can create new databases
-D, --no-createdb role cannot create databases (default)
-e, --echo show the commands being sent to the server
-g, --role=ROLE new role will be a member of this role
-i, --inherit role inherits privileges of roles it is a
member of (default)
-I, --no-inherit role does not inherit privileges
-l, --login role can login (default)
-L, --no-login role cannot login
-P, --pwprompt assign a password to new role
-r, --createrole role can create new roles
-R, --no-createrole role cannot create roles (default)
-s, --superuser role will be superuser
-S, --no-superuser role will not be superuser (default)
-V, --version output version information, then exit
--interactive prompt for missing role name and attributes rather
than using defaults
--replication role can initiate replication
--no-replication role cannot initiate replication
-?, --help show this help, then exit
$ createuser -s postgres
Let’s double check in psql.
$ psql postgres
# from within psql
\du
# output
Role name | Attributes | Member of
---------------+------------------------------------------------------------+-----------
jordanpapaleo | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
jp-tester | | {}
newuser | Create DB | {}
postgres | Superuser, Create role, Create DB | {}
Looks like we have a few stragglers in there. I checked my notes (yes that’s why I take them) and in our binaries we have a command dropuser
. It does not take a rocket science to guess what this does.
# From our terminal, not psql
$ dropuser newuser
$ dropuser jp-tester
$ psql postgres
# From within psql
\du
# output
Role name | Attributes | Member of
---------------+------------------------------------------------------------+-----------
jordanpapaleo | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
postgres | Superuser, Create role, Create DB | {}
Ok, trying my node command again…. Whoa… it worked.
I am going to start talking to someone for a minute. No clue who I am talking to: internal monolog, the person reading this, a native inhabitant of postgres; totally up to you.
What do you mean, what node script ? Seriously, I never told you about this…. Why don’t you take a look at day 1 paragraph 3. Remember my node app? Ah ha. I am using the npm postgres package to write postgres from within a node process. This is the file I have been trying to run this whole time while be attacked by postgres trickery.
// initdb.js
const postgres = require('postgres')
const initdb = async () => {
try {
const sql = postgres({
database: 'jordan', // Name of database to connect to
port: 5432, // Postgres server port
})
await sql`DROP TABLE IF EXISTS users`
await sql`CREATE TABLE IF NOT EXISTS users (
id INT NOT NULL PRIMARY KEY GENERATED ALWAYS AS IDENTITY
, user_id varchar(50) NOT NULL
, measure_date date NOT NULL
, weight numeric(5,1) NOT NULL
)`
await sql.end()
} catch (err) {
console.log(err)
throw err
}
}
initdb()
.then(() => { console.log('FINISHED') })
.catch(() => { console.log('YOU ARE A FAILURE') })
node ./initdb.js
So we made a database, we made users, we figured out issues with the postgres role, and now, we actually created a table. I just want to stop a minute and bask in this moment… until tomorrow.