SLIDE 1
Overview
Areas covered:
- Controlling who can connect
- Table-level privileges
- Defining user groups
- Remote connections to PostgreSQL running on Bio-Linux
- Backups
- Recovery
NEBC Database Course 2008 Database Users And Security Backing-Up - - PowerPoint PPT Presentation
NEBC Database Course 2008 Database Users And Security Backing-Up - - PowerPoint PPT Presentation
NEBC Database Course 2008 Database Users And Security Backing-Up Data Tim Booth : tbooth@ceh.ac.uk Overview Areas covered: Controlling who can connect Table-level privileges Defining user groups Remote connections to
SLIDE 2
SLIDE 3
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?
SLIDE 4
Creating Users
Administrative users may add other users (aka user roles). 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'
SLIDE 5
User Privileges
A administrator or 'super-user' has full rights to all database
- bjects.
The owner of the object also has this ability. 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.
SLIDE 6
Privileges on Tables
For a given user, four main 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;
SLIDE 7
Scope of privileges
- User accounts are global to the database
server.
- Server can have many databases
- Database can have many schemas
- Schema can have many objects
- (Table can have many rows)
SLIDE 8
User Groups
- Setting individual privileges for every user on every table is
tiresome.
- As well as user roles you can define group roles. All users in
the group, including any added later, will inherit the group rights.
- 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.
SLIDE 9
Modifying Groups
- CREATE ROLE staff;
- GRANT staff TO goldie;
- GRANT INSERT ON video TO staff;
- CREATE USER tim IN ROLE staff;
Sack Goldie (possibly for problems of bad attitude):
- REVOKE staff FROM goldie;
- DROP USER goldie;
Make our video list public:
- GRANT SELECT ON video TO PUBLIC;
SLIDE 10
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;
SLIDE 11
Remote Connections to PostgreSQL
- For security reasons, non-local access is disabled by default
- When you do enable it, PostgreSQL defaults to using encrypted
connections.
- You need to modify postgresql.conf and pg_hba.conf
SLIDE 12
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.
SLIDE 13
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
SLIDE 14
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 manager): sudo -u postgres pg_dumpall > dump.sql
SLIDE 15
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 eg.:
psql -f globdump.sql
SLIDE 16
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.
SLIDE 17