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)