Thread: Need Help

Need Help

From

nandan

Date:

09 August 2013, 23:43:58

Hello All ;

Please help me in knowing below queries which are in Mysql to Postgresql.

 1.
SELECT user,host,password
  FROM mysql.user
  WHERE password = '';
SET PASSWORD FOR <user>@<host> =
  PASSWORD ('newpass');

2.
SELECT user,host,password
  FROM mysql.user
  WHERE user  = '';
DELETE FROM mysql.user
  WHERE user  = '';
FLUSH PRIVILEGES;

3.
SELECT user,host,password
  FROM mysql.user
  WHERE user = 'root' AND host = '%';
DROP USER root@'%';
FLUSH PRIVILEGES;

4.
SELECT user,host,password
  FROM mysql.user
  WHERE length(password) <> 41;
SET PASSWORD FOR <user>@<host> =
  PASSWORD ('newpass');

5.
Do not enable insecure password generation option

Setting can be verified by viewing the MySQL config file as per the
Recommended settings, OR by issuing the following command:

mysqladmin var | grep old_passwords


6.
Enable secure password authentication option by blocking connections from
all accounts that have passwords stored in it.

Setting can be verified by viewing the MySQL config file as per the
Recommended settings, OR by issuing the following command:

mysqladmin var | grep secure_auth


7.
Binary logging should be enabled to log all completed transactions, and
allow for point-in-time recovery.  This can be enabled via the log-bin
status variable in the mysql configuration file.  As an example, the
following entry will place all binary log files in the /var/lib/mysql/logs
directory, and use 'binlog' as the filename prefix to get binary log files
names such as binlog.000001:

   log-bin=/var/lib/mysql/logs/binlog

Setting can be verified by viewing the MySQL config file as per the
Recommended settings, OR by issuing the following command:

mysqladmin var | grep log_bin


8.
Prevent the grant statement from creating new users unless a non-empty
password is specified (v5.0.2+)

Setting can be verified by viewing the MySQL config file as per the
Recommended settings, OR by issuing the following command:

mysqladmin var | grep sql_mode

9.
Do not allow new user creation by a user that does not have write access to
the mysql.user table.  Note that this setting may not appear when viewing
status variables via "show variables" or "mysqladmin var", and should
therefore be validated by checking the MySQL config file.


Disable global access to test% databases by issuing the following sample
SQL statements:

SELECT user,host,db
  FROM mysql.db
  WHERE db LIKE 'test%';
DELETE FROM mysql.db
  WHERE db LIKE 'test%';
FLUSH PRIVILEGES;



9.
Identify and remove privileges on non-existent database objects by issuing
the following sample SQL statements:

SELECT d.host, d.db, d.user
  FROM mysql.db AS d LEFT OUTER JOIN
    information_schema.schemata AS s
  ON s.schema_name LIKE d.db
  WHERE s.schema_name IS NULL;
DELETE FROM mysql.db
  WHERE db=<obsolete_db>;

SELECT d.host, d.db, d.user, d.table_name
  FROM mysql.tables_priv AS d
  LEFT OUTER JOIN
    information_schema.schemata AS s
  ON s.schema_name LIKE d.db
  WHERE s.schema_name IS NULL;
DELETE FROM mysql.tables_priv
  WHERE db=<obsolete_db>;

SELECT d.host, d.db, d.user, d.table_name, d.column_name
  FROM mysql.columns_priv AS d
  LEFT OUTER JOIN
    information_schema.schemata AS s
  ON s.schema_name LIKE d.db
  WHERE s.schema_name IS NULL;
DELETE FROM mysql.columns_priv
  WHERE db=<obsolete_db>;


10.
SUPER privileges can be verified by issuing the following SQL statement:

SELECT user, host,
       super_priv AS 'SUPER'
  FROM mysql.user
  WHERE super_priv = 'Y';



11.
Additional global admin privileges can be reviewed by issuing the following
sample SQL statement:

SELECT user, host,
       super_priv AS 'SUPER',
       shutdown_priv AS 'SHUTDOWN',
       process_priv AS 'PROCESS',
       grant_priv AS 'GRANT',
       create_user_priv AS 'CREATE_USER',
       file_priv AS 'FILE',
       reload_priv AS 'RELOAD',
       show_db_priv AS 'SHOW_DATABASES',
       lock_tables_priv AS 'LOCK_TABLES',
       repl_slave_priv AS 'REPL_SLAVE',
       repl_client_priv AS 'REPL_CLIENT'
  FROM mysql.user
  WHERE super_priv = 'Y' OR shutdown_priv = 'Y'
     OR process_priv = 'Y' OR grant_priv = 'Y'
     OR create_user_priv = 'Y' OR file_priv = 'Y'
     OR reload_priv = 'Y' OR show_db_priv = 'Y'
     OR lock_tables_priv = 'Y'
     OR repl_slave_priv = 'Y'
     OR repl_client_priv = 'Y';



12.
SHUTDOWN privileges can be verified by issuing the following SQL statement:

SELECT user, host,
       shutdown_priv AS 'SHUTDOWN'
  FROM mysql.user
  WHERE shutdown_priv = 'Y';
