OpenBSD / PostgreSQL / Authentication

!
Warning: This post is over 365 days old. The information may be out of date.

If you are an OpenBSD user and you are using PostgreSQL, you can use the BSD authentication to login into PostgreSQL. We will see how to do this.

Install PostgreSQL

On my OpenBSD system, i have got an user pea and doas is configured to allow me to do all the administrator’s things.

The setup is quite easy:

$ doas pkg_add postgresql-server
quirks-2.395 signed on 2017-11-27T16:02:16Z
postgresql-server-9.6.6:postgresql-client-9.6.6: ok
postgresql-server-9.6.6: ok
The following new rcscripts were installed: /etc/rc.d/postgresql
See rcctl(8) for details.
Look in /usr/local/share/doc/pkg-readmes for extra documentation.

If you re not an OpenBSD user, you should read the file /usr/local/share/doc/pkg-readmes/postgresql-server-9.6.6 to see all the options available.

Create the cluster:

$ doas su _postgresql
$ mkdir /var/postgresql/data
$ initdb -D /var/postgresql/data/ -U postgres -E UTF-8 -A md5 -W
The files belonging to this database system will be owned by user "_postgresql".
This user must also own the server process.

The database cluster will be initialized with locale "C".
The default text search configuration will be set to "english".

Data page checksums are disabled.

Enter new superuser password:
Enter it again:

fixing permissions on existing directory /var/postgresql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 30
selecting default shared_buffers ... 128MB
selecting dynamic shared memory implementation ... posix
creating configuration files ... ok
running bootstrap script ... ok
performing post-bootstrap initialization ... ok
syncing data to disk ... ok

Success. You can now start the database server using:

pg_ctl -D /var/postgresql/data/ -l logfile start

Configuration

Now we will configure PostgreSQL to use the BSD authentication. For this, we need:

  • Add the user _postgresql (the user running PostgreSQL) to the auth group

    $ doas usermod -G auth _postgresql
    
  • Add a line to pg_hba.conf to specify the BSD authentication

    local	all		pea		bsd
    

Trick: this method is not mentionned in the file pg_hba.conf

Warning: the user pea must exist in PostgreSQL too !

Test

  • Start PostgreSQL

    $ doas /etc/rc.d/postgresql start
    postgresql(ok)
    
  • Create an user and a database

    $ psql -U postgres 
    Password for user postgres: 
    psql (9.6.6)
    Type "help" for help.
    
    postgres=# CREATE ROLE pea LOGIN ;
    CREATE ROLE
    postgres=# CREATE DATABASE pea OWNER pea ;
    CREATE DATABASE
    
  • Try it

    $ psql 
    Password: 
    psql (9.6.6)
    Type "help" for help.
    
    pea=> select user ;
    ┌──────────────┐
    │ current_user │
    ├──────────────┤
    │ pea          │
    └──────────────┘
    

(1 row)

pea=> select version();

┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐ │ version │ ├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┤ │ PostgreSQL 9.6.6 on x86_64-unknown-openbsd6.2, compiled by OpenBSD clang version 5.0.0 (tags/RELEASE_500/final) (based on LLVM 5.0.0), 64-bit │ └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘ (1 row) ```

Et voila 😃 !!

Related Posts