Accessing DBs: Users, Groups and Permissions

User (Client) Authentication

When a client application connects to the database server,
it specifies which PostgreSQL user name it wants to connect as.

You can access a database by running the interactive terminal psql
with the "-U" command line option to indicates the postgre user.

Users

DB users are conceptually completely separate from operating system users.
DB user names are global across a DB cluster installation (not per individual DB).
Passwords can be managed with the query language commands,
with CREATE USER and ALTER USER. Example: CREATE USER foo WITH PASSWORD 'secret'

Remove an existing user with SQL command:

DROP USER name
Shell scripts createuser and dropuser are provided as wrappers around these SQL commands.

User Attributes

Superuser: A DB superuser bypasses all permission checks.
Only a superuser can create new users, thus create a DB superuser with: CREATE USER name CREATEUSER DB creation: A user must be explicitly given permission to create DBs (except for superusers): CREATE USER name CREATEDB password User attributes can be modified after creation with ALTER USER.

Groups

As in Unix, groups are a way of logically grouping users to ease management of permissions: CREATE GROUP name To add users to or remove users from a group, use ALTER GROUP name ADD USER uname1, ... ALTER GROUP name DROP USER uname1, ...