PROCESS privileges can be verified by issuing the following SQL statement:

SELECT user, host,
       shutdown_priv AS 'PROCESS'
  FROM mysql.user
  WHERE process_priv = 'Y';
GRANT privileges can be verified by issuing the following SQL statement:

SELECT user, host,
       shutdown_priv AS 'GRANT'
  FROM mysql.user
  WHERE grant_priv = 'Y';
These privileges can be verified by issuing the following SQL statement to
display all global database privileges, inclusive of the mysql database.

SELECT user, host,
       select_priv AS 'SELECT',
       insert_priv AS 'INSERT',
       update_priv AS 'UPDATE',
       delete_priv AS 'DELETE',
       show_db_priv AS 'SHOWDB',
       create_priv AS 'CREATE',
       drop_priv AS 'DROP',
       index_priv AS 'INDEX', alter_priv AS 'ALTER',
       create_routine_priv AS 'CREATE_ROUTINE',
       alter_routine_priv AS 'ALTER_ROUTINE',
       create_view_priv AS 'CREATE_VIEW',
       show_view_priv AS 'SHOW_VIEW',
       create_tmp_table_priv AS 'CREATE_TEMP',
       execute_priv AS 'EXECUTE',
       references_priv AS 'REFERENCES'
FROM mysql.user
WHERE select_priv = 'Y' OR insert_priv = 'Y'
     OR update_priv = 'Y' OR delete_priv = 'Y'
     OR show_db_priv = 'Y'
     OR create_priv = 'Y' OR drop_priv = 'Y'
     OR index_priv = 'Y' OR alter_priv = 'Y'
     OR create_routine_priv = 'Y'
     OR alter_routine_priv = 'Y'
     OR create_view_priv = 'Y' OR show_view_priv = 'Y'
     OR create_tmp_table_priv = 'Y'
     OR execute_priv = 'Y' OR references_priv = 'Y';
These privileges can be verified by issuing the following SQL statements to
display additional privileges granted against database/table/column objects
of the mysql database.

SELECT user, host, db
     FROM mysql.db
     WHERE db='mysql';

SELECT user, host, db, table_name
     FROM mysql.tables_priv
     WHERE db='mysql';

SELECT user, host, db, table_name, column_name
     FROM mysql.columns_priv
     WHERE db='mysql';




What is the SQL Query or command for  Postgres to check the following
points?

1.No blank passwords
2.No anonymous-user accounts
3.No remotely-accessible root accounts
4.No insecure passwords
5.MySQL config file setting:
  old-passwords = 0  OR
  old_passwords = 0

6.MySQL config file setting:
  secure-auth [= 1]  OR
  secure_auth [= 1]

7.MySQL config file setting:
  log-bin [= /path/to/log/file-prefix]  OR
  log_bin [= /path/to/log/file-prefix]

8.MySQL config file setting:
  sql-mode = no_auto_create_user  OR
  sql_mode = no_auto_create_user

9.MySQL config file setting:
  safe-user-create [=1]  OR
  safe_user_create [=1]

10.No global access to test% databases
11.Remove obsolete privileges



--
View this message in context: http://postgresql.1045698.n5.nabble.com/Need-Help-tp5767001.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

Re: Need Help

From

John R Pierce

Date:

10 August 2013, 00:11:30

On 8/9/2013 10:59 AM, nandan wrote:
> Hello All ;
>
> Please help me in knowing below queries which are in Mysql to Postgresql.
>
>   1.
> SELECT user,host,password
>    FROM mysql.user
>    WHERE password = '';
> SET PASSWORD FOR <user>@<host> =
>    PASSWORD ('newpass');

postgres has no concept of user@host.    host restrictions can be
implemented in the pg_hba.conf file.

ALTER ROLE user WITH PASSWORD 'newpass';



>
> 2.
> SELECT user,host,password
>    FROM mysql.user
>    WHERE user  = '';
> DELETE FROM mysql.user
>    WHERE user  = '';
> FLUSH PRIVILEGES;

no such concept in postgres.  you would drop a user with..

DROP ROLE username;

but there are no empty/null users.


> 3.
> SELECT user,host,password
>    FROM mysql.user
>    WHERE user = 'root' AND host = '%';
> DROP USER root@'%';
> FLUSH PRIVILEGES;

no root user in postgres.

>
> 4.
> SELECT user,host,password
>    FROM mysql.user
>    WHERE length(password) <> 41;
> SET PASSWORD FOR <user>@<host> =
>    PASSWORD ('newpass');

not even sure why you would set all users who's password is not 41 chars
long to a specific password, so I'm not going there.


>
> 5.
> Do not enable insecure password generation option
>
> Setting can be verified by viewing the MySQL config file as per the
> Recommended settings, OR by issuing the following command:
>
> mysqladmin var | grep old_passwords
>
>
> 6.
> Enable secure password authentication option by blocking connections from
> all accounts that have passwords stored in it.
>
> Setting can be verified by viewing the MySQL config file as per the
> Recommended settings, OR by issuing the following command:
>
> mysqladmin var | grep secure_auth

these two don't make any sense in postgres.

