EGTDC Database Course 2004 Database Users And Security ...

cuttlefishblueData Management

Dec 16, 2012 (4 years and 6 months ago)

190 views

Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
EGTDC Database Course 2004
Database Users And Security
Backing­Up Data
Tim Booth : tbooth@ceh.ac.uk
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Overview
Areas covered:

Controlling who can connect

Table­level privileges

Defining user groups

Remote connections to PostgreSQL running on Bio­Linux

Backups

Recovery
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Importance of Security
Unless you are running a major, multi­user database, some of the security 
facilities are overkill.  However, you should consider the following:
As the super­user, you are only a typo away from disaster.  DROP TABLE 
can't be undone!
If you are considering public browsing of the database, you should at least 
have a read­only account.
Is there sensitive/unpublished information in the database?
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Creating Users
Administrative users may add other users. 
In PostgreSQL this is done either by issuing an SQL command or with the 
createuser
 command­line program.
SQL version: note that everything after the first line is optional.
CREATE USER
goldie
WITH ENCRYPTED PASSWORD '
n14hcniK001
'
CREATEDB
NOCREATEUSER
VALID UNTIL '
16 Mar 2004
'
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
User Privileges
The database administrator has full rights to all database objects.
A new user must be granted rights to view or modify objects in the 
database.
Rights may be assigned with the GRANT and REVOKE commands:
eg: 
GRANT UPDATE ON 
customer 
TO
 goldie;
Our new user may now update the information held in the customer table.
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Privileges on Tables
For a given user, four privileges apply to each table, view or sequence.  
These correspond to the four SQL statements which manipulate data.
SELECT
 : Ability to see the data
UPDATE
 / 
DELETE
 : Ability to modify the data
INSERT
 : Ability to append new rows
To allow our user to see data about movies, and alter or delete reservations:
GRANT 
SELECT
 ON 
movie
 TO 
goldie
;
GRANT 
ALL
 ON 
reservation
 TO 
goldie
;
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
User Groups

Setting privileges for every user on every table is tiresome.

You can put users into groups and grants rights to the group.  All users in 
the group, including any added later, will inherit the group rights.

You can give permissions on a table to any number of groups.

A user can be a member of any number of groups.

You can grant privileges to 
PUBLIC 
so that all users have the specified 
permissions.

A user gets all permissions given to them explicitly, plus any group 
permissions, plus any public permissions.

Tables are never owned by a group, always a user.
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Modifying Groups

CREATE GROUP 
staff
;

ALTER GROUP 
staff
 ADD USER 
goldie
;

GRANT INSERT ON 
video
 TO GROUP 
staff
;

CREATE USER 
tim
 IN GROUP 
staff;
Sack Goldie (possibly for problems of bad attitude):

ALTER GROUP
 staff 
DROP USER
 goldie; 

DROP USER
 goldie;
Make our video list public:

GRANT SELECT ON
 video 
TO
 PUBLIC;
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
MySQL
See the MYSQL documentation.  Briefly:

Users are created with GRANT
GRANT ALL on
mydb
.* TO
goldie
@%
IDENTIFIED BY “
password
”;
DELETE FROM user WHERE User = “
goldie
”;

MySQL does not have groups, but you can assign privileges at the table 
or column level.  You can also set different privileges when the same user 
connects from different hosts.

Always remember to 
FLUSH PRIVILEGES;
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Remote Connections to PostgreSQL

For security reasons, this is disabled by default.

Bio­Linux specific details are on the EGTDC website:
http://envgen.nox.ac.uk/envgen/software/archives/000447.html

You need to modify 
postgresql.conf
and
pg_hba.conf.

If security is important, use the built in SSL encryption.
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
User Authentication

pg_hba.conf

controls user authentication.  Modify the examples in 
the file to your needs.
Basic methods:
trust = 
No authentication at all – use only for debugging!
ident sameuser = 
  The default.  You can connect as the user with the same 
name as your Linux login.
md5 = 
Users will be challenged for a password, which will be transmitted 
securely.  Recommended for all remote connections.
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Backups

You do backup your files, don't you?

Most sites offer some form of backup facility, or you can use the CD 
writer on the machine.

You can lose data for any number of reasons.

Human error

Hardware failure

Malicious action/hacking

Or you might simply want to copy your database to another machine
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Backups
It is possible to backup a database by simply saving the 
/home/db/postgres
 working directories as part of your regular backups. 
 This is fine for disaster recovery, but not ideal because:

These files cannot be read by newer or older versions of the PostgreSQL 
server.

There is no way to control what data you backup or restore.

There is no way to see what a backup file contains.

If you accidentally leave PostgreSQL running when you restore then 
bad 
things
 will happen.
The one­shot command (as user postgres):
pg_dumpall > dump.sql
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Selective Backup and Restoring
Y
ou can also back up a single database using pg_dump:
pg_dump --create mydb > mydb.sql
If you want to restore the database onto a new machine, you also need to 
backup the system tables, to get back all the users and groups – and you 
will need to restore these first!:
pg_dumpall -g > globdump.sql
To restore backups, use 
psql -f <dumpfile.sql>
:
psql -f globdump.sql; psql -f mydb.sql
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
Regaining Control

It is possible to lock yourself out of your own database, even if you are 
the manager/root user on your machine!

If you have have set a password but forgotten it, you can temporarily 
modify the pg_hba.conf file to 'trust' all local users.  Then:
sudo pg_ctl reload
psql -U postgresql

On MySQL, you can stop the server and restart giving the 

skip

grant

tables
 option.  You will be permitted to login 
without a password.  Set a new password, then 
FLUSH PRIVILEGES
.
Environmental Genomics Thematic Programme
Data Centre
http://envgen.nox.ac.uk
References and Further Information
Any database administrator needs the manual to hand!
http://www.postgresql.org/docs/7.3/interactive/sql-grant.html
If you have a PostgreSQL book, such as Worsley/Drake, be aware that 
there have been several changes to the details of user admin in recent 
releases of PG – it is always best to consult the specific documentation 
for your version.