Re: Need Help - Mailing list pgsql-general

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