Create A User (aka Role) In Postgres
There aren't "Users" in Postgres. Only "Roles". Create a power role that can create databases by logging in with the root postgres role:
psql -u postgres
Then create the new role with:
CREATE ROLE new_role_name CREATEDB LOGIN ENCRYPTED PASSWORD 'some strong password';
Which will return
CREATE ROLE as the response.
Note that the
psql command defaults to connecting to a database with the same name as the role name, but creating a role does not create a database. So, if you immediately try to do
psql -U new_role_name it will fail with something like:
psql: error: connection to server on socket "/tmp/.s.PGSQL.5432" failed: FATAL: database "new_role_name" does not exist
You can either create a new database with the same name (TODO: Add directions or a link to create the user database), or you can log into the default postgres database with:
psql -d postgres -U new_role_name
Once you're logged in, you can create new database, then log out and log back in to the new database to work with it.
If you create a role and database with the same name as your local user account you can log in with just
psql I use this for basic database storage for my local tools.
Create a Roll for TDD Tests
sudo -u postgres psql -c "CREATE ROLE tdd_connections CREATEDB NOINHERIT;"
Steps from initial install via
This is the core of what's happening. The convenience commands are above are better for general usage.
psqlas the user
sudo -u postgres psql
 Create the role (e.g.
CREATE ROLE role_name LOGIN;
\q if you're done with it.
- Only roles that have the LOGIN attribute can be used as the initial role name for a database connection. A role with the LOGIN attribute can be considered the same as a "database user". To create a role with login privilege, use either:
As above: Database roles are global across a database cluster installation (and not per individual database).
In order to bootstrap the database system, a freshly initialized system always contains one predefined role. This role is always a "superuser", and by default (unless altered when running initdb) it will have the same name as the operating system user that initialized the database cluster. Customarily, this role will be named postgres. In order to create more roles you first have to connect as this initial role.
- Docs: https://www.postgresql.org/docs/9.5/static/database-roles.html
Create a role with
Even though Postgres calls them
roles instead of
users, the convenience command is:
sudo -u postgres createuser some_role_name
This works with a Ubuntu 16_04 server where postgresql is installed. (The
postgres user is installed at the same time). With the rest of the permissions stuff, it looks like logging in and setting stuff is more useful overall than
createuser. So, planning to go that way for the most part.
Database roles not confined to a particular database. They are global across the entire cluster