PostgreSQL Basics

Access Permissions
The default access permission (at least on Debian) are:


 * It is only possible to connect to the database using a Unix socket or TCP connection to localhost (127.0.0.1 or ::1).
 * Access through the Unix socket requires no password (users are identified by their shell name using the ident mechanism)
 * Access through a TCP connection requires a password.
 * The only user defined at installation is the user "postgres", a superuser, and has no password

The above means that after installation, it is only possible to access the database by becoming the postgres user in a shell:

% sudo su - postgres % psql template1

(template1 is a database that is present by default)

To alter the (host based) access permission, see the file /etc/postgresql/pg_hba.conf.

Creating and Viewing Users
To create a user, as a superuser do:

% sudo su - postgres   # log in as superuser "postgres" % createuser joe

To edit the password of the newly created user:

% pgsql =# alter user joe password 'some_password';

or:

=# \password

To delete a user, as a superuser do:

% sudo su - postgres   # log in as superuser "postgres" % dropuser joe

To view the current user list, first connect to the server:

% psql =# \du List of roles Role name | Superuser | Create role | Create DB | Connections | Member of ---+---+-+---+-+--- joe      | no        | no          | yes       | no limit    | {} postgres | yes       | yes         | yes       | no limit    | {} (2 rows)

Create and View Databases
To create a new database, joedb, with joe as owner:

% sudo su - postgres   # log in as superuser "postgres" % createdb -O joe joedb

To list all databases:

% psql =# \l List of databases Name   |  Owner   | Encoding ---+--+-- joedb     | joe      | UTF8 postgres | postgres | UTF8 template0 | postgres | UTF8 template1 | postgres | UTF8 (3 rows)

or simply:

% psql -l

Create Specific Access Privileges
Owners of a database have full access permissions, and so have superusers. It is wise to create an account with limited privileges (e.g. only INSERT, CREATE, UPDATE, DELETE) for (web) scripts that access a database. This prevents that such users can DROP database.

To create a user joehelper with limited privileges to the tables "table1" and "table2" in the joedb database:

% createuser joehelper % psql joedb =# ALTER USER joehelper PASSWORD 'some_password'; =# GRANT SELECT, INSERT, UPDATE, DELETE ON table1, table2 TO joehelper;

Note that (unlike MySQL), it is not possible in PostgreSQL to grant SELECT privileges to all tables in a database. You can find scripts on the Internet that apply the GRANT on all tables and views, but that will not apply to future tables.

To view access privileges on a database:

% psql joedb =# \dp Access privileges for database "joedb" Schema |  Name    | Type  |                 Access privileges +---+---+ public | table1    | table | {postgres=arwdxt/postgres,joehelper=arwd/postgres} public | table2   | table | {postgres=arwdxt/postgres,joehelper=arwd/postgres} (2 rows)