How PostgreSQL security

Dr.MohitBansal Profile Pic
Published Date:26-10-2017
Your Website URL(Optional)
Security In this chapter, we will cover the following recipes: f The PostgreSQL superuser f Revoking user access to a table f Granting user access to a table f Creating a new user f Temporarily preventing a user from connecting f Removing a user without dropping their data f Checking whether all users have a secure password f Giving limited superuser powers to specific users f Auditing DDL changes f Auditing data changes f Always knowing which user is logged in f Integrating with LDAP f Connecting using SSL f Using SSL certicat fi es to authenticate the client f Mapping external usernames to database roles f Encrypting sensitive data 157Security Introduction Databases are mostly used to keep data with several restrictions on how it can be used. Some records or tables can only be seen by certain users, and even for those tables that are visible to everyone, there can be restrictions on who can insert new data or change the existing data. All of this is managed by a privilege system, where users are granted different privileges for different tables or other database objects, such as schemas or functions. It is good practice not to grant these privileges directly to users, but to use an intermediate role to collect a set of privileges. Then, instead of granting all the same privileges to the actual user, this entire role is granted to users needing these privileges. For example, a "clerk" role may have rights to both insert data and update existing data in theuser_account table, but may have rights to only insert data in theaudit_log table. Another aspect of database security is making sure that only the right people can access the database, and that one user can't see what other users are doing (unless you are an administrator or auditor), and whether users can or cannot grant forward the roles granted to them. Yet another important part of security is to make sure that database servers are in physically secure locations, and that the procedures to access these servers are secure. However, this is not a general guide to securing your database, server machine, or network, which is too large a topic to be covered here. If you are serious about security, then read some of the available books and articles on security, or hire a security consultant. Database security is just a small piece in the overall security puzzle. Typical user role The minimal production database setup contains at least two types of users, namely administrators and end users, where administrators can do everything (they are superusers), and end users can only do very little, usually just modify the data in only a few tables and read from a few more. It is not a good idea to let ordinary users create or change database object definitions, meaning that they should not have theCREATE privilege on any schema, includingPUBLIC. There can be more roles for different types of end users, such as analysts, who can only select from a single table or view, or some maintenance script "users" who see no data at all and just have the ability to execute a few functions. Alternatively, there can also be a manager role, which can grant and revoke roles for other users but is not supposed to do anything else. 158Chapter 6 The PostgreSQL superuser In this recipe, you will learn how to turn a user into an all-powerful superuser and back to an ordinary user. A PostgreSQL superuser is a user that can do anything in the database regardless of what privileges it has been granted. How to do it… A user becomes a superuser when it is created with theSUPERUSER attribute set: CREATE USER username SUPERUSER; A user can be deprived of its superuser status by removing theSUPERUSER attribute, using this command: ALTER USER username NOSUPERUSER; A user can be restored to superuser status later, using the following command: ALTER USER username SUPERUSER; When neitherSUPERUSER norNOSUPERUSER is given in theCREATE USER command, then the default is to create a user who is not a superuser. How it works… Rights to some operations in PostgreSQL cannot be granted. They must be performed by a special user who has this special attribute set. The preceding commands set or reset this attribute for the user. There's more… The PostgreSQL system comes set up with at least one superuser. Most commonly, this superuser is namedpostgres, but it is actually named the same as the system user who owns the database directory and with whose rights the PostgreSQL server runs. Other superuser-like attributes In addition toSUPERUSER, there are two lesser attributes—CREATEDB andCREATEUSER—that give the user only some of the power reserved to superusers, namely creating new databases and users. See the Giving limited superuser powers to specific users recipe for more information on this. 159Security Attributes are never inherited Later, you will learn about granting one role to another, role inheritance, and how privileges can be granted through these intermediate "group roles". None of this applies to attributes—to perform superuser-only operations, you must be that user. See also Also check out the Always knowing which user is logged in recipe in this chapter. All of the following recipes assume a non-superuser unless explicitly mentioned that they apply to or need a superuser. Revoking user access to a table This recipe answers the question, "How do I make sure that user X cannot access table Y?" Getting ready The current user must either be a superuser, the owner of the table, or a user with aGRANT option for the table. Also, you can't revoke rights from a user who is a superuser. How to do it… To revoke all rights on thetable1 table from theuser2 user, you must run the following SQL command: REVOKE ALL ON table1 FROM user2; However, ifuser2 had been granted another role that gives them some rights ontable1, say role3, this command is not enough; you must also choose one of the following options: f "Fix" the user; that is, revokerole3 fromuser2 f "Fix" the role; that is, revoke privileges ontable1 fromrole3 Both choices are imperfect, because of their side effects: the former will revoke all the privileges associated torole3, not only the privileges concerningtable1; the latter will revoke the privileges ontable1 from all the other users that have been grantedrole3, not only fromuser2. 160Chapter 6 It is normally better to avoid damaging other legitimate users, so we opt for the first solution. Here is a worked example: 1. Using psql, display the list of roles that have been granted at least one privilege on table1, by issuing\z table1. For instance, you can obtain the following output (an extra column about column privileges has been removed from the right-hand side because it was not relevant here): Access privileges Schema Name Type Access privileges ... ++-+-+ ... public table1 table postgres=arwdDxt/postgres+ ... role3=r/postgres + ... role5=a/postgres ... (1 row) 2. Then, we check whetheruser2 is a member of any of those roles by typing\du user2: List of roles Role name Attributes Member of -++- user2 role3, role4 3. From the previous step, we notice thatrole3 had been granted theSELECT privilege ("r" for "read") by thepostgres user, so we must revoke it, as follows: REVOKE role3 FROM user2; 4. We must also inspectrole4. Even if it doesn't have privileges ontable1, in theory it could be a member of one of the three roles that have privileges on that table. We issue\du role4 and get the following output: List of roles Role name Attributes Member of -++- role4 Cannot login role5 Our suspicion was founded:user2 can get theINSERT privilege ("a" for "append") on table1, first via role4 and then viarole5. So, we must break this two-step chain, as follows: REVOKE role4 FROM user2; 161Security This example may seem too unlikely to be true. We unexpectedly gain access to the table via a chain of two different role memberships, which was made possible by the fact that a non-login role such asrole4 was made a member of another non-login role, that is,role5. In most real-world cases, superusers will know very well whether such cases exist at all, so there will be no surprise; however, the goal of this recipe is to make sure that the user cannot access the table, meaning we cannot exclude less likely options. How it works… The\z command, as well as its synonym,\dp, displays all privileges granted on tables, views, and sequences. If theAccess privileges column is empty, it means "default privileges"; that is, all privileges are given to the owner (and the superusers, as always). The\du command shows attributes and roles that have been granted to roles. Both commands accept an optional name or pattern to restrict the display. There's more… Here we'll cover some good practices on user and role management. Database creation scripts For production systems, it is usually a good idea to always includeGRANT andREVOKE statements in the database creation script so that you can be sure that only the right set of users has access to the table. If this is done manually, it is easy to forget. Also, in this way, we are sure that the same roles are used on development and testing environments, so there are no surprises at deployment time. The following is a sample extract from the database creation script: CREATE TABLE table1( ... ); GRANT SELECT ON table1 TO webreaders; GRANT SELECT, INSERT, UPDATE, DELETE ON table1 TO editors; GRANT ALL ON table1 TO admins; 162Chapter 6 Default search path It is always good practice to use a fully qualified name when revoking or granting rights; otherwise, you may be working with the wrong table inadvertently. To see the effective search path for the current database, run the following: pguser= show search_path ; search_path "user",public (1 row) To see which table will be affected if you omit the schema name, run the following in psql: pguser= \d x Table "public.x" Column Type Modifiers ++- Thepublic.x table name in the response contains the full name, including the schema. Securing views It is a common technique to use a view to disclose only some parts of a secret table; however, a clever attacker can use access to the view to display the rest of the table using log messages. For instance, consider the following example: CREATE VIEW for_the_public AS SELECT FROM reserved_data WHERE importance 10; GRANT SELECT ON for_the_public TO PUBLIC; A malicious user could define this function, as follows: CREATE FUNCTION f(text) RETURNS boolean COST 0.00000001 LANGUAGE plpgsql AS BEGIN RAISE INFO '1: %', 1; RETURN true; END; ; 163Security Then, they could use it to filter rows from the view: SELECT FROM for_the_public x WHERE f(x :: text); The PostgreSQL optimizer will then internally rearrange the query, expanding the definition of the view and then combining the two filter conditions into a single WHERE clause. The trick here is that the function has been "told" to be very cheap using theCOST keyword, so the optimizer will choose to evaluate that condition first. In other words, the function will access all the rows in the table, as you will realize when you see the correspondingINFO lines on the console if you run the code yourself. This security leak has been fixed in PostgreSQL version 9.2 with the introduction of the security_barrier attribute: ALTER VIEW for_the_public SET (security_barrier = on); This means that the conditions that define the view will always be computed first, irrespective of cost considerations. The performance impact of this fix has been mitigated by introducing the LEAKPROOF attribute for functions. In short, a function that cannot "leak" information other than its output value can be marked asLEAKPROOF by a superuser, so the planner will know that it's secure to compute the function before the other view conditions. Granting user access to a table A user needs to have access to a table in order to perform any action on it. Getting ready Make sure that you have appropriate roles defined, and that privileges are revoked from the PUBLIC role. How to do it… Grant access to the schema containing the table, as follows: GRANT USAGE ON someschema TO somerole; GRANT SELECT, INSERT, UPDATE, DELETE ON someschema.sometable TO somerole; GRANT somerole TO someuser, otheruser; 164Chapter 6 How it works… This sequence of commands first grants full access to all objects in that schema to a role, gives viewing (SELECT) and modifying (INSERT,UPDATE, andDELETE) rights on that table to the role, and then grants membership in that role to two database users. There's more… There is no requirement in PostgreSQL to have some privileges in order to have others. This means that you may well have "write-only" tables, where you are allowed to insert but you can't select. This can be used to implement a mail-queue-like functionality, where several users post messages to one user, but they can't see what other users have posted. Alternatively, you can write a record, but you can't change or delete it. This is useful for auditing log type tables, where all changes are recorded, and which are not tampered with. Access to the schema We had to grant access to the schema in order to allow access to the table. This suggests that access to a given schema can be used as a fast and extreme way to prevent any access to any object in that schema. Otherwise, if you want to allow some access, you must use specific GRANT andREVOKE statements as needed. Granting access to a table through a group role It is often desirable to give a group of users similar permissions to a group of database objects. To do this, you first assign all the permissions to a proxy role (also known as a permission group), and then assign the group to selected users, as follows: CREATE GROUP webreaders; GRANT SELECT ON pages TO webreaders; GRANT INSERT ON viewlog TO webreaders; GRANT webreaders TO tim, bob; Now, bothtim andbob have theSELECT privilege on thepages table andINSERT on the viewlog table. You can also add privileges to the group role after assigning it to users. Consider the following command: GRANT INSERT, UPDATE, DELETE ON comments TO webreaders; After running this command, bothbob andtim have all of the aforementioned privileges on thecomments table. This assumes that both thebob andtim roles were created with theINHERIT default setting. Otherwise, they do not automatically "inherit" the rights of roles but need to explicitly set their role to the granted user to make use of the privileges granted to that role. 165Security Granting access to all objects in a schema Before version 9.0 of PostgreSQL, there was no easy way to manipulate privileges to more than one object at a time, except listing them all in theGRANT orREVOKE command. Version 9.0 added a capability to grant or revoke privileges on all objects of a certain kind in a specific schema: GRANT SELECT ON ALL TABLES IN SCHEMA staging TO bob; You still need to grant the privileges on the schema itself in a separateGRANT statement. Creating a new user In this recipe, we will show you two ways of creating a new database user, one with a dedicated command-line utility, and one using SQL commands. Getting ready To create new users, you must either be a superuser or have theCREATEROLE or CREATEUSER privilege. How to do it… From the command line, you can run thecreateuser command: pguserhvost: createuser bob If you add theinteractive command-line option, you activate the interactive mode, which means you will be asked some questions, as follows: pguserhvost: createuser interactive alice Shall the new role be a superuser? (y/n) n Shall the new role be allowed to create databases? (y/n) y Shall the new role be allowed to create more new roles? (y/n) n Withoutinteractive, the preceding questions get "no" as the default answer; you can change that with the-,-d, and-r command-line options. In interactive mode, questions are asked only if they make sense. One example is the case when the user will be a superuser; no other questions are asked because a superuser is not subject to privilege checks. Another example is when using one of the preceding options to specify a non-default setting; the corresponding question will not be asked. Theinteractive switch has been introduced in version 9.2; the interactive behavior is always active in prior versions. 166Chapter 6 How it works… Thecreateuser program is just a shallow wrapper around executing SQL against the database cluster. It connects to thepostgres database and then executes SQL commands for user creation. To create the same users through SQL, you can issue the following commands: CREATE USER bob; CREATE USER alice CREATEDB; There's more… You can check the attributes of a given user in psql, as follows: pguser= \du alice This gives the following output: List of roles Role name Attributes Member of -++- alice Create DB TheCREATE USER andCREATE GROUP commands are actually variations ofCREATE ROLE. TheCREATE USER username; statement is equivalent toCREATE ROLE username LOGIN;, and theCREATE GROUP groupname; statement is equivalent toCREATE ROLE groupname NOLOGIN;. Temporarily preventing a user from connecting Sometimes, you need to temporarily revoke a user's connection rights without actually deleting the user or changing the user's password. This recipe presents ways to do this. Getting ready To modify other users, you must either be a superuser or have theCREATEROLE privilege (in the latter case, only non-superuser roles can be altered). 167Security How to do it… To temporarily prevent the user from logging in, run this command: pguser= alter user bob nologin; ALTER ROLE To let the user connect again, run the following: pguser= alter user bob login; ALTER ROLE How it works… This sets a flag in the system catalog, telling PostgreSQL not to let the user log in. It does not kick out already connected users. There's more… Here are some additional remarks. Limiting the number of concurrent connections by a user The same result can be achieved by setting a connection limit for that user to 0: pguser= alter user bob connection limit 0; ALTER ROLE To allow 10 concurrent connections for thebob user, run this command: pguser= alter user bob connection limit 10; ALTER ROLE To allow an unlimited number of connections for this user, run the following: pguser= alter user bob connection limit -1; ALTER ROLE Forcing NOLOGIN users to disconnect In order to make sure that all users whose login privilege has been revoked are disconnected right away, run the following SQL statement as a superuser: SELECT pg_terminate_backend(pid) FROM pg_stat_activity a JOIN pg_roles r ON a.usename = r.rolname AND not rolcanlogin; 168Chapter 6 This disconnects all users who no longer are allowed to connect by terminating the backends opened by these users. The preceding query works with PostgreSQL version 9.2 or above; on older releases, you must replacepid withprocpid in the first line. Removing a user without dropping their data When trying to drop a user who owns some tables or other database objects, you get the following error, and the user is not dropped: testdb= drop user bob; ERROR: role "bob" cannot be dropped because some objects depend on it DETAIL: owner of table bobstable owner of sequence bobstable_id_seq This recipe presents two solutions to this problem. Getting ready To modify users, you must either be a superuser or have theCREATEROLE privilege. How to do it… The easiest solution to this problem is to refrain from dropping the user, and use the trick from a previous recipe to prevent the user from connecting: pguser= alter user bob nologin; ALTER ROLE This has the added benefit of the original owner of the table being available later, if needed, for auditing or debugging purposes ("Why is this table here? Who created it?"). Then, you can assign the rights of the "deleted" user to a new user, using the following code: pguser= grant bob bobs_replacement; GRANT 169Security How it works… As noted previously, a user is implemented as a role with the login attribute set. This recipe works by removing that attribute from the user, which then is kept just as a role. If you really need to get rid of a user, you have to assign all ownerships to another user. To do so, run the following query, which is a PostgreSQL extension to SQL standard: REASSIGN OWNED BY bob TO bobs_replacement; It does exactly what it says—assigns ownership of all database objects currently owned by the bob role to thebobs_replacement role. However, you need to have privileges on both the old and the new roles to do that, and you need to do it in all databases wherebob owns any objects, as theREASSIGN OWNED command works only on the current database. After this, you can delete the original user,bob. Checking whether all users have a secure password PostgreSQL has no built-in facilities to make sure that you are using strong passwords. The best you can do is make sure that all users' passwords are encrypted, and that your pg_hba.conf file does not allow logins with a plain password. That is, always use MD5 as the login method for users. For client applications connecting from trusted private networks, either real or virtual (VPN), you may use host-based access, that is, if you know that the machine on which the application is running is not used by some non-trusted individuals. For remote access over public networks, it may be a better idea to use SSL client certificates. How to do it… To see which users have unencrypted passwords, use this query: test2= select usename,passwd from pg_shadow where passwd not like 'md5%' or length(passwd) 35; usename passwd + tim weakpassword asterisk md5chicken (2 rows) 170Chapter 6 To see users with encrypted passwords, use the following: test2= select usename,passwd from pg_shadow where passwd like 'md5%' and length(passwd) = 35; usename passwd +- bob2 md518cf038878cd04fa207e7f5602013a36 (1 row) How it works… Having the passwords encrypted in the database is just half of the equation. The bigger problem is making sure that users actually use passwords that are hard to guess; that is, passwords such aspassword,secret, ortest are out, and most common words are not good passwords either. If you don't trust your users to select strong passwords, you can write a wrapper application that checks the password strength and make them use that when changing passwords. There exists a contrib module for doing so for a limited set of cases (password sent from client to server in plain text). Visit passwordcheck.html for more information on this. Giving limited superuser powers to specific users First, the superuser role has some privileges, which can also be granted to non-superuser roles separately. To give thebob role the ability to create new databases, run this: ALTER ROLE BOB WITH CREATEDB; To give thebob role the ability to create new users, run the following: ALTER ROLE BOB WITH CREATEUSER; However, it is also possible to give ordinary users more fine-grained and controlled access to some action reserved for superusers, using SECURITY DEFINER functions. The same trick can also be used to pass partial privileges between different users. 171Security Getting ready First, you must have access to the database as a superuser in order to delegate some powers. Here, we assume the use of the default superuser namedpostgres. We will demonstrate two cases of making some superuser-only functionality available to select an ordinary user. The database must have support for the PL/pgSQL embedded language installed. Starting from PostgreSQL 9.0, the recommended default behavior is to have PL/pgSQL installed in a newly created database, but this can be changed by package creators or site administrators. If it is not, run the following as a PostgreSQL superuser: test2= CREATE LANGUAGE plpgsql; CREATE LANGUAGE How to do it… One thing that a superuser can do and ordinary users cannot is telling PostgreSQL to copy table data from a file: pguserhvost: psql -U postgres test2 ... test2= create table lines(line text); CREATE TABLE test2= copy lines from '/home/bob/names.txt'; COPY 37 test2= SET ROLE to bob; SET test2= copy lines from '/home/bob/names.txt'; ERROR: must be superuser to COPY to or from a file HINT: Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone. 172Chapter 6 To letbob copy directly from the file, the superuser can write a special wrapper function for bob, as follows: create or replace function copy_from(tablename text, filepath text) returns void security definer as declare begin execute 'copy ' quote_ident(tablename) ' from ' quote_literal(filepath) ; end; language plpgsql; It is usually a good idea to restrict usage of such a function to the intended user only: revoke all on function copy_from( text, text) from public; grant execute on function copy_from( text, text) to bob; You may also want to verify thatbob imports files only from his home directory. Unfortunately, this solution is not completely secure against superuser privilege escalation by a malicious attacker. This is because the execution of theCOPY command inside the function will also cause the execution, as thepostgres user, of all side effects, such as the execution of anyINSERT trigger, computation of anyCHECK constraint, computation of any functional index, and more. In other words, if the user wants to execute a given function as the superuser, it's enough to put that function inside any of the preceding functions. There are a few workarounds for this security hole, none of which is optimal: f Require that the table has no triggers,CHECK constraints, and functional indexes. f Instead of runningCOPY on the given table, create a new table with the same structure using theCREATE newtable(LIKE oldtable) syntax. Run theCOPY against the new table, drop the old table, and rename the new table like the old table. 173Security How it works… When a function defined with security definer is called, PostgreSQL changes the session's rights to those of the user who defined the function while that function is being executed. So, whenbob executes thecopy_from(tablename, filepath) function,bob is effectively promoted to superuser for the time the function is running. This behavior is similar to thesetuid flag in Unix systems, where you can have a program to be run by anybody (with execute access) as the owner of that program. It also carries similar risks. There's more… There are other operations that are reserved for PostgreSQL superusers, such as setting certain parameters. Writing a debugging_info function for developers Several of the parameters controlling logging are reserved for superusers. If you want to allow some of your developers to set logging on, you can write a function for them to do exactly that: create or replace function debugging_info_on() returns void security definer as begin set client_min_messages to 'DEBUG1'; set log_min_messages to 'DEBUG1'; set log_error_verbosity to 'VERBOSE'; set log_min_duration_statement to 0; end; language plpgsql; revoke all on function debugging_info_on() from public; grant execute on function debugging_info_on() to bob; 174Chapter 6 You may also want to have a function to go back to the default logging state by assigning DEFAULT to all the variables involved: create or replace function debugging_info_reset() returns void security definer as begin set client_min_messages to DEFAULT; set log_min_messages to DEFAULT; set log_error_verbosity to DEFAULT; set log_min_duration_statement to DEFAULT; end; language plpgsql; There's no need forGRANT andREVOKE statements here, as setting them back to default does not pose a security risk. Instead ofSET xxx to DEFAULT, you can also use a shorter version of the same command, namelyRESET xxx. Alternatively, you can simply end your session, as the parameters are valid only for the current session. Auditing DDL changes This recipe shows you how you can collect Data Definition Language (DDL) from database logs in order to audit changes to the database structure. Getting ready Edit yourpostgresql.conf file to set the following: log_statement = 'ddl' Setting it tomod orall is also OK for this. Don't forget to reload the configuration: /etc/init-d/postgresql reload 175Security How to do it… Now find all occurrences of the CREATE,ALTER, andDROP commands in the log: postgreshvost: egrep -i "createalterdrop" \ /var/log/postgresql/ postgresql-9.4-main.log If log rotation is in effect, you may need to use grep on older logs as well. If the available logs are too new, and you haven't saved the older logs in some other place, you are out of luck. The default settings in thepostgresql.conf file for log rotation are as follows: log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_age = 1d log_rotation_size = 10MB Log rotation can also be implemented with third-party utilities. For instance, the default behavior on Debian and Ubuntu distributions is to use thelogrotate utility to compress or delete old log files, according to the rules specified in the /etc/ logrotate.d/postgresql-common file. To make sure you have the full history of DDL commands, you may want to set up a cron job that saves the DDL statements extracted from the main PostgreSQL log to a separate DDL audit log. You would still want to verify that the logs are not rotating too fast for this to catch all DDL statements. How it works… The changes topostgresql.conf instruct PostgreSQL to log all DDL commands in PostgreSQL's main log. Theegrep… command extracts only the DDL queries from the log file. There's more… Some additional auditing information can be accessed as explained here. 176

Advise: Why You Wasting Money in Costly SEO Tools, Use World's Best Free SEO Tool Ubersuggest.