Re: Need Help

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(at)host(dot) 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