One of the greatest fears I have is getting locked out of my own data due to the security features of a heavy weight database such as PostgreSQL (and PostGIS). Whilst I can log in as the administrator, I would like to do things properly and create users with defined roles. unfortunately in my preliminary testing, I have never been able to get this to work reliably. I clearly do not understand the security model of PostgreSQL as managed by pgAdmin III.
- Is there a simple guide to setting up roles and users in PostgreSQL?
- Can data be recovered in the case of lost passwords?
- Can PostgreSQL be used with security off similar to ms-access?
- In the PostgreSQL security model, the users are created at server level and can be assigned to different roles in each of the databases listed within a server?
- Is there a trick with creating users and roles (see below)?
In ms-access if a user is created called 'ted' and given a password, then deleted and then a user called 'ted' recreated. The first ted and second ted are not the same. In addition to the visible name, there is a hidden unique code which is generated each time a user is created. This makes it impossible to recreate a deleted user. I just wonder if PostgreSQL has hidden logic like that that can bamboozle a new user.
The key issue I am having is that when I define permissions for a user on a particular database via PgAdmin III, I can't connect to the database using that users name and password from QGIS.
Currently, I am using SpatialLite to avoid this issue.