PostgreSQL Notes

I don’t use Postgres quite heavily enough to remeber how to do certain administrative tasks. Having gotten sick of flipping back and forth between various sections in the manual, I’m making a page of notes here.

Anything here is probably permenantly burned in the brains of heavier users, but perhaps this will be useful to other light users.

General

Postgres generally runs as a dedicated user, and that dedicated user probably has complete control over adding roles and dbs.

To use postgres as a specific user, “psql -U username” is easier than su’ing to that user (and the postgres user you want may not be a system user anyway.

Creating a DB

createdb -U postgresuser newdbname

Adding a User

Users are just roles that can login.

createuser -P newusername

The -P flag is to tell it to set a password. It is possible to have accounts without passwords.

To see the users, from inside psql, do \du.

Assigning the DB to a user

This is night the same as granting rights to users (see the GRANT command), although the DB owner does have full rights in the DB.

alter database dbname owner to username;