postgresql setup 2: authentication and roles
After you've created the database cluster, it's time to set up authentication and roles.
Authentication is handled through the pg_hba.conf (HBA stands for Host-Based Authentication) file. This configuration file supports seven formats of authentication rules:
For a standalone machine where local users are trusted, a rule of:
is sufficient. If password authentication is needed, several methods are supported, including MD5 hashing, Kerberos, LDAP, SSL Certificates, and PAM authentication. The PostgreSQL documentation explains authentication in detail.
The next step is to create user roles. Once the user logs in, PostgreSQL needs to know what to allow the user to do. A role can be assigned to a user or a group.
To begin assigning roles, log in to psql. As user postgres, you can simply type psql on the command line.
If your username is myuser, and you want to be able to log into PostgreSQL and create databases, and you want to encrypt the password in the database, you can create the role like this:
Except, of course, you'll want to choose a stronger password.
If you'll be connectiong to PostgreSQL through Apache, you'll need to create a role for the apache user. There's no need for Apache to create databases, so you can leave off that option.
The PostgreSQL documentation gives all the details on creating roles.
That should be enough to get PostgreSQL up and running. You can no exit the postgres user and try connecting from your own account.
Authentication is handled through the pg_hba.conf (HBA stands for Host-Based Authentication) file. This configuration file supports seven formats of authentication rules:
local database user auth-method [auth-options]
host database user CIDR-address auth-method [auth-options]
hostssl database user CIDR-address auth-method [auth-options]
hostnossl database user CIDR-address auth-method [auth-options]
host database user IP-address IP-mask auth-method [auth-options]
hostssl database user IP-address IP-mask auth-method [auth-options]
hostnossl database user IP-address IP-mask auth-method [auth-options]
For a standalone machine where local users are trusted, a rule of:
local all all trust
is sufficient. If password authentication is needed, several methods are supported, including MD5 hashing, Kerberos, LDAP, SSL Certificates, and PAM authentication. The PostgreSQL documentation explains authentication in detail.
The next step is to create user roles. Once the user logs in, PostgreSQL needs to know what to allow the user to do. A role can be assigned to a user or a group.
To begin assigning roles, log in to psql. As user postgres, you can simply type psql on the command line.
If your username is myuser, and you want to be able to log into PostgreSQL and create databases, and you want to encrypt the password in the database, you can create the role like this:
CREATE ROLE myuser WITH CREATEDB LOGIN ENCRYPTED PASSWORD 'password1'
Except, of course, you'll want to choose a stronger password.
If you'll be connectiong to PostgreSQL through Apache, you'll need to create a role for the apache user. There's no need for Apache to create databases, so you can leave off that option.
CREATE ROLE apache WITH LOGIN ENCRYPTED PASSWORD 'letmein'
The PostgreSQL documentation gives all the details on creating roles.
That should be enough to get PostgreSQL up and running. You can no exit the postgres user and try connecting from your own account.
Labels: Linux, PostgreSQL
posted by BruceA at
10:18 PM
0 Comments:
Post a Comment
Links to this post:
Create a Link
<< Home