>
>
> 7.
> Binary logging should be enabled to log all completed transactions, and
> allow for point-in-time recovery.  This can be enabled via the log-bin
> status variable in the mysql configuration file.  As an example, the
> following entry will place all binary log files in the /var/lib/mysql/logs
> directory, and use 'binlog' as the filename prefix to get binary log files
> names such as binlog.000001:
>
>     log-bin=/var/lib/mysql/logs/binlog
>
> Setting can be verified by viewing the MySQL config file as per the
> Recommended settings, OR by issuing the following command:
>
> mysqladmin var | grep log_bin

read the section on WAL Archiving in the postgres user manual, if done
correctly, this allows Point In Time Recovery.

>
>
> 8.
> Prevent the grant statement from creating new users unless a non-empty
> password is specified (v5.0.2+)
>
> Setting can be verified by viewing the MySQL config file as per the
> Recommended settings, OR by issuing the following command:
>
> mysqladmin var | grep sql_mode

the GRANT statement doesn't create roles, CREATE ROLE (or CREATE USER) does.


>
> 9.
> Do not allow new user creation by a user that does not have write access to
> the mysql.user table.  Note that this setting may not appear when viewing
> status variables via "show variables" or "mysqladmin var", and should
> therefore be validated by checking the MySQL config file.

only users/roles with the specific CREATEUSER or SUPERUSER attributes
can create/modify users.



> 9.
> Identify and remove privileges on non-existent database objects by issuing
> the following sample SQL statements:
> ...

nonexisting objects don't have any privileges to identify or remove.

>
> 10.
> SUPER privileges can be verified by issuing the following SQL statement:
>
> SELECT user, host,
>         super_priv AS 'SUPER'
>    FROM mysql.user
>    WHERE super_priv = 'Y';
>
easy way:
in psql comand line shell, use the \du  metacommand to display all
users, note who has superuser.

harder way:
there's probably a query of the pg_catalog metadata but I'm not looking
it up.


> 11.
> Additional global admin privileges can be reviewed by issuing the following
> sample SQL statement:
>
> SELECT user, host,
>         super_priv AS 'SUPER',
>         shutdown_priv AS 'SHUTDOWN',
>         process_priv AS 'PROCESS',
>         grant_priv AS 'GRANT',
>         create_user_priv AS 'CREATE_USER',
>         file_priv AS 'FILE',
>         reload_priv AS 'RELOAD',
>         show_db_priv AS 'SHOW_DATABASES',
>         lock_tables_priv AS 'LOCK_TABLES',
>         repl_slave_priv AS 'REPL_SLAVE',
>         repl_client_priv AS 'REPL_CLIENT'
>    FROM mysql.user
>    WHERE super_priv = 'Y' OR shutdown_priv = 'Y'
>       OR process_priv = 'Y' OR grant_priv = 'Y'
>       OR create_user_priv = 'Y' OR file_priv = 'Y'
>       OR reload_priv = 'Y' OR show_db_priv = 'Y'
>       OR lock_tables_priv = 'Y'
>       OR repl_slave_priv = 'Y'
>       OR repl_client_priv = 'Y';

see answer to previous.     postgres is shutdown from the system shell,
not from within postgres.   most of those other global privileges don't
exist on postgres, just SUPERUSER, CREATEUSER, CREATEDB attributes.


>
>
> 12.
> SHUTDOWN privileges can be verified by issuing the following SQL statement:

no such privilege.

>
>
>
> What is the SQL Query or command for  Postgres to check the following
> points?
>
> 1.No blank passwords
> 2.No anonymous-user accounts
> 3.No remotely-accessible root accounts
> 4.No insecure passwords
> 5.MySQL config file setting:
>    old-passwords = 0  OR
>    old_passwords = 0
>
> 6.MySQL config file setting:
>    secure-auth [= 1]  OR
>    secure_auth [= 1]
>
> 7.MySQL config file setting:
>    log-bin [= /path/to/log/file-prefix]  OR
>    log_bin [= /path/to/log/file-prefix]
>
> 8.MySQL config file setting:
>    sql-mode = no_auto_create_user  OR
>    sql_mode = no_auto_create_user
>
> 9.MySQL config file setting:
>    safe-user-create [=1]  OR
>    safe_user_create [=1]
>
> 10.No global access to test% databases
> 11.Remove obsolete privileges

thats mostly mysql specific stuff not applicable to postgres, and highly
redundant with what you asked above.


--
john r pierce                                      37N 122W
somewhere on the middle of the left coast

Re: Need Help

From

Alban Hertroys

Date:

10 August 2013, 12:11:26

On Aug 9, 2013, at 19:59, nandan <nandand1985@gmail.com> wrote:

> Hello All ;
>
> Please help me in knowing below queries which are in Mysql to Postgresql.

Postgres and MySQL are very different in these respects. Answering these questions is rather pointless, as it will not
giveyou an understanding of how the role and grant system works in PostgreSQL and you'll need that kind of
understandingif you're as concerned about security as you appear to be. 

Have a look at chapters 19 & 20 in the fine manual: http://www.postgresql.org/docs/9.2/static/index.html

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.