Postgresql – Getting locked out of PostgreSQL databases

postgresql

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.
Questions:

  1. Is there a simple guide to setting up roles and users in PostgreSQL?
  2. Can data be recovered in the case of lost passwords?
  3. Can PostgreSQL be used with security off similar to ms-access?
  4. 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?
  5. 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.

Best Answer

  1. The documentation should cover that pretty well. See in particular pg_hba.conf. It's hard to make "simple" guides on security, because if you simplify in the wrong way you make it wrong.

  2. Trivially so long as you have the ability to edit pg_hba.conf and reload the server. Just add an pg_hba.conf entry with trust authentication, reload PostgreSQL (pg_ctl reload, /etc/init.d/postgresql reload, whatever), change the password, and put pg_hba.conf back how it was.

  3. Disabling hostbased and password login security in PostgreSQL can be done by setting trust authentication in pg_hba.conf. It's usually much saner to use peer authentication for local connections instead, but this only works on unix systems.

  4. Users are just roles with the LOGIN attribute. Roles are global across the server. Most GRANTs of rights to users/roles are specific to particular databases or objects within databases. See the documentation for the GRANT command.

  5. "trick?". I think you'll want to post a new, more detailed and specific question on https://dba.stackexchange.com/ with the details, including exact commands run, exact error messages, etc.

I'm kind of surprised by your expectation that dropping then re-creating a user would retain their settings. This would create the opportunity for colossal security holes. The sysadmin joe leaves, you drop their account. New data entry joe starts, you create their account. Oops! The new Joe has all the rights of the completely different old Joe.

If you drop a table then re-create it, does it still have its contents? Why would users/roles be any different?

If you don't want to drop a user account, don't drop the user account. Disable it by REVOKEing CONNECT rights on the database(s), changing their password, etc.

Related Question