EGTDC Database Course 2004 Database Users And Security Backing-Up - - PowerPoint PPT Presentation

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
• r 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

You 